Sql script display leading 0 in excel output file_问答_开发者

Sql script display leading 0 in excel output file

开发者 https://www.devze.com 2023-02-20 19:18 出处：网络

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 t

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