Loading .txt files

[#1]

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

[jpenuliar]

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.

[#3]

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

[flipside]

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

[flipside]

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

[#6]

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.........................

[MarcoWedel]

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

[MarcoWedel]

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

[flipside]

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

[jpenuliar]

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,

JP