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
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
0

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
0

Simplest way to achieve this!!!

concatenate your coulumn 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 a invisible char representation of a ASCII value.

|improve this answer

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.