How to validate datatypes of columns in csv file.

Hello,

I have a task to validate datatype of the columns and raise a flag if Numeric field is String. the file which I'm using is .CSV so when I import data into Alteryx, it by default picking that up in String format for all the columns. How can I sort out this problem .

Please help!!!

Regards,

Simi

Transformation

Accepted answers

Ben_H

Hi @Simi_M

I've attached a way of comparing the datatypes to a reference set.

I've got to be honest though I'm struggling to understand this problem.

I know you've probably just been supplied with the csv but the act of exporting to a csv effectively means that every field could be the incorrect data type. (depending on the specific formatting of strings).

What will you do with this data once you've flagged the fields that have changed data types?

Regards,

Ben

Compare DataTypes.yxzp

All comments

Jean-Balteryx

Hi @Simi_M ,

Have you tried the Auto-Field tool ? It assign the optimal type to your fields and is often paired with CSV imports.

Cheers,

Jean-Baptiste

DavidP

Hi @Simi_M

If your numeric columns have hidden characters like leading or trailing whitespace or new lines, Alteryx will load the columns as text, but if you run the worklflow, these fields will have a little red indicator in the results preview window and when you hover over it, it will tell you that there are whitespace of new lines, if that is the case.

There are other options in the Data Cleanse tool too, to remove non-numeric characters.

You can then use a Data Cleanse tool to remove this and use the Auto Field tool as @Jean-Balteryx suggested.

Simi_M

Yes I did but that does not solve my problem because my task is to check datatype of the columns if they are other than what they should be. for example, If I have a column which as per the rule should be numeric but it becomes string in csv. so Autofield will sense it to be numeric and make it numeric and when I will run my validation check it will Pass but It should Fail because originally its string in the file and same goes with Date column as well. I'm not allowed to give or change the datatype as I have to perform Validation Test on there actual datatype.

I know this is bit tricky but I'm sure there must be some way.

Regards,

Simi

Jean-Balteryx

@Simi_M ,

So as you only have strings in a CSV you want to flag fields that shouldn't be strings ?

Cheers,

Jean-Baptiste

Simi_M

Numeric and Date fields turned as String after import. Also, is there any way If we perform the test in Python and then get it into Alteryx?

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703