ZIP codes keep coming out as number in output csv

[Brettschenk]

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.

[ChrisTX · Accepted solution]

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

 

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

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