[sqlite] Problems uploading CSV into sqlite3 DB

[Carlos A. Gorricho (HGSAS)]

Dear all,

A brief update on how I managed to solve the problem...thank you all for
you remarks and comments, since these were instrumental in the solution.

Indeed sqlite versions had to do a lot with the problem, where 3.8 can
handle csv imports, not without some glitches. I had to go in circles
several times to realize how to make sure I was always working on the right
sqlite version.

I also noticed that I had some problems with my data, for csv import
purposes, in the sqlite environment. This problem did NOT affect csv import
into MS Access.

The problem was a field with a very long blank string, something like 40
space-characters. Apparently the sqlite import engine was not able to
recognize the end of this string and was merging it with the next field,
causing as mismatch between the column field names and the data fields.
This mismatch would compound with every new line imported.

I found the solution by sheer luck. I added manually a new column in the
csv file (from Excel) with a formula to test if the blank field was empty
or text. This created a column filled with boolean values, which sqlite csv
import engine was able to recognize, hence delimiting the large string of
spaces...odd I know, but it worked!


Saludos/Cheers,


*CARLOS A. GORRICHO*
Managing Partner
Heptagon Group S.A.S.
cgorri...@heptagongroup.co
Cel COL +57 314 771 0660
Cell USA +1 713 574 2242
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Problems uploading CSV into sqlite3 DB

[Carlos A. Gorricho (HGSAS)]

So, any ideas on how to solve this issue will be more than welcome. I have
tried several shortcuts...none works.

Latest was to install an Ubuntu 14.04 Virtual Machine on my Mac, via
Parallels software.

Downloaded sqlite de Linux way:

$ sudo apt-get install sqlite3


Ran the CSV .import protocol and didn't work Result was eve weirder, since
it create a table using ALL the records as columns headers...



Saludos/Cheers,


*CARLOS A. GORRICHO*
Managing Partner
Heptagon Group S.A.S.
cgorri...@heptagongroup.co
Cel COL +57 314 771 0660
Cell USA +1 713 574 2242


2014-09-03 10:24 GMT-05:00 Carlos A. Gorricho (HGSAS) <
cgorri...@heptagongroup.co>:

> ​When I start sqlite double clicking the icon in the Mac Applications
> folder:
>
> Last login: Wed Sep  3 10:13:32 on ttys000
>
> HEPTAGON-GROUP-SAS:~ carlosgorricho$ /Applications/sqlite3 ; exit;
>
> SQLite version 3.8.5 2014-06-04 14:06:34
>
> Enter ".help" for usage hints.
>
> Connected to a *transient in-memory database*.
>
> Use ".open FILENAME" to reopen on a persistent database.
>
> sqlite>
>
> ​
> When starting sqlite on a Terminal Window:
>
> Last login: Wed Sep  3 10:11:42 on ttys002
>
> HEPTAGON-GROUP-SAS:~ carlosgorricho$ sqlite3
>
> SQLite version 3.7.13 2012-07-17 17:46:21
>
> Enter ".help" for instructions
>
> Enter SQL statements terminated with a ";"
>
> sqlite>
>
>
> I downloaded the only precompile binary currently available for Mac in
> www.sqlite.org website.
>
> To perform the job described earlier, I run the Terminal version in batch
> mode. That is, I created a script file with all the .commands to upload de
> CSV file, and ran it on a new database:
>
> $ sqlite3 newDB.db < my_script_file
>
>
> When I got the tab1 error message, I reverted to performing the job from
> inside the database, creating first the table and then uploading the data.
> The result was the huge single-record-field DB I shared earlier.
>
> I would venture to say I did this from the Terminal sqlite version, but I
> am not certain...
>
> Thanks in advance for your comments and guidance. Please let me know if I
> can do anything to help you in other fronts.
>
>
>
>
> Saludos/Cheers,
>
>
> *CARLOS A. GORRICHO*
> Managing Partner
> Heptagon Group S.A.S.
> cgorri...@heptagongroup.co
> Cel COL +57 314 771 0660
> Cell USA +1 713 574 2242
>
>
> 2014-09-03 9:25 GMT-05:00 Simon Slavin <slav...@bigfraud.org>:
>
>
>> On 3 Sep 2014, at 1:51pm, Richard Hipp <d...@sqlite.org> wrote:
>>
>> > What version of SQLite is installed?
>>
>> Or rather, when you start the shell tool 'sqlite3' what version does it
>> report itself as ?
>>
>> 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] Problems uploading CSV into sqlite3 DB

