ZIP codes keep coming out as number in output csv

Hello,

I've looked through many answers and I feel that my workflow is correct as each step shows leading zeroes on the file output but when I actually open the Excel CSV file, it's showing ZIP codes as numbers and dropping the zeroes. Any help is really appreciated. Thanks in advance.

FA Workflow Test.yxmd

Datasets

Accepted answers

ChrisTX

You're likely facing an Excel "feature", not an Alteryx issue.

Open the CSV file with a text editor. I'm guessing the leading zeroes for ZIP actually exist in the file.

Instead of opening your CSV file quickly in Excel (double-clicking the file name, clicking the link for the Output Data tool in Alteryx, etc), take the longer road:

Open the Excel program
Choose menu option Data > Get Data > From File > From Text/CSV
Browse to the folder that contains the CSV and open the CSV
Now Excel will open the CSV file with a wizard, giving you the option to change how Excel is working
In the wizard, click the button for Transform Data at the bottom right
For your ZIP, click one cell in that column or click the column header
On the right under Applied Steps, click the red X next to Changed Type

Screenshot 2023-12-07 052107.png

There's probably a way to save your Transformation steps. But I haven't worked much with this feature. I just know that Excel is not always the best tool to view data, because it tries to "help" by changing the way the data is displayed.

I use Notepad++ frequently for data research.

Chris

All comments

ChrisTX

You're likely facing an Excel "feature", not an Alteryx issue.

Open the CSV file with a text editor. I'm guessing the leading zeroes for ZIP actually exist in the file.

Instead of opening your CSV file quickly in Excel (double-clicking the file name, clicking the link for the Output Data tool in Alteryx, etc), take the longer road:

Open the Excel program
Choose menu option Data > Get Data > From File > From Text/CSV
Browse to the folder that contains the CSV and open the CSV
Now Excel will open the CSV file with a wizard, giving you the option to change how Excel is working
In the wizard, click the button for Transform Data at the bottom right
For your ZIP, click one cell in that column or click the column header
On the right under Applied Steps, click the red X next to Changed Type

Screenshot 2023-12-07 052107.png

I use Notepad++ frequently for data research.

Chris

dYoast

Add a single quote to the front of the Zip Code field before writing to the csv.

This tells Excel to interpret the field as a string instead of a number and will keep leading zeroes.

Brettschenk

Thank you! Your way of opening the file worked so then I went looking for a more permanent solution and found:

Excel Options > Data > Automatic Data Conversion > Remove Leading Zeros and convert to a number

Since we deal with ZIP codes a lot and need the files in CSV for import into other software, I think this should be the way forward.

Appreciate it.

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703