0

I have spooled a file to CSV using sqlplus. Everything works great but there is one issue. I have mobile numbers which start with 0, for example 04589 and 02586.

After the spooling is complete, 0s are truncated and the end results are 4589 and 2586.

I want the output to display in CSV as:

04589
02586
| improve this question | |
  • 1
    Not nearly enough information for a meaningful answer. What is the column data type? What does your query look like? When you view query results in SQL*Plus, how do the look? You're not opening the CSV file in Excel, are you? – mustaccio Jan 8 '19 at 16:27
  • I’m voting to close this question because it needs more details but has been abandoned by the author. – mustaccio Aug 26 at 19:28
1

String are written between "" in the CSV. So your datatype is not string type, or the tool you use for viewing the CSV automatically converts and trims it.

[oracle@o71 ~]$ sqlplus -S bp/bp
set markup csv on
set echo off
spool 1.csv
select '04589' as phone from dual
union all select '02586' from dual;

"PHONE"
"04589"
"02586"

exit
[oracle@o71 ~]$ cat 1.csv

"PHONE"
"04589"
"02586"

[oracle@o71 ~]$
| improve this answer | |
1

Simplest way to achieve this:

concatenate your column, which has leading zero, with prefix CHR(28) and you are done.

e.g.

spool file1.csv
select numbercol from dual;
select CHR(28)||numbercol from table;
spool off;

CHR(28) is an invisible char representation of a ASCII value.

| improve this answer | |
-1

It's not an Oracle issue. It's an Excel issue, unfortunately.
I've looked high and low for the answer myself with zero success. I have tried formatting the column in the spreadsheet, but at that point, the horse has left the barn so to speak.

| improve this answer | |
  • 1
    In some circumstances Excel can be the issue when converting/opening CSV files, but without more information/details, it's hard to make that assumption. – John A Jan 8 '19 at 19:00
  • 1
    True. Opening the csv in a plain-text editor will yield the proper formatting. My personal experience shows that opening in MS Excel truncates the leading zero. – RAF Jan 8 '19 at 19:58
  • It's possible that writing the number out in double quotes (from Oracle to CSV) would make Excel load it as text. – RDFozz Jan 8 '19 at 21:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.