Your achievements

Level 1

0% to

Level 2

Tip /
Sign in

Sign in to Community

to gain points, level up, and earn exciting badges like the new
Bedrock Mission!

Learn more

View all

Sign in to view all badges

SOLVED

Leading zero in to csv file send to sftp

Greeshma_Sampath1
Level 4
Level 4

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_ 

@Deb007 

@Jyoti_Yadav 

 

Thanks & Regards,

Greeshma

 

 

1 Accepted Solution
Greeshma_Sampath1
Correct answer by
Level 4
Level 4

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

View solution in original post

7 Replies
Manoj_Kumar_
Community Advisor
Community Advisor

@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
Level 4
Level 4

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

Greeshma_Sampath1_0-1638808453222.png

 

In the sftpfile

Greeshma_Sampath1_1-1638808522544.png

 

 

Darren_Bowers
Community Advisor
Community Advisor

@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
Level 4
Level 4

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?

Greeshma_Sampath1_0-1638893889880.png

 

Thanks & Regards,

Greeshma

 

Darren_Bowers
Community Advisor
Community Advisor

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
Level 2
Level 2

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
Correct answer by
Level 4
Level 4

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