Unsupported Screen Size: The viewport size is too small for the theme to render properly.

Import Excel Data – Incorrect Values

forums › forums › SQLyog › Using SQLyog › Import Excel Data – Incorrect Values

Tagged: 

  • This topic is empty.
Viewing 2 reply threads
  • Author
    Posts
    • April 24, 2015 at 11:43 am #35322
      Abhishek Kumar Pandey
      Member

      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.

    • April 24, 2015 at 2:01 pm #35323
      fvdan
      Member

      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!

    • April 27, 2015 at 6:12 am #35324
      Abhishek Kumar Pandey
      Member

      Thanks for the info  🙂

  • Author
    Posts
Viewing 2 reply threads
  • You must be logged in to reply to this topic.