Database records does not match or missing data with Excel database Follow

Avatar
Andrew Broughton

BarTender Content Team


Symptom

BarTender is connected to an Excel spreadsheet, but the data populated on the label does not match Excel or is missing entirely such as the example shown below: 

mismatch.png

Environment

BarTender Designer

Diagnosis

This issue is generally related to the format of the field in your Excel file. When a column in the spreadsheet contains mixed data (i.e.: some fields are alphanumerical and other numerical.), Excel will auto-detect the type of data entered in the cells, however this auto-detection routine sometimes fails and the data type is formatted incorrectly.
To verify if this is the case, when you look at the fields in BarTender, you may see the field type is set to something like Floating Point (Double).  

2020-10-14_06_01_03-Clipboard.png

 

Solution

To fix this issue, you would change the field format to Text in Excel with the following steps: 

  1. Open the file in Excel, and highlight the column you would like to change.
  2. Under the "Data" tab in the ribbon, select "Text to Columns" 2020-10-14_06_05_32-Clipboard.png
  3. In the "Convert Text to Columns Wizard", steps 1 and 2 relate to the delimitation of the data in the field, and the default options should be fine.
    2020-11-04_06_53_35-Convert_Text_to_Columns_Wizard_-_Step_1_of_3.png2020-11-04_06_54_21-Book1_-_Excel.png

  4. For Step 3, choose "Text" under the "Column Data Format". Then click Finish. 2020-10-14_06_21_50-Clipboard.png

Save and close the spreadsheet, and close and reopen BarTender. The field will then be formatted as Text.

2020-10-14_06_23_55-Window.png

 

Internal only information

The following link explains more about the limitations of Excel and why this issue can occur.

https://support.seagullscientific.com/hc/en-us/articles/360000298388-No-Alphanumeric-Numeric-Characters-Showing-in-Objects-Connected-to-a-Database-Field

 

Do you have feedback or questions on this article? We encourage you to post them on our Community Forums