Home » SQL & PL/SQL » Client Tools » Help in getting the results correctly (Oracle 10g, Excel 2007)
Help in getting the results correctly [message #550853] Fri, 13 April 2012 01:11 Go to next message
manirocks
Messages: 65
Registered: October 2007
Member
Hi,

We wrote a query to spool the results in TAB delimited flat file, Results are ok but when copied into excel the leading zeroes are getting wiped off.

Example:

Result: 0000123 and when copied into excel the value is just 123. I need to retain the value as it is 0000123.

Can we control it in the query itself rather than correcting the excel.

Thanks
Re: Help in getting the results correctly [message #550855 is a reply to message #550853] Fri, 13 April 2012 01:34 Go to previous messageGo to next message
Littlefoot
Messages: 21357
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Well, it's more about Excel (which *thinks* that value you selected is a number) than Oracle.

Anyway: according to information you provided, that column is a VARCHAR2(7). I created a small test case; see how it works and, if it suits your needs, use it. Disregard its beauty (not!).

/forum/fa/10051/0/

P.S. This is the statement I used (it is kind of difficult to copy/paste it from an image, I guess).

select '="'|| to_char(col1,'FM0000000') ||'"', col2 from test;
Re: Help in getting the results correctly [message #550871 is a reply to message #550855] Fri, 13 April 2012 02:20 Go to previous messageGo to next message
manirocks
Messages: 65
Registered: October 2007
Member
I tried the test case exactly and the result I am getting in the excel is

="0000123"

However the exact requirement is I have a database column with VARCHAR2(15). The column can have values like

1. G1234
2. ABCDEFGHG
3. 000001234
4. 1234
5. XY

The problem is with the data with leading zeroes like "000001234" when copied into excel the value is coming as 1234 and the file is getting rejected due to this.

Thanks
Re: Help in getting the results correctly [message #550873 is a reply to message #550871] Fri, 13 April 2012 02:23 Go to previous messageGo to next message
Michel Cadot
Messages: 65377
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
As Littlefoot said it is NOT an Oracle problem and he gave and showed you the solution.
Now if you can't make it work then the problem is how you do it which we don't know.

Regards
Michel
Re: Help in getting the results correctly [message #550925 is a reply to message #550871] Fri, 13 April 2012 09:19 Go to previous message
c_stenersen
Messages: 255
Registered: August 2007
Senior Member
For a csv file try to remove the = from the select in Littlefoot's query and only surround the value with double quotes.
Example:
SQL> with tab as
  2  (select * from
  3         (select 'G1234' val from dual) union all
  4         (select 'ABCDEFGHI' from dual) union all
  5         (select '000001234' from dual) union all
  6         (select '1234' from dual) union all
  7         (select 'XY' from dual)
  8  )
  9  select '"' || val || '"' col
 10  from tab
 11  /
 
COL
-----------
"G1234"
"ABCDEFGHI"
"000001234"
"1234"
"XY"
Previous Topic: Compilation Errors in TOAD
Next Topic: Unable to open file
Goto Forum:
  


Current Time: Sat Feb 17 23:35:15 CST 2018

Total time taken to generate the page: 0.19326 seconds