Field conversion errors (Nulls and datetime)

[eric_gauthier]

Hello,

I am running into errors when deigning a simple Excel to SQL transfer program. I will break them out by type below.

1. String (text) in number column: - I have a couple columns that are dollar values, but instead of writing $0 it has a text string for some reason. I tried to solve this by forcing the type to Double the with Select tool, then Imputing to replace Null with "0". When I run it I get a conversion error "...is not a valid number." This is on every null value, even after I tried to use the Data Cleansing tool to remove unwanted characters and letters. As a matter of fact, the Data Cleansing step is removing 0 rows for some reason.

2. I read that Alteryx uses ISO DateTime configuration DD-MM-YYYY HH:MM:SS. I'm using Microsoft SQL Server and the column I am trying to load to is also DateTime but "Processing Date 5/18/2023 is not a valid DateTime." When I change it to just Date, I get that "5/18/2023 is not a valid date." Maybe because it isn't a two digit month? 

3. I simply cannot get the program to actually process the file like I have for other similar situations.

[binuacs]

@eric_gauthier Alteryx default datetime format is YYYY-MM-DD  HH:MM:SS, so if you have a date in a different format you need to convert that into Alteryx DateTime type using the DateTimeParse() function. In your case if your date is in the format of 5/18/2023 then it should convert using the DateTimeParse([Date Field],'%m/%d/%Y') this will convert the date into 2023-05-18

for your first question, Anything other than numbers Alteryx is considered as string. $0 is actually string type and if you convert it into a number it will throw an error. If you want to convert $0 into number first you need to remove the $ then convert it into number. For this use the formula toNumber(ReplaceChar([Field],'$','') ), this will convert the field into double

[Yoshiro_Fujimori]

Hi @eric_gauthier ,

As @binuacs explained, when "$0" and "5/18/2023" are input, Alteryx stores them to String column.

To deal with them effectively on Alteryx, you need to convert them.

Input Data

Metadata

In order to convert "Data" and "Date", you need to use Formula tool as @binuacs mentioned.

The below expression creates a new column [Number] with data type [Int16].

It removes "$" and then converts the result to number, so that the result can be stored Integer field.

The below expression converts the string "5/18/2023" to Date type, and stores the result to [Date] type.

If you need to manipulate "time", you may want to store it as [DateTime] type as below.

Output data

Metadata

If you want to learn more, you may want to take the Interactive Lessons below.

String Functions

Numeric Functions

DateTime Functions

Good luck!