topic How to read first single-quote from Excel data in QlikView

How to read first single-quote from Excel data

atsushi_saijo — Thu, 18 Jun 2015 12:42:53 GMT

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?

Re: How to read first single-quote from Excel data

stigchel — Thu, 18 Jun 2015 12:53:19 GMT

Maybe this post will help you:

Escape sequences

Re: How to read first single-quote from Excel data

kuba_michalik — Thu, 18 Jun 2015 12:54:49 GMT

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.

Re: How to read first single-quote from Excel data

atsushi_saijo — Thu, 18 Jun 2015 13:49:26 GMT

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?

Re: How to read first single-quote from Excel data

atsushi_saijo — Thu, 18 Jun 2015 13:50:43 GMT

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

Re: How to read first single-quote from Excel data

giakoum — Thu, 18 Jun 2015 13:52:34 GMT

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