Doing a lot of reports where the report is a file in CSV format and the user opens the report in Excel. What can I do to stop Excel from thinking a value is a number that is expressed in scientific notation?
Open excel. Type the text 1226E1 into a cell and press enter. The value is displayed in scientific notation ( 1.23E+04 ). Then you format the cell as text and excel displays 12260, replacing the E1 with a 0.
I have checked on the excel forum and saw a message saying that is just the way it is, nothing can be done outside of replacing the CSV import file with one in XML open interchange whatever form.
Enclosing the value in double quotes makes no difference. The best I am doing so far is to append a "." to the front of the value. Which is nuts.
I actually would not mind having to write the CSV file as XML, but in my brief experience doing that the user could not automatically open an email attachment as XML into Excel like they can a CSV file.
If you use OpenOfficeXML to create the file, your users will still be able to open them as email attachments because it will create an actual excel file. It just won't require excel to create the file so you could do so on a server.
@SteveRichter:If I want to force a cell to be text, then I usually apply a ' (single-quote) prefix to it. Excel knows to ignore that character, and treat it as a 'format as text' operator.
I wouldn't open the CSV from the common dialog. Use the import from text in the ribbon. Ensure that your columns are of type text.
Comments have been closed since this content was published more than 30 days ago, but if you'd like to continue the conversation, please create a new thread in our Forums, or Contact Us and let us know.