Re: [sqlite] Problems uploading CSV into sqlite3 DB
On 10 Sep 2014, at 3:45am, Carlos A. Gorricho (HGSAS) wrote: > The problem was a field with a very long blank string, something like 40 > space-characters. Was this value correctly delimited with quotes, or was it just 40 spaces between two commas separating the fields ? Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Problems uploading CSV into sqlite3 DB
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
Humour us and paste the first 4 lines or so of your CSV text here On 2014/09/05 04:17, Carlos A. Gorricho (HGSAS) wrote: 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, ___ 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
I get similar results on OS X 10.9.4 if I try to use the version of SQLite supplied with the operating system (3.7.13) to import a CSV. The simple ".mode csv" and ".import tab1" method works if I run the copy of SQLite 3.8.5 I built myself from the source code, or if I use the Mac OS X precompiled SQLite 3.8.5 shell from the web site. (I prefer to build my own copy because the one on the web site doesn't include the readline library, so if used interactively it can't recall previous command lines with up-arrow.) The problem is likely to be that the method you are using to launch sqlite3, which is using the system's default search path to locate sqlite3, and that will be the Apple-supplied version (3.7.13) which is located at /usr/bin/sqlite3. It appears that version doesn't import CSVs properly. The easiest way to avoid the problem is to specify the path to your downloaded copy of sqlite3. Assuming you have put the downloaded sqlite3 in your Applications folder, then to run it from Terminal you can use this command: $ /Applications/sqlite3 newDB.db < my_script_file On 4/09/2014, at 3:24 am, Carlos A. Gorricho (HGSAS) wrote: > 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, ___ 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) wrote on Friday, September 05, 2014 4:17 AM >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. > Maybe line endings... -- This message, and any attachments, is for the intended recipient(s) only, may contain information that is privileged, confidential and/or proprietary and subject to important terms and conditions available at http://www.bankofamerica.com/emaildisclaimer. If you are not the intended recipient, please delete this message. ___ 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
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 : > > >> 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
Re: [sqlite] Problems uploading CSV into sqlite3 DB
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
Re: [sqlite] Problems uploading CSV into sqlite3 DB
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
