ETL and Data Integration tools - Modern ETL software for everyone

Hi,

I am using Advanced ETL Processor Pro version 4.2.1.1

I have a transformation that is reading an excel file. There is a "shipped quantity" field with a numeric value that is formatted as 'General' in excel. I am loading this value to a table with a field named 'Units'.

The transformation returns an error message "Can't convert value General to NUMBER for field UNITS". Advanced ETL Processor Pro is actually reading the value in the field as 'GBeneral'. See attached screen shot. When I reformat the "shipped quantity" field in the source excel file to 'Number', the transformation then reads the value properly.

This problem happens only when the excel file is generated in an older version of excel (.xls). It works fine when the excel file is generated from a new version of excel (.xlsx)

Unfortunately, I cannot control the version of excel our client is using.

I don't remember this being a problem prior to the enhancements made to Advanced ETL Processor Pro pertaining to excel. Can a change be made to correct this?

Thanks for your time and assistance.

Steve V

Steve

Can't see any screenshots here.

It would be great to see the data and reader settings.

we use to use odbc driver for excel before.
Unfortunately it does not work with mixed data types.
You may use it if you wish, just create odbc or ole db connection

Mike

I am attempting to attach the reader settings as well as a screenshot of the data as it is being read by Advanced ETL Pro.

I don't understand why it works for a new verion of excel (.xlsx) and not (.xls)

I am not able to open the file you provided

I don't understand why it works for a new verion of excel (.xlsx) and not (.xls)

xls is a binary format
xlsx is a zip file with xml files inside

If you rename it into zip you will see what I mean

Mike

Got your file

Interesting.. I have never seen anything like this before

Can you email us the actual excel file please

Mike

Steve the problem you reported was resolved in latest version

4.2.2.4

Changes are

MD5 transformation function was added
Scripting language was extended
Various Excel related bug fixes
Documentation update

Mike