Trouble exporting Excel data to MySQL

Question

I am attempting to export Excel data into MySQL 8.0 using 'MySQL for Excel' in Excel with datatype VarChar(255).

The longest of my strings is 40 characters, yet I keep on getting the error message:

MySQL Error 1406: Data too long for column 'Column' at row 150

I switched the MySQL mode to not strict in the my.ini file using these instructions, but it didn't resolve the issue.

When I increase the VarChar length to 800, I receive the error message:

MySQL Error 1071: Specified key was too long; max key length is 3072 bytes

Anything lower than VarChar(800) gives me the first error message.

Any ideas how to successfully export the data into MySQL?

I assume you did check that the data on row 150 of the spreadsheet does not have a string that is longer than you thought? — RiggsFolly, Jul 29 '20 at 15:53
If you are using a 4byte encoding like `utf8mb4` then varchar(800) is too big, 800*4=3200 — RiggsFolly, Jul 29 '20 at 15:54
@RiggsFolly yes I checked row 150, none of the strings are longer than 40 characters. — Sarah, Jul 29 '20 at 16:18

score 0 · Answer 1 · answered Jul 29 '20 at 22:43

0

I suggestion you to use MysqlWorkbench to import your Excel data file. It's very easy and simple to import data into database, you just need to create database structure first time and then use that widget to import file data into.

answered Jul 29 '20 at 22:43

integer

221
4
13

Thank you! I ended up using the import wizard in MySQL, frustrating it's not easier to export via Excel. – Sarah Jul 30 '20 at 12:21

Trouble exporting Excel data to MySQL

1 Answers1