Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

quoting - non-standard characters

I have a text file to import where the fields are separated by carets (^) and the text fields are surrounded by tildes (~). I figured out how to use the custom delimiter but how do I specify tildes as the quoting character? Is this even possible?

Thanks,

Jimmy

Tags (2)
2 Replies
MVP
MVP

quoting - non-standard characters

I think the only place where you can specify the quoting style is in the format specifier, where you can specify 'msq' (modern style quoting) or 'no quotes'. I think standard quoting using single or double quotes is default.

So have you tried importing your file with one of these settings and then REPLACE or PURGECHAR the tildes in your text fields?

MVP
MVP

Re: quoting - non-standard characters

Hi Jimmy,

Since it's not possible to tell QlikView what character is the quoting symbol, you can do something like the following:

Data:

LOAD

SubField(Replace(@1, '~', Chr(39)), '^', 1) AS Field1,

SubField(Replace(@1, '~', Chr(39)), '^', 2) AS Field2,

// and so

FROM

File.txt

(txt, codepage is 1252, no labels, delimiter is '^');

That will load each row, storing each string separated by "^" as a different field, and replace the "~" char for the single quote (ascii 39) or any other more suitable character, or even nothing.

Load will take longer since it's doing some string formatting, but it might do.

Hope that helps.

Miguel

Community Browser