Linker IT Software
menubar-top-links menubar-top-rechts
Home Help Search Login
Welcome, Guest. Please Login.
SQL*XL: Database to Excel bridge litLIB: Excel power functions pack ExcelLock: Locking and securing your valuable Excel spreadsheets encOffice: Protect your Excel file easy and safe encOffice: Protect your Excel file easy and safe
Pages: 1
Prevent values displayed as ###### (Read 3815 times)
Gerrit-Jan Linker
YaBB Administrator

Posts: 75
Prevent values displayed as ######
16.12.05 at 10:09:24
Li-yu from the USA asked the following question'
I am executing queries with SQL*XL. Some values are displayed as #####. I don't want these values to be printed like that. How can I avoid this?
Excel displays ##### in a cell when the value in the cell cannot be properly displayed. The value in the cell is the value that is in your database, just the display of it leaves a bit to be desired.
I found that only when you put large text values with multiple lines in a cell that you can get this behavior.  
I cannot code around this easially. It has to do with setting the Format of the cell to Text. If SQL*XL does not do that it will work properly however... you will loose other functionality. E.g. if your string column contains a number with leading zeroes. These leading zeroes will be lost as Excel will recognise the value as a number.
You can do two things to avoid this problem. You can specify in the resultset options dialog that SQL*XL should not format the output. Or you can explicitly set the format of the column.  
There is a command for setting the column format. This command can run together with your query. The command is called: column format. You can use this command whenever you want to explicitly format a column and when you want to override SQL*XL's default formatting.
column address format 'General';
select address from myaddresstable;
When you execute these statements from the SQL dialog, SQL*XL will apply the General format to the address column.

Back to top
« Last Edit: 16.12.05 at 10:20:24 by Gerrit-Jan Linker »  

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
Pages: 1