Import Excel Data – Incorrect Values

[fvdan]

I am attempting to import an Excel file and in doing so, two values are being interpreted as decimals instead of Varchars as I have specified.  This is adding a “.0” to the end of my values.

 

For example, when I load 10018, it appears in my table as 10018.0

 

The Create Table for my destination table is below.  I have adjusted the mapping in the SQLyog import wizard to force varchar on the two fields impacted:  DS_Scorecard_ID and DS_Include_Acct_01.  No other fields are loading incorrectly.  I examined the job import xml and it shows varchar as the data type for each.

 

I’m using 32 bit Enterprise, v12.1 and am importing in Excel format (MS Excel 2010, 32 bit).  Target MySQL database is 5.6.19.   Please advise – thank you!

 

CREATE TABLE `DeptDetail` (

  `OrgID` int(12) NOT NULL,

  `OrgID_L1` int(12) NOT NULL,

  `OrgID_L2` int(12) NOT NULL,

  `Payload_Source_File` varchar(200) COLLATE utf8_bin DEFAULT NULL,

  `Payload_DateTime` datetime DEFAULT NULL,

  `Payload_UUID` varchar(40) COLLATE utf8_bin NOT NULL,

  `ActiveFlag` bit(1) NOT NULL DEFAULT b’1′,

  `DS_Scorecard_ID` varchar(15) COLLATE utf8_bin NOT NULL,

  `DS_Parent_ID_L1` varchar(15) COLLATE utf8_bin DEFAULT NULL COMMENT ‘Parent scorecard ID, level 1’,

  `DS_Parent_ID_L2` varchar(15) COLLATE utf8_bin DEFAULT NULL COMMENT ‘Parent scorecard ID, level 2’,

  `DS_Parent_ID_L3` varchar(15) COLLATE utf8_bin DEFAULT NULL COMMENT ‘Parent scorecard ID, level 3’,

  `DS_Short_Name` varchar(80) COLLATE utf8_bin DEFAULT NULL,

  `DS_Title_1` varchar(80) COLLATE utf8_bin DEFAULT NULL,

  `DS_Title_2` varchar(80) COLLATE utf8_bin DEFAULT NULL,

  `DS_First_Name` varchar(80) COLLATE utf8_bin DEFAULT NULL,

  `DS_Last_Name` varchar(80) COLLATE utf8_bin DEFAULT NULL,

  `DS_Email` varchar(80) COLLATE utf8_bin DEFAULT NULL,

  `DS_Scorecard_Incr` int(11) NOT NULL AUTO_INCREMENT,

  `DS_Include_Acct_01` varchar(15) COLLATE utf8_bin DEFAULT NULL COMMENT ‘Accounting field 1’,

  `DS_Include_Department_01` varchar(80) COLLATE utf8_bin DEFAULT NULL,

  `DS_Include_Department_02` varchar(80) COLLATE utf8_bin DEFAULT NULL,

  `FV_Fix1_Applied` varchar(45) COLLATE utf8_bin DEFAULT NULL,

  `FV_Fix2_Applied` varchar(45) COLLATE utf8_bin DEFAULT NULL,

  `FV_Fix3_Applied` varchar(45) COLLATE utf8_bin DEFAULT NULL,

  PRIMARY KEY (`DS_Scorecard_Incr`,`OrgID`,`DS_Scorecard_ID`),

  KEY `IN_DS_Acct_01` (`OrgID`,`DS_Include_Acct_01`),

  KEY `IN_DS_Dept_01` (`OrgID`,`DS_Include_Department_01`),

  KEY `IN_DS_Dept_02` (`OrgID`,`DS_Include_Department_02`),

  KEY `IN_DS_Email` (`OrgID`,`DS_Email`),

  KEY `FK_Payload_DeptDetail_idx` (`Payload_UUID`),

  CONSTRAINT `FK_Organization_DeptDetail` FOREIGN KEY (`OrgID`) REFERENCES `Organization` (`OrgID`) ON DELETE CASCADE ON UPDATE CASCADE

) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8 COLLATE=utf8_bin

[Abhishek Kumar Pandey]

Hi,

 

The value returned by your ODBC driver is used for insertion into the table. You number datatype in excel is SQL_DOUBLE for odbc driver. Please refer-https://msdn.microsoft.com/en-us/library/ms712640%28v=vs.85%29.aspx

So here if you mapped DS_Scorecard_ID to Varchar(15) It doesn’t force the double value to be truncated. If you are sure you doesn’t want the value in double format(with “.0”) why not map  DS_Scorecard_ID to INT in import external wizard? I guess this will remove “.0” from the value returned from ODBC driver.

[fvdan]

Thanks for pointing me in the right direction.  The Excel ODBC driver makes some quirky assumptions indeed.  I found I was able to change the column data type within my Excel spreadsheet to text explicitly and this solved the problem.  Apparently the driver scans the first 8 rows and if a majority are numeric, it assigns the Double type.

 

I was able to do a Format Cells > Number > Text on the impacted columns and this forced the ODBC driver to send the right values to MySQL.

 

Here’s a Microsoft KB article that covers this exact issue:  https://support.microsoft.com/en-us/kb/141284

 

Appreciate your assistance!

[Abhishek Kumar Pandey]

Thanks for the info  🙂

[Author]

Posts