Re: [sqlite] importing data from file with 3 colums to table with 4 columns
Thanks Monte for your response. I am still pretty new to SQlite, but it looks very similar to what Simon suggested. I think it would work just as well. From my perspective the ideal way to do this would be to specify the columns names the following data in the file is to load into. Then have an option in the .import function to say that the 1st line in the file defines the column names. -Chris - Original Message - From: "Monte Milanuk" To: sqlite-users@sqlite.org Sent: Friday, July 9, 2010 10:11:41 AM Subject: Re: [sqlite] importing data from file with 3 colums to table with 4 columns Just because I was bored ;) I decided to take a stab at this one as well (following Simon's excellent guidance). I had thought because of what it says in the FAQ here: http://sqlite.org/faq.html#q1 that it should be possible to import the values directly into the final table and have sqlite auto-populate the primary key field as it went. If it is possible, I haven't found any good examples of *how*. As it is... I came up with the following import-csv.sql file for importing your CSV data: CREATE TABLE t1(ID integer primary key autoincrement, Name varchar(40), Category varchar(40), Recommendation varchar(40)); CREATE TABLE t2(a, b, c); .separator "|" .import data.csv t2 INSERT INTO t1(Name, Category, Recommendation) SELECT * FROM t2; DROP TABLE t2; .headers on .mode column .width 5 20 9 15 SELECT * FROM t1; After that... running the following command should net the resulting output: E:\sqlite>sqlite3 temp.db ".read import-csv.sql" ID Name Category Recommendation - - --- 1 Barracuda seafood No 2 Catfish seafood No 3 Caviar seafood No 4 Conch seafood No 5 Herring(pickled) seafood No 6 Lox(smoked salmon) seafood No 7 Octopus seafood No E:\sqlite> Not sure if that was exactly what the OP was after, but it kept me entertained ;) ___ 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
Re: [sqlite] importing data from file with 3 colums to table with 4 columns
On 9 July 2010 06:18, wrote: > > > Thank you very much Simon. > > That worked very slick. > > > > Say, is there a way to put all of the SQLite3 commands I used into a script > and have SQLite3 execute them in the script sequentially? > Yes, several ways: use .read command (sqlite3 tst.db ".read script.sql") use redirection to script file when invoking sqlite (sqlite3 tst.db < script.sql) scripts can use .read to invoke other scripts > > > -Chris > Regards, Simon ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] importing data from file with 3 colums to table with 4 columns
Thank you very much Simon. That worked very slick. Say, is there a way to put all of the SQLite3 commands I used into a script and have SQLite3 execute them in the script sequentially? -Chris - Original Message - From: "Simon Davies" To: "General Discussion of SQLite Database" Sent: Thursday, July 8, 2010 4:48:20 PM Subject: Re: [sqlite] importing data from file with 3 colums to table with 4columns On 9 July 2010 00:07, wrote: > > > Hello, > > I have a db tbl with the following schema: > > > > _ID integer primary key autoincrement > > name varchar(40) > > category varchar(40) > > recommendation varchar(40) > > > > I have a data file I want to import which contains 3 columns worth of data. > > It looks like this: > > > > Barracuda|seafood|No > Catfish|seafood|No > Caviar|seafood|No > Conch|seafood|No > Herring(pickled)|seafood|No > Lox(smoked salmon)|seafood|No > Octopus|seafood|No > > > > When I try and import it I get the following error: > > "line 1: expected 4 columns of data but found 3" > > > > Since I obviously don't want to explicitly load data into the _ID column, how > do I tell it to put the data from the import file into the 3 remaining > columns? > Create a table (tmp) with three cols to receive the data from your file, then use INSERT INTO tbl( name, category, recommendation ) SELECT * from tmp; > > > Thank you in advance. > > -Chris > Regards, Simon ___ 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
Re: [sqlite] importing data from file with 3 colums to table with 4 columns
On 9 July 2010 00:07, wrote: > > > Hello, > > I have a db tbl with the following schema: > > > > _ID integer primary key autoincrement > > name varchar(40) > > category varchar(40) > > recommendation varchar(40) > > > > I have a data file I want to import which contains 3 columns worth of data. > > It looks like this: > > > > Barracuda|seafood|No > Catfish|seafood|No > Caviar|seafood|No > Conch|seafood|No > Herring(pickled)|seafood|No > Lox(smoked salmon)|seafood|No > Octopus|seafood|No > > > > When I try and import it I get the following error: > > "line 1: expected 4 columns of data but found 3" > > > > Since I obviously don't want to explicitly load data into the _ID column, how > do I tell it to put the data from the import file into the 3 remaining > columns? > Create a table (tmp) with three cols to receive the data from your file, then use INSERT INTO tbl( name, category, recommendation ) SELECT * from tmp; > > > Thank you in advance. > > -Chris > Regards, Simon ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] importing data from file with 3 colums to table with 4 columns
Hello, I have a db tbl with the following schema: _ID integer primary key autoincrement name varchar(40) category varchar(40) recommendation varchar(40) I have a data file I want to import which contains 3 columns worth of data. It looks like this: Barracuda|seafood|No Catfish|seafood|No Caviar|seafood|No Conch|seafood|No Herring(pickled)|seafood|No Lox(smoked salmon)|seafood|No Octopus|seafood|No When I try and import it I get the following error: "line 1: expected 4 columns of data but found 3" Since I obviously don't want to explicitly load data into the _ID column, how do I tell it to put the data from the import file into the 3 remaining columns? Thank you in advance. -Chris ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
