Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

Qlikview delimiter 'µ' import

Hi, I'm trying to import data from a text file into Qlikview 10. The textfile uses the delimiter µ. I know how to import CSV's and other data sources, but this text file is different because it uses a different delimiter (µ). There's no way to change the file format to CSV (because the people at my project refuse to change the file format, since the file is updated daily). Is there a way you can import a 'µ' delimited text file into Qlikview 10 using the File Wizard? Thanks in advance

7 Replies
Employee
Employee

Qlikview delimiter 'µ' import

There is a simple work-around. Load it as a fix-record file, and then use subfield fo split the line up into fields:

Load

          subfield(Line,'µ',1) as A,

          subfield(Line,'µ',2) as B,

          subfield(Line,'µ',3) as C;

LOAD @1:n as  Line

FROM [Mu_test.txt] (fix, codepage is 1252);

/HIC

flipside
Valued Contributor II

Re: Qlikview delimiter 'µ' import

I have managed to load this directly as follows ...

Data:

LOAD *

FROM

[mutest.txt]

(txt, utf8, embedded labels, delimiter is \xb5, msq);

I had to change the encoding to utf8 (Unicode Utf8) and I converted the decimal ascii value µ (181) to Hex which is b5, hence the delimiter \xb5.

Helpful sites:

http://www.nationalfinder.com/html/char-asc.htm

http://www.theasciicode.com.ar/ascii-codes-table/ascii-codes-181.html

http://easycalculation.com/decimal-converter.php

flipside

EDIT:

FYI: The encoding only seems to need changing when specifying embedded labels.  The following works fine ..

LOAD @1, @2

FROM

[mutest.txt]

(txt, codepage is 1252, no labels, delimiter is \xb5, msq);

Not applicable

Re: Qlikview delimiter 'µ' import

Hi Henric, your solutions seems to be working, but I'm having difficulties with the column names.

The first row of my text file contains my column names. If I use your solution, the column names are included in my data records. Any idea on how to filter out those column names by using your solution?

thanks :-)

Re: Qlikview delimiter 'µ' import

Hi,

Probably this will work fine:

Load

          subfield(Line,'µ',1) as A,

          subfield(Line,'µ',2) as B,

          subfield(Line,'µ',3) as C;

LOAD @1:n as  Line

FROM [Mu_test.txt] (fix, codepage is 1252)

WHERE RecNo() > 1; // Read from the second row on

Hope that helps.

Miguel

erich_shiino
Honored Contributor

Re: Qlikview delimiter 'µ' import

Hi, Flipside

I used subfield() for a very long time and then I saw your code.

The columns are recognized correctly using this syntax, but I got strange characters at the end of field names and also in the end of the field values.

Did you experience any of this problems before?

Regards,

Erich

flipside
Valued Contributor II

Re: Qlikview delimiter 'µ' import

Hi Erich,

I'm not getting any errors with my small test file, but after a bit more investigation perhaps there is some difference in the use of ascii codes depending on location or user settings.  My code for µ of 181 is from ISO 8859-1 Symbols (which is the default character set in most browsers according to W3Schools.com), although in the link I gave for theasciicode.com µ is designated as code 230.

I don't know why it works with the browser set and not the extended ascii set.  Maybe my solution needs a bit more testing !

flipside

Not applicable

Re: Qlikview delimiter 'µ' import

I also got the strange character at the end of the field names and at the end of the field values. As it turns out, my delimiter is 16 bits, not 8 bits. So the first 8 bits of the delimiter is taken off while the remaining 8 bits are the strange character which becomes a part of the field names and field values.

And since QlikView does not support utf16 or multiple characters as delimiter, I guess I am stuck with using subfield.

Community Browser