community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Community v19.6

Looks aren't everything... But the latest Community refresh looks darn good!

Learn More
SOLVED

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

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. 

 

Fireball

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

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.

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

Bolide

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

 

Best,

mmenth

Alteryx Certified Partner
Alteryx Certified Partner

@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 reboot. Order shall return.
Highlighted
Aurora

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 

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