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

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.

inputissue1.PNG

Input

Accepted answers

MarqueeCrew

@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

All comments

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.

inputissue2.PNG

MarqueeCrew

@helenjin1 ,

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

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.

excelddrdata.PNG

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703