Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

How do I stop the Input Tool from adding zeros to columns in my csv. file?

helenjin1
8 - Asteroid

Hi Alteryx Community!

 

Something weird is happening with my Input Tool. When I input a csv file, it adds zeros (up to 7 characters) in a certain column, FDA Application Number.

 

How do I make the input tool put my csv file as is? 

 

I've attached what I'm seeing below. For example the FDA application column has 0202008 and 0007259. I need the numbers to show up as 202008 and 7259. 

 

7 REPLIES 7
kelsey_kincaid
12 - Quasar

Hi @helenjin1 

I'm not sure of a way to prevent this from happening through the input tool as those zeroes are likely present in some way in your CSV file. However, you can always trim the zeroes off after the input tool using a formula. A simple TrimLeft formula should get your data formatted the way you'd like. An example is below.

 

TrimLeft.PNG

helenjin1
8 - Asteroid

Thanks for the reply!

 

They aren't in my csv file (attached a photo). Which concerns me because I'm worried that the data in other rows might be messed up too. 

 

It's odd that extra zeros are getting added, I've haven't changed the delimiters or anything when I used the input tool.

patrick_digan
17 - Castor
17 - Castor

@helenjin1 Try opening your csv in notepad to check if it has leading zeros. Perhaps excel is recognizing that column as numeric and removing leading zeros. Alteryx reads everything in a csv as a string.

mmenth
11 - Bolide

Any chance you can include the file here? Is this happening for other csv files?

 

Best,

mmenth

MarqueeCrew
20 - Arcturus
20 - Arcturus

@helenjin1 ,

 

Excel is funny (hahahaha).  It gives you the ability to mask the field and doesn't differentiate from the value to the mask in what is displayed.  Say you read the data as an amount, you could see $1,234.00 but the real data was 1234.  In this case, I think that there is a "General" mask for the column.  It recognizes all the values as numbers (strings can start with a 0, while numbers can't).  

 

If you change the data type to a NUMBER (e.g. Double or INT64), you'll see the leading 0's disappear.  But be careful, if there are empty or spaces or missing values, they will convert to 0.

 

Cheers,

 

Mark

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
danilang
19 - Altair
19 - Altair

Hi @helenjin1 

 

AS @patrick_digan suggested, it's not Alteryx that's adding zeros, it's Excel that's removing them.  Take the step to examine your CSV in Notepad or another text editor and you'll see that alteryx is importing your data exactly as it is in the file.

 

Dan 

helenjin1
8 - Asteroid

Thanks for the reply! I checked on Notepad and there are no leading zeroes (huh, odd right?). The csv file is too big to be attached on here but I've put up a screenshot of what I'm seeing.

 

For now I'm going to use the select tool and change the values from V_String to Int64.

 

Labels