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开发者_Python百科 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
Using oracle:
Say your attribute is called 'number'
select '0' || to_char(number) as number
from table mytable
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 = "@"
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.
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.
- 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.
- #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.
I hope this helps.
P.S. as you appear to be a new user, if you get an answer that helps you please remember to mark it as accepted, and/or give it a + (or -) as a useful answer
加载中,请稍侯......
精彩评论