Change ColumnType based on separate table

I have “Table 1” with data and I have a separate “metadata table” containing column name and column type (string, double, integer, date, etc). Is there a way to change the Column Type for columns in Table 1 based on the metadata table? Im not sure how to do this dynamically since there can be more columns in the Table 1 then defined in the Metadata table.

hi @vpinrange ,
I’ve tried to address this problem with this workflow

Input data
immagine

Type conversions (only 5 cols out of 6 need a type conversion)
immagine

The loop converts the column tables by target type group.
Different type conversions must be managed by different, dedicated nodes. The metanode “Type switch” contains 4 of these nodes, each of wich can be customised according to your needs


“column_type_no” identifies the target branch of the switch. E.g. the columns you want to convert to int must have column_type_no=0. The column_type string is a description that can be suppressed

Result
immagine

2 Likes

Not bad @duristef .

Just one comment, your workflow assumes that all input columns are of type string. There can be cases where an input column is already of the correct type, in which case “String To X” node would probably fail. For eample, if column4 is already of type date as input, I think the workflow would complain.

@bruno29a , Yes, you are right, it would raise an error. But it’s sufficient to ignore the columns that don’t need to be converted in the table “columns”, as in the case of column 6

@duristef and @bruno29a This is really interesting, I will test it out this weekend. I wonder if you can join Table spec to find the current column type and filter to string type columns only, this would address the error.

For now I was able to use Column Auto Type Cast Node, and realized that all date fields end with a specific sub-string. Then used String to Date&Time Node with wildcard selection using the substring to convert these fields from Date/Time to Date (yyyy-MM-dd). The only problem is that I get errors when if the on the String to Date&Time Node when there is a row that does not have a date value (i.e. “y2022q4”). However, this issue is not related to our current topic as I have addressed it with string manipulations to remove these values.

@vpinrange As I explained to Bruno, there is no error to address. In my example, the “data” table has 6 columns
immagine

but I want to convert only the first 5, so I simply ignore column6 in the second table, called “columns”
immagine

The workflow doesn’t touch it. In other words, only the columns you explicitly mention in the “columns” table are converted, the others are left the same, be they strings or whatever type else

I see, this makes a lot of sense. Ok I will test it out and provide an update. Thank you

1 Like

This worked as expected. Thank you.

2 Likes

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.