Re: [sqlite] how can I import CSV file into SQLite quickly
sorry ,maked an mistake.
actually,the data size is 805 vars, 118519 obs.
2006/1/5, ronggui wong <[EMAIL PROTECTED]>:
> Thanks to all give response to help.
> This is my solution using the luanguage I familiar.(http://www.r-project.org).
>
> I use the code to read a 11819x807 csv file and it takes 10 minus.I think is
> not too slow .(My PC:1.7G,512M RAM)
>
> #code begins
> rm(list=ls())
> f<-file("D:\\wvsevs_sb_v4.csv","r")#134M
> i <- 0
> done <- FALSE
> library(RSQLite)
> con<-dbConnect("SQLite","c:\\sqlite\\database.db3")
> tim1<-Sys.time()
>
> while(!done){
> i<-i+1
> tt<-readLines(f,2500)
> if (length(tt)<2500) done <- TRUE
> tt<-textConnection(tt)
> if (i==1) {
> assign("dat",read.table(tt,head=T,sep=",",quote=""));
> # to make the variable names elegent
> nam<-names(dat);
> nam<-gsub("^X.","",nam);
> nam<-tolower(gsub(".$","",nam))
> names(dat)<-nam
> #
> }
> else assign("dat",read.table(tt,head=F,sep=",",quote=""))
> close(tt)
> ifelse(dbExistsTable(con, "wvs"),dbWriteTable(con,"wvs",dat,append=T),
> dbWriteTable(con,"wvs",dat) )
> }
> close(f)
> #cal the time require
> Sys.time()-tim1
>
> #code end.
>
Re: [sqlite] how can I import CSV file into SQLite quickly
Thanks to all give response to help.
This is my solution using the luanguage I familiar.(http://www.r-project.org).
I use the code to read a 11819x807 csv file and it takes 10 minus.I think is
not too slow .(My PC:1.7G,512M RAM)
#code begins
rm(list=ls())
f<-file("D:\\wvsevs_sb_v4.csv","r")#134M
i <- 0
done <- FALSE
library(RSQLite)
con<-dbConnect("SQLite","c:\\sqlite\\database.db3")
tim1<-Sys.time()
while(!done){
i<-i+1
tt<-readLines(f,2500)
if (length(tt)<2500) done <- TRUE
tt<-textConnection(tt)
if (i==1) {
assign("dat",read.table(tt,head=T,sep=",",quote=""));
# to make the variable names elegent
nam<-names(dat);
nam<-gsub("^X.","",nam);
nam<-tolower(gsub(".$","",nam))
names(dat)<-nam
#
}
else assign("dat",read.table(tt,head=F,sep=",",quote=""))
close(tt)
ifelse(dbExistsTable(con, "wvs"),dbWriteTable(con,"wvs",dat,append=T),
dbWriteTable(con,"wvs",dat) )
}
close(f)
#cal the time require
Sys.time()-tim1
#code end.
Re: [sqlite] how can I import CSV file into SQLite quickly
Arjen Markus wrote: Hm, there is a CSV reading module in Tcllib, so one could contemplate using Tcl instead of Perl for this. That ought to take care of the quotes and other nastiness... Perl's Text::CSV module available from CPAN also handles these issues.
Re: [sqlite] how can I import CSV file into SQLite quickly
Jay Sprenkle wrote: On 12/6/05, ronggui wong <[EMAIL PROTECTED]> wrote: I have a very large CSV file with 1 rows and 100 columns.and the file looks like the following: "a","b","c","d", "1","2","1","3" , "3","2","2","1", .. If I use .import,It seems I have to set the variable names manually . Is there any way to import the whole data file into SQLite quickly? Use vi, emacs, ed, or sed to change the data to insert statements. You'll be done in ten minutes and no programming is needed. Bravo! The practical person's voice of experience. Such a simple solution. Some time back we got sick of trying to use tedious Windows editors and substituted Vim, with an instant productivity increase and drop in the frustration level. JS
Re: [sqlite] how can I import CSV file into SQLite quickly
On 12/6/05, ronggui wong <[EMAIL PROTECTED]> wrote: > I have a very large CSV file with 1 rows and 100 columns.and the > file looks like the following: > "a","b","c","d", > "1","2","1","3" , > "3","2","2","1", > .. > > If I use .import,It seems I have to set the variable names manually . > Is there any way to import the whole data file into SQLite quickly? Use vi, emacs, ed, or sed to change the data to insert statements. You'll be done in ten minutes and no programming is needed.
Re: [sqlite] how can I import CSV file into SQLite quickly
Aaron Peterson wrote: > > On 12/7/05, Teg <[EMAIL PROTECTED]> wrote: > > Hello All, > > > > Wouldn't it make sense to write a program that reads it in, one line > > at a time, splits and inserts the data into the proper tables? Even > > creating the table on the fly? That's what I'd do, a little command > > line utility. > > One could probably look at the mysqlimport source code for > inspiration. It would be nice to have a little command line utility > for this as part of the main sqlite package... This *can* be done > with a sed or perl script, but it becomes increasingly difficult when > the values also include commas, in which case programs often > additionally enclose the values in quotes, etc. mysqlimport has nice > switches (--optionally-enclosed-by) to take care of cases like these > so that everyone doesn't have to be a perl or sed genius to import > some text file data into a table. > Hm, there is a CSV reading module in Tcllib, so one could contemplate using Tcl instead of Perl for this. That ought to take care of the quotes and other nastiness... Regards, Arjen
Re: [sqlite] how can I import CSV file into SQLite quickly
PBDBMS on www.hellobasic.com All through ADO.. - Original Message - From: "Cariotoglou Mike" <[EMAIL PROTECTED]> To: <sqlite-users@sqlite.org> Sent: Wednesday, December 07, 2005 9:23 AM Subject: RE: [sqlite] how can I import CSV file into SQLite quickly sqlite3Explorer does that From: John Stanton [mailto:[EMAIL PROTECTED] Sent: Wed 07-Dec-05 8:00 AM To: sqlite-users@sqlite.org Subject: Re: [sqlite] how can I import CSV file into SQLite quickly Someone somwhere must have a simple Perl script which does what you want. JS Robert L Cochran wrote: I create an SQL file that has contents like this: [EMAIL PROTECTED] elections]$ cat insert_precinct.sql BEGIN TRANSACTION; INSERT INTO "precinct" VALUES(1, 'Community Center 15 Crescent Road', 3, 'Greenbelt', 'Maryland', 0); INSERT INTO "precinct" VALUES(2, 'Police Station 550 Crescent Road', 6, 'Greenbelt', 'Maryland', 0); INSERT INTO "precinct" VALUES(3, 'Springhill Lake Recreation Center 6111 Cherrywood Lane', 8, 'Greenbelt', 'Maryland', 0); COMMIT; Then I fire up sqlite3 on the command line, and issue .read insert_precinct.sql I realize this will probably make you unhappy because it means editing your CSV file so that each line is transformed into an sql statement. This can be done most easily with sed (if you are a Linux or Unix person), but you need to know sed commands and you need to be willing to patiently experiment until the sed script applies exactly the right edits. Bob Cochran ronggui wong wrote: I have a very large CSV file with 1 rows and 100 columns.and the file looks like the following: "a","b","c","d", "1","2","1","3" , "3","2","2","1", .. If I use .import,It seems I have to set the variable names manually . Is there any way to import the whole data file into SQLite quickly? Thank you! ronggui
RE: [sqlite] how can I import CSV file into SQLite quickly
sqlite3Explorer does that From: John Stanton [mailto:[EMAIL PROTECTED] Sent: Wed 07-Dec-05 8:00 AM To: sqlite-users@sqlite.org Subject: Re: [sqlite] how can I import CSV file into SQLite quickly Someone somwhere must have a simple Perl script which does what you want. JS Robert L Cochran wrote: > I create an SQL file that has contents like this: > > [EMAIL PROTECTED] elections]$ cat insert_precinct.sql > BEGIN TRANSACTION; > INSERT INTO "precinct" VALUES(1, 'Community Center 15 Crescent Road', 3, > 'Greenbelt', 'Maryland', 0); > INSERT INTO "precinct" VALUES(2, 'Police Station 550 Crescent Road', 6, > 'Greenbelt', 'Maryland', 0); > INSERT INTO "precinct" VALUES(3, 'Springhill Lake Recreation Center 6111 > Cherrywood Lane', 8, 'Greenbelt', 'Maryland', 0); > COMMIT; > > Then I fire up sqlite3 on the command line, and issue > > .read insert_precinct.sql > > I realize this will probably make you unhappy because it means editing > your CSV file so that each line is transformed into an sql statement. > This can be done most easily with sed (if you are a Linux or Unix > person), but you need to know sed commands and you need to be willing to > patiently experiment until the sed script applies exactly the right edits. > Bob Cochran > > > ronggui wong wrote: > >> I have a very large CSV file with 1 rows and 100 columns.and the >> file looks like the following: >> "a","b","c","d", >> "1","2","1","3" , >> "3","2","2","1", >> .. >> >> If I use .import,It seems I have to set the variable names manually . >> Is there any way to import the whole data file into SQLite quickly? >> Thank you! >> >> ronggui >> >> >> >> >
Re: [sqlite] how can I import CSV file into SQLite quickly
Someone somwhere must have a simple Perl script which does what you want. JS Robert L Cochran wrote: I create an SQL file that has contents like this: [EMAIL PROTECTED] elections]$ cat insert_precinct.sql BEGIN TRANSACTION; INSERT INTO "precinct" VALUES(1, 'Community Center 15 Crescent Road', 3, 'Greenbelt', 'Maryland', 0); INSERT INTO "precinct" VALUES(2, 'Police Station 550 Crescent Road', 6, 'Greenbelt', 'Maryland', 0); INSERT INTO "precinct" VALUES(3, 'Springhill Lake Recreation Center 6111 Cherrywood Lane', 8, 'Greenbelt', 'Maryland', 0); COMMIT; Then I fire up sqlite3 on the command line, and issue .read insert_precinct.sql I realize this will probably make you unhappy because it means editing your CSV file so that each line is transformed into an sql statement. This can be done most easily with sed (if you are a Linux or Unix person), but you need to know sed commands and you need to be willing to patiently experiment until the sed script applies exactly the right edits. Bob Cochran ronggui wong wrote: I have a very large CSV file with 1 rows and 100 columns.and the file looks like the following: "a","b","c","d", "1","2","1","3" , "3","2","2","1", .. If I use .import,It seems I have to set the variable names manually . Is there any way to import the whole data file into SQLite quickly? Thank you! ronggui
Re: [sqlite] how can I import CSV file into SQLite quickly
I create an SQL file that has contents like this: [EMAIL PROTECTED] elections]$ cat insert_precinct.sql BEGIN TRANSACTION; INSERT INTO "precinct" VALUES(1, 'Community Center 15 Crescent Road', 3, 'Greenbelt', 'Maryland', 0); INSERT INTO "precinct" VALUES(2, 'Police Station 550 Crescent Road', 6, 'Greenbelt', 'Maryland', 0); INSERT INTO "precinct" VALUES(3, 'Springhill Lake Recreation Center 6111 Cherrywood Lane', 8, 'Greenbelt', 'Maryland', 0); COMMIT; Then I fire up sqlite3 on the command line, and issue .read insert_precinct.sql I realize this will probably make you unhappy because it means editing your CSV file so that each line is transformed into an sql statement. This can be done most easily with sed (if you are a Linux or Unix person), but you need to know sed commands and you need to be willing to patiently experiment until the sed script applies exactly the right edits. Bob Cochran ronggui wong wrote: I have a very large CSV file with 1 rows and 100 columns.and the file looks like the following: "a","b","c","d", "1","2","1","3" , "3","2","2","1", .. If I use .import,It seems I have to set the variable names manually . Is there any way to import the whole data file into SQLite quickly? Thank you! ronggui
