Query: Column Count

Hi

We have had a scenario where an inbound CSV file is basically the same shape as expected however, they have inserted blank columns, thus offsetting required data. There are ways we can visually determine that the file is malformed, but I was wondering if there was already a method in AETL that we could use a validator to check column count (metadata?).

this won't always work because do on occasion receive CSV files that have variable number of columns per row (or set of rows). Therefore, the ideal situation would be to explicitly "Read" say row x and determine if that has y columns or perhaps a known text value or is not blank. This would be challenging to make available across file formats. But thought I might raise the question in case you have come across it before.

thanks in advance

This is one of those problems where the ideal solution does not exist.

One thing you can try is to use flags

If the field is empty set it to one otherwise set it to zero.
Then you add all flags together and accumulate value using the running total.
If the resulting number is above the threshold there is something wrong wit the data.

Thanks for that. Definitely hadn't considered that but will take a look and see if I can mesh something out.

Thanks again