MYSQL Crashes while importing CSV using import wizard

Question

I have set up MySQL 8.0.23 365764 CE. Have created the Schema and Table in it. Have 3 Tables. Importing Tab1 and 2 from CSV works perfectly fine. Tab1 has 66 and Tab2 54714 records. I am using Importing Wizard here. For some reason, MYSQL Workbench just crashes without any error when I try importing Tab3. Tab3 has 6200 records. All these data are copied from Excel. Made sure the only plain data without format is copied. Not sure of the reason for its crashing.

Steps for replicating -

Windows log has this info -

*Fault bucket 1625754702484237642, type 4
 Event Name: APPCRASH
 Response: Not available
 Cab Id: 0
 Problem signature:
 P1: MySQLWorkbench.exe
 P2: 8.0.23.0
 P3: 5fdaedc8
 P4: _mforms.pyd
 P5: 0.0.0.0
 P6: 5fdaed25
 P7: c0000005
 P8: 0000000000004485
 P9: 
 P10: 
 Attached files:
 \\?\C:\ProgramData\Microsoft\Windows\WER\Temp\WER8389.tmp.mdmp
 \\?\C:\ProgramData\Microsoft\Windows\WER\Temp\WER87EF.tmp.WERInternalMetadata.xml
 \\?\C:\ProgramData\Microsoft\Windows\WER\Temp\WER881E.tmp.xml
 \\?\C:\ProgramData\Microsoft\Windows\WER\Temp\WER882C.tmp.csv
 \\?\C:\ProgramData\Microsoft\Windows\WER\Temp\WER888B.tmp.txt
  These files may be available here:

  C:\ProgramData\Microsoft\Windows\WER\ReportArchive\AppCrash_MySQLWorkbench.e_6bxxxxxx....

 Analysis symbol: 
 Rechecking for solution: 0
 Report Id: da476bd8-6fcb-46da-9054-75a3bd33e01a
 Report Status: 268435456
 Hashed bucket: fecdb3dd9b2c1a53768fd749cd7e6d4a
 Cab Guid: 0*

CREATE TABLE `mapinfo` (
  `ID` int NOT NULL,
   `BusNumber` varchar(45) DEFAULT NULL,
   `AreaNum` varchar(2000) DEFAULT NULL,
   `DD` varchar(2000) DEFAULT NULL,
   `BasekV` varchar(45) DEFAULT NULL,
   `Mapping` varchar(45) DEFAULT NULL,
   `MatchingWith` varchar(45) DEFAULT NULL,
   `Note` varchar(45) DEFAULT NULL,
   `Assignee` varchar(45) DEFAULT NULL,
   `ExtractedKV` varchar(45) DEFAULT NULL,
   `ExtractedCoord` varchar(45) DEFAULT NULL,
    PRIMARY KEY (`ID`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

Could you post to pastebin.com the A) SHOW CREATE TABLE table-3; results and B) 6200 row csv input file to allow testing/analysis? — Wilson Hauck, Feb 12 '21 at 14:09
Thanks, @WilsonHauck - The excel file is confidential. Sorry, can't share it. Here is the create table statement - — Ashwin Kumar, Feb 12 '21 at 15:27
Are those single quotes in the SQL, or are they the upper left key on the keyboard? — Kieveli, Feb 12 '21 at 15:41
OK, if you can not supply a sample/example CSV, there is not much we can do for you to verify anything you are working with. — Wilson Hauck, Feb 12 '21 at 21:03
Didn't know you were expecting sample. Have pasted the sample and you can access it here - https://pastebin.com/1TzQqLSM — Ashwin Kumar, Feb 13 '21 at 08:00
I just tried importing the sample data. It didn't crash this time, but didn't import any rows. Had created the table before importing. However, I tried doing same by including the headers in the first line. Have pasted the sample with this https://pastebin.com/vtNEqyaH Please let me know if any of you have any suggestion in handling this. — Ashwin Kumar, Feb 13 '21 at 18:20

score 1 · Accepted Answer · answered Feb 15 '21 at 14:46

1

Your SHOW CREATE TABLE indicated every column after ID is varchar.

According to this tutorial about CSV input data, every VARCHAR column should be surrounded by "xxxxxx xxxx" - https://www.mysqltutorial.org/import-csv-file-mysql-table/

Your data does not follow this pattern very well.

answered Feb 15 '21 at 14:46

Wilson Hauck

2,094
1
11
19

Are you suggesting to have a row data - 1,340553,314,Wilson Substation,161,Completed,Envision,Manually extracted for Winson,Ashwin,345'|'161'|'0,37.44939'| '-87.0884 as 1,"340553","314","Wilson Substation","161","Completed","Envision","Manually extracted for Winson","Ashwin","345'|'161'|'0,37.44939'| '-87.0884" – Ashwin Kumar Feb 17 '21 at 10:21
Through the eighth CSV column, you followed the double quote requirement. After "Ashwin" is quite strange. View my profile, Network profile for contact info and free downloadable Utility Scripts to assist with performance tuning and get in touch, please. – Wilson Hauck Feb 17 '21 at 12:52
After "Ashwin" I am combining entries in 3 fields separated by "|". Was planning to split this in my script after importing into MySQL. – Ashwin Kumar Feb 17 '21 at 13:33
There are 11 columns and the data are matching fine. Have just pasted the data here again - https://pastebin.com/Rk4FyV7H Have removed all non-matching data. Can you please have a look and let me know. – Ashwin Kumar Feb 20 '21 at 16:32
If you want to Skype TALK in English, I usually check Skype 3 x a day. Schedule a local time and tell me what time zone you are in so I can adjust my time-of-day to look for you on Skype. – Wilson Hauck Feb 20 '21 at 17:48
1

Thanks a lot @Wilson Your observation was right. Had to remove single quote from the column entries after "Ashwin" and everything worked fine. Was able to import all the data. Thanks for answering my questions and helping with this with patience. – Ashwin Kumar Feb 21 '21 at 07:23
Wrong. You don't need to wrap with double quotes when importing in Workbench using CSV. Just tried it myself. – Alex G Jun 28 '21 at 00:38

MYSQL Crashes while importing CSV using import wizard

1 Answers1