topic Re: Proposed enhancement, specify text enclosure for loading text files in QlikView

Proposed enhancement, specify text enclosure for loading text files

Fri, 29 Apr 2011 14:49:26 GMT

I have used QlikView a few times now with great pleasure but there is one thing that puzzled me but now I finally found the problem.

When importing a text file into QlikView that uses text-enclosure it is not possible to specify the enclosure itself. This is a problem when you import a text-file that uses the double quote as an enclosure but one or more of the fields contain a single quote at the beginning.

Perhaps this is a problem that only the Dutch face because some of our street- and city names start with a '....

You can reproduce the problem by creating a csv-file that uses "" as the enclosure:

---- 8x --------------------------------------------------------------------------------------
0Correct?;1Field1;2Field2;3Field3;4Field4
Yes;abcd;efgh;klmn;"op""qr"
Yes;abcd;'efgh;klmn;opqr
No, field 1 and 2 are concatenated;'abcd;efgh;'klmn;opqr
-----------------------------------------------------------------------------------------------

When you import it into QlikView it will not go as planned

The script that was used:

LOAD * FROM <file.csv>
(txt, codepage is 1252, embedded labels, delimiter is ';', msq );

It does not matter if you use MSQ or standard.

I would really like it if an option was added allowing you to specify the enclosure. For example:
LOAD * FROM <file.csv>
(txt, codepage is 1252, embedded labels, delimiter is ';', enclosure is '"', msq);

Have any of you had similar problems and how have you solved them and would you also like to have this feature added?

I replaced the ' in the textfile [:S]

Proposed enhancement, specify text enclosure for loading text files

Miguel_Angel_Baeyens — Fri, 29 Apr 2011 15:04:28 GMT

Hello,

Use the "No quotes" instead of MSQ or standard:

LOAD @1, @2, @3, @4, @5FROM file.csv (txt, codepage is 1252, no labels, delimiter is ';', no quotes);

As a temporary workaround, you can replace the character when loading, then replace it again. Or use instead a two step SubField() (or a one step should the number of fields is fixed):

Table:LOAD SubField(@1:n, ';', 1) AS Field1, SubField(@1:n, ';', 2) AS Field2, SubField(@1:n, ';', 3) AS Field3FROM file.csv (fix, codepage is 1252);

Hope that helps.

Proposed enhancement, specify text enclosure for loading text files

Fri, 29 Apr 2011 15:17:46 GMT

Hi Miguel,

Thank you for the suggestions. The first won't work because the file contains the delimiter ";" in the fields as well. By not using standard or new style quotes this will not work I think.

The second suggestion might work, but I think I would run into the same problem with fields containing the delimiter.

A better example of my problem would have been the following "file":

0Correct?;1Field1;2Field2;3Field3;4Field4
Yes;abcd;efgh;klmn;"op;qr"
Yes;abcd;'efgh;klmn;opqr
No, field 1 and 2 are concatenated;'abcd;efgh;'klmn;opqr

This example goed wrong when importing with no quotes.

I have also tried the second solution on it and it gave the same problem which I would expect.

Re: Proposed enhancement, specify text enclosure for loading text files

Sun, 03 Feb 2013 22:46:34 GMT

I am faced with a comma separated file that includes double quoted field values that include commas.

Thus,

value1, value2, "value list, 1,2,3", value4

is a data set of four values with the third value containing commas.

I think the text-enclosure option is an excellent idea.

How do we get this in the QV queue?