Text file Transformation Error

[regowins]

Hi,

I am trying to source a text file using the fixed records file type and then do a conditional delete transformation on the rows I do not want.  The Transformed script looks like the below:  However, when I realod the document I get an error (attached) message saying 'Field not found - <@1:8>' Not sure what is causing this .  If I dont do any transformation the table loads fine with the fixed records.

LOAD @1:8 as ID,
     @9:14 as Gross,
     @15:43 as Client_Name,
     @44:55 as City,
     @56:64 as State,
     @65:77 as Invoice,
     @78:88 as Change,
     @89:98 as Amount,
     @99:112 as Number,
     @113:n as Date
FROM
Example.TXT
(fix, codepage is 1252, header is 8 lines, filters(
Remove(Row, RowCnd(CellValue, 5, StrCnd(null))),
Remove(Row, RowCnd(CellValue, 5, StrCnd(contain, 'TO'))),
Remove(Row, RowCnd(CellValue, 5, StrCnd(contain, 'RE'))),
Remove(Row, RowCnd(CellValue, 5, StrCnd(contain, '--'))),
Remove(Row, RowCnd(CellValue, 5, StrCnd(contain, 'A/'))),
Remove(Row, RowCnd(CellValue, 5, StrCnd(contain, 'AC'))),
Remove(Row, RowCnd(CellValue, 5, StrCnd(contain, 'WI'))),
Remove(Row, RowCnd(CellValue, 5, StrCnd(contain, 'CH'))),
Remove(Row, RowCnd(CellValue, 7, StrCnd(contain, 'XX'))),
Remove(Row, RowCnd(CellValue, 7, StrCnd(contain, 'A/R')))
));

Thanks for the help!

[Gysbert_Wassenaar]

Try putting square brackets around the field names. For example [@1:8]  as Gross. Alternatively you could try using a where clause. Can you post a sample txt file?

[regowins]

So I tried using the square brackets and it still did not work.  I have attached the example text file.

Thanks!

[maxgro]

starting from  Gysbert directions

SET ThousandSep='.';

SET DecimalSep=',';

SET MoneyThousandSep='.';

SET MoneyDecimalSep=',';

SET MoneyFormat='€ #.##0,00;-€ #.##0,00';

SET TimeFormat='hh:mm:ss';

SET DateFormat='DD/MM/YYYY';

SET TimestampFormat='DD/MM/YYYY hh:mm:ss[.fff]';

SET MonthNames='gen;feb;mar;apr;mag;giu;lug;ago;set;ott;nov;dic';

SET DayNames='lun;mar;mer;gio;ven;sab;dom';

load RowNo() as RN,*

where

len(trim(State))>0

and not WildMatch(State, 'TO', 'RE', '--', 'A/', 'AC', 'WI', 'CH')

and not WildMatch(State, 'A/R', 'STATE')

//and not WildMatch(Change, 'XX', 'A/R')

;

//Remove(Row, RowCnd(CellValue, 5, StrCnd(null))),

//Remove(Row, RowCnd(CellValue, 5, StrCnd(contain, 'TO'))),

//Remove(Row, RowCnd(CellValue, 5, StrCnd(contain, 'RE'))),

//Remove(Row, RowCnd(CellValue, 5, StrCnd(contain, '--'))),

//Remove(Row, RowCnd(CellValue, 5, StrCnd(contain, 'A/'))),

//Remove(Row, RowCnd(CellValue, 5, StrCnd(contain, 'AC'))),

//Remove(Row, RowCnd(CellValue, 5, StrCnd(contain, 'WI'))),

//Remove(Row, RowCnd(CellValue, 5, StrCnd(contain, 'CH'))),

//Remove(Row, RowCnd(CellValue, 7, StrCnd(contain, 'XX'))),

//Remove(Row, RowCnd(CellValue, 7, StrCnd(contain, 'A/R')))

LOAD

  [@1:8] as ID,

     [@9:14] as Gross,

     [@15:43] as Client_Name,

     [@44:55] as City,

     [@56:64] as State,

     [@65:77] as Invoice,

     [@78:88] as Change,

     [@89:98] as Amount,

     [@99:112] as Number,

     [@113:n] as Date

FROM

Example.TXT

(fix, codepage is 1252, header is 8 lines

//,

//filters(

//Remove(Row, RowCnd(CellValue, 5, StrCnd(null))),

//Remove(Row, RowCnd(CellValue, 5, StrCnd(contain, 'TO'))),

//Remove(Row, RowCnd(CellValue, 5, StrCnd(contain, 'RE'))),

//Remove(Row, RowCnd(CellValue, 5, StrCnd(contain, '--'))),

//Remove(Row, RowCnd(CellValue, 5, StrCnd(contain, 'A/'))),

//Remove(Row, RowCnd(CellValue, 5, StrCnd(contain, 'AC'))),

//Remove(Row, RowCnd(CellValue, 5, StrCnd(contain, 'WI'))),

//Remove(Row, RowCnd(CellValue, 5, StrCnd(contain, 'CH'))),

//Remove(Row, RowCnd(CellValue, 7, StrCnd(contain, 'XX'))),

//Remove(Row, RowCnd(CellValue, 7, StrCnd(contain, 'A/R')))

//)

);

[Gysbert_Wassenaar]

Try this:

LOAD @1:8 as ID,

     @9:14 as Gross,

     @15:43 as Client_Name,

     @44:55 as City,

     @56:64 as State,

     @65:77 as Invoice,

     @78:88 as Change,

     @89:98 as Amount,

     @99:112 as Number,

     @113:n as Date

FROM comm106893.txt

(fix, codepage is 1252, header is 8 lines)

where @65:77 ;

As long as your invoice numbers are numbers this will work. @65:77 will be a number and therfore evaluated as TRUE, which is why where @65:77 will load only the records where Invoice is a number.

[regowins]

Thanks both solutions worked!  However this is part of another question I had Bring Text file into QV with Transformation  Do you have any ideas on how I can accomplish that? I am able to get the dates into separate variables and next step was to do what we did here. however, I dont know how to assign a date to various areas as described in the link.

Thanks again!

[maxgro · Accepted solution]

see attachment

[regowins]

This is great!  Exactly what I needed. I was going down a different path but your version is much simpler.

Thanks again!!!