Solved: Issue in Loading .TXT file with Delimited | - Qlik Community

kishoreravi1983 · ‎2018-03-23

Hi All,

I have an issue while loading .TXT file.

This is how my Data is coming up. Attached the Sample CSV File.

I am trying to load like this.

LOAD

MANDT#,

#PALEDGER#,

#VRGAR#,

#VERSI#,

#VV030#,

#VV788#,

#VV960

FROM [lib://DATA1/SEAP_CE1SMIS_0001_002 - Copy.TXT]

(txt, unicode, embedded labels, comment is #, delimiter is '|', no eof);

But i am unable to remove the # and also my data file first and last column has no # on one side.

How to handle this. Any pointer would be very helpul.

MANDT#|#PALEDGER#|#VRGAR#|#VERSI#|#PERIO#|#PAOBJNR#|#PASUBNR#|#BELNR#|#POSNR#|#HZDAT#|#USNAM#|#GJAHR#|#PERDE#|#WADAT#|#FADAT#|#BUDAT#|#ALTPERIO#|#PAPAOBJNR#|#PAPASUBNR#|#KNDNR#|#ARTNR#|#FKART#|#FRWAE#|#KURSF#|#KURSBK#|#KURSKZ#|#REC_WAERS#|#KAUFN#|#KDPOS#|#RKAUFNR#|#SKOST#|#PRZNR#|#BUKRS#|#KOKRS#|#WERKS#|#GSBER#|#VKORG#|#VTWEG#|#SPART#|#HRKFT#|#PLIKZ#|#KSTAR#|#PSPNR#|#KSTRG#|#RBELN#|#RPOSN#|#STO_BELNR#|#STO_POSNR#|#PRCTR#|#PPRCTR#|#RKESTATU#|#TIMESTMP#|#COPA_AWTYP#|#COPA_AWORG#|#COPA_BWZPT#|#COPA_AWSYS#|#KUNWE#|#LAND1#|#WW001#|#WW002#|#WW003#|#WW004#|#WW005#|#WW006#|#WW008#|#PRDHA#|#WW009#|#BZIRK#|#KTGRD#|#PSTYV#|#WW007#|#WW010#|#VKBUR#|#VKGRP#|#MATKL#|#COPA_KOSTL#|#KDGRP#|#ABSMG_ME#|#VV901_ME#|#VV902_ME#|#VV903_ME#|#VV904_ME#|#VV905_ME#|#VV907_ME#|#VV601_ME#|#VV602_ME#|#VV604_ME#|#VV605_ME#|#VV607_ME#|#VV608_ME#|#VV610_ME#|#VV611_ME#|#ABSMG#|#VV001#|#VV002#|#VV003#|#VV004#|#VV005#|#VV006#|#VV007#|#VV008#|#VV009#|#VV010#|#VV011#|#VV012#|#VV013#|#VV014#|#VV015#|#VV016#|#VV017#|#VV018#|#VV019#|#VV020#|#VV021#|#VV022#|#VV023#|#VV024#|#VV025#|#VV026#|#VV027#|#VV028#|#VV029#|#VV030#|#VV031#|#VV032#|#VV033#|#VV034#|#VV035#|#VV036#|#VV037#|#VV038#|#VV039#|#VV040#|#VV041#|#VV042#|#VV043#|#VV044#|#VV045#|#VV046#|#VV047#|#VV048#|#VV049#|#VV050#|#VV051#|#VV052#|#VV053#|#VV054#|#VV055#|#VV056#|#VV057#|#VV058#|#VV059#|#VV060#|#VV061#|#VV062#|#VV063#|#VV064#|#VV065#|#VV066#|#VV067#|#VV068#|#VV069#|#VV070#|#VV071#|#VV072#|#VV073#|#VV074#|#VV075#|#VV076#|#VV077#|#VV078#|#VV079#|#VV080#|#VV901#|#VV902#|#VV903#|#VV904#|#VV905#|#VV906#|#VV907#|#VV601#|#VV602#|#VV603#|#VV604#|#VV605#|#VV606#|#VV607#|#VV608#|#VV609#|#VV610#|#VV611#|#VV612#|#VV792#|#VV081#|#VV082#|#VV890#|#VV788#|#VV960

888#|#01#|#F#|##|#003.2017#|#0023606023#|#0001#|#3012457192#|##|#20170619#|#SUPPORT_JOBS#|#2017#|#003#|#20170619#|#20170619#|#20170619#|##|#0000000000#|#0000#|#0000254143#|#000000000036716785#|#YSON#|#USD#|#1.00000#|##|##|#USD#|#RG24YF17#|#000010#|##|#0000003502#|##|#AMC#|#SMIS#|#MKTG#|#7000#|#MKTG#|#01#|#DV#|##|#0#|##|##|##|#0095713746#|#000039#|##|##|#0000003502#|##|##|#8667199070000#|#VBRK#|##|##|##|#0000254143#|#AE#|#14#|##|#4340#|#402#|#JEA#|#D#|##|#4340IZEIFZF20T#|#F20T#|##|#12#|#TAS#|#A#|#IZEIFZ#|##|##|#4020#|#0000003502#|#Y#|#PC#|#CCM#|##|##|##|##|##|##|##|##|##|##|##|##|##|#645.000#|#23052.30#|#0.00#|#0.00#|#0.00#|#0.00#|#0.00#|#0.00#|#0.00#|#0.00#|#0.00#|#0.00#|#0.00#|#22104.15#|#0.00#|#0.00#|#0.00#|#0.00#|#0.00#|#0.00#|#0.00#|#0.00#|#0.00#|#0.00#|#0.00#|#0.00#|#0.00#|#0.00#|#0.00#|#0.00#|#0.00#|#0.00#|#0.00#|#0.00#|#0.00#|#0.00#|#0.00#|#0.00#|#0.00#|#0.00#|#0.00#|#0.00#|#0.00#|#0.00#|#0.00#|#0.00#|#0.00#|#0.00#|#0.00#|#0.00#|#0.00#|#0.00#|#0.00#|#0.00#|#0.00#|#0.00#|#0.00#|#0.00#|#0.00#|#0.00#|#0.00#|#0.00#|#0.00#|#0.00#|#0.00#|#0.00#|#0.00#|#0.00#|#0.00#|#0.00#|#0.00#|#0.00#|#0.00#|#0.00#|#0.00#|#0.00#|#0.00#|#0.00#|#0.00#|#0.00#|#0.00#|#4010674.500#|#0.000#|#0.000#|#0.000#|#0.000#|#0.00#|#0.000#|#0.000#|#0.000#|#0.00#|#0.000#|#0.000#|#0.00#|#0.000#|#0.000#|#0.00#|#0.000#|#0.000#|#0.00#|#0.00#|#0.00#|#0.00#|#0.00#|#0.00#|#0.00

888#|#02#|#F#|##|#

Thanks,

Ravi

jonathandienst · ‎2018-03-23

Use script like this. This could perhaps be streamlined a little if some time is spent on it.

// --- First load the header line and create the field list for the LOAD From_Field

Header:

First 1

LOAD Replace([@1:n], '#|#', ',') as Fields

FROM

(fix, unicode);

Let vFields = Peek('Fields');

Let vFieldList = '';

For i = 1 To SubStringCount(vFields, ',') + 1

vField = '@' & i & ' as ' & SubField(vFields, ',', i);

vFieldList = vFieldList & If(Len(vFieldList) > 0, ',', '') & vField;

Next

// --- Load the complete lines (skip header)

Rows:

LOAD Replace([@1:n], '#|#', ',') as Lines

FROM

(fix, unicode, header is 1 lines);

// --- Parse the lines using the field list above

Data:

LOAD $(vFieldList)

From_Field(Rows, Lines) (txt, utf8, no labels, delimiter is ',', msq);

// Clean up

DROP Table Header;

DROP Table Rows;

Set vFields = ;

Set vFieldList =;

Set i =;

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein

View solution in original post

shiveshsingh · ‎2018-03-23

Can you try Replace?

kakani87 · ‎2018-03-23

Something like this??

jonathandienst · ‎2018-03-23

Use script like this. This could perhaps be streamlined a little if some time is spent on it.

// --- First load the header line and create the field list for the LOAD From_Field

Header:

First 1

LOAD Replace([@1:n], '#|#', ',') as Fields

FROM

(fix, unicode);

Let vFields = Peek('Fields');

Let vFieldList = '';

For i = 1 To SubStringCount(vFields, ',') + 1

vField = '@' & i & ' as ' & SubField(vFields, ',', i);

vFieldList = vFieldList & If(Len(vFieldList) > 0, ',', '') & vField;

Next

// --- Load the complete lines (skip header)

Rows:

LOAD Replace([@1:n], '#|#', ',') as Lines

FROM

(fix, unicode, header is 1 lines);

// --- Parse the lines using the field list above

Data:

LOAD $(vFieldList)

From_Field(Rows, Lines) (txt, utf8, no labels, delimiter is ',', msq);

// Clean up

DROP Table Header;

DROP Table Rows;

Set vFields = ;

Set vFieldList =;

Set i =;

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein

tresesco · ‎2018-03-23

I tried like:

LOAD MANDT,

PALEDGER,

VRGAR,

VERSI,

PERIO,

PAOBJNR,

...

VV082,

VV890,

VV788,

VV960

FROM

[Sample Data.TXT]

(txt, unicode, embedded labels, delimiter is '#|#', msq);

And seemed to worked for me.

kakani87 · ‎2018-03-23

Tresesco Actually this was posted by Ravi Kishore

i tried like below using Subfield() for few fields

Temp:

LOAD MANDT#|#PALEDGER#|#VRGAR#|#VERSI#|#PERIO#|#PAOBJNR#|#PASUBNR#|#BELNR#|#POSNR#|#HZDAT#|#USNAM#|#GJAHR#|#PERDE#|#WADAT#|#FADAT#|#BUDAT#|#ALTPERIO#|#PAPAOBJNR#|#PAPASUBNR#|#KNDNR#|#ARTNR#|#FKART#|#FRWAE#|#KURSF#|#KURSBK#|#KURSKZ#|#REC_WAERS#|#KAUFN#|#KDPOS#|#RKAUFNR#|#SKOST#|#PRZNR#|#BUKRS#|#KOKRS#|#WERKS#|#GSBER#|#VKORG#|#VTWEG#|#SPART#|#HRKFT#|#PLIKZ#|#KSTAR#|#PSPNR#|#KSTRG#|#RBELN#|#RPOSN#|#STO_BELNR#|#STO_POSNR#|#PRCTR#|#PPRCTR#|#RKESTATU#|#TIMESTMP#|#COPA_AWTYP#|#COPA_AWORG#|#COPA_BWZPT#|#COPA_AWSYS#|#KUNWE#|#LAND1#|#WW001#|#WW002#|#WW003#|#WW004#|#WW005#|#WW006#|#WW008#|#PRDHA#|#WW009#|#BZIRK#|#KTGRD#|#PSTYV#|#WW007#|#WW010#|#VKBUR#|#VKGRP#|#MATKL#|#COPA_KOSTL#|#KDGRP#|#ABSMG_ME#|#VV901_ME#|#VV902_ME#|#VV903_ME#|#VV904_ME#|#VV905_ME#|#VV907_ME#|#VV601_ME#|#VV602_ME#|#VV604_ME#|#VV605_ME#|#VV607_ME#|#VV608_ME#|#VV610_ME#|#VV611_ME#|#ABSMG#|#VV001#|#VV002#|#VV003#|#VV004#|#VV005#|#VV006#|#VV007#|#VV008#|#VV009#|#VV010#|#VV011#|#VV012#|#VV013#|#VV014#|#VV015#|#VV016#|#VV017#|#VV018#|#VV019#|#VV020#|#VV021#|#VV022#|#VV023#|#VV024#|#VV025#|#VV026#|#VV027#|#VV028#|#VV029#|#VV030#|#VV031#|#VV032#|#VV033#|#VV034#|#VV035#|#VV036#|#VV037#|#VV038#|#VV039#|#VV040#|#VV041#|#VV042#|#VV043#|#VV044#|#VV045#|#VV046#|#VV047#|#VV048#|#VV049#|#VV050#|#VV051#|#VV052#|#VV053#|#VV054#|#VV055#|#VV056#|#VV057#|#VV058#|#VV059#|#VV060#|#VV061#|#VV062#|#VV063#|#VV064#|#VV065#|#VV066#|#VV067#|#VV068#|#VV069#|#VV070#|#VV071#|#VV072#|#VV073#|#VV074#|#VV075#|#VV076#|#VV077#|#VV078#|#VV079#|#VV080#|#VV901#|#VV902#|#VV903#|#VV904#|#VV905#|#VV906#|#VV907#|#VV601#|#VV602#|#VV603#|#VV604#|#VV605#|#VV606#|#VV607#|#VV608#|#VV609#|#VV610#|#VV611#|#VV612#|#VV792#|#VV081#|#VV082#|#VV890#|#VV788#|#VV960 as fld

FROM

(txt, unicode, embedded labels, delimiter is '\t', msq);

T1:

load

SubField(fld,'#|',1) as MANDT

,SubField(fld,'#|#',2) as PALEDGER

,subfield(fld,'#|#',3) as VRGAR

,subfield(fld,'#|#',4) as VERSI

,subfield(fld,'#|#',5) as PERIO

Resident Temp;

kishoreravi1983 · ‎2018-03-23

Thanks a lot Jonathan! It helped.