topic Re: Loading .txt files in QlikView

Loading .txt files

Tue, 27 Jan 2015 12:01:45 GMT

HI ,

Can any one of you Let me know how can I upload the attached text file with delimiter as Inverted question mark.

Please find the attached file and let me know the solution .

Regards,

kunna

Re: Loading .txt files

jpenuliar — Tue, 27 Jan 2015 12:16:58 GMT

inverted question mark or ASCII code 168 is an extended code and does not seem to appear in the custom Delimiter list in Qlikview. You can have a open your file with a CSV editor and replace the character with a valid delimeter. you can use notepad++ to do that.

You can also go back to the creator of the csv file and request to change the delimiter.

Re: Loading .txt files

Tue, 27 Jan 2015 12:42:23 GMT

Let me know the Expected format is like attached file r not?

Re: Loading .txt files

flipside — Tue, 27 Jan 2015 13:24:40 GMT

One option is to inspect the headers and build dynamic script based on the fields found ...

raw:
first 1
LOAD
@1 as header,
SubStringCount(@1,'¿') as headercount
FROM
[Kunna.txt] (txt, codepage is 1252, no labels, no quotes);

let h = peek('header');
let hc = peek('headercount');
let scr = '';
let c = ',';

Drop Table raw;

//Build dynamic script
for i = 1 to $(hc)

if $(i) = $(hc) then
let c = ''; //handles comma for last line
end if

let scr = scr & 'subField(@1,' & chr(39) & '¿' & chr(39) & ',$(i)) as [' & subfield('$(h)','¿',$(i)) & ']$(c)'; //wrap [] around fields

next i;

//Load data using dynamic script
Data:
Load
$(scr)
from Kunna.txt (txt, codepage is 1252, no labels, no quotes, header is 1 lines);

It's not ideal and relies on files arriving in a consistent manner, but it's an option.

flipside

Re: Loading .txt files

flipside — Tue, 27 Jan 2015 14:32:41 GMT

Actually, this doesn't seem to load everything. This may be a limitation in Qlikview or even a bug because Excel loads the data correctly using codepage 1252.

If you can get the codepage used at output then that may allow you to select it using the wizard and the delimiter may change to one of the standard ones. For instance if you change the codepage (character set) to 20127 (US_ASCII) then the delimiter can be specified as a normal ? and this gives you the field names but the fields don't match up exactly.

flipside

Re: Loading .txt files

Tue, 27 Jan 2015 14:58:59 GMT

Yes Ram, it is same format I need it how can I do It from qlikview can you Please let me know .

Lots of Thanks.........................

Re: Loading .txt files

MarcoWedel — Wed, 28 Jan 2015 21:48:34 GMT

Hi,

one solution could be to define the delimiter \xbf in the load statement manually:

LOAD * FROM [http://community.qlik.com/servlet/JiveServlet/download/701911-146733/Kunna.txt]
(txt, codepage is 1252, embedded labels, delimiter is \xbf, msq);

hope this helps

regards

Marco

Re: Loading .txt files

MarcoWedel — Wed, 28 Jan 2015 22:07:56 GMT

fixed small issue with trailing character in field names:

tabHeader:
CrossTable (ColNum, ColNam)
LOAD 1, *
FROM [http://community.qlik.com/servlet/JiveServlet/download/701911-146733/Kunna.txt]
(txt, codepage is 1252, no labels, delimiter is \xbf, msq)
Where RecNo()=1;

mapHeader:
Mapping LOAD ColNum, ColNam Resident tabHeader;

DROP Table tabHeader;

table1:
LOAD * FROM [http://community.qlik.com/servlet/JiveServlet/download/701911-146733/Kunna.txt]
(txt, codepage is 1252, no labels, delimiter is \xbf, msq)
Where RecNo()>1;

RENAME Fields using mapHeader;

hope this helps

regards

Marco

Re: Loading .txt files

flipside — Thu, 29 Jan 2015 09:13:41 GMT

Marco,

It seems you are right that the delimiter may be ┐(ascii 191) which converts to hex bf (and therefore delimiter \xbf). And using codepage 65001 (UTF-8) we might have a simpler result ...

Data:
Load * FROM
[Kunna.txt]
(txt, codepage is 65001, embedded labels, delimiter is \xbf, no quotes);

I also had to set quoting off ('no quotes').

flipside

Re: Loading .txt files

jpenuliar — Thu, 29 Jan 2015 09:45:24 GMT

Thanks a lot Marco,

I picked-up a few tricks from your example.

1. \xbf has lead me to mojibake.

2. Load from web attachment.

3. Cross Tab + Rename() combo

Best Regards,