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

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

 


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
    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...