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

[helenjin1]

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. 

[kelsey_kincaid]

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.

[helenjin1]

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]

@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]

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

Best,

mmenth

[MarqueeCrew · Accepted solution]

@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

[danilang]

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]

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.