I’m often tasked with getting data from a flat format (Excel, CSV, etc.) into a database. When the format is Excel  and I have only a few files to work with I find it easiest to export the Excel file to CSV or TSV format. This makes it much easier to script the data conversion using PHP. There’s only one problem, Excel doesn’t always represent the total number of columns in each row. If there are blank values in the columns at the end of a row in the spreadsheet then the exported data may have fewer value delimeters than expected.

This bug has been documented by Microsoft for Excel 2003 and earlier. The solution given by Microsoft is poorly worded, but basically it says to make sure the cells at the end of a row always contain data. Not always a realistic proposition. Luckily I found a step-by-step solution that works perfectly:

Put a formula that evaluates to empty (=”” ) in the last column of  the rows that are empty:

1. select the range in the last column: edit->goto, click on the “Special…” button, Choose “Blanks” and click OK.
2. type the formula: =””
3. hit ctrl-enter

Now when you save the document the appropriate number of delimeters should be present, even in rows with empty cells.