Excel vs. CSV format

Submitted by User Profilejeff on 4:34pm, Tuesday, 2004-09-28
We have some tools that require files formatted in the CSV (comma-separated value) format. I use Excel to edit this file and save it as CSV. However, Excel has the nasty habit of lopping off decimal places in numbers if it thinks they are not needed. For instance, I have a column for Release numbers. "17.0" is a valid release number but "17" is not. However, regardless of how many different ways I format the column as General, Text or Number with 1 decimal place, when I reload the CSV file it still has 17. This is because format information is not preserved in CSV... A relate Microsoft acknowledgement of this problem is at: http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q216023 But their solution does not work. I wonder if I have to save it as a Excel workbook (xls) and then convert it to CSV when we need it... Stupid! Excel SHOULD just provide an option to load in the values as they see them when loading a CSV and NOT interpret the values as numbers, text, dates, etc... Suggestion? Regards, Jeff
‹ XML's DTD antivirus problem ›
At 5:54pm, Tuesday, 2004-09-28, User ProfileRob said:
When you set the formatting of a cell in Excel, in my experience, the value is not updated to reflect the change. This happens to me with dates. For example, I could have a cell that contains "Jan 5, 2004" as text, then set the format to date and Excel will still treat the cell as text. One way to test for this is with a formula that references the cell. In your case, I believe you're trying to take a number and turn it in to text. Suppose cell A1 contains 17.0 (a number) formatted as "General" and you want it to have "17.0" (text) for the purpose of your csv file. In B1, put [code:1]=istext(A1)[/code:1] For me, the result is FALSE. Then set the formatting of A1 to "Text" (Ctrl-1, Number tab, pick Text under Category). The formula in B1 remains FALSE, indicating that the 17.0 in A1 is still a number although the cell is formatted as Text. Now edit A1 (press F2 or double-click the cell) and just press Enter. The formula in B1 turns to TRUE. The content of the cell is now treated as text. This has been one of my annoyances with Excel for a long time. In the past, I've written macros to get around it. More recently, I've found there's an easier way most of the time. First, select the data to fix. Go to the "Data" menu and choose "Text to Columns..." You'll get a wizard dialog, just choose the default options for the first two steps. In the third step you can choose the new format (with one format per column). Pick Text as the format for all the columns. This should have the same effect as editting each cell and pressing enter.

Post new comment

  • Web page addresses and e-mail addresses turn into links automatically.
  • Allowed HTML tags: <a> <em> <strong> <cite> <code> <ul> <ol> <li> <dl> <dt> <dd>
  • Lines and paragraphs break automatically.
  • You can use BBCode tags in the text.
  • You can enable syntax highlighting of source code with the following tags: <code>, <blockcode>. Beside the tag style "<foo>" it is also possible to use "[foo]".

More information about formatting options

CAPTCHA
This question is for testing whether you are a human visitor, sorry but we need it to help prevent automated submissions.
19 + 1 =
Solve this simple math problem and enter the result. E.g. for 1+3, enter 4.