Early bird tickets for Inspire 2023 are now available! Discounted pricing closes on January 31st. Save your spot!

Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer and Intelligence Suite.

Dynamically change data type of fields

Meet_Nandu
6 - Meteoroid

Hi everyone. 

 

I have a bunch of files with different columns in each file. I want to upload these files in a SQL database. I have created a macro wherein I can read each file and it even writes to the database I mention.

The problem I am facing now is that all the fields in all the files are being imported as a string, by default as it is from a flat file.

I want to dynamically change the data type of each field. I have another file mentioning the file (table name) along with the column (name) and the data type I want. Is there any way to automate this? The file looks like this -

 

Meet_Nandu_1-1606484464630.png

The table name is the file name which will contain the given columns. And in the last column I have the data type in which I want the column to be imported. 

9 REPLIES 9
Emil_Kos
17 - Castor
17 - Castor

Hi @Meet_Nandu,

 

I know that it isn't a solution that you are looking for but did you tried auto field tool?

 

Maybe it is worth to give it a try and compare if you will not receive the same results.  

Meet_Nandu
6 - Meteoroid

Hi @Emil_Kos,

 

I did try the auto field tool. But I have many columns like Account Number which needs to be in string format but the auto field tool converts it to Double or Int data type. 

Emil_Kos
17 - Castor
17 - Castor

Hi @Meet_Nandu,

 

Alternatively you can use dynamically select tool.

If the columns are always in the same order you can try to dynamically select them by using a field number. 


Please check the article below:

 

https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/Tool-Mastery-Dynamic-Select/ta-p/89...

 

As this is quite complex tool. Maybe you will find an inspiration. 

 

I will wait for suggestions of others because your problem is really interesting and I wonder if there is a way to import the metadata from the excel file as I am not aware of it. 

Meet_Nandu
6 - Meteoroid

Hi @Emil_Kos,

 

I have used the dynamically select tool in my workflow as there are many unwanted columns in the raw data. Unfortunately, there is no way that I can change the data type for the columns as all my files have a different number of columns and all in no order. 

 

Even I tried researching a lot but to no avail. I am hoping that I get a solution from the large community we have here. Thanks for your input! 

DavidP
17 - Castor
17 - Castor

Hi @Meet_Nandu 

 

You're right, the Dynamic Select tool can't change data types. In fact, the only tool (as far as I know) that can dynamically change data types is the Multi-Field Formula Tool, but you can only pick one data type at a time to change.

 

So, armed with this knowledge, I played around with a batch macro. Attached is an example of a batch macro and workflow. It uses a reference list like the one in your post with the field name and desired datatype as the control parameter input. The macro then takes one field at a time and changes the datatype, but the result is that every field is in a different row for every record after the macro is run, so you have re-combine them back to a singe row for each record, hence the summarize tool.

 

Have a play with it - it probably needs a bit more fine-tuning. The macro needs to be updated for multiple filenames.

 

DavidP_0-1606492940879.png

DavidP_1-1606493269617.png

 

 

DavidP
17 - Castor
17 - Castor

Something like this will ensure the datatypes for the right file are picked for the macro.

 

DavidP_0-1606495182391.png

 

OllieClarke
14 - Magnetar
14 - Magnetar

Hi @Meet_Nandu I took a slightly different approach to @DavidP, combining dynamic selects and the autofield tool.   

OllieClarke_0-1606495699767.png

This didn't work perfectly, as there were a couple of fields which had a smaller/different data type to what I was hoping for.

OllieClarke_2-1606495821202.png

The benefit of this approach is that it will run no matter the number of columns, or what their names are. It can also convert Spatial data. The drawback is the limitations of the autofield tool. It does protect strings from the autofield tool though.

 

Spoiler
I also tried to build a completely successful converter using the join multiple tool and many dynamic select/multi-field combinations, however when one of the inputs to the join multiple tool didn't contain any data, the tool would error. If you can be sure that all required data types will be present, then you could take the below approach, which would allow you to control precisely what type data was (e.g. the difference between int16 and int32), but that felt unlikely to me.
 OllieClarke_3-1606496069750.png

 

 

 

jemaleng
5 - Atom

Hi, 

 

A bit late but I have recently faced the same case so I'm sharing here the solution.

For the data type, you'll indeed need a mapping between alteryx data type and sql data type.

 

To dynamically change the data type based on an input file, the result can be achieved with a batch macro and a select tool, using the Interface/Action tool on the select tool with an 'updtate Raw XML with Formula' action type, modifying the xml properties of the select tool.

 

jemaleng_0-1657806352716.png

 

Actually, an .yxmd is nothing else but an xml configuration script that has node property for each tool and link that define the branch and its origin tool and destination tool. 

 

Alteryx offer through the 'update raw xml with formula' to directly update the properties

In the select tool, when you see the table : 

jemaleng_1-1657806465124.png

 

 

All per default options are not shown in the xml equivalent (e.g default data type assignation)

jemaleng_2-1657806506238.png

 

However, if I modify my first field 'MANDANT' to a string of a size of 50 : 

jemaleng_3-1657806554875.png

 

It will add a row to the xml : 

jemaleng_4-1657806575563.png

 

So in our case, we can use this in the update raw xml with formula by using the batch configuration input that would contain the data type definition and size for each field.

 

Do the following actions : 

1) In your original file, add a column via formula tool that should follow the following structure, taking the data type and size from your first columns : <SelectField field="MY_FIELD_NAME" selected="True" type="MY_FIELD_ALTERYX_DATA_TYPE" size="MY_FIELD_ALTERYX_DATA_SIZE" />

 

2) After the formula, use a transpose tool in order to make a concatenate of all the scripts rows (eventually put a 'new line' character as separator, so it's more readable. You have only one line remaining

 

3) Make a macro following my original screenshot

jemaleng_0-1657806352716.png

3) Configure the action tool with 'Update Raw XML with Formula', click on 'selectFields' for the xml element to update, select 'update inner xml' (so you only update the content of the selected node) and for the formula, just put the content of the control parameter

jemaleng_5-1657806954420.png

 

4) Pass the unique row column per file generated at step 2 to the control parameter of the macro --> This will replace the xml content with the generated xml based on your data type needs.

 

It's done, In my example, I had a MANDANT field that was considered as a byte, and I cast it to a string 50 with this method.

 

jemaleng_6-1657807160974.png

 

 

It's a one process per file and seems better than the other solutions involving the creation of more rows to finally regroup them.

 

 

Mayank68
5 - Atom

Can you please attach your workbook or share the formula you mentioned in Step 1?

Labels