topic Re: quoting - non-standard characters in QlikView

quoting - non-standard characters

Thu, 05 Jan 2012 16:44:46 GMT

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

quoting - non-standard characters

swuehl — Thu, 05 Jan 2012 17:11:47 GMT

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?

Re: quoting - non-standard characters

Miguel_Angel_Baeyens — Thu, 05 Jan 2012 17:16:14 GMT

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