CODEWITHSUNDEEP
excelshellsql-scripts
jack
jack

Reputation: 163

Sql script display leading 0 in excel output file

I have sql script "example.sql": SPOOL &1 Select '<.TR>'||'<.TD align="left">'||column_name||'<./TD>'||'<.TR>' from table1; spool off..which dumps it contents to cshell script "getdata.csh" this is how i get data from sql script to csh script sqlplus $ORA_UID/$ORA_PSWD @${SQL}example.sql ${DATA}${ext} once i extract data from it i create a excel file by combining 3 files header. html <html> <.head> <.title) Title <./title> <./head> <.body> <.table > <.tr> <.th>Column Name<./th> <.tr>ext file that has query results and trailer.html <./tr> <./table> <./body> <./html> and i save this file as .xls and send it through email as attachment.. Now my problem is Column_name has data that starts with 0 but when i open excel file leading 0 are gone but i wanna keep that 0.. so what can i add to make sure that email attached excel file will have leading 0 when that is opened on the other side.. plz any help would be good

Upvotes: 0

Views: 1242

Answers (4)

shellter
shellter

Reputation: 37298

I have dealt with this issue in the past, and the problem is strictly a "feature" of Excel formatting. Unfortunately, I don't have the resources to completely test an answer, but here are two things you can try.

  1. Add a step inside your cshell script to surround your $1 value with ="",

awk '{$1= "="" $1 """; print $0}' inFile > outFile

The downside is that you're now telling Excel to treat these values as strings. If you're doing any fancy calculations on these values you may have different problems.

  1. #2 (why does SO formatting always renumber numbered blocks as 1 !;-!) . As this is really an Excel formatting problem AND in my recollection, you can't retrieve the leading zero once the file has been opened and processed, I seem to remember I had a trick of pre-formatting a black worksheet, saving it as a template, and then loading the file into the template. I recall that was tricky too, so don't expect it to work. You might have to consult Excel users on the best tactics if #1 above doesn't work.

You might also want to tell people what version of Excel you are using, if you go to them for help.

IHTH

Upvotes: 0

user470714
user470714

Reputation: 2888

If you're generating the excel file on the fly, you could prepend those numbers with an apostrophe, ie '

This causes Excel to treat the number like a string. The only downside is it might cause some side effects if the sheet has any equations that use those numbers.

Upvotes: 0

JonH
JonH

Reputation: 33173

Use the excel object model, or a macro to go into the excel file grab the column and change the formatting.

In your case:

Range("A1").Numberformat = "@"

Upvotes: 0

ryebr3ad
ryebr3ad

Reputation: 1248

Using oracle:

Say your attribute is called 'number'

select '0' || to_char(number) as number
from table mytable

Upvotes: 0

Related Questions