Some Unicode characters are not convertable by Alteryx workflow

[kmoon]

Hi,

Recently I went through an experience where despite using the function TitleCase(DecomposeUnicodeForMatch([_CurrentField_])), some unicode characters are not passed through the workflow and write to database. One of the column field had value of K’collon. Alteryx didn't recognize this character and truncated all the value after this character. I see only the value K in the database table. This special character is typed by Alt+0146. The symbol is well observed in Excel.

Can Alteryx provide any mean to recognize this character and pass all the string after this special symbol to database?

Your response would be appreciated.

Thanks.

[kmoon]

Hi,

I have a column that has a special character in between the string '. I used a field formula using the DecomposeUnicodeForMatch([_CurrentField_]). Say the field value is    L'Zola,California .

When getting the output, it shows the conversion errors stating that 

ConvError: Multi-Field Formula : DecomposeUnicodeForMatch: Some Unicode characters were not convertable ("L'Zola,California").

The corresponding output in the DB table is coming to a single character for all the values. However, when I run the workflow using a Browse tool or excel file, it provides the full value instead of single character.

Does it have anything to do with the special character of  '   as shown above.

Would appreciate your response.

Thanks.

[TrevorS]

Hello @kmoon 

I would recommend checking out the Code Page help document. Depending on your source document, you may be able to have your Input tool read in the CodePage (#11 on the input data Options) to read for Unicode UTF-8.

I would recommend starting here and see if this takes care of the issue for you. I would also look at the Functions help page, check the examples.

Thanks!

TrevorS

[kmoon]

Hi @TrevorS ,

Thanks for your reply. 

The source file is in Excel (.xlsx) format. So the input tool doesn't have the option #11 for CodePage to read for Unicode UTF-8.

One interesting thing observed is, in the Alteryx browse tool, the out put showing is K?collon for the corrupted value.

Could you provide some insight if ConvertfromCodePage or ConvertotoCodepage might help?

Any other suggestion is welcomed.

@AdamR_AYX : Could you show some light w.r.t the problem?

Thanks.

[AdamR_AYX]

Can you supply an example data file?

The attached seems to read that character OK for me.

[JohnJPS]

Hi @kmoon 

Interestingly, we just had a similar question (over here ... @Fnold, FYI), where I recommended using DecomposeUnicodeForMatch rather than R code... however we also found the solution for the R code, and it works for that character:

x <- read.Alteryx("#1", mode="data.frame")
x$outputField <- iconv(x$inputField, from="UTF-8", to="ASCII//TRANSLIT")
write.Alteryx(x, 1)

This will convert [inputField] to [outputField], replacing special characters with their ASCII equivalent. It also converts the special quote to a regular quote.

Hope that helps!

John

[kmoon]

Hi @JohnJPS ,

I checked using the R-tool functions too. I am getting rid of the display K?collon for the value K’collon in the browse tool.

The R functions used is:

x <- read.Alteryx("#1", mode="data.frame")
x$outputfield <- iconv(x$Name, from="UTF-8", to="ASCII//TRANSLIT")
write.Alteryx(x,1) .

However, when it is writing down the database column, it still writes it as K .

Is the issue in database?

What if I replace the specific character ’ by '   

Would appreciate your feedback.

Thanks.

[JohnJPS]

I'm not fully understanding the issue... even after converting to ASCII, it fails to write to the database?

I've attached a workflow containing some of the words/characters in your post, where it analyzes each character:

So, for "outputField" here, every character is in the normal range... I could comprehend the database choking on the one character that has an INT value of 8217, (part of inputField), but anything from outputField really should be OK.

[kmoon]

Hi @JohnJPS ,

For an alternate solution, I tried to replace the character ’ with ' using Regex_Replace([Inputfield],"’","'") . It converted the value in browse tool and excel file. However when writing to the database, it again shows single character and not the whole value.

My database column size is 150. Does that impacting?

Thanks.

[JohnJPS]

Just out of curiosity, what if after doing the Regex_Replace, but before writing to SQL, you replace all ' with a double ''. The theory being that these may need to be escaped when writing to SQL.  Just a thought.