In MySQL Workbench, using "Table Data Import Wizard" to import CSV creates empty table

Question

I am attempting to import a csv file into a MySQL table using the Table Data Import Wizard. The sample section at the bottom of the Configure Import Settings screen looks fine and when I run the import, it says all of my entries were loaded successfully. However, when I go to view the contents of the table, only the columns are there and none of my actual data loaded. Does anyone know why this might be happening and how to correct it?

EDIT:

These are a few lines from my CSV file:

STATION,STATION_NAME,ELEVATION,LATITUDE,LONGITUDE,DATE,MLY-TAVG-NORMAL,MLY-TMAX-NORMAL,MLY-TMIN-NORMAL,Average Temp,Max Temp,Min Temp GHCND:USW00094085,PIERRE 24 S SD US,647.4,44.0194,-100.353,201001,218,322,113,21.8,32.2,11.3 GHCND:USW00094085,PIERRE 24 S SD US,647.4,44.0194,-100.353,201002,246,354,137,24.6,35.4,13.7 GHCND:USW00094085,PIERRE 24 S SD US,647.4,44.0194,-100.353,201003,339,451,226,33.9,45.1,22.6 GHCND:USW00094085,PIERRE 24 S SD US,647.4,44.0194,-100.353,201004,463,588,337,46.3,58.8,33.7 GHCND:USW00094085,PIERRE 24 S SD US,647.4,44.0194,-100.353,201005,573,696,450,57.3,69.6,45

Here are some images of the import process:

And here is what I see when I attempt to view the newly created table:

Second Edit:

Here is an image of my settings:

And here is an image of my CSV file showing line endings:

Answer 1

I was getting 0 records imported because I had a COMPOSITE PRIMARY KEY and my CSV file did not have data for one of the primary columns. Make sure you have something for all primary key columns.

Answer 2

I faced that problem too and it was caused by my date column being in a wrong format in my csv file. I changed the date format to 2022-01-16 instead of 2022/01/16 and my data was imported correctly.

Answer 3

I had the same problem. After searching, I figured out that I had made a mistake in the declaration of the table's columns. For example, I had a column that had a type INT, and the CSV file I tried to import had a VARCHAR there instead of an INT.

Answer 4

I ran into the same problem and tried to solve it with changing settings, but it didn't work for me. I just import all data as txt datatype and

(ALTER TABLE tablename MODIFY COLUMN datatype)

later to correct the data type.

Answer 5

Verify the encoding of your file. When parsing a source file as UTF-8, the workbench silently bombs out when it encounters non UTF-8 characters. I used cp1250 (windows 1250) for a file exported from Excel and everything worked properly.

Answer 6

Tried with your sample data and it imported fine. There must be something wrong with further data I guess. Maybe one (or more) of the records are not as they should be. The preview is good for the first number of records, so your import definition seems fine. Try importing smaller chunks of records. Start with 1, extend to ten etc. Until you see the import fail. You can also try with only one column and if that succeeds do a binary search for the column that fails.

Note: there is bug when displaying the imported rows (and has been fixed for the next version). That always shows the total count of records, not those actually imported.

Answer 7

You need to ensure that your csv options are set correctly when importing the table.

When you get to the below screen notice how red arrow points to the wrench. By clicking that an option drop down to set your line separator, enclosing strings, and field separator will appear. If these are not set right then the data will load unsuccessfully.

Below is a sample CSV file. Notice how in Notepad++ the wrapper button is pressed so you can see the end of line. In this case the end of line is CR LF. The default option for Workbench Import wizard is just LF. Additionally, default field separator appears to be a ; and not a ,.

You can read more here at dev.mysql