Line terminator while loading text file other than default \n

[#1]

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 '$$$'

[#2]

Any Ideas Qlikexperts??

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

[jagan]

Hi Vikram,

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

Regards,

Jagan.

[kedar_dandekar]

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,

KD

[rbecher]

Hi Virkam,

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

- Ralf

[#6]

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.

[ashwanin]

Hi Vikram,

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

rgds

Ashwani Kumar

[rbecher · Accepted solution]

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

[#9]

Hi Ralf,

Thank you Sir . That worked extremely well.

Regards,

Vikram

[rbecher]

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