1

End-users of a system we are developing are given a plain-old CSV file that end users will be opening in MS Excel. One of the columns will contain entries which consist of strings of numeric digits. These are not numbers but rather identifiers. We have noticed that the default formatting applied to this column causes the entries to be interpreted as large numbers which are subsequently rendered in scientific notation. Already, this is a bad user experience - but wait, it gets disastrous. Upon saving the file, the data is apparently being interpreted as a single-precision floating-point value and all digits after the 6th are being truncated completely. If the user changes the formatting so that the column displays properly before saving, Excel does not exhibit this behavior and the data is not corrupted upon saving.

I have found that either (1) changing the format of the column or (2) disabling formula evaluation keep both problems from happening.

Now, my question: is it possible to take either of the above actions, or some other action that will prevent the problems noted above, by default upon opening a CSV-type file in MS Excel? Note that it would probably be an impossibly hard sell to get the client to agree that end users have to disable formula execution for all files, including Excel-formatted files. Is there some way to set global settings? Some way to configure how CSVs in particular are treated? Some way to distribute a macro to users' Excel installations which run on all opened CSV files?

8
  • What happens if you quote " the identifiers?
    – DavidPostill
    Feb 15, 2016 at 14:10
  • @DavidPostill Will try and let you know. If it works, could I get Excel to quote all the cells in the spreadsheet automatically upon opening the document?
    – Patrick87
    Feb 15, 2016 at 14:32
  • It would be easier to modify the csv file ...
    – DavidPostill
    Feb 15, 2016 at 14:33
  • @DavidPostill For me, yes. For the end users, apparently not. We'd like to avoid adding any extra processing steps for them. Also, I don't know whether quoting the cells would work if the users subsequently modify and save the document; if they enter a new row and don't quote the entry in the problematic column, that row could get corrupted, right? Of course - if Excel can auto-quote all cells upon opening and/or before saving (really would be great if we could do it immediately upon editing any cell) then that would be ideal.
    – Patrick87
    Feb 15, 2016 at 14:39
  • No idea. You need to try it and see. I'm no Excel expert and I don't have it installed :/ In any case, can't you modify the csv before you give it to the users?
    – DavidPostill
    Feb 15, 2016 at 14:41

1 Answer 1

0

I found that this can be accomplished by writing an Excel add-in. On workbook open, I can do the following:

// set format of everything to text
Wb.ActiveSheet.UsedRange.NumberFormat = "@";

// disable further formatting - may not be necessary
Wb.ActiveSheet.EnableFormatConditionsCalculation = "false";

// for any existing cells, overwrite the formatted data with the real data, e.g., the formula
Wb.ActiveSheet.UsedRange.Value = Wb.ActiveSheet.UsedRange.Formula;

This works for me in Excel 2010.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .