"""Returns the basename of the file without the extension"""įilename = os.path.splitext(os.path.basename(givenPath)) Stmt = "INSERT INTO ads VALUES(%s) " % ','.join('?' * len(cols))ĭATABASE_FOLDER = os.path.join(UP_FOLDER, "Databases") Reader = csv.reader(escapingGenerator(fin)) Stmt = "CREATE TABLE ads (%s)" % ",".join(cols) # Keep the order of the columns name just as in the CSV With open(csvFile,mode='r', encoding="ISO-8859-1") as fin: Yield line.encode("ascii", "xmlcharrefreplace").decode("ascii")ĭef csvToDb(csvFile, outputToFile = False): Raise Exception("Failed to find all the columns data types - Maybe some are empty?") # TODO: Currently there's no support for DATE in sqllite My 2 cents (more generic): import csv, sqlite3ĭr = csv.DictReader(fin) # comma is default delimiterįeildslLeft = With open(str(csv)+"_nohead", "w") as target: Again, I didn't want to read the whole files into memory at any point: with open(csv, "r") as source: Not really the main point of the question, but here's what I used. Unfortunately, since subprocess passes all follow-ons to -cmd as quoted strings, you need to double up your backslashes if you have a windows directory path. In the shell, each command has to be in quotes, but here, they just need to be their own element of the sequence -cmd flag after the database allows you to pass multiple follow on commands to the sqlite program.The argument to n() is a sequence of strings which are interpreted as a command followed by all of it's arguments. The -skip 1 argument will give an error prior to 3.32įrom the command line, the command you're looking for is sqlite3 my.db -cmd ".mode csv" ".import file.csv table". If you have an older version of sqlite3, you may need to first create the table, then strip off the first row of the csv before importing. import command has improved so that it can treat the first row as header names or even skip the first x rows (requires version >=3.32, as noted in this answer. In the example below, I assume the table already exists, but the csv file has headers in the first row. A lot of the top answers to this question involve native python loops, but if your files are large (mine are 10^6 to 10^7 records), you want to avoid reading everything into pandas or using a native python list comprehension/loop (though I did not time them for comparison).įor large files, I believe the best option is to use n() to execute sqlite's import command. import is the way to go, but that's a command from the SQLite3 command line program. Finally, feedback is given as to the number of records added to the database. This is used to insert the data into the database. The records are then processed one by one. An SQL ‘ Insert‘ statement is constructed, incorporating the values from the record in the records object. If successfully found, the file is assigned to a reader object and the rows of data, or records, are assigned to a records object. A further check is made to see if the CSV file exists. If it does, a connection to the database is established. "firstname","lastname","title","dob"īelow is an example of how Java can be used to import data from a CSV file called ‘personimport.csv’, into a table called ‘person’, which was used in the examples for selecting, inserting, updating and deleting data. For this to work the Apache Commons CSV dependency needs to be used, along with the SQLite JDBC Driver used previously.įirstly, a check is made to see if the database file actually exists. In the example contents of a CSV file below, each row contains information relating to a person, including their first name, last name, title and date of birth in the format YYYY-MM-DD (four digit year, two digit month and two digit day). Each row in the file thereafter is a record of related data. The first row in a CSV file often contains headers for each column of data. A CSV file, or Comma Separated Values file, is a delimited file that uses commas to separate values. In order to import data into an SQLite database, it must be in a suitable format.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |