Excel Export to CSV Missing Last/Trailing Empty Columns

My god how I hate Excel. Often it’s because people use it for some purpose it’s not really suitable for, where a relational database (even Access!) would be much better because (they think) they know how to use Excel and god forbid anyone take the time to learn to do something properly. Sometimes however, Excel is just plain fucking stupid. This is one of those times.

Recently I was trying to save an Excel spreadsheet as a CSV, to import into SQL Server (importing direct from Excel without converting to CSV gives other problems, like the Excel driver ‘helping’ you by detecting the wrong column types).

It was during this apparently innocuous task that I discovered a massive fail in Excel, which resulted in the import failing because some columns were missing. SQL Server’s helpful error message read:

Data conversion failed when converting column x to column y. The conversion returned status value 2 and status text “The value could not be converted because of a potential loss of data.”

Trying the same thing using BULK INSERT gave the error:

Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 17

followed by the same message for a number of other rows.

Continue reading “Excel Export to CSV Missing Last/Trailing Empty Columns”