Start a new topic

Line 16: Number of fields in this data row (XX) does not match number of fields profile (YY).

Occasionally users will report that they receive this message in the error log for rows 16 and greater in their data file:

Line 16: Number of fields in this data row (XX) does not match number of fields profile (YY).

I can sum up the cause of this error in four words: "Bill Gates hates CSV". Excel is an incredibly useful tool, but it's clear that MS has it in for the CSV format (it's cousin, the tab-delimited format, is on the hit list too). If you have a CSV file that has empty values at the end of a row, Excel will save them with the appropriate number of commas as long as any row in that 16-row-block have a value at the end. However, if none of the rows in the 16-row-block have a value, Excel decides it doesn't have to deal with all that drudgery of outputting those commas and decides to go fishing instead. Yes, Microsoft knows about this. So, the first block of rows will typically have the correct number of commas because the header row will have values in every column, but then the errors will start on the 17th line (the 16th data row after the header). If you open the file in Notepad and scroll to the right it's usually pretty easy to see what's going on (whereas it's nearly impossible to tell by looking at it in Excel). The red box below shows the first 16 rows that have the trailing commas, followed by the blue box that show rows without:


So what's the solution? The easiest option is if you can arrange the last column to be one that always has a value. For instance, if your data will always have a transaction ID value and you can make that the last field in your data file then you will never have this problem. If that isn't the case for you, or if that would require massaging your data more than you'd like to, another option may be to use OpenOffice's Calc rather than Excel. OpenOffice is a free, open source alternative to Microsoft's Office suite that is (mostly) compatible with MS Office's document formats for Word, Excel, etc. In our tests with Calc it successfully saved the correct number of commas at the end of each row (YMMV). Another solution we've heard (though we're a bit leery of) is doing a search-and-replace in Excel to replace empty cells with a space (" ") character. This might work as I-O-M will typically ignore such a character, but we're thinking Calc might be a better way to handle this issue. If anyone finds any other solutions, please post them here and let us know. Thanks


Another option is to add an additional column to your file as the very last column and populate all rows with dummy data, such as "XX". Since there are now data in the last column in all rows, excel will save the csv file with the correct number of fields. In the profile, simply map this dummy field to "Ignore".
I ran into this issue this morning.

I will be taking Sue's advice and rebuilding my profile to make sure the last column always contains data.

Hi Alan,

No need to rebuild the profile, just right-click on the last row in your field mapping and add another row after it to hold your dummy row.


This may be a silly question, but what happens to your virtual fields if you add the dummy row?

Hi Janine,

That's not a silly question at all!

When you add a row in the Field Map, the Virtual Fields increment by one letter for each row you add.

If you were to remove a row in the Field Map, the Virtual Fields would decrease by one letter for each row you removed.



Login or Signup to post a comment