Needed Leading Zeros in the data feilds while saving the data in CSV format

[Ram_RK]

Hi All,

While I am saving the CSV format I am missing all the leading zeros added to the Data attributes, Since the data is huge I am using CSV format, 

Kindly help as I tried to convert V_String to String using Formulae tool its not helping out to save the data with leading zeros

For Ex Input file has field records like 

00000000012

00000001111

00000000001

While saving in CSV output file I am getting like 

12

1111

1

Kindly help for CSV format. 

[Raj]

@Ram_RK 
this is due to excel property
but when you open the file you will get an pop up then click on don't convert
pop up screen shot attached below
hope this helps.

[Ram_RK]

Actually, I am not getting the pop up for  "don't convert" 

Is there any other alternative to save the data with the leading zeros in CSV format?

[apathetichell]

So a few questions:

1) are you opening your .csv file to check for leading zeros in excel or notepad? as @Raj was pointing out - excel may not be showing you what you have in your data.

2) In Alteryx do you have the leading zeros (ie is your column a number or a string). It needs to be a string. You need your leading zeros in Alteryx. If you converted to a number - covert back to a string and padleft with the number of leading zeros. Sometimes a source system converts out the zeros - so you'll need to readd them in workflow via the padleft function.

3) If you see the leadinger zeros in Alteryx - but don't see leading zeros in the file. Run again. You could have opened in excel and screwed with it because excel does stuff. Reminder for 1) Open in NotePad - not excel for leading zeros.

[Ram_RK]

Hi @apathetichell , yes my question is for point number 1. I'm trying to check downloading the file in .csv with the leading zeros.

My input file has leading chose in the data fields whereas my output file is not giving me the leading zeros while downloading it and saving it as .csv

[cjaneczko]

Check the input Anchor on your output tool and validate that the leading 0's are there upon export. If they are the next thing you want to check is your settings in Excel. Make sure Excel isnt bypassing the popup to Not Convert the leading 0's. You can also make it so Excel doesnt remove leading 0's by unchecking the box in the Red area in the below screenshot. The input file doesnt matter in this case as you could be doing something down stream in the workflow that is trimming the 0's off. You need to check the Output Tool anchor and validate the 0's are there.

[cjaneczko]

If the 0's arent on your Output tool then add a formula tool to add the 0's back in.

PadLeft([Field1], 11, '0')

[apathetichell]

@Ram_RK I'm not fully clear on your alteryx workflow here. what do you mean downloading? Alteryx workflows run on your desktop. 

Alteryx workflow takes in data with leading zeros.

Alteryx workflow processes data with leading zeros.

Alteyrx workflow creates .csv output.

Can you share a sample of your source data file and your output data file?

note you can see in your workflow if there are leading zeros.

usually user opens .csv in excel and says OH NO! I don't have leading zeros - which actually means user opened .csv file in excel which autoconverted out your leading zero. Is this a problem - sure - but it's not my problem because I'm here to help you with Alteryx - not excel.

Sometimes zeros are dropped in Alteryx. This would be because a) they weren't there at ingestion - thanks excel. or b) they were convereted out or c) other.

[Ram_RK]

@apathetichell I am aware about the automatic data conversion is applied by default but needed to know how to disable it using Alteryx or in Excel Output. 

Why I am using .csv and not using .xlsx.? - It doesn't have row limit and I deal with records more than 1048576 rows.

In Excel we use Prefixing a number with Single Quote to treat the number as text

For Ex Entering '00123 in a cell will display 00123 in Output as it treats the cell as Text. Can we apply that logic in Alteryx for output 

@cjaneczko  - I am using Microsoft 365 I hope my screenshot below shows different settings without that options you showed above.  Can you suggest or help me here. 

My Excel Data settings not giving me the Automatic Data Conversion option as in Microsoft 365 the settings are different.

[cjaneczko · Accepted solution]

@Ram_RK Check the Advanced tab?