Data Load from TXT with multiple delimeters

[sona_sa]

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.

[whiteline]

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.

[sona_sa]

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

Thanks for reply.

[whiteline]

Text file loading resistant to extra CF/LR

[rbecher]

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

[sona_sa]

Thanks.

Thanks & Regards

Amit Kumar

Mobile : 91-7738750800

>

[rbecher]

Please mark as answered if it works.

- Ralf