0

I have a command as follows:

SPOOL &&p_CSVfile

SELECT
  YEAR || ', ' ||
  TO_CHAR(AMOUNT, '$9,999,999,999.99')
FROM TABLE  

SPOOL OFF

...and values in the AMOUNT field can contain commas. When this file is opened in Microsoft Excel, the AMOUNT field is treated as two separate fields if it contains a comma. Is there a simple way around this that I am not aware of, or will I have to change my delimiter?

Jake
  • 604
  • 3
  • 9
  • 33
  • if you don't need comma separators for thousands, millions, etc. just replace the comma `REPLACE(Amount,',','')`, or wrap your value in quotes `'"' + Amount + '"'` – interesting-name-here Jun 01 '17 at 17:59
  • 1
    Spool just creates a text file. It doesn't wrap strings in quotes. You can use the Oracle utl_file package to control your output exactly, or use something like SQLDeveloper version 4 which has the "hint " /* csv*/ to delimit the csv output correctly. – Lord Peter Jun 01 '17 at 18:54
  • https://stackoverflow.com/questions/4168398/how-to-export-query-result-to-csv-in-oracle-sql-developer? – xQbert Jun 01 '17 at 19:02

2 Answers2

3

A CSV wraps each comma separated value in double quotes. Are each of your values in double quotes? If not,

Consider: (copy and paste below to notepad save as a csv)

"This is a","test"
"This is,a","test"
10,000,500
"$10,000","500"
"And she said, ""Hello my love""","end"

Lines 1,2,4,5 only result in 2 columns. Line 3 results in 3. Quotes matter.

So... To resolve add begin/end " to each of your values being output (what is " chr(34)?)

SELECT chr(34) || YEAR || chr(34) ||',' || 
       chr(34) || AMOUNT ||chr(34)
FROM TABLE

if your exported string data has quotes in it... simply double them up to escape properly.

xQbert
  • 34,733
  • 2
  • 41
  • 62
  • Why would you use chr(34) instead of " " ? – Jake Jun 01 '17 at 18:23
  • Also, when I follow this method, I get the following error: ORA-01741: illegal zero-length identifier – Jake Jun 01 '17 at 18:44
  • sounds like some years and amounts or other values may be "NULL" and concat a null doesn't work. need to coalesce(field,'') to put to a zero length string... though oracle – xQbert Jun 01 '17 at 18:49
  • I'm currently working with a tiny data set and there are no nulls =/ – Jake Jun 01 '17 at 18:51
  • my example didn't have chr(34) before/after the comma I've updated. wrapped SQL for readability. – xQbert Jun 01 '17 at 18:53
  • 1
    We seem to be reinventing the wheel--https://stackoverflow.com/questions/4168398/how-to-export-query-result-to-csv-in-oracle-sql-developer – xQbert Jun 01 '17 at 19:02
0

Wrap your value in double quotes '"' || Amount || '"'.

Full Example

SPOOL &&p_CSVfile

SELECT
  '"' || YEAR || '","' || AMOUNT || '"'
FROM TABLE  

SPOOL OFF

If you don't need commas in a specific field, you can also REPLACE them, i.e. REPLACE(Amount,',','').

interesting-name-here
  • 1,851
  • 1
  • 20
  • 33
  • So the end users would manually have to format the column as currency? – Jake Jun 01 '17 at 18:49
  • Not in the first example, but the second example they would. Is your amount column a `money` type or a `string` type? – interesting-name-here Jun 01 '17 at 18:52
  • It is a number that is being converted with TO_CHAR - I have updated my question to include this detail. – Jake Jun 01 '17 at 19:00
  • Okay, I was just getting at design principal is all. I'll update my answer to exclude the additional information based on my assumption of `string` being the column type. – interesting-name-here Jun 01 '17 at 19:01
  • I do need the commas, so I'm not using REPLACE(). I'm still getting the same issue. Hmmm. I may just have to change my delimiter. – Jake Jun 01 '17 at 19:11