How to change any string into V_WString

[jensroy]

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.

[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 · Accepted solution]

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 · Accepted solution]

 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.

[MarqueeCrew]

Hey @jensroy,

Do you have CReW macros?  I may have over-engineered this solution, but here's a picture:

• TOP:  Get 1st record, then skip it.  What I'm doing here is getting the output field order to be exactly the same as the incoming data.
• BOTTOM:  I am finding all STRING fields.
  • Top:  I am setting up a rename for all incoming STRING fields to become "XX_" + Name.  I will later De-Select all original STRING Fields.
  • Bottom:  I am creating a formula to name all string fields without the "XX_" and changing the type to V_WString without changing the length.
• MIDDLE:  I am renaming strings and applying the dynamic formula to create the original named fields as V_WString.

Then I union the format from the top with the data and it works.  As I said, it might be over-engineered but it works.  I am not packaging the workflow.  I will place the workflow as is into the post.  

Cheers,

Mark

[MarqueeCrew]

@jdunkerley79,

Do you like my non-macro approach (uses a CReW macro)?

Cheers,

Mark

[jdunkerley79 · Accepted solution]

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

[jensroy]

Awesome, this worked well! I will dive in to in more on monday to see what is going on under the hood.

[jensroy]

Very clever! This is my first time to see/use the "update raw xml". Thanks for showing me this :)