how can I import CSV file into SQLite quickly

classic Classic list List threaded Threaded
14 messages Options
Reply | Threaded
Open this post in threaded view
|

how can I import CSV file into SQLite quickly

ronggui wong
I have a very large CSV file with 10000 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
Reply | Threaded
Open this post in threaded view
|

Re: how can I import CSV file into SQLite quickly

Robert L Cochran
I create an SQL file that has contents like this:

[rlc@bobcp4 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 10000 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
>
>
>  
>

Reply | Threaded
Open this post in threaded view
|

Re[2]: how can I import CSV file into SQLite quickly

Teg-3
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.

It's basically the "convert to SQL" technique you suggest where the
SQL conversion's done programatically in the utility.

Tuesday, December 6, 2005, 11:50:50 PM, you wrote:

RLC> I create an SQL file that has contents like this:

RLC> [rlc@bobcp4 elections]$ cat insert_precinct.sql
RLC> BEGIN TRANSACTION;
RLC> INSERT INTO "precinct" VALUES(1, 'Community Center 15 Crescent Road', 3,
RLC> 'Greenbelt', 'Maryland', 0);
RLC> INSERT INTO "precinct" VALUES(2, 'Police Station 550 Crescent Road', 6,
RLC> 'Greenbelt', 'Maryland', 0);
RLC> INSERT INTO "precinct" VALUES(3, 'Springhill Lake Recreation Center 6111
RLC> Cherrywood Lane', 8, 'Greenbelt', 'Maryland', 0);
RLC> COMMIT;

RLC> Then I fire up sqlite3 on the command line, and issue

RLC> .read insert_precinct.sql

RLC> I realize this will probably make you unhappy because it means editing
RLC> your CSV file so that each line is transformed into an sql statement.
RLC> This can be done most easily with sed (if you are a Linux or Unix
RLC> person), but you need to know sed commands and you need to be willing to
RLC> patiently experiment until the sed script applies exactly the right edits.

RLC> Bob Cochran


RLC> ronggui wong wrote:

>>I have a very large CSV file with 10000 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
>>
>>
>>  
>>




--
Best regards,
 Teg                            mailto:[hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: how can I import CSV file into SQLite quickly

John Stanton-3
In reply to this post by Robert L Cochran
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:
>
> [rlc@bobcp4 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 10000 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
>>
>>
>>  
>>
>

Reply | Threaded
Open this post in threaded view
|

RE: how can I import CSV file into SQLite quickly

Cariotoglou Mike
In reply to this post by ronggui wong
sqlite3Explorer does that

________________________________

From: John Stanton [mailto:[hidden email]]
Sent: Wed 07-Dec-05 8:00 AM
To: [hidden email]
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:
>
> [rlc@bobcp4 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 10000 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
>>
>>
>>
>>
>



Reply | Threaded
Open this post in threaded view
|

Re: how can I import CSV file into SQLite quickly

Edwin Knoppert
PBDBMS on www.hellobasic.com

All through ADO..


----- Original Message -----
From: "Cariotoglou Mike" <[hidden email]>
To: <[hidden email]>
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:[hidden email]]
Sent: Wed 07-Dec-05 8:00 AM
To: [hidden email]
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:
>
> [rlc@bobcp4 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 10000 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
>>
>>
>>
>>
>





Reply | Threaded
Open this post in threaded view
|

Re: Re[2]: how can I import CSV file into SQLite quickly

Aaron Peterson-2
In reply to this post by Teg-3
On 12/7/05, Teg <[hidden email]> 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.

Aaron

Aaron
Reply | Threaded
Open this post in threaded view
|

Re: how can I import CSV file into SQLite quickly

Arjen Markus
Aaron Peterson wrote:

>
> On 12/7/05, Teg <[hidden email]> 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

Reply | Threaded
Open this post in threaded view
|

Re: how can I import CSV file into SQLite quickly

Arjen Markus
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...
>
>

Come to think it, my Fortran library to interface with SQLite has
an example of reading a CSV file and putting it in a SQLite database.
Fortran 90/95 has the ability to properly read CSV files "out of the
box", via its READ(..,*) statement.

The library can be retrieved at: http://www.sf.net/flibs
(Use anonymous CVS, no tarfile yet).

Regards,

Arjen

Reply | Threaded
Open this post in threaded view
|

Re: how can I import CSV file into SQLite quickly

Jay Sprenkle
In reply to this post by ronggui wong
On 12/6/05, ronggui wong <[hidden email]> wrote:
> I have a very large CSV file with 10000 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.
Reply | Threaded
Open this post in threaded view
|

Re: how can I import CSV file into SQLite quickly

John Stanton-3
Jay Sprenkle wrote:

> On 12/6/05, ronggui wong <[hidden email]> wrote:
>
>>I have a very large CSV file with 10000 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
Reply | Threaded
Open this post in threaded view
|

Re: how can I import CSV file into SQLite quickly

Eric Bohlman
In reply to this post by Arjen Markus
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.
Reply | Threaded
Open this post in threaded view
|

Re: how can I import CSV file into SQLite quickly

ronggui wong
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.
Reply | Threaded
Open this post in threaded view
|

Re: how can I import CSV file into SQLite quickly

ronggui wong
sorry ,maked an mistake.
actually,the data size is 805 vars, 118519 obs.

2006/1/5, ronggui wong <[hidden email]>:

> 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.
>