Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
Customers, Partners & Luminaries only: You're invited to a Data Analytics Roadmap session. Read More
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Not applicable

Loading .txt files

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

9 Replies
Highlighted
Partner
Partner

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.

Highlighted
Not applicable

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

Highlighted
Specialist II
Specialist II

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

Highlighted
Specialist II
Specialist II

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


Highlighted
Not applicable

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


Highlighted

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);

QlikCommunity_Thread_149708_Pic1.JPG

hope this helps

regards

Marco

Highlighted

fixed small issue with trailing character in field names:

QlikCommunity_Thread_149708_Pic2.JPG

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

Highlighted
Specialist II
Specialist II

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

Highlighted
Partner
Partner

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