topic Re: Line terminator while loading text file other than default \n in QlikView

Line terminator while loading text file other than default \n

Thu, 29 Aug 2013 06:29:41 GMT

Can we specify explicit line terminator while loading text file in qlikview.

Every end of the line contains '$$$ ' in text file . So while loading this text file can i specify something like line delimiter '$$$'

Re: Line terminator while loading text file other than default \n

Mon, 02 Sep 2013 08:55:33 GMT

Any Ideas Qlikexperts??

i need to explicitly specify a row terminator $$$ instead of default /n, as one of my column contains '\n'.

Re: Line terminator while loading text file other than default \n

jagan — Mon, 02 Sep 2013 09:02:13 GMT

Hi Vikram,

I think there is no such option, I tried it once and not get succeeded.

Regards,

Jagan.

Re: Line terminator while loading text file other than default \n

kedar_dandekar — Mon, 02 Sep 2013 18:04:43 GMT

Hi Vikram,

Just to bump an idea,

if your data that contains '\n' is enclosed within double-quotes you can still load this data retaining your '\n' (newline) character, if you opt for the MSQ Quoting option (Modern Style Quoting) in your text file load wizard..

e.g. the following file:

--------------------------------------

TICKETNUMBER, STATUS

CD1300000256245, "Normal Status line"

CD1300000256246, "Status with a

new line

character"

CD1300000256247, "Normal Status line"

--------------------------------------

would load using MSQ quoting option as:

Caveat: However, since your new line character is retained in the data,

post reload, you may have to adjust the row height for QlikView tables (Multiline settings), to allow users to see the entire data..

HTH,

Re: Line terminator while loading text file other than default \n

rbecher — Mon, 02 Sep 2013 18:34:56 GMT

Hi Virkam,

can you upload an example. Maybe you can use fixed and a nested subfield().

- Ralf

Re: Re: Line terminator while loading text file other than default \n

Tue, 03 Sep 2013 05:47:00 GMT

Hi Ralf Becher and KedarDan

Please find the attached example.As you can see the field seperator is '###/t' ,and the row separator is $$$.

The issue is with cardtypeid 1900001388,1900001246 and 1900001437.

Re: Line terminator while loading text file other than default \n

ashwanin — Tue, 03 Sep 2013 05:56:34 GMT

Hi Vikram,

You can specify any delimeter which is embedded in quoting. Even "#" is one them.

rgds

Ashwani Kumar

Re: Re: Re: Line terminator while loading text file other than default \n

rbecher — Tue, 03 Sep 2013 07:43:29 GMT

Hi Vikram,

this works with the sample data:

raw_data:
LOAD replace(replace(concat(raw_line, '', rec_no),'###',''),'$$$','') as raw_line group by group_no;
LOAD recno() as rec_no, if(index(@1:n,'$$$'), recno(), recno()+1) as group_no, @1:n as raw_line
FROM testtext.txt
(fix, codepage is 1252, header is 1 line);

result:
LOAD subfield(raw_line,chr(9),1) as CARDTYPEID, 
     subfield(raw_line,chr(9),2) as NAME, 
     subfield(raw_line,chr(9),3) as CARDTYPE2GRAPHICSBITMAP, 
     subfield(raw_line,chr(9),4) as CARDTYPE2BROWSERBITMAP, 
     subfield(raw_line,chr(9),5) as TABLENAME, 
     subfield(raw_line,chr(9),6) as CLASS, 
     subfield(raw_line,chr(9),7) as DESCRIPTION, 
     subfield(raw_line,chr(9),8) as PORTSTARTNUMBER, 
     subfield(raw_line,chr(9),9) as SLOTSTARTNUMBER, 
     subfield(raw_line,chr(9),10) as CARDSIZE, 
     subfield(raw_line,chr(9),11) as CRITICALFLAG, 
     subfield(raw_line,chr(9),12) as POWERSUPPLIED, 
     subfield(raw_line,chr(9),13) as POWERUSED, 
     subfield(raw_line,chr(9),14) as POWERUNITS, 
     subfield(raw_line,chr(9),15) as COOLINGSUPPLIED, 
     subfield(raw_line,chr(9),16) as COOLINGUSED, 
     subfield(raw_line,chr(9),17) as COOLINGUNITS, 
     subfield(raw_line,chr(9),18) as ISVISIBLE, 
     subfield(raw_line,chr(9),19) as LABEL
Resident raw_data;

You can be glad that you have a line break after the '$$$' line delimiter.. 😉

- Ralf

Re: Line terminator while loading text file other than default \n

Tue, 03 Sep 2013 10:06:47 GMT

Hi Ralf,

Thank you Sir . That worked extremely well.

Regards,

Vikram

Re: Line terminator while loading text file other than default \n

rbecher — Tue, 03 Sep 2013 10:09:22 GMT

Vikram, you're welcome!

Just another hint, this works only if one field has line breaks. If more than one field has line breaks it needs some adjustments..