fixed length file where last field is variable length

[hsahay]

Hi

We need to load a supplier provided file which is supposed to be fixed length but the last field, which is numeric, is not being padded by space to make it fixed length. It is variable length as shown below

aaaaBBBBBccccc9<LF>
xxxxBBBBBccccc43<LF>


We are using the following schema file to load it

// Fixed width file
record
{final_delim=end,delim=none,quote=none,charset='UTF-16LE'}
(
JunkPCode:USTRING[15];
SuppRowID:USTRING[21];
OrigTitle:USTRING[256];
OrigArtist:USTRING[256];
OrigWriter:USTRING[256];
Plays:USTRING[2];
)

Now the first record that has a single digit value is being rejected with the message

input_file,0: Field "Plays" with 'delim=end' did not consume entire input

I tried final_delim=none and final_delim=end

I was hoping it would work when final_delim=end but it didn't.

Is there anyway to make it work ?

[chulett]

Have them correct the file. There's no such thing as a "fixed length file where last field is variable length" unfortunately.

Perhaps someone knows of a workaround, however.

[qt_ky]

If the file is consistently formatted as described then you could read every record as a single varchar column with a high length limit (read it as a delimited file; delimiter choice would not matter), and do the columnization within your job design, i.e. substring each column in a Transformer stage.

[hsahay]

Thanks. Eventually we may have to implement it that way for just this one supplier.

Currently, we load hundreds of flat files from different providers and the way we have designed the system, we have a generic load job that uses schema files to do the load. For each new supplier we just create a new schema file. the job discovers at run time which schema file to use.

So I just wanted to check with the experts if there is any hack that would allow us to make some change to the schema file and make it work. But looks like there isn't any.

[premupdate]

Why cant the last field can be split in to two with Nullable yes property and then combine in it further stages,if this schema going to be constant ?

Thanks,
Prem