0

We allow users to export data in a CSV format from an Oracle database, which opens up in Excel in a new window. To retain the data as it is in the database, each column uses the =t() function to keep text as text. However....some of the data fields have commas in them. So where we have a column of:
=t("12345,67890")
you end up with 2 columns in Excel rather than one column with the whole string in. Any suggestion on how to retain the use of the t() function AND stop the data splitting into two columns?

Dubison
  • 750
  • 5
  • 12
Emily
  • 3
  • 2
  • 1
    Might help to add some detail on the exact process used to create the CSV – Tim Williams Mar 11 '15 at 05:55
  • I'm a little confused. How can you have values in two columns produced by a formula in a single cell? – ttaaoossuuuu Mar 11 '15 at 07:44
  • If we are only talking about `=T()` function and not any macro involved in this, it is not possible that function can split a data into 2 columns. You need to provide more information about your case I think. – Dubison Mar 11 '15 at 15:06
  • Apologies the above was not clear. – Emily Mar 12 '15 at 06:51
  • The =t("12345,67890") is generated from a database, so an excel spreadsheet can be displayed automatically. The data in the database includes commas which is really the crux of the problem but the customer insists these are required in certain cases. As the field can start with numbers but should be treated as a character field, we added the t() function call to protect the contents of the data. Removing the t() call means the data appears in one cell when the CSV is opened in Excel but runs the risk of losing the leading characters if they are zeroes. – Emily Mar 12 '15 at 06:55
  • I was after a solution to produce a CSV text output that will retain the comma, display the data in one cell when put into a spreadsheet and not lose any characters if they are seen as unneeded. No macros can be involved, just pure excel functions / text. – Emily Mar 12 '15 at 06:55

1 Answers1

0

Easiest solution is to change your output to prefix all text values with a ' (single apostrophe) instead of T() and change the delimiter that the Oracle DB outputs to TAB. The ' does the same as the T() functions (forces Excel to parse the cell value as a string) but seems somewhat more elegant. You have to change the delimiter as it cannot be contained inside the data as Excel won't be able to differentiate them. After changing the output then it just becomes a trivial matter of opening the file as a Delimited text file and selecting TAB as the delimiter. Hope this helps.

nbayly
  • 2,167
  • 2
  • 14
  • 23
  • Thanks for the suggestion. That will work in some cases, but we cannot change the delimiter. The idea of the processing is we create a CSV file which Excel can automatically open and format. The users will not go through the process of saving a file then opening in Excel as delimted unfortunately. – Emily Mar 12 '15 at 22:37
  • Doing some further research I found this question answered on another stack questions: http://stackoverflow.com/questions/769621/dealing-with-commas-in-a-csv-file Apparently you can escape commas in CSVs by using double quotes. There is reference to a CSV spec sheet that may be useful. Cheers, – nbayly Mar 13 '15 at 15:55
  • Cheers...found a few ideas to get round this. – Emily Mar 15 '15 at 09:58