Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

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
jpenuliar
Valued Contributor III

Re: Loading .txt files

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.

Not applicable

Re: Loading .txt files

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

flipside
Valued Contributor II

Re: Loading .txt files

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
Valued Contributor II

Re: Loading .txt files

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


Not applicable

Re: Loading .txt files

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

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

Re: Loading .txt files

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

flipside
Valued Contributor II

Re: Loading .txt files

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
Valued Contributor III

Re: Loading .txt files

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