So I have a dataset which I want to change it dynamically. Input: E_NameaccountphonecheckAlex1235768491leo8903450 For the above data I want to change it with the below type and size. NameTypeSizeE_NameV_WString167AccountDouble8 phoneInt6434checkDouble8 . So for an example, If E_Name type is V_string so we have to change it to V_WString and if size is 255 I have to change it to 167 (dynamically)

Great post! The workflow you provided is extremely helpful! I am able to change the datatype without changing the order of the column!!

Dynamically Change the data type

Sshasnk

So I have a dataset which I want to change it dynamically.

Input:

E_Name	account	phone	check
Alex	123	576849	1
leo	890	345	0

For the above data I want to change it with the below type and size.

Name	Type	Size
E_Name	V_WString	167
Account	Double	8
phone	Int64	34
check	Double	8

So for an example, If E_Name type is V_string so we have to change it to V_WString and if size is 255 I have to change it to 167 (dynamically)

Workflow

Developer

Common Use Cases

Accepted answers

All comments

alexnajm

Could you use the Auto Field tool?

Sshasnk

@alexnajm Actually in every run I have to change the data type, and check if it matches to the below data type

alexnajm

As long as the name remains the same then, a Select tool should work fine!

Sshasnk

@alexnajm Umm no actually, the data type list I mentioned above is coming from different source and I have to keep constant. Plus I have 250 fields and I have to convert it every time in the select tool.

alexnajm

I’m not sure the problem is clear then - can you explain further? Having your workflow plus data would help us see the issue.

From the description above, it sounds like you just need to resize fields to which the Select tool will work. Once you set it once, you can copy and paste the same Select tool to keep the configuration and apply it to a different source.

Sshasnk

@alexnajm Hi Sorry, I could not explain the issue. I cant share the worklfow because of the confidentiality reasons.

But let me break down the issue for you.

1. I have a data set from excel which has 250 fields, Which I want to push it into the database.

2. My data gets errored out because the data type in the database is different and my data has different data types.

3. I cant use select tool because I dont want to do this in every run because the db team changes something is the back end I get an error and I have to change the data type for all the data.

4 So to resolve this I am calling the DB (Input tool) and checking the database data type (using info tool) <- Once I get the data type I want to replicate the same data type in my data. So it wont error out.

So, if you see the second table that is an example which I am getting DB info (Step 4)

I hope this helps

danilang

Hi @Sshasnk

All the config info of the various tools is stored as XML, so we just need to build the correct XML for a Select tool, using your field types and sizes and then pass this XML as a Control Parameter to macro. That's what the top branch of the workflow does.

Inside the macro, the Action tool uses the Update Raw XML with Formula to update the entire xml of the action tool.

Here are the type and sizes of the fields in the Data before and after the Macro

Before Before

After After

In your case connect the output of your Field Info tool to first formula in the top branch. The only issue is your data type input shows the length of an Int64 as 34, but in most data bases it is 8.

Dan

DynamicallyChangingTypes.yxzp

apathetichell

while there's no real reason not to use @danilang 's suggestion - if you are scared of editing the raw XML because you were terrorized as a young child by an evil Uncle who forced you to endlessly build SOAP API calls - you could do some dynamic changes in a batch macro as attached. note the output has to be unioned together in a way I personally find annoying. I've thrown in some dynamic select tool and summarize tools to drop the offending nulls.

Alternative way to change columns.yxzp

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703