How do I stop the Input Tool from adding zeros to columns in my csv. file?
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
Solved! Go to Solution.
- Labels:
- Input
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Any chance you can include the file here? Is this happening for other csv files?
Best,
mmenth
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.