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
2
  • 2
    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
    I’m voting to close this question because it needs more details but has been abandoned by the author. – mustaccio Aug 26 '20 at 19:28
2

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
0

What has worked for me when exporting/importing to csv while keeping my leading zeros is using LPad and length in unison. It might slow down larger queries but I found it to be useful.

''||trim(LPad(ColumnName,LENGTH(ColumnName),0))||''
Improve this answer
1
  • This is quite terse; your answer could be more valuable if it explained why this piece of code addressed the OP's requirements. – mustaccio Oct 6 '20 at 0:15
-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
3
  • 2
    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
-2

In Excel: Data>Import from CSV (rather than just double-clicking the CSV file to open it).

Improve this answer
2
  • @Ryan - please register your account - that will give you the ability to login on multiple devices and receive notifications from the site etc. Also, please take the tour - thanks! – Hannah Vernon♦ Dec 17 '20 at 20:17
  • Regarding your answer, how does this prevent the .csv data export from dropping leading zeros on numbers? – Hannah Vernon♦ Dec 17 '20 at 20:17

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.