CSV Import is clunky then Silently fails
I am trying to import CSV data into a table.
First problem is usability.
My CSV has only three columns I care about: ItemName, ItemCode, Quantity. The Table has Id, CreatedOnDate, IsPurchase, etc, many more columns.
Id is an auto increment column, so I can't put a value in my CSV for it. What do I do here?
CreatedOnDate should simply be UtcNow(), IsPurchase defaults to false. How can I specify default values for these?
Even when I tell it that the first row is a header, it doesn't seem to be able to match columns. The columns have to be in exact order as they appear in DB.
So the work around is to manipulate the spreadsheet to look exactly like the table does. Though at this point it would have been quicker to simply write a script to parse the csv, and create insert statements.
The Second Problem is a Bug.
If any errors occur in the import, nothing happens. no error message, nothing written to the specified error log. Nothing. In fact I thought the feature was just flat out broken, until I realized, I had the order of a couple column incorrect.
Please sign in to leave a comment.
Actually, confirming that the second issue is a bug regardless. I can't seem to get it to import anything. All, looks good, I hit ok, and it just closes the dialog with zero feedback. Zero rows added to table. Maybe the Id column is the problem?
Like Shea, I'd also like to know how to map CSV columns to database columns.
Just encountered this same issue with a test table.
I have 3 columns; id (primary key), name (varchar), quota (int)
I have a csv with a name and quota per line. The id isn't specified since the id is automatically set.
When I try to import, I get an error saying that the number of columns in the csv doesn't match the number of columns in the table.
Yeah, I think DG is not really usable for importing yet.
One alternative if you are using SQL Server is from Excel into SMS. This can be slow but works:
1. Manipulate the columns in Excel to be what you want. 2. Then then do Edit top 100 in SMS. 3. Edit the SQL in SMS so that the columns match what you have in Excel. 4. Cut/Paste from Excel to SMS.
What I did was write a small ruby script to parse the CSV and generate insert statements into a SQL script file. Funny thing is that DataGrip could not even handle running the SQL script. My ruby app created a script with 10,000 inserts (so not much), running that script in DG took 20 minutes, before it crashed saying it had run out of memory. The same script in SMS ran in under 20 seconds.
I like DB for some things, but it is clear that it is not intended for importing data in any fashion.
Hi guys,
We plan to improve importing functionality soon. Please follow the https://youtrack.jetbrains.com/issue/DBE-3373
@Andrey Dernov
I get "This page was not found"
me too.
Sorry, there was trailing space in the link itself, corrected it now.