Allow leading zeros in exported CSV file from Electronic reporting

(0) Share

Report

Posted on by Tech T

Hi Experts,

I am using Text file with file extension as .CSV to export payment file in CSV format in which I need to show the date value format as ddMMyyyy. But, when I check the output CSV file it trims leading zero. (i.e) If the date value is 03092020, it is showing as 3092020 in CSV file. Are there any formulas or am I missing anything please help on this.

Output CSV file:

I have the same question (0)

All responses (7)

Answers (2)

nunomaia 25 Moderator on at

Like (0)

Report

Can you open the CSV in Notepad ? Excel converts strings to number and removed leading zeros in values.

Was this reply helpful? Yes No
Suggested answer

nmaenpaa 101,162 Moderator on at

Like (1)

Report

Check the file with notepad, most likely there's nothing wrong with the exported data, instead it's the Excel cell formatting.

Was this reply helpful? Yes No
Suggested answer

Ludwig Reinhard Microsoft Employee on at

Like (0)

Report

Hello Tarique,

Can you please check your excel template that was used for this electronic report.

In the template you can format the cells as you like.

I wonder if the cell is formatted as an Excel text cell and then attached/linked to your electronic report configuration whether this fixes the issue.

I would expect that but have not tested that.

I leave that to you ;-)

Best regards,

Ludwig

Was this reply helpful? Yes No
Tech T 10 on at

Like (0)

Report

Yes Nuno & Nikolaos.

I can the see the expected output when I open the csv file from Notepad. But, after setting screen as ER destination system is using Excel to open the csv file and also it stores the file with excel format in ER reporting jobs. If I upload payment file to Bank SFTP folder as the destination, I wonder How Bank will open the file to process. Do you have any idea?

Was this reply helpful? Yes No
Verified answer

nmaenpaa 101,162 Moderator on at

Like (1)

Report

Banks obviously process the files automatically, and can definetely handle leading zeros in fields where they are supported.

Was this reply helpful? Yes No
Tech T 10 on at

Like (0)

Report

Hello Ludwig,

I have not used excel template in my reporting. If this is a right way to export payment file in csv format, Kindly provide material links.

Was this reply helpful? Yes No
Verified answer

WillWU 22,363 on at

Like (0)

Report

Hi Tarique,

If the column is a fixed eight digit number in Excel, then you could try to set the cell format.

Was this reply helpful? Yes No