OraFAQ Forum: Client Tools » Help in getting the results correctly

Home » Other » Client Tools » Help in getting the results correctly (Oracle 10g, Excel 2007)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Help in getting the results correctly [message #550853]

Fri, 13 April 2012 01:11

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

Report message to a moderator

Re: Help in getting the results correctly [message #550855 is a reply to message #550853]

Fri, 13 April 2012 01:34

Littlefoot
Messages: 21782
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;

Attachment: leading_zero.png
(Size: 28.38KB, Downloaded 1224 times)

Report message to a moderator

Re: Help in getting the results correctly [message #550871 is a reply to message #550855]

Fri, 13 April 2012 02:20

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

Report message to a moderator

Re: Help in getting the results correctly [message #550873 is a reply to message #550871]

Fri, 13 April 2012 02:23

Michel Cadot
Messages: 68506
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

Report message to a moderator

Re: Help in getting the results correctly [message #550925 is a reply to message #550871]

Fri, 13 April 2012 09:19

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"

Report message to a moderator

Previous Topic:	Compilation Errors in TOAD
Next Topic:	Unable to open file

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Sep 28 00:08:32 CDT 2023