Re: [sqlite] .import on a .csv file

[Rich Shepard]

On Thu, 22 Oct 2009, Simon Davies wrote:

> One solution is to replace your existing separators  (,) with a character
> that does not exist in your data, specify that character as the separator
> to sqlite, and you should be good to go .import.

   I found the solution was to change all field separators from "," to "|"
which is the default SQLite field separator. This way, after more cleaning
and tuning, I could remove all double quotation marks and each column
remained delimited while embedded commas remained.

   I used emacs for this but any text editor will work.

Rich

-- 
Richard B. Shepard, Ph.D.   |  IntegrityCredibility
Applied Ecosystem Services, Inc.|Innovation
 Voice: 503-667-4517  Fax: 503-667-8863
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] .import on a .csv file

[Simon Davies]

2009/10/22 Scott Baker :
> I'm trying to .import a CSV file and I can't quite figure out the syntax.
>
> I created a table, and then did:
>
> .separator ,
> .import /tmp/foo.csv mytable
>
> This works sort of, unless my data has , in it. Something like "last,
> first". Because it tries to split at that , and then the number of rows
> doesn't match my table.
>
> Then I tried setting the separator to
>
> .separator \",\"
>
> Which works correctly (i.e. it splits the data properly). However, now my
> first and last columns have " on the beginning/end of them. Is there a way
> to import a well formed CSV.
>
> My CSV data looks like this:
>
> "38665","101977","Deadly Sparrows Inc.","1435 S. Doolis
> Ln","Donkville","OR","90210","Doolis, Jason","5032349422","Active"
>
> Help!

There was a discussion on csv import a little while ago:
http://www.nabble.com/Tedious-CSV-import-question-to25601326.html#a25601326

In essence, you need to do some work if your field separator character
appears inside your data fields.

One solution is to replace your existing separators  (,) with a
character that does not exist in your data, specify that character as
the separator to sqlite, and you should be good to go .import.

>
> --
> Scott Baker - Canby Telcom
> System Administrator - RHCE - 503.266.8253

Rgds,
Simon
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] .import on a .csv file

[Adam DeVita]

Do you have any new lines, returns, or tabs in any of the real data?  Can
you prove it?

Is this a 1 off thing or are you going to do this routinely?

There has been a lot of discussion on this list about importing csv data and
the hardships of writing a good csv importer.

If this is a one off, some possible tricks:

1) If there are no newlines or tabs inside the data, perhaps you can run
away from your embedded delimiter by changing the delimiter to a tab?
2) Have you considered using Access, XL, or open office  to see if you can
get a clean import into there?  This may allow you to save into another
format or...
2b) Use the spreadsheet to create the sql you want to import.

The merits of various solutions can be found by searching the archive.

Go to
http://www.mail-archive.com/sqlite-users@sqlite.org/info.html

and search on:
Tedious CSV import question This was a good discussion.



On Thu, Oct 22, 2009 at 12:16 PM, Scott Baker  wrote:

> I'm trying to .import a CSV file and I can't quite figure out the syntax.
>
> I created a table, and then did:
>
> .separator ,
> .import /tmp/foo.csv mytable
>
> This works sort of, unless my data has , in it. Something like "last,
> first". Because it tries to split at that , and then the number of rows
> doesn't match my table.
>
> Then I tried setting the separator to
>
> .separator \",\"
>
> Which works correctly (i.e. it splits the data properly). However, now my
> first and last columns have " on the beginning/end of them. Is there a way
> to import a well formed CSV.
>
> My CSV data looks like this:
>
> "38665","101977","Deadly Sparrows Inc.","1435 S. Doolis
> Ln","Donkville","OR","90210","Doolis, Jason","5032349422","Active"
>
> Help!
>
> --
> Scott Baker - Canby Telcom
> System Administrator - RHCE - 503.266.8253
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
VerifEye Technologies Inc.
905-948-0015x245
7100 Warden Ave, Unit 3
Markham ON, L3R 8B5
Canada
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] .import on a .csv file

[Scott Baker]

I'm trying to .import a CSV file and I can't quite figure out the syntax.

I created a table, and then did:

.separator ,
.import /tmp/foo.csv mytable

This works sort of, unless my data has , in it. Something like "last, 
first". Because it tries to split at that , and then the number of rows 
doesn't match my table.

Then I tried setting the separator to

.separator \",\"

Which works correctly (i.e. it splits the data properly). However, now my 
first and last columns have " on the beginning/end of them. Is there a way 
to import a well formed CSV.

My CSV data looks like this:

"38665","101977","Deadly Sparrows Inc.","1435 S. Doolis 
Ln","Donkville","OR","90210","Doolis, Jason","5032349422","Active"

Help!

-- 
Scott Baker - Canby Telcom
System Administrator - RHCE - 503.266.8253
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users