How to change any string into V_WString

Hello,

I am looking for a way to create a macro that converts any string field into a V_WString.
First I will use auto field to convert all fields into the smallest size. Then I will convert all strings that are not V_WString into V_WSting.

Why:

Auto field converts this "MAATALOUSYHTYMÄ ISTOLAHTI MARJA, ISTOLAHTI MIKKO, ISTOLAHTI PÄIVI JA ISTOLAHTI TAPIO" into a V_String with length 84. And yes, counting characeters of this string is indeed 84.

When I use Alteryx to write this data to SAP HANA then I get an error saying "Value too large for column". It will not fit into a column of 84 in width.
If I output to SAP HANA using V_String, the column is created as a VARCHAR. If I output as V_WString then the column is created as NVARCHAR2.
The value in question takes up 86 characters as VARCHAR but only takes up 84 characters as NVARCHAR2.

So I wish to convert any string type into V_WString to make sure we have one way that will always work.

Macros

Database Connection

Accepted answers

jensroy

It almost worked!

If only I did not have to define the column size as well (each field will get the same column value).

Thanks though, it was a great suggestion

jdunkerley79

That was my first thought but preserving the size is hard! If not an issue the use @MarqueeCrew approach. If however you want to preserve size and V_ ness then I have attached a macro and batch macro which should do what you need.

It gets the incoming field type and filters to non-unicode strings. It then converts these in a dynamically constructed select within the batch macro. This results in preservation of all the sizes.

The main catch in this version is column order is not preserved. I can enhance this to do that if needed.

Dynamic String Change.yxzp

jdunkerley79

@MarqueeCrew Oooh dynamic formula construction - very nice

Gist of it is similar but I built a dynamic select tool and it builds a dynamic formula tool.

All comments

MarqueeCrew

One way might be to use a multi field formula. By selecting all strings fields you can use the current field as the formula and change the data type on exit.

Cheers,

Mark

jensroy

It almost worked!

If only I did not have to define the column size as well (each field will get the same column value).

Thanks though, it was a great suggestion

jdunkerley79

It gets the incoming field type and filters to non-unicode strings. It then converts these in a dynamically constructed select within the batch macro. This results in preservation of all the sizes.

The main catch in this version is column order is not preserved. I can enhance this to do that if needed.

Dynamic String Change.yxzp

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703