[Carlos A. Gorricho (HGSAS)]

​When I start sqlite double clicking the icon in the Mac Applications
folder:

Last login: Wed Sep  3 10:13:32 on ttys000

HEPTAGON-GROUP-SAS:~ carlosgorricho$ /Applications/sqlite3 ; exit;

SQLite version 3.8.5 2014-06-04 14:06:34

Enter ".help" for usage hints.

Connected to a *transient in-memory database*.

Use ".open FILENAME" to reopen on a persistent database.

sqlite>

​
When starting sqlite on a Terminal Window:

Last login: Wed Sep  3 10:11:42 on ttys002

HEPTAGON-GROUP-SAS:~ carlosgorricho$ sqlite3

SQLite version 3.7.13 2012-07-17 17:46:21

Enter ".help" for instructions

Enter SQL statements terminated with a ";"

sqlite>


I downloaded the only precompile binary currently available for Mac in
www.sqlite.org website.

To perform the job described earlier, I run the Terminal version in batch
mode. That is, I created a script file with all the .commands to upload de
CSV file, and ran it on a new database:

$ sqlite3 newDB.db < my_script_file


When I got the tab1 error message, I reverted to performing the job from
inside the database, creating first the table and then uploading the data.
The result was the huge single-record-field DB I shared earlier.

I would venture to say I did this from the Terminal sqlite version, but I
am not certain...

Thanks in advance for your comments and guidance. Please let me know if I
can do anything to help you in other fronts.




Saludos/Cheers,


*CARLOS A. GORRICHO*
Managing Partner
Heptagon Group S.A.S.
cgorri...@heptagongroup.co
Cel COL +57 314 771 0660
Cell USA +1 713 574 2242


2014-09-03 9:25 GMT-05:00 Simon Slavin :

>
> On 3 Sep 2014, at 1:51pm, Richard Hipp  wrote:
>
> > What version of SQLite is installed?
>
> Or rather, when you start the shell tool 'sqlite3' what version does it
> report itself as ?
>
> 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

[sqlite] Fwd: Problems uploading CSV into sqlite3 DB

[Carlos A. Gorricho (HGSAS)]

Dear all,

I am experiencing some difficulties loading CSV data into it.

I am using the precompiled binary available on the web site for Mac OSX. I
am running OS X 10.9.4 on a Mac Book Pro.

When following instructions to upload CSV on a new database:

sqlite> *.mode csv*
sqlite> *.import My-CSV-file.csv tab1*


I get an error message:

sqlite> .import BD-Costes-17072014.csv tab1

Error: no such table: tab1

sqlite>


I understood that if tab1 did not exist, sqlite3 would create a new one and
use the first line in the CSV file as column headers.

Then I created the table tab1:

sqlite> .tables

tab1

sqlite> .schema

CREATE TABLE tab1 (

CODIGO

DESCRIPCION

TIPO_ARTICULO

DESCRIPCION_TIPO_ARTICULO

NIVEL

CODIGO_MATERIAL

MATERIAL

CàDIGO_SUSTITUTO

SUSTITUTO

FACTOR

FACTOR_X_SKU

UNIDAD

PRECIO

COSTE);

sqlite>


These are the exact same headers as in my CSV file. I removed the headers
from the CSV file and run the .import command again, apparently with no
error.

When checking for the amount of records uploaded:

sqlite> select count(*) from tab1;

1

sqlite>

The CSV files has 2651 records. Apparently, all the records are being
loaded as one huge string into the first db record, AND the first field:

sqlite> select CODIGO from tab1 LIMIT 1;

24873,PULP DURAZNO 0.150 ML X 96,4,PRODUCTO TERMINADO,1,559,COLA PARA
PITILLOS HOT MELT ADHESIVE JOWATERM 25030

[OUTPUT GOES ON TO THE END OF THE CSV FILE...]


Compare this with a query on the second field:

sqlite> select DESCRIPCION from tab1;

Error: no such column: DESCRIPCION


I have not been able to understand what is going on. I am an intensive user
of CSV files for analytical purposes, hence being able to upload them
correctly into SQLite3 is key.

I am suspecting maybe an environmental variable or something like that
needs to be modified, but would not venture without guidance.

Looking forward to hearing your feedback.

Attached the original CSV file and resulting database.



Saludos/Cheers,


*CARLOS A. GORRICHO*
Managing Partner
Heptagon Group S.A.S.
cgorri...@heptagongroup.co
Cel COL +57 314 771 0660
Cell USA +1 713 574 2242
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users