How to read first single-quote from Excel data

[atsushi_saijo]

In the current project, I must load an excel data, which is a string literal. Sometimes there are cases 'xxx' in excel. I have to parse exactly how business has described it because it is legally-binded texts.

Now, when I tried loading, QlikView load it as  xxx' instead of 'xxx'.

Would you possibly advise how we can workaround about the omission of single quote in excel data?

[stigchel]

Maybe this post will help you:

Escape sequences

[kuba_michalik]

I don't think it can be done, not because of QlikView, but because Excel itself treats this single quote differently - it means "text follows", and this quote is not shown even in Excel. Trying to load it in would be like trying to load formula text instead of formula result into QlikView.

[atsushi_saijo]

Hi I went thorugh on HIC's comment, but it is how to escape character on QlikView. However, since single quote is in Excel-side, and QlikView does not take it, do we have any way to handle this problem?

[atsushi_saijo]

It seems you are right. So I think I would store the excel as something else, and parse it.

[giakoum]

Did you try to add a single quote in your code, if a single quote is present (the closing one)?

For example:

if index(yourExcelField, '''), chr(39) & yourExcelField, yourExcelField