csv file with different number of columns in rows

[dprice583]

Hi,

We are reading in a CSV file where there are different record formats. The first two records in the CSV file have 2 columns and 7 columns respectively. All of the other records in the CSV have 10 columns. Right now I am getting around this by making the first two records have 10 columns also. I was wondering if you had any advice on another way to handle this problem.

Thanks,
Don Price for the Kansas Turnpike Authority

[Support_Rick]

Don,

The only options you have here are to do as you are ... (adding columns so that all records have 10) or by removing the first 2 columns (separating them from the table) and reading those as separate values all together from a Temp file.

In either case, the ReadCSV must have an equal number of rows for each row, even the header(sub-header rows)

[madiom]

Is this still true in the current version of MFT? All post I found referencing this seem to be a few years old and I am hoping there is a workaround under the current system, because I am running into the same issue and manually deleting rows from a file make the automation features of MFT pointless.

[Support_Rick]

Yes, this is true for all CSV files as the structure of the file must be consistent.

You do have the option of using the ModifyRowset task to manipulate your data before writing it out ... but, in either case, if you're using CSV, you will need the same number of columns for all rows (including headers).

You could read flat files this way (basing it on the end of record marker instead of the field markers) .. but, then you have to parse out each field based on record size and/or type.

There are multiple ways to do this, just have to pick which is easier for you to accomplish.

[kgutierrez]

I am having this same issue so is it possible to split out the different rows to different temp storage and then read those temp spaces individually to add to database? For all my files, the header record is always different than the detail records and ignoring first row isn't working. This is causing quite a few headaches in processing these files so looking for some alternate solutions. Header record in this case has 4 fields and detail has 256 fields but even ignoring the first row, the insert into SQL is always trying to do 4 fields instead of the 256 and generating an error.