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.
Csv to sqlite online windows#
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.
0 kommentar(er)
