If you are unable to create a new account, please email support@bspsoftware.com

 

News:

MetaManager - Administrative Tools for IBM Cognos
Pricing starting at $2,100
Download Now    Learn More

Main Menu

Leading zeros stripped in csv output

Started by wbarry123, 16 Dec 2010 04:45:15 PM

Previous topic - Next topic

wbarry123

Does anyone know how to maintain leading zeros in a characterLength16 field? 
Cognos 8.3
SQL Server

cognooby

When you say leading 0's were stripped, do you mean to say that you exported a CSV out of Cognos, but then tried to open it in excel and noticed the leading 0's missing?  If so, that is a known issue with excel.  Please open the CSV with a text editor before opening it in excel and see if the leading 0's are there.  If yes, then you need to look up the excel leading 0 issue on google etc for a workaround.
Here's one:
_hxxp://social.msdn.microsoft.com/Forums/en/isvvba/thread/aae07b39-865f-4c68-a07f-7cad2dfd6733

wbarry123

I am not trying to open in Excel.  I produce the output file as csv (tab delimited) and when I open in notepad, the leading zeros are stripped.

Lynn

I am going to make a guess ... the leading zeros you see when you run in other formats might be coming from a data format applied in the layout. The padding does not exist in the datasource and would not come through when run to CSV.

If I'm correct, then you would want to modify the query item expression to include the zero padding. Something like:

right('0000000000000000' || char([Database Layer].[EU_EQPMASFL].[EMCATG]),16)

If I'm wrong ... well ... wouldn't be the first time.

wbarry123

Actually not.  The data filed is a login ID that is 10 characters long.  About half of the ID's are in the following format:
0123456789
and the rest are:
1234567890

the customer needs the leading zero to not be stripped when exporting to csv.  I can force it to display the zeros in Excel but that does not serve their needs.

CognosPaul

How are you viewing the CSV? If it's in Excel then Excel would strip out the zeroes. Try viewing the CSV in a text editor, it's possible the zeroes may actually be there.


wbarry123

I am reading the file in notepad and the zeros are not there.

sir_jeroen