Thanks, will have a look at that as well. In my particular situation though I wouldn't want the auto data-type detection as that will slow things down and I will always know what the table fields affinities should be. Is there a way to use the other features, but not this one, say if the table is already there?
RBS On Fri, May 31, 2013 at 11:21 PM, Roger Binns <rog...@rogerbinns.com> wrote: > -----BEGIN PGP SIGNED MESSAGE----- > Hash: SHA1 > > On 31/05/13 05:34, Bart Smissaert wrote: > > Importing a. csv file via the. import command of sqlite3.exe. As the > > first line holds the field names I want to skip that. There is no > > problem achieving this in code, but could I do this purely via sqlite3 > > commands? > > You can also use the enhanced shell in APSW (Python wrapper). You don't > need to use or write any Python and can just execute it for the import. > > Of particular use is that it has a .autoimport command which automatically > sets up the correct column names. It also deduces the type in each column > so for example phone numbers won't be mangled to integers, and dates will > be "fixed" automatically determining if they are US or correct format. It > also automatically works out separators (eg csv, tabs, pipes). > > http://apidoc.apsw.googlecode.com/hg/shell.html > > sqlite> .help 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.12 (GNU/Linux) > > iEYEARECAAYFAlGpIoEACgkQmOOfHg372QRSqwCg3WTRwifRKC+tK55BmTbomVyo > PHsAnR8v79nKkpVZ7WYWydeTYxsHbZoE > =c95H > -----END PGP SIGNATURE----- > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
