How to validate datatypes of columns in csv file.

[Simi_M]

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

[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

[Ben_H]

Hi @Simi_M,

CSVs are by their nature text files. They do not retain the data type of the column they came from.

Regads,

Ben

[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?

[Jean-Balteryx]

Do you have a file or a reference containing the rules ?

Cheers,

Jean-Baptiste

[Ben_H · Accepted solution]

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

[Simi_M]

Great... Many thanks .... I understand what you are saying but some times you get requirements like this where you do not have any other option. Somebody, told me to explore calling python scripts and perform validation in Python only. 

you are a savior!!!