Description:
MySQL for Excel would not create tables and insert data while importing data from excel worksheet, when length of data of a column increases after the 1000 rows maximum limit mentioned under Advanced Export Options
MySQL Server Version : 5.7.11-log MySQL Community Server (GPL)
MySQL for Excel Version : 1.3.6
Microsoft Excel Version : 2007
OS : MS Windows 7 Professional - 64 bit OS
.NET : Microsoft .NET Framework 4 Client Profile
Visual Studio : Microsoft Visual Studio 2010 Tools for Office
Runtime (x64)
I am trying to import pincode data of a state (Karnataka) in India from a excel sheet which has the following columns:
OFFICE NAME
OFFICE STATUS
PINCODE
TELEPHONE NO.
TALUK
DISTRICT
STATE/U.T.
POSTAL DIVISION
POSTAL REGION
POSTAL CIRCLE
One row sample data from the excel is given below :
Katkol S.O Sub Post Office 591114 08335-2562130 RAMDURG BELGAUM KARNATAKA BELGAUM NORTH KARNATAKA KARNATAKA
The TELEPHONE_NO column size created by Excel is VarChar(12)
The Telephone No. for the row 1482 (in excel) - where the mysql error (1406) was reported is:
Row 1482 - 08335-2562130
which has one extra digit (08335-256213(0) in this case), than the other.
Telephone No. for rows that are before 1482 which is given below :
Row 949 - 08288-233130
Row 950 - 08288-233205
Row 961 - 08288-275240
Row 954 - 08288-223221
Error Message Reported is given below:
Error while inserting rows...
MySQL Error 1406:
Data too long for column 'TELEPHONE_NO.' at row 1482
This happens for all rows above 1000 where the data length is greater than the specified data length for that column.
However if I manually edit the column data type to VarChar(25) for the TELEPHONE_NO column, the table is created and rows are inserted successfully in mysql table.
How to repeat:
Install MySQL for Excel either stand alone or through MySQL Installer --
For Download - http://dev.mysql.com/downloads/windows/excel/
Documentation - http://dev.mysql.com/doc/mysql-for-excel/en/mysql-for-excel-install.html
1) Start MySQL instance in Windows.
2) Open karnataka.xls which has more than 1000 rows data with increasing data
length after 1000th row.
3) Select all the rows in the excel to be imported.
4) Click on Data Menu in the excel - which displays MySQL for Excel on the
sub-menu. Click on that and this opens up MySQL for Excel window at the
right hand side of the Excel.
5) Connect to the Local Instance of MySQL in the Excel.
6) Select a Database Schema, where the excel data needs to be imported.
7) Click on Export Excel Data to New Table
8) Export Data to MySQL pop-up window opens. Give a name for the new table.
9) Click on Export Data
10) If there is a variation in data length, after the 1000th row "An Error
occurred" message is displayed
11) Click on Show Details to see a detailed error message.
Error while inserting rows...
MySQL Error 1406:
Data too long for column 'TELEPHONE_NO.' at row 1482
Suggested fix:
Can the number of rows in
Use the first 1000 Excel data rows to preview and calculate data types.
under Export Data to MySQL --> Advanced Options --> Advanced Export Options
be increased to select the total number of rows in the excel sheet?
Description: MySQL for Excel would not create tables and insert data while importing data from excel worksheet, when length of data of a column increases after the 1000 rows maximum limit mentioned under Advanced Export Options MySQL Server Version : 5.7.11-log MySQL Community Server (GPL) MySQL for Excel Version : 1.3.6 Microsoft Excel Version : 2007 OS : MS Windows 7 Professional - 64 bit OS .NET : Microsoft .NET Framework 4 Client Profile Visual Studio : Microsoft Visual Studio 2010 Tools for Office Runtime (x64) I am trying to import pincode data of a state (Karnataka) in India from a excel sheet which has the following columns: OFFICE NAME OFFICE STATUS PINCODE TELEPHONE NO. TALUK DISTRICT STATE/U.T. POSTAL DIVISION POSTAL REGION POSTAL CIRCLE One row sample data from the excel is given below : Katkol S.O Sub Post Office 591114 08335-2562130 RAMDURG BELGAUM KARNATAKA BELGAUM NORTH KARNATAKA KARNATAKA The TELEPHONE_NO column size created by Excel is VarChar(12) The Telephone No. for the row 1482 (in excel) - where the mysql error (1406) was reported is: Row 1482 - 08335-2562130 which has one extra digit (08335-256213(0) in this case), than the other. Telephone No. for rows that are before 1482 which is given below : Row 949 - 08288-233130 Row 950 - 08288-233205 Row 961 - 08288-275240 Row 954 - 08288-223221 Error Message Reported is given below: Error while inserting rows... MySQL Error 1406: Data too long for column 'TELEPHONE_NO.' at row 1482 This happens for all rows above 1000 where the data length is greater than the specified data length for that column. However if I manually edit the column data type to VarChar(25) for the TELEPHONE_NO column, the table is created and rows are inserted successfully in mysql table. How to repeat: Install MySQL for Excel either stand alone or through MySQL Installer -- For Download - http://dev.mysql.com/downloads/windows/excel/ Documentation - http://dev.mysql.com/doc/mysql-for-excel/en/mysql-for-excel-install.html 1) Start MySQL instance in Windows. 2) Open karnataka.xls which has more than 1000 rows data with increasing data length after 1000th row. 3) Select all the rows in the excel to be imported. 4) Click on Data Menu in the excel - which displays MySQL for Excel on the sub-menu. Click on that and this opens up MySQL for Excel window at the right hand side of the Excel. 5) Connect to the Local Instance of MySQL in the Excel. 6) Select a Database Schema, where the excel data needs to be imported. 7) Click on Export Excel Data to New Table 8) Export Data to MySQL pop-up window opens. Give a name for the new table. 9) Click on Export Data 10) If there is a variation in data length, after the 1000th row "An Error occurred" message is displayed 11) Click on Show Details to see a detailed error message. Error while inserting rows... MySQL Error 1406: Data too long for column 'TELEPHONE_NO.' at row 1482 Suggested fix: Can the number of rows in Use the first 1000 Excel data rows to preview and calculate data types. under Export Data to MySQL --> Advanced Options --> Advanced Export Options be increased to select the total number of rows in the excel sheet?