On a number of occasions I’ve found it necessary to escape characters within the .csv format that is used by excel or try and output data that when entered directly into a .csv file shows oddly because the data source has commas in it as either thousand number separators or as part of some company names etc. So here are two methods that I’ve used in the past, being posted here for posterity as for some reason I can never remember the format for the first.
Initial delimiter line
It is possible to change the character that excel will use as a delimiter at the beginning of the file, with this method it is possible to change the delimiter used on a per-file basis and if the file is being created programmatically then this can be passed in as a variable from an input box or similar.
All that needs to be done is for the following line to be used at the head of the file:
Sep=#
This will cause the file to be read in such a way that whatever character is inserted (and it can only be one character) after the “sep=” will be used as the delimiter for the whole file, in the above example the hash character would be interpreted as the delimiter
Individual value escapes
It is also possible to escape individual cell values for situations where changing the delimiter for the whole file is not desirable. Broadly I find that the offices that I support have used excel which allows the use of quotation marks at the beginning and end of a value that contains the delimiter character in it.
ID,Value 5,”here, a value”
Meaning that the above text within a csv file should display as expected with two columns rather than having the second value of the second line split across two.
It is also worth mentioning that this approach will allow for a lot more flexibility within the cell in question. For example it is possible to insert carriage returns for use within an individual cell without breaking the way that the file is interpreted.
