Appending leading Zeros and writting to excel file

neo637 · Post by **neo637** » Wed Feb 11, 2009 11:22 am

Hi,

I am trying to generate excel file in data stage.
In doing so i have a problem, When i am trying to append leading Zeros to a filed and witting into excel file all the leading zeros are stripped off.

Can some one help ma in solving this.

Thanks in advance.

chulett · Post by **chulett** » Wed Feb 11, 2009 11:27 am

First, define "excel file" - .xls or .csv? And are they "stripped off" in the file you generated or after loading the file into Excel?

neo637 · Post by **neo637** » Wed Feb 11, 2009 11:32 am

Hi Craig,

the excel file is .xls.
The leading zeros are stripped off after witting in to xls file.

Thanks

gateleys · Post by **gateleys** » Wed Feb 11, 2009 1:01 pm

neo637 wrote:Hi Craig,

the excel file is .xls.
The leading zeros are stripped off after witting in to xls file.

Thanks

That's because the column format is Number and not Text, and hence, Excel considers the leading zero useless... and deletes it. You will need to change the column to Text.

ray.wurlod · Post by **ray.wurlod** » Wed Feb 11, 2009 4:15 pm

Or change the format of the cells if they really must be Number.

It's NOT a DataStage issue.

neo637 · Post by **neo637** » Thu Feb 12, 2009 6:14 am

Hi

Thanks for ur sugession.
Yes even i do agree with your comments.
Its not a datastage issue but the cell properties of .xls file are always
taken as general by default.

When the generate the file the defaulf cell prperties are General, So could you please help me is there any way can we change the cell properties ti text or Number dynamically.

Sainath.Srinivasan · Post by **Sainath.Srinivasan** » Thu Feb 12, 2009 6:40 am

Work in csv or txt.

Prefix all with a dummy character so all will be text.

ray.wurlod · Post by **ray.wurlod** » Thu Feb 12, 2009 1:28 pm

Can you prefix the result with a single quote character, so that Excel treats the value as literal text?

neo637 · Post by **neo637** » Fri Feb 13, 2009 1:00 am

Yes i even tried out with the prefix (') quote charector.
Its is working fine, But the i have one more concern here.
If some system process the same excel file what i generated will the value of the filed will have quote?
Like if they trying to load the same file in to a Database then that particular filed wil have only Number value in the Table or with quote and Number.

Could you please help me in this.

Or some one can suggest if there is any way handling the properties of an .xls cell with UNIX shell scripting.

ray.wurlod · Post by **ray.wurlod** » Fri Feb 13, 2009 4:08 am

There is no way to manipulate an Excel workbook with UNIX scripting because Excel does not run on UNIX machines.

In my experience the only effect of the leading single quote is to force Excel to accept the following value as text. There are no real data types within Excel - if that value can participate in numeric operations (that is, if that value is numeric in any sense) then it will not throw an error.