Dynamically change data type of fields

[Meet_Nandu]

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 -

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. 

[Emil_Kos]

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]

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]

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]

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]

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]

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

[OllieClarke]

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

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.

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.
 

[jemaleng]

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.

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 : 

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

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

It will add a row to the xml : 

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

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

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.

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]

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