Leading zero in to csv file send to sftp

[Greeshma_Sampath1]

Hi All,

Iam sending files to sftp with a set of values from different columns from a schema using delivery template.

One of my field in schema has string value length as 9 digits. So if the number is "1234567" it is stored in the schema as "001234567". But when we see this value in the csv file from sftp the leading zero is not coming. I tried adding Tostring to the filed from delivery template also but it didn't work.

Could someone please help me know what the issue can be?

@DavidKangni 

@Manoj_Kumar_ 

@Deb_Tripathy 

@Jyoti_Yadav 

Thanks & Regards,

Greeshma

[Manoj_Kumar_]

@Greeshma_Sampath1 Ideally it shouldn't happned unless there is an enrichment which is adding 00 as a prefix.

Can you check the data flowing through your transitions to identify which activity is altering the data?

[Greeshma_Sampath1]

Hi @Manoj_Kumar_ 

The flow contains only query and delivery activity and the delivery activity transition data contains leading zero but still after the delivery template runs the file doesn't have leading zero.

In transition

In the sftpfile

[Darren_Bowers]

@Greeshma_Sampath1- if you are opening the CSV file in Excel, it will convert the zero padded text string to a number and remove the leading zeros. You should open the CSV in a text file editor like Notepad++ or alternatively Import the CSV into Excel and ensure the column for "Next Appointment Site ID" is imported as a text column and not auto-imported as a numeric.

Cheers

Darren

[Greeshma_Sampath1]

Hi @Darren_Bowers 

The file coming from the delivery template is first stored in teh server and that server file has the leading zero for the column i mentioned. But when the same file is moved to sftp the leading is not coming.

To move the file from server to sftp i have used data loading activity with the column i need leading zero as string.

Is something I am missing in the process while exporting?

Thanks & Regards,

Greeshma

[Darren_Bowers]

Hi @Greeshma_Sampath1 - I don't think there is anything wrong with your process.

In your screenshot above you are opening the CSV file in Excel. Excel will auto-convert the text column into a numeric column when you open it. That is likely why you are seeing the leading zeros missing.

Don't use excel to open the CSV file. Use a text editor and take a look at what the column data looks like.

[NJT562]

Have you tried opening the CSV file after it's transferred with a text editor instead of Excel... Excel will drop leading zeros if you open a CSV file no matter what you format the fields as unless you open Excel and then import the file and set the column to text instead of General. Your process is working the issue is how you are reviewing the data. If the end user of the data is opening the file in Excel and saying there is an issue you may just need to train them on how to open CSV in Excel without losing leading zeros. Or alternatively have you considered outputting the file as Pipe delimited or Tab delimited or even comma delimited but with a txt extension instead of csv?

[Greeshma_Sampath1 · Accepted solution]

Hi All,

Thank you  for all the suggestions but I have used the below code in the fields expression in delivery template  and after that I am getting the leading zero.

'=TEXT('||<column name>||',"000000000")'

Thanks & Regards,

Greeshma