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
– Wilson Hauck, Commented Feb 12, 2021 at 14:09
Thanks, @WilsonHauck - The excel file is confidential. Sorry, can't share it. Here is the create table statement - — Ashwin Kumar
– Ashwin Kumar, Commented Feb 12, 2021 at 15:27
Are those single quotes in the SQL, or are they the upper left key on the keyboard? — Kieveli
– Kieveli, Commented Feb 12, 2021 at 15:41

Wilson Hauck · Accepted Answer · 2021-02-15 14:46:39Z

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, 2021 at 14:46

Wilson Hauck

2,3831 gold badge14 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Ashwin Kumar Over a year ago

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"

Wilson Hauck Over a year ago

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.

Ashwin Kumar Over a year ago

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 Over a year ago

There are 11 columns and the data are matching fine. Have just pasted the data here again - pastebin.com/Rk4FyV7H Have removed all non-matching data. Can you please have a look and let me know.

Ashwin Kumar Over a year ago

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.

|

Collectives™ on Stack Overflow

MYSQL Crashes while importing CSV using import wizard

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related