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

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

1111

Kindly help for CSV format.

Output

Accepted answers

cjaneczko

@Ram_RK Check the Advanced tab?

All comments

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.

Screenshot 2024-07-02 200453.png

2124.yxmd

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

@Ram_RK Check the Advanced tab?

apathetichell

@Ram_RK - are you saying when you open the file in Notepad that you see the leading zeros? Again - not an Alteryx issue here - but if you are for some insane reason going to post process your csvs in excel and check for accuracy in excel which you know auto converts junk - prepend an apostrophe in your workflow.

If you have an issue with Alteryx not producing the leading zeros - upload some samples - if not follow @cjaneczko 's instructions. You don't have a data issue - you have an excel/enduser issue.

Let me be clear - from a data ingestion standpoint 0000005558 and '0000005558 are not the same. if you ingest your data downstream in something real (ie excel is not real) you will screw up your ingestion system if you add the apostrophe's natively to your .csv files. if you know you are processing csvs of over 1mm records why would you check this in Excel? Ignore it. Load it in Polars locally - and see if you get the leading 0s.

Ram_RK

thanks for the details. @cjaneczko I will ensure the same.

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703