Problem in files from UNIX Server

[Former Member]

Dear All,

We are facing a problem in loading Excel files when we are trying to load from Excel File Format.

Here is a sample input which we are giving.

1327334

65767502

66674783

This is going through excel files. I have about 100 excel files to be loaded everyday. And this was working fine when my DS was installed on WINDOWS environment.

But when we shift to UNIX. The output of this file format comes as :

1327334.0

6.5767502E7

6.6674783E7

I have no clue currently why is it happening and how to correct it.

I cant change for everyfile so a standard solution is what i am looking for. If i change to .csv , this works fine, but i cant change my 100 files to CSV as there are some complications in it.

I am on Data Services 3.2.

Kindly suggest.

[Former Member]

DS uses different methods for reading from excel on windows and Unix so problem could be with the Unix interface

what is the datatype of the column after its imported in DS ? double or varchar(255) ?

[Former Member]

thanks for the reply Manoj.

Its varchar 255.

Please suggest how can we correct this if we are using UNIX.

[Former Member]

any idea gurus ?

Manoj, do you need any other information please let me know ..

I am really struggling to resolve this. I have raised it to SAP as well but n response yet.

[Former Member]

the sample excel file that you have attached to SAP Incident work correctly in both windows and linux

when I import the excel I see the datatype of the field as double not varchar(255), can you attach the excel that imports the field as varchar ?

[Former Member]

Hi Manoj,

Thanks fro you response.

I couldnt clearly get you.

You want me to attach a sample file in the SAP Incident which i have opened ??

If yes, i did attach a file with name FILE1 yesterday to it.

Let me know if i got your correctly.

Regards,

Manu

[Former Member]

yes, I want you to attach the excel that behaves differently on windows and unix to the sap incident I will take it from there

[former_member184494]

Sometimes this issue can also be caused due to th transfer mechanism - Excel files are usually transferred using Binary method ... can yu check this ..?

I am no expert in Data Integration - I am more of SAP BW - but we have had similar issues when transferring files in Binary mode as opposed to ASCII mode which causes variations...

[Former Member]

HI Manoj,

I have attached a file with name FILE1.

This file works well in WINDOWS but not in UNIX.

Let me know if you are able to see it or should i reattach it.

Regards,

Manu

[Former Member]

in the xls file attached to case the first column has first few rows with numeric data and rest with character data

when you import an excel the datatype is determined from the first 8 or 10 rows in this case the datatype of the column will be imported as double, not sure how it got imported as varchar(255) for you, did you change it after import ?

with datatype as double the columns with character data will not be read or will be read the value as NULL

anyways I didn't see any mismatch in data when reading it from windows or Linux (didn't try on other Unix, should be same since the interface is same)

I moved the character rows to the begining, and created a new excel format in DS, the datatype is imported as VARCHAR(255) by default and in both winodws and Linux I can read all the rows, no data mis-match

let me work with support on this

[Former Member]

Hi Manoj,

Did you get any solution to this ?

Regards,

MG

[Former Member]

I tried this on Windows and Linux, but the behavior is same in both

which Unix you are using ? SUN, AIX, HP ? let me try with that

[Former Member]

Thanks for looking into this.

AIX 6.1 is what we are using right now.

We are still struggling to resolve this. It would be great if you could help.

Regards,

Manu

[Former Member]

I am able to reproduce the issue, I see the problem if excel column has numeric data, the column datatype in DS when importing this excel is double and after importing the excel the datatype is changed to varchar(255)

I see problem only for the numeric data that has 8 digits, in the M1.xls if you remove 1 digit from the rest of the rows the data is read correctly, if you see, only the first row is read correctly which has only 7 digits

not sure if the problem is OS Specific with converting a double to varchar or Excel Adapter specifc, I will have to debug this