Dynamically change data type of fields

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.

Dynamic Processing

Accepted answers

All comments

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.

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?

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703