Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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);
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 🙂
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
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
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
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.