Bug #92390 Unable to import data to Excel from MySQL database.
Submitted: 12 Sep 2018 15:40 Modified: 28 May 2019 21:04
Reporter: Madhav Vij Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL for Windows: MySQL for Excel Severity:S2 (Serious)
Version:MySQL Server = 8.0.12; OS:Windows (MySQL for Excel Version = 1.3.7)
Assigned to: CPU Architecture:Any
Tags: Failure to Import Data, Incorrect Column Mapping, issue

[12 Sep 2018 15:40] Madhav Vij
Description:
[Incorrect Column Mapping while importing data from Database to Excel (using MySQL for Excel plugin)]

Task:
Connect MySQL database table to excel sheet and import data.

Issue:
While importing data, first the error observed was: [Error: Error copying table data, Input string was not in a correct format]. To remedy this, every column in database was changed to varchar.

Error Description:
|| Exception message:
|| Input string was not in a correct format.

|| Exception thrown by:
|| MySqlDataTable.CopyTableData

|| Stack trace:
||    at System.Number.StringToNumber(String str, NumberStyles options, NumberBuffer& number, NumberFormatInfo info, Boolean parseDecimal)
||    at System.Number.ParseInt32(String s, NumberStyles style, NumberFormatInfo info)
||    at System.String.System.IConvertible.ToInt32(IFormatProvider provider)
||    at System.Data.Common.Int32Storage.Set(Int32 record, Object value)
||    at System.Data.DataColumn.set_Item(Int32 record, Object value)

On resolving it, the data imported is not correctly mapped to the column name (which I suspect was actually causing the issue with importing as MySQL for Excel was trying to map int type data to a varchar column).

How to repeat:
MySQL Server Version: 8.0.12
MySQL for Excel Version: 1.3.7
OS Version: Windows 10 (1803) 64 bit.

Table name: consultant
Table create statement:
CREATE TABLE `consultant` (
  `id` int(11) NOT NULL,
  `email` varchar(45) DEFAULT NULL,
  `sow_id` varchar(255) DEFAULT NULL,
  `title` varchar(50) DEFAULT NULL,
  `fname` varchar(50) DEFAULT NULL,
  `mname` varchar(50) DEFAULT NULL,
  `lname` varchar(50) DEFAULT NULL,
  `consultant_email` varchar(50) DEFAULT NULL,
  `pnum` varchar(20) DEFAULT NULL,
  `dob` varchar(200) DEFAULT NULL,
  `ssn` varchar(10) DEFAULT NULL,
  `e_area` varchar(100) DEFAULT NULL,
  `e_years` varchar(10) DEFAULT NULL,
  `dirname` varchar(50) DEFAULT NULL,
  `manname` varchar(50) DEFAULT NULL,
  `dirname_other` varchar(100) DEFAULT NULL,
  `manname_other` varchar(100) DEFAULT NULL,
  `start_date` varchar(200) DEFAULT NULL,
  `estart_date` varchar(200) DEFAULT NULL,
  `anthem_proj` varchar(100) DEFAULT NULL,
  `anthem_manager` varchar(50) DEFAULT NULL,
  `client_location` varchar(100) DEFAULT NULL,
  `anthem_id` varchar(100) DEFAULT NULL,
  `job_status` varchar(10) DEFAULT NULL,
  `c2c_payrate` varchar(10) DEFAULT NULL,
  `c2c_details` varchar(255) DEFAULT NULL,
  `feedback` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

> Open Excel
> In "Data" tab, select "MySQL for Excel"
> click on consultant table and select "Import MySQL Data"
> Error: Input string not in correct format.
> Change the int type column to varchar in database (id).
> Repeat above steps.
>Issue: Column mapping is wrong (data randomly mapped to different columns).

Suggested fix:
Correcting column mapping for data will resolve the issue.
[12 Sep 2018 16:55] MySQL Verification Team
Thank you for the bug report.
[18 Dec 2018 5:01] Javier Treviño
Hi Madhav Vij,
The bug report was incorrectly validated (the error reported by Miguel does not remotely resemble what you report).

Can you please clarify the steps you are following to reproduce the error?
I am not understanding what you are doing, I used the CREATE TABLE statement you supplied where every column is varchar except for the id, since you did not supply any sample data, I populated the table with some data and I am able to import it to Excel.

What's the exact problem you are running into?
You mention several times the word "mapping" but you are not stating what is being mapped with what.
Thanks.
[28 May 2019 21:04] Javier Treviño
Since no feedback was given after my last reply, and reading the bug report again, I will assume this is a duplicate of bug 93501 already fixed.