Leading zero in to csv file send to sftp | Community
Skip to main content
G
Greeshma_Sampath1
Level 4
December 6, 2021
Solved

Leading zero in to csv file send to sftp

  • December 6, 2021
  • 2 replies
  • 3710 views

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_ 

@debtr 

@jyoti_yadav 

 

Thanks & Regards,

Greeshma

 

 

This post is no longer active and is closed to new replies. Need help? Start a new post to ask your question.
Best answer by Greeshma_Sampath1

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

2 replies

Manoj_Kumar
Community Advisor
Manoj_KumarCommunity Advisor
Community Advisor
December 6, 2021

@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?

Manoj  | https://themartech.pro
G
Greeshma_Sampath1Author
Level 4
December 6, 2021

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
Darren_Bowers
Level 9
December 7, 2021

@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

    NJT562
    NJT562
    Level 2
    December 9, 2021

    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?

    G
    Greeshma_Sampath1AuthorAccepted solution
    Level 4
    December 10, 2021

    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

      Terms & ConditionsAccessibility statement

      Loading footer...