Excel problem with 'General' format

More
12 years 10 months ago #1203 by Steve Vaughn
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

Please Log in or Create an account to join the conversation.

More
12 years 10 months ago #1204 by admin
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

Mike
ETL Architect

Please Log in or Create an account to join the conversation.

More
12 years 10 months ago - 12 years 10 months ago #1207 by Steve Vaughn
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)

File Attachment:

File Name: DB_Soft_Ex...em_1.doc
File Size:102 KB
Attachments:
Last edit: 12 years 10 months ago by Steve Vaughn.

Please Log in or Create an account to join the conversation.

More
12 years 10 months ago #1208 by admin
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

Mike
ETL Architect

Please Log in or Create an account to join the conversation.

More
12 years 10 months ago #1209 by admin
Got your file

Interesting.. I have never seen anything like this before

Can you email us the actual excel file please

Mike

Mike
ETL Architect

Please Log in or Create an account to join the conversation.

More
12 years 10 months ago #1210 by admin
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

Mike
ETL Architect

Please Log in or Create an account to join the conversation.