Oracle – Leading Zeros Truncated When Spooling to CSV

csvoraclesqlplus

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

Best Answer

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 ~]$

Related Solutions

Oracle SQLPlus – ORA-01017 Logon Denied from One Host

At first make sure the database host is available ping host1.

Then you have to install a client and configure service name.

To install the oracle client correctly and be able to use it you have to:

Choose the "Administrator" type of the oracle client installation, not instant client, it provides you database administrator tools.
After your client installation run Net Manager (netmgr) to add a new service name. When you start testing the connection (Final step), make sure the "scott" user is available in your database.
Then run tnsping ~~YOUR_SERVICE_NAME~~CONNECTION_NAME(Thanks @vapcguy for the correction) - tnsping is a tool that comes in the administration installation).
Run sqlplus sys/passwd@<YOUR_SERVICE_NAME> as sysdba or sqlplus sys/passwd@host1:PORT/<DATABASE_SERVICE_NAME>

How to format sql-plus-spool-file to *.csv with all columns in one line and row-content with linebreaks as one field

1) If you want the headings in one line, you have to use a UNION with a select from dual. Example:

select '"'||'col1'||'","'||'"col2"'||'","'||'"col3"'
  from dual
union
select '"'||col1||'","'||col2||'","'||col3||'"'
  from table;

2) This one is tricky. It helps to know what ascii character your line breaks actually are. Depending on how they were written, generally (but not always) they will be chr(10) or a combination of chr(10)chr(13). You can use the REPLACE function in your query to replace those with '', which should get rid of them.

3) If you want quotes, see my example #1 above.

HTH.

Best Answer

Related Solutions

Oracle SQLPlus – ORA-01017 Logon Denied from One Host

How to format sql-plus-spool-file to *.csv with all columns in one line and row-content with linebreaks as one field

Related Question