Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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

share|improve this question

migrated from stackoverflow.com Jan 24 '10 at 2:10

This question came from our site for professional and enthusiast programmers.

    
What type of data is it after row 17? –  JustAPleb Jan 22 '10 at 16:40
    
"Stops"? What does that mean? Stops running? File mysteriously ends? Application that's supposed to read the CSV stops running? What does "stops" mean? –  S.Lott Jan 22 '10 at 16:42
    
Not programming related –  BlueRaja Jan 22 '10 at 16:51

2 Answers 2

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.

share|improve this answer

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.