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)

[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

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

[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. 

[Lichunhuang]

Great post! 

The workflow you provided is extremely helpful! 

I am able to change the datatype without changing the order of the column!!