topic Re: Data Load from TXT with multiple delimeters in QlikView

Data Load from TXT with multiple delimeters

sona_sa — Fri, 30 Aug 2013 19:00:47 GMT

Hi,

If you will open my attached notepad :

CARDTYPEID###

NAME###

CARDTYPE2GRAPHICSBITMAP###

CARDTYPE2BROWSERBITMAP###

TABLENAME###

CLASS###

DESCRIPTION###

PORTSTARTNUMBER###

SLOTSTARTNUMBER###

CARDSIZE###

CRITICALFLAG###

POWERSUPPLIED###

POWERUSED###

POWERUNITS###

COOLINGSUPPLIED###

COOLINGUSED###

COOLINGUNITS###

ISVISIBLE###

LABEL###

These are my field. Start from CARDTYPEID### and End with LEVEL###. CARDTYPEID### is unique key and It start with some number. LEVEL### is End of the Column - 0$$$.

Some of the data is null so \N is available, At data entry time some raw is blank so it start with new row. Now I want to identify a single row which has been start with Unique Key and end with 0$$$. Due to blank row I am not able to Identify the same. I have removed the delimeter. But Some row is coming blank and for that new row is coming with different field.

So Help on this. Also find the attached QVW and TXT file.

Re: Data Load from TXT with multiple delimeters

whiteline — Fri, 30 Aug 2013 19:46:44 GMT

Hi.

I think the easiest way is to process it with two separate steps:

1) Ignore tabs considering each line as a string (field) to remove the accident returns and join the corriupted strings back. Then save the uniformed data into a temporary text file.

2) load that temporary file as a regular tab separated fields.

Re: Data Load from TXT with multiple delimeters

sona_sa — Sat, 31 Aug 2013 05:29:13 GMT

Can you give me a example, I have shared my QVW and TXT file.

Thanks for reply.

Re: Data Load from TXT with multiple delimeters

whiteline — Tue, 03 Sep 2013 20:59:41 GMT

Text file loading resistant to extra CF/LR

Re: Data Load from TXT with multiple delimeters

rbecher — Tue, 03 Sep 2013 21:49:18 GMT

Hi Amit,

this script works with the given source file:

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

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;

- Ralf

Re: Data Load from TXT with multiple delimeters

sona_sa — Wed, 04 Sep 2013 06:22:15 GMT

Thanks.

Thanks & Regards

Amit Kumar

Mobile : 91-7738750800

Re: Data Load from TXT with multiple delimeters

rbecher — Wed, 04 Sep 2013 06:53:33 GMT

Please mark as answered if it works.

- Ralf