Uploaded image for project: 'MariaDB Connector/ODBC'
  1. MariaDB Connector/ODBC
  2. ODBC-18

only first half of each VARCHAR value is displayed in Excel

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 1.0.0
    • None
    • None
    • None
    • Windows 7 Professional, Service Pack 1, 64-bit OS.
      (Version 6.1.7601 Service Pack 1 Build 7601)
      Office Professional Plus 2013, 32-bit.

    Description

      Load this in your MariaDB:
      create table show_truncation (
      ID INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
      LOAD_TIME TIMESTAMP NOT NULL,
      LABEL VARCHAR(20),
      A_NUMBER DOUBLE(20,10)
      );

      insert into show_truncation (LABEL, A_NUMBER) VALUES ('ABCDEFGHIJKLMNO', 1234567890.0987654321), ('Z',9.87), ('YX',55555.55555), ('PQRS',1234);

      Use ‘Microsoft ODBC Administrator’ (32-bit version: C:\Windows\SysWOW64\odbcad32.exe) to create a DSN for your MariaDB database. Leave all the option checkboxes blank/unchecked.
      In a new Excel file, select cell A1, then click Data->From Other Sources -> From Data Connection Wizard
      Select “ODBC DSN”, click Next
      Select the ODBC Source you just created, click Next.
      Select the table “show_truncation”, click Next
      Click Finish in the “Save Data Connection File and Finish” dialog.
      Click OK to insert the data into a cell of the spreadsheet.
      Notice the values in column C (“LABEL”) are truncated:
      • the “ABCDEFGHIJKLMNO” value only shows ABCDEFG
      • the Z is completely missing
      • YX shows only Y
      • PQRS shows only PQ.
      The ID, LOAD_TIME and A_NUMBER are OK.

      (screen shot and .xlsx file are attached below)

      More Info: Strangely, this additional sequence of steps shows the full values are being sent from the database.
      In Excel, select the “Design” tab (if not already there).
      Click the down-arrow below “Refresh” (in “External Table Data” section of the ribbon), select “Connection Properties”.
      Click on the Definition tab.
      Click “Edit Query…”
      When the warning dialog appears (“This query cannot be edited by the Query Wizard”), click OK.
      ‘Microsoft Query” window appears, and shows the full values in the label column!

      Attachments

        1. show_truncation_screenshots.docx
          193 kB
          Danny Read
        2. show_truncation.xlsx
          10 kB
          Danny Read

        Activity

          People

            Lawrin Lawrin Novitsky
            mystryd Danny Read
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.