Import CSV in sqlite3?

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

Import CSV in sqlite3?

Joe Noon
Id like to import a csv, comma delimited file, into a table (sqlite3
db) in my Ruby application.  I can do it either of three ways:

1.) With a SQL statement like the COPY command.  Ive read this was
taken out, but then found a place that said it has been reimplemented?
 Ive had no luck with anything Ive tried this route.

2.) I can execute a system command through code:
 $> sqlite3 < myfile
Obviously that isnt the right syntax, but Im looking for the correct
syntax to import a csv into a specifc table from the command line like
that.

3.) I dont see how it would be possible to use the .import
functionality through code? If its possible please enlighten me.

Any helpful suggestions would be very much appreciated!

Joe Noon
Reply | Threaded
Open this post in threaded view
|

Re: Import CSV in sqlite3?

Jay Sprenkle
Can you write C?
Steal the code from the sqlite command line tool source. :)

On 8/5/05, Joe Noon <[hidden email]> wrote:
> Id like to import a csv, comma delimited file, into a table (sqlite3
> db) in my Ruby application.  I can do it either of three ways:
>
Reply | Threaded
Open this post in threaded view
|

Re: Import CSV in sqlite3?

Dennis Cote
In reply to this post by Joe Noon
Joe Noon wrote:

>Id like to import a csv, comma delimited file, into a table (sqlite3
>db) in my Ruby application.  I can do it either of three ways:
>
>1.) With a SQL statement like the COPY command.  Ive read this was
>taken out, but then found a place that said it has been reimplemented?
> Ive had no luck with anything Ive tried this route.
>
>2.) I can execute a system command through code:
> $> sqlite3 < myfile
>Obviously that isnt the right syntax, but Im looking for the correct
>syntax to import a csv into a specifc table from the command line like
>that.
>
>3.) I dont see how it would be possible to use the .import
>functionality through code? If its possible please enlighten me.
>
>Any helpful suggestions would be very much appreciated!
>
>Joe Noon
>
>  
>
Joe,

You can use a system call to execute a command like the following;

sqlite3 -separator , test.db ".import test.csv sometable"

You can of course change the file and table names when you build the
command string to pass to the system call.

HTH
Dennis Cote
Reply | Threaded
Open this post in threaded view
|

Re: Import CSV in sqlite3?

Joe Noon
> You can use a system call to execute a command like the following;
>
> sqlite3 -separator , test.db ".import test.csv sometable"
>

That is exactly what I needed! Thanks a ton.  

Joe Noon
Reply | Threaded
Open this post in threaded view
|

Re: Import CSV in sqlite3?

Joe Noon
> > You can use a system call to execute a command like the following;
> >
> > sqlite3 -separator , test.db ".import test.csv sometable"
> >
>
> That is exactly what I needed! Thanks a ton.
>
> Joe Noon
>

My issue now is that the csv may or may not have all of the columns
that my table has.  For instance, my table has 12 columns, but the csv
has only 2 or 3 columns worth of data in some instances.  This is
possibly due to a mal-formatted csv to begin with, but its all I have
to work with here.  sqlite obviouslly errors out saying the file only
has x records but the table contains 12.  Is there any way to force
the import no matter what exists?

For some background on what Im trying to do.. basically Im just trying
to get a csv file into the db so i can use SQL to search by the first
column.  The column names are just a,b,c etc.  Data integrity is a
non-issue here.
Reply | Threaded
Open this post in threaded view
|

Re: Import CSV in sqlite3?

Jens Miltner

Am 6.8.05 um 09:06 schrieb Joe Noon:

>>> You can use a system call to execute a command like the following;
>>>
>>> sqlite3 -separator , test.db ".import test.csv sometable"
>>>
>>>
>>
>> That is exactly what I needed! Thanks a ton.
>>
>> Joe Noon
>>
>>
>
> My issue now is that the csv may or may not have all of the columns
> that my table has.  For instance, my table has 12 columns, but the csv
> has only 2 or 3 columns worth of data in some instances.  This is
> possibly due to a mal-formatted csv to begin with, but its all I have
> to work with here.  sqlite obviouslly errors out saying the file only
> has x records but the table contains 12.  Is there any way to force
> the import no matter what exists?
>
> For some background on what Im trying to do.. basically Im just trying
> to get a csv file into the db so i can use SQL to search by the first
> column.  The column names are just a,b,c etc.  Data integrity is a
> non-issue here.
>

You could preprocess the csv file (e.g. using awk or similar tools)  
and generate a file that contains only the first column if that's all  
you're interested in.
With this file and a table that only contains a single column, the  
import should succeed.

</jum>