Unfortunately, the 3 main families of small computer operating systems have 3 different definitions of what a text file is...
DOS/Windows (PC): lines are terminated with CR+LF Unix: lines are terminated with LF Macintosh: lines are terminated with CR This causes no end of trouble when moving text files between these kinds of systems. Java takes the approach that any of (CR, LF, or CR+LF) is a valid line terminator, but many or perhaps most non-Java software insists on having the correct line terminator for the platform where it's running. g -----Original Message----- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Kavita Raghunathan Sent: Tuesday, September 08, 2009 2:14 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] Importing data into SQLite FYI: Mac excel does not separate rows with \r, but inserts a ^M instead. (I dont have a windows machine with me, I wonder if this is Mac specific) Sqlite does not like this because a large file with many rows appears as 1 huge infinite line to sqlite. Kavita ----- Original Message ----- From: "Kavita Raghunathan" <kavita.raghunat...@skyfiber.com> To: "General Discussion of SQLite Database" <sqlite-users@sqlite.org> Sent: Tuesday, September 8, 2009 11:47:38 AM GMT -08:00 US/Canada Pacific Subject: Re: [sqlite] Importing data into SQLite Yes, this works. Must have been my original csv file. I was using mac based excel and I'll now try using the windows based excel. Thanks to all for the awesome support. Kavita ----SQLite version 3.3.6 Enter ".help" for instructions sqlite> create table test(name text, id integer); sqlite> .separator "," sqlite> .import data.csv test sqlite> sqlite> select * from test ...> ; "a",1 "b",2 "c",3 sqlite> Original Message ----- From: "Robert Citek" <robert.ci...@gmail.com> To: "General Discussion of SQLite Database" <sqlite-users@sqlite.org> Sent: Monday, September 7, 2009 9:08:41 AM GMT -08:00 US/Canada Pacific Subject: Re: [sqlite] Importing data into SQLite On Sun, Sep 6, 2009 at 9:32 PM, Kavita Raghunathan<kavita.raghunat...@skyfiber.com> wrote: > Timothy and all, > When I try to import a .csv, I get a segmentation fault: > 1) First I set .seperator to , > 2) Then I type .import <csv filename> <table name> > 3) I see "Segmentation fault" > > Any ideas ? Here's an example of how it worked for me. $ cat data.csv "a",1 "b",2 "c",3 $ sqlite3 sample.db .schema CREATE TABLE data (foo text, bar int); $ sqlite3 -separator , sample.db '.imp "data.csv" "data" ' $ sqlite3 -header -column sample.db 'select * from data ;' foo bar ---------- ---------- "a" 1 "b" 2 "c" 3 More details here, including caveats: http://www.sqlite.org/cvstrac/wiki?p=ImportingFiles Personally, I prefer to used tab-delimited files and then import by specifying the separator as a tab: $ sqlite3 -separator $'\t' sample.db '.imp "data.tsv" "data" ' This takes advantage of the bash shell's use of $'\t' to encode a tab. Regards, - Robert _______________________________________________ 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 _______________________________________________ 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
