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.

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.

share|improve this question
    
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 Sep 12 '12 at 3:00
add comment

3 Answers 3

up vote 3 down vote accepted

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.

share|improve this answer
    
The link above is specifically for Excel 2003. Here are the specs for Excel 2007: office.microsoft.com/en-us/excel-help/… Although it is not specified here, Excel 2007 also has the same character limit on Text-formatted cells. –  Excellll Sep 11 '12 at 20:46
add comment

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?

share|improve this answer
    
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 Sep 11 '12 at 19:48
    
Can you also expand the rows? –  Joseph Sep 11 '12 at 20:27
    
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 Sep 11 '12 at 21:43
add comment

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

share|improve this answer
add comment

Your Answer

 
discard

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.