Excel to CSV comma delimited

Question

When I try to save a long list in Excel as a CSV comma delimited it works for the first 17 rows and then stops. Why and how I can fix it?

It puts commas after the data in the first 17 rows and then it doesn't any more. I tried removing the 18th row in case it had bad data in it, but the next row had the same problem

Sample data:

201735,,,,,,60286214,,Anna ,,,07,,,,9999,,,,,,,,,,,,,,,,,344446,,,,,,11111111,,C Lxxxxxxd-Wilson,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,, ,,,07,,,,295,,,,,,,,,,,,,,,,,725002,,,,,,11111111,,xxxxxxxxxxxxxx

Answer 1

This has to do with the fact that Excel determines the amount of columns per block of 16 rows. You can easily determine this if you make a sheet with two columns where you leave the second column empty, except for every, say, 40 rows. In the CSV file you will find blocks of 16 lines that have trailing commas (and one line that has the data you entered in the column).

Example: create 60 rows with one column, where the value is "a", on the first and the 40th row put a second columns with value "b". Inspect the CSV file using an editor.

The fix is to put a formula that resolves to an empty string, e.g. ="" in the empty cells.

Answer 2

Reading your comments, it looks like you may have some bad data in your Excel file. I've never heard of Excel crashing before on CSV exports. Maybe you have multiple bad rows in your file, or perhaps some wierd characters Excel is choking on. I suspect though as your prime culprit, that you have commas in the data in your cells?

Here's an unothordox "hack" to try to get your data into a CSV file. Select all of the cells (cells, not rows and columns) that have the data in it. Copy it. Open up Notepad, and paste everything in there. Next, in Notepad, do Edit->Replace and for Find What: (press tab) and for Replace With: (press comma key), and then press Replace All.