-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 On 01/09/12 12:19, joe.fis...@tanguaylab.com wrote: > Is there some way to load a ".csv" file into a SQLite database table > when the first row (record) of the file contains headers?
You may want to consider using the APSW shell. It has a .autoimport command that automatically figures out separators, what fields are text/date/numeric etc. You can then use the resulting database with whatever you use for SQLite. http://apidoc.apsw.googlecode.com/hg/shell.html Example usage (a few .dump lines trimmed) against your test csv: $ python -c "import apsw;apsw.main()" test.db SQLite version 3.7.13 (APSW 3.7.13-r1) Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> .autoimport test.csv Detected Format excel Columns 4 Rows 4 Auto-import into table "test" complete sqlite> .dump test CREATE TABLE test(id, barcode_id, light, bpm); INSERT INTO test VALUES(1,'000002455',1,180.2); INSERT INTO test VALUES(2,'000002455',1,168.3); INSERT INTO test VALUES(3,'000002455',1,189.4); You'll note that it figured out barcode field was a string despite consisting of only digits. This is the .help for autoimport: .autoimport FILENAME ?TABLE? Imports filename creating a table and automatically working out separators and data types (alternative to .import command) The import command requires that you precisely pre-setup the table and schema, and set the data separators (eg commas or tabs). In many cases this information can be automatically deduced from the file contents which is what this command does. There must be at least two columns and two rows. If the table is not specified then the basename of the file will be used. Additionally the type of the contents of each column is also deduced - for example if it is a number or date. Empty values are turned into nulls. Dates are normalized into YYYY-MM-DD format and DateTime are normalized into ISO8601 format to allow easy sorting and searching. 4 digit years must be used to detect dates. US (swapped day and month) versus rest of the world is also detected providing there is at least one value that resolves the ambiguity. Care is taken to ensure that columns looking like numbers are only treated as numbers if they do not have unnecessary leading zeroes or plus signs. This is to avoid treating phone numbers and similar number like strings as integers. This command can take quite some time on large files as they are effectively imported twice. The first time is to determine the format and the types for each column while the second pass actually imports the data. Roger -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.11 (GNU/Linux) iEYEARECAAYFAlBCclIACgkQmOOfHg372QQV2QCg3s6VpgCbvGG9xQqvQ1fSvBpj OjkAn1afh25GTDpjPUYuHoJASmHdQ/3Z =2S51 -----END PGP SIGNATURE----- _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
