Leading Zero Issue in CSV/Excel Formatterd Report

Your rating: None Average: 4.2 (5 votes)

If you use APP ENGINE to report the data from the temp table /staging table on to CSV file using excel format, there is a known issue with fields that have leading zeros. For ex:You have a field of length 6 characters. If the field value is 002123, the excel file removes the leading zeros. But if the requirement is to preserve those leading zeros, we can do something like this.
Update the temp table right before you write out into a file.

Update %Table(Temp_Stg) A
Set A.Field = (Select '"='||A1.Field||'"' from  %Table(Temp_Stg) A1 where A1.Keyfield = A.Keyfield)
Where A.Process_Instance = A1.Process_Instance

Basically , excel file can retain the leading zeros if the field is of the following format : "=Field"

Regards
Shiva

Please try to help out with unanswered topics on the forum. Chances are you have had the same issue/question some time in your IT career!

Comments

Have a question? Please ask it on the forum instead.
Deepthi's picture
User offline. Last seen 8 years 32 weeks ago. Offline
Joined: 07/23/2008
Posts: 1
Re: Leading Zero Issue in CSV/Excel Formatterd Report

Hi Shiva,

As you specified an excel file can retain the leading zeros if the field is of the following format : "=Field", I tried to implement it in SQR and here is my code looks like:

begin-select
pw.mmc_kronos_payrule
'"='||pw.position_nbr||'"' &pw.position_nbr

and in the show statement I can see sth like "=00002345", but when I open a CSV file its truncating the leading zeros.
Please advise where I did wrong.

Thanks in advance,
Deepthi

Guest's picture
Guest (not verified)
Re: Leading Zero Issue in CSV/Excel Formatterd Report

Actually we also got the same problem when i loaded the data into csv file, as per the proprites of the csv it does not show the leading zeros, but when we copy it to other format or when we use that csv file for other purpose, it shows the actual number with zeros. we also tried with '=field name, but it shows leading zeros in the csv file,but when u copy this file into other format or when u use this csv for other purpose, u will get that value with concatination of '=field name.

Lepa's picture
User offline. Last seen 1 week 6 days ago. Offline
Joined: 06/23/2008
Posts: 591
Re: Leading Zero Issue in CSV/Excel Formatterd Report

Let $QUOTEEQUAL = '",="' !trick to prevent leading zero's getting dropped in Excel

Quote:
and in the show statement I can see sth like "=00002345"

It looks like you need a "," right before your "="

Give back to the community and help it grow!
* Help with unanswered forum questions and issues
* Register or login to share your knowledge at your own blog

XLent's picture
XLent (not verified)
Re: Leading Zero Issue in CSV/Excel Formatterd Report

Easy.
(but tricky, like all things microsoft...)
Before creating the csv file, insert an ASCII carriage return (NOT carriage return line feed or line fee) in front of the zero-starting string (say, a US East Coast Zip code like 08172) .
Enclose the whole thing in quotes, like you would a string with a comma in it
"[CR]08712"
(The [CR] represents whatever you have to input to the csv generating program to generate an ascii carriage return character)
XL does not recognize it has to do anything with the [CR] when it imports the csv automatically, but it treats whatever follows as a string, not a number.
Viola! (Tested up to XL XP only)

Guest's picture
Guest (not verified)
Re: Leading Zero Issue in CSV/Excel Formatterd Report

Hello!
I would need help displaying the leading zero values when importing csv files into excel WITHOUT having users formatting the cells/columns.
Thank you.
John

Pplsoft user's picture
Pplsoft user (not verified)
Re: Leading Zero Issue in CSV/Excel Formatterd Report

Hi

I tried "=00123" and ",=00123". Both didnt work.
Place the equal sig outside the quotes and it works ="00123"

Peter.Wan's picture
Peter.Wan (not verified)
Re: Leading Zero Issue in CSV/Excel Formatterd Report

Hi All,
I haved solved the problem by this way!
&DE = """" | Char(9) | """";
&Data_Str = &Data_Str | &DE | """" | "=" | """" | &EMPLID;

Guest's picture
Guest (not verified)
Re: Leading Zero Issue in CSV/Excel Formatterd Report

this works. i tried exporting to .txt and using the excel wizard but that's too much work for users. i also tried ="

this seems to be the best and easiest solution.

thanks.

Guest's picture
Guest (not verified)
Re: Leading Zero Issue in CSV/Excel Formatterd Report

Here is the Solution if you are loading from a SQL to CSV format.

This worked for me

'="'||value_u_want_pass||'"'

Hope this works.

Thanks,
Nitin Singh

Guest's picture
Guest
Re: Leading Zero Issue in CSV/Excel Formatterd Report

It works! It is exactly what I wanted. Thank you so much!

Guest's picture
Guest
Re: Leading Zero Issue in CSV/Excel Formatterd Report

Today is Dec 13, 2013 and a Friday to boot. I have been searching for HOURS today trying to find an answer on how to retain leading zeros in CSV. I'm using .Net to read in a text, do some processing and output a .csv file. Your solution worked for me ... over 4 years later! THANK YOU SOOOOO MUCH!!

Guest's picture
Guest
Re: Leading Zero Issue in Fixed length while using File Layout

Hi Friends.

I have the same kind of issues while writing fixed length file.
My requirement is to write a Fixed length file for this am using App engine and file layout.

In my file layout I have some number fields and those number filed values like 001 or 002 or 033...

When I write the a fixed length for this number field values are showing like 1 or 2 or 33...

Could any one help me how to print leading zeros in my fixed length files ?

Guest's picture
Guest
Re: Leading Zero Issue in CSV/Excel Formatterd Report

It worked '="' + field + '"'

Guest's picture
Guest
Re: Leading Zero Issue in CSV/Excel Formatterd Report

This solution works for Excel 2010 BUT it does not work for Excel 2013. Does anyone have a method to keep leading zeros when opening the CSV file using Excel 2013? When I use the methods above Excel 2013 drops the leading zeros.

Post new comment

CAPTCHA
The question below is to prevent automated spam submissions.
4 + 13 =
Solve this simple math problem and enter the result. E.g. for 1+3, enter 4.