Print Wizard Query Numbers And Letters Follow

0
Avatar
Legacy Poster

I am using Print Wizard Query to select specific records from an Excel DB (.xls). The query uses product item numbers, half of which are only numbers (e.g. 1234567), and the other half that use a single letter suffix "U" (e.g. 1234567U). 

 

The Query doesn't recognize any of records with the "U" suffix, and when I go in to look at the database records through BT, those item numbers in the Excel file have been removed and the cells are empty. No other fields in the Excel file have had this issues and they're a combination of numbers, text, UPC codes, etc. 

 

As the fields are empty, I then get an error code when I try to print with the Wizard: OLE DB OX80040E14, IDispatch Error #3092

 

 

2 comments

0
Avatar
Fernando Ramos Miracle
Moderator
Comment actions Permalink

Hello EAB,

 

1.    Please note that Excel is not a database, it is a spreadsheet application where its documents (worksheets) can contain tabulated data rather like a standard database table.  We would always recommend using a proper database if possible, although Excel can suffice in many occasions.  Please note that yours is an issue not confined to BarTender alone, but to any application which connects to a data file of some sort.
 
2.    The main problem with Excel is the way in which it stores data.  The underlying data is stored in a single standard way using a standard, "one size fits all" data type.  This is necessary to maintain the flexibility of what can be entered into individual cells.  Excel can then apply extra formatting to the data when presenting it in the Excel workbook.  When an external application connects to the underlying data of a work sheet the ODBC or OLE DB driver requires that a data type be selected for each column of data.  The driver makes a best guess based on the data for each column for the first few rows excluding the field header.  Any data that does not fit within the data type specified by the driver gets dropped by the driver which is why you are seeing missing field data.
 
Example if field A is assigned a numeric data type based on the data for the first few rows, should it then come across alpha-numeric data for the field the data type would not match and the data will be lost, not even making it to BarTender.  Formatting of cells in Excel is not relevant as the driver does not use it as this is an Excel only process.
 
You can force Excel to store data as a string (text) data type by prefixing cell values with a ['] mark.  For example:
 
'12345678
'ABCDEFGH
'1234ABCD
 
Alternatively you could save the Excel file as a CSV file which is in effect a text file using comma and quote delimiters.  The formatting done in Excel with the exception of the ' prefix has absolutely no effect on the underlying data, which is why it makes no difference when read with BarTender.
0
Avatar
Legacy Poster
Comment actions Permalink

Gracias Fernando. Totally makes sense and I'll follow your recommendations. 

Please sign in to leave a comment.