Data Import plugin - database error loading CSV file

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

Data Import plugin - database error loading CSV file

cassandracyril
I've got the Data Import plugin loaded and supposedly working, but when I try and fill a table with a CSV file I get "A database error occurred while inserting data"

During the data insert process, I see the full preview of my CSV data correctly, and it reads the column names and rows and everything just fine.  It's only when I say "go ahead and create it" that it crashes.

This question's been asked before, but not since ~2008 and it was never answered fully then, so hopefully we can figure it out this time.  I'm a new user to SQuirreL SQL, and trying to get it set up to load in a fairly large data set (currently in .dat format).
Reply | Threaded
Open this post in threaded view
|

Re: Data Import plugin - database error loading CSV file

Alexandre Peshansky
I usually see that when I forget to modify the CSV defaults, especially date format.

--
Alexandre Peshansky, MS
Senior Bioinformatics Analyst, Research Informatics Core (RIC)
Harold & Muriel Block Institute for Clinical & Translational Research at Einstein and Montefiore
Albert Einstein College of Medicine
1300 Morris Park Ave, Bronx, NY 10461
(718) 430-2440 (Mo, Tu, Th, Fr)  (718) 920-4885 (We)


-----Original Message-----
From: cassandracyril [mailto:[hidden email]]
Sent: Friday, September 05, 2014 12:20 AM
To: [hidden email]
Subject: [Squirrel-sql-users] Data Import plugin - database error loading CSV file

I've got the Data Import plugin loaded and supposedly working, but when I try and fill a table with a CSV file I get "A database error occurred while inserting data"

During the data insert process, I see the full preview of my CSV data correctly, and it reads the column names and rows and everything just fine.
It's only when I say "go ahead and create it" that it crashes.

This question's been asked before, but not since ~2008 and it was never answered fully then, so hopefully we can figure it out this time.  I'm a new user to SQuirreL SQL, and trying to get it set up to load in a fairly large data set (currently in .dat format).


------------------------------------------------------------------------------
Slashdot TV.  
Video for Nerds.  Stuff that matters.
http://tv.slashdot.org/
_______________________________________________
Squirrel-sql-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/squirrel-sql-users
Reply | Threaded
Open this post in threaded view
|

Re: Data Import plugin - database error loading CSV file

cassandracyril
Would that be the case even if there are no dates in my data?

Do you modify the CSV formatting within Excel, or in the DataImport plugin?
Reply | Threaded
Open this post in threaded view
|

Re: Data Import plugin - database error loading CSV file

Alexandre Peshansky
I modify format in the DataImport plugin settings screen, and also have to always modify default ";" separator to ",".
Sorry - never had issues with anything except dates.
--
Alexandre Peshansky, MS
Senior Bioinformatics Analyst, Research Informatics Core (RIC)
Harold & Muriel Block Institute for Clinical & Translational Research at Einstein and Montefiore
Albert Einstein College of Medicine
1300 Morris Park Ave, Bronx, NY 10461
(718) 430-2440 (Mo, Tu, Th, Fr)  (718) 920-4885 (We)



-----Original Message-----
From: cassandracyril [mailto:[hidden email]]
Sent: Friday, September 05, 2014 12:55 PM
To: [hidden email]
Subject: Re: [Squirrel-sql-users] Data Import plugin - database error loading CSV file

Would that be the case even if there are no dates in my data?

Do you modify the CSV formatting within Excel, or in the DataImport plugin?



--
View this message in context: http://squirrel-sql-client.10976.n7.nabble.com/Data-Import-plugin-database-error-loading-CSV-file-tp3403p3405.html
Sent from the Users mailing list archive at Nabble.com.

------------------------------------------------------------------------------
Slashdot TV.  
Video for Nerds.  Stuff that matters.
http://tv.slashdot.org/
_______________________________________________
Squirrel-sql-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/squirrel-sql-users

------------------------------------------------------------------------------
Slashdot TV.  
Video for Nerds.  Stuff that matters.
http://tv.slashdot.org/
_______________________________________________
Squirrel-sql-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/squirrel-sql-users
Reply | Threaded
Open this post in threaded view
|

Re: Data Import plugin - database error loading CSV file

cassandracyril
Thanks - sorry if this is a silly follow-up, but what do you modify it to? What is the default date setting in Excel CSV?  Given that I don't even have any dates in this sample dataset, it's hard to see what it wants to be reading.
Reply | Threaded
Open this post in threaded view
|

Re: Data Import plugin - database error loading CSV file

Alexandre Peshansky
There's no default setting, AFAIK - CSV file will reproduce the format you set up in Excel (if you started with Excel and save as CSV).  I usually use mm/dd/yyyy format, so this is what I change it to.  But you can format column (select column, then "Format Cell") with dates to yyyy-mm-dd hh:mm:ss (custom formats, enter manually) and avoid changing format on the DataImport.

--
Alexandre Peshansky, MS
Senior Bioinformatics Analyst, Research Informatics Core (RIC)
Harold & Muriel Block Institute for Clinical & Translational Research at Einstein and Montefiore
Albert Einstein College of Medicine
1300 Morris Park Ave, Bronx, NY 10461
(718) 430-2440 (Mo, Tu, Th, Fr)  (718) 920-4885 (We)



-----Original Message-----
From: cassandracyril [mailto:[hidden email]]
Sent: Friday, September 05, 2014 1:13 PM
To: [hidden email]
Subject: Re: [Squirrel-sql-users] Data Import plugin - database error loading CSV file

Thanks - sorry if this is a silly follow-up, but what do you modify it to?
What is the default date setting in Excel CSV?  Given that I don't even have any dates in this sample dataset, it's hard to see what it wants to be reading.



--
View this message in context: http://squirrel-sql-client.10976.n7.nabble.com/Data-Import-plugin-database-error-loading-CSV-file-tp3403p3407.html
Sent from the Users mailing list archive at Nabble.com.

------------------------------------------------------------------------------
Slashdot TV.  
Video for Nerds.  Stuff that matters.
http://tv.slashdot.org/
_______________________________________________
Squirrel-sql-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/squirrel-sql-users

------------------------------------------------------------------------------
Slashdot TV.  
Video for Nerds.  Stuff that matters.
http://tv.slashdot.org/
_______________________________________________
Squirrel-sql-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/squirrel-sql-users
Reply | Threaded
Open this post in threaded view
|

Re: Data Import plugin - database error loading CSV file

Brian McCullough
On Fri, Sep 05, 2014 at 05:17:41PM +0000, Alexandre Peshansky wrote:
> There's no default setting, AFAIK - CSV file will reproduce the format you set up in Excel (if you started with Excel and save as CSV).  I usually use mm/dd/yyyy format, so this is what I change it to.  But you can format column (select column, then "Format Cell") with dates to yyyy-mm-dd hh:mm:ss (custom formats, enter manually) and avoid changing format on the DataImport.

I would also look at strings.  The best idea there is to make sure that
they are all quoted.  Some CSV file "generators" don't do that, and some
file readers don't like that.


Brian


------------------------------------------------------------------------------
Slashdot TV.  
Video for Nerds.  Stuff that matters.
http://tv.slashdot.org/
_______________________________________________
Squirrel-sql-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/squirrel-sql-users