Opening CSV file created by script loses leading zero on phone number 1

[tical2g]

Hi All,

I have a script that is using sqlplus to pull information from a database. I need this in a excel report to then email to the business.

I have done it to create a csv file, looks fine but when I then open the file with excel the phone numbers have any leading zeros removed.

How can I retain the leading zeros when opening in excel? I have managed this before but cant remember how, I think the number field needs to be single or double quoted? I couldnt work out how to do this in my script as kept erroring out.

The code for reference is below, I have took out all the joining table information as it is not relevant:

#!/bin/bash
#

#
# Set up the various script variables.

ORACLE_HOME=/u01/app/oracle/product/10.2.0
export ORACLE_HOME
baseDir=/home/bin

# run sql to get all Orders for Last 24 hours

`/u01/app/oracle/product/10.2.0/bin/sqlplus -S DB/DB@SCHEMA >> orders.csv <<EOF
set head off
set pages 0
set feedback off
set linesize 600
select distinct TO_CHAR (T.CREATED_DATE, 'DD-MON-YYYY HH24:MI:SS')||','||TO_CHAR (DD.DELIVERY_DATE, 'DD-MON-YYYY')||','||rtrim(a.web_order_id)||','||rtrim(ps.product_number)||','||rtrim(n.title)||','||rtrim(n.forename)||','||rtrim(n.surname)||','||rtrim(CD.DAY_PHONE)||','||rtrim(CD.EMAIL_ADDRESS)
from blah blah
and t.created_date between sysdate - 24/24 and sysdate - 0/24;
EOF`

 

[KenCunningham]

Make the column a text type, add a leading single quote - a telephone number isn't a 'real' number in that sense.

The internet - allowing those who don't know what they're talking about to have their say.

 

[tical2g]

Thanks Ken, the problem is I cant work out how to put the single quote in the sql command? I have tried escaping the character like

rtrim(n.surname)||',\''||rtrim(CD.DAY_PHONE)||

I have also tried

rtrim(n.surname)||','||'\''||rtrim(CD.DAY_PHONE)||

When ran gives the message "ERROR:
ORA-01756: quoted string not properly terminated

 

[PHV]

perhaps this ?

rtrim(n.surname)||','''||rtrim(CD.DAY_PHONE)||

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[tical2g]

Yes thats it PHV! Great stuff, many thanks to both.

I thought the escape character was backslash, must be single quote then.