How to save Excel file as CSV, without losing long text?

I have an Excel file I want to save as a CSV. Some of the cells has a lot of data in it, and so Excel shows it as

##############

And the sort. But of course, the data is actually there.

But when I save it as a CSV, it actually overwrites the data in those cells with those #######

And thus I lose data. How can I fix this, so it doesn't overwrite data with ######?

Double-clicking the cell length to widen it does not work, as the cell is so large even Excel doesn't expand it long enough. Expanding the rows and/or columns did not help either, still showing up as ###### because there's not enough room to display it on the screen/cell.

microsoft-excel

JBurace

Posted 2012-09-11T17:51:11.223

Reputation: 397

The Text property of a cell is it's displayed value. The Value property is the underlying data. Sounds like your using code accessing the Text property to output a CSV file. – Brian – 2012-09-12T03:00:22.553

Answers

The issue may be that you have a Text format applied to the offending cells. In an Excel cell with Text format, there is a limit of 1,024 characters. For example, the two cells below contain the same text, but A1 is formatted as General and B1 is formatted as Text. When saved as CSV, B1 is saved as pound signs.

enter image description here

Try removing the text format if it is not absolutely necessary to presenting the data properly, and then saving as a CSV. That should do the trick.

If you have long data that needs the Text format to keep Excel from changing your data (e.g. a long sequence of numbers with leading zeroes), you can enter an apostrophe before the text in the formula bar and then convert the cell format to General.

Excellll

Posted 2012-09-11T17:51:11.223

Reputation: 11 857

The link above is specifically for Excel 2003. Here are the specs for Excel 2007: http://office.microsoft.com/en-us/excel-help/excel-specifications-and-limits-HP010073849.aspx Although it is not specified here, Excel 2007 also has the same character limit on Text-formatted cells.

– Excellll – 2012-09-11T20:46:52.100

Maybe not the best solution, but use Ctrl+A to select all columns, double click a column separator to make it expand to the size to fit the cell contents, then save the csv, and ctrl+z to undo the column expansion?

Joseph

Posted 2012-09-11T17:51:11.223

Reputation: 425

The cell is too long to do that, it still expands but not the full length of the cell (there's paragraphs of data) and still shows up as ###### – JBurace – 2012-09-11T19:48:52.427

Can you also expand the rows? – Joseph – 2012-09-11T20:27:41.740

I tried, still didn't give enough room. The data is an export of SQL from a content website, and this field is the content. – JBurace – 2012-09-11T21:43:49.750

Maybe try expanding the width of the column containing the data, to eliminate the ###### before saving as a CSV.

JoshP

Posted 2012-09-11T17:51:11.223

Reputation: 2 236