MySQL Bugs: #97813: Wont fully import CSV file.

Bug #97813	Wont fully import CSV file.
Submitted:	27 Nov 2019 13:40	Modified:	31 Dec 2019 7:35
Reporter:	Brandon Hoffman	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Workbench: SQL Editor	Severity:	S1 (Critical)
Version:	8.0.18,8.0.20, 8.0.28	OS:	Windows (10)
Assigned to:		CPU Architecture:	x86 (quad core 2.2 ghz)
Tags:	.csv file, importing, workbench 8

Description:
Hello I did find this bug but no solution was reported. I can not fully import .csv file. I read this is a bug and has been reported several times. If the solution is easy let me know what to do. Otherwise here are snapshots and the result. The original file has 1400 records to upload to database. I have tried: renaming, saving, closing everything and restarting computer. I have other tables that worked just excellent. Computer is dedicated to developing vb.net and workbench database.The "work around's" don't work. it does the same thing.

How to repeat:
1. Use table data import wizard.
2. Select file and run through the wizard.
3. Once done it will say 75 records have been imported. 
4. Right click table and view 1000 records
5. Records go to 23 and then jump to 400's and then stops after 75 total records. 
6. Successfully repeated 3 times.

Suggested fix:
If you could tell me that would be great. All my programs are up to date and fresh install.

final failing result

Attachment: final.PNG (image/png, text), 85.58 KiB.

creating a new table

Attachment: newtablecreation.PNG (image/png, text), 31.44 KiB.

showing the correct filepath

Attachment: selected.PNG (image/png, text), 10.92 KiB.

showing schema without the table present

Attachment: withouttable.PNG (image/png, text), 44.37 KiB.

An update. I have tried saving the file in UTf-8 CSV, and that did not work. I tried to changed encoding to Latin1 and others and still did not work. I also realized that the file had so many records that it truncated with an error. After investigation is there a way to import the .csv file with the option to stop truncation. Or is there a place where the truncation limit can be set. Can this be turned into a feature?

Hello Brandon Hoffman,

Thank you for the report.
To investigate further this issue at our end, may I kindly request you to provide the .csv file? Thank you.

Regards,
Ashwini Patil

failing file

Attachment: em_thk_tooling.csv (application/vnd.ms-excel, text), 42.03 KiB.

Update: I tried to see if it is the file size or record limit by dissecting the file into 6 smaller files with only 249 records to see if that made a difference, did not. Some thing is wrong with data that I cant tell or Workbench is not working properly.

Hello Brandon Hoffman,

Thank you for the requested details.

Regards,
Ashwini Patil

Bug #99733 marked as duplicate of this one.

dataset

Attachment: data-large.csv (application/vnd.ms-excel, text), 958.70 KiB.

I am surprised to see no solution provided for this issue.

you guys are really doodoo, go and check yourself if your duplicate big has solution provided by you.

shame on you Oracle dammn i just cant understand you guys just labeling as duplicate instead of providing solution i am switching on microsoft sql
shitty product sql u have 

by the the duplicate u said dont have any solution in there

Hello,

Thank you for writing us.
We once again revisited your issue internally with WB coordinator and concluded below points.

A CSV file contains strings of characters. Even if they appear numeric, they are still characters, not numbers. Consider a CSV file that contains:

"What follows is a string, even though there are no quotes:",1

Examine the bytes stored in that file, and you'll see that the last value is not something like 0x01, but rather 0x31, which is the code for the character "1".

The import wizard looks at the first few rows of data in the CSV, sees some numeric characters with no quote marks, and guesses that field is supposed to represent a double. If you insert a string into a numeric type like int or double, MySQL follows the rules documented at <https://dev.mysql.com/doc/refman/8.0/en/type-conversion.html> for converting a string into a number.

At line 24 in the file you've attached(em_thk_tooling.csk), the value provided for one of those "double" fields is an empty string.

24,0.5,double d,0.13,0.232,,

An empty string has no exact numeric equivalent. It's not 0; that's a number with a string representation "0", and is the answer to "How many pink elephants are tap dancing in your living room right now?". It's not NULL, because we represent that with \N and is the answer to "What is the number of your bank account?" (there is such a number, but my answer is "I won't tell you", which you can record as NULL, meaning "unknown"). It isn't a number at all. It's just empty.

Years ago, MySQL's default behavior was to try to accept these ambiguities and just use the default value for that column type. Users liked that when it made their imports run quietly, but didn't like it when they couldn't tell the difference between a provided "0" value and a missing value. So, we now use "strict" mode as default: if you provide a value that doesn't fit the column type, MySQL gives an error, which lets you know right away that something went wrong, so you can decide what to do.

Three possible choices:

1. You can fix the CSV file so that it doesn't have empty strings where numeric digits are supposed to be. Put a 0 there, or \N if you want it to be NULL.
2. You can change the column type for that field to TEXT, which is happy to store empty strings. Later you can go back and update those empty strings to be strings of "0" or NULL or whatever, and then change the column type to FLOAT or DOUBLE or DECIMAL.
3. You can SET GLOBAL sql_mode=''; before opening the connection that will run the wizard, which tells MySQL to pretend it's 1999 and just accept whatever you hand it and do its best to convert values to what you might or might not have meant.

Regards,
Ashwini Patil