Skip to Main Content
  • Questions

  • Formatting a VarChar2 field when spooling to a CSV file

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Kerry.

Asked: December 11, 2015 - 9:12 pm UTC

Last updated: December 14, 2015 - 3:46 am UTC

Version: 3.2.09

Viewed 1000+ times

You Asked

Hello!
I am spooling output of an SQL query to a CSV file. In the file is a field for CUSIPs which is formatted as a VarChar2 in the database. However, some CUSIPs are all numbers and some have letters and numbers (field length 9). When spooling into a CSV file the CUSIPs that are all numbers will show in EXCEL as a number. This can cause issues when the CUSIP has a leading zero(s). And heaven help us if the 6th character (out of 9) is the letter E because EXCEL will format that CUSIP as scientific notation. Is there a way to ensure that all the data in the CUSIP field spools as text? I have tried setting the column format to A9, have tried TO_CHAR but assume because of the mix of letters and numbers '999999999' will not work. Concatenating an apostraphe is not an option.

Examples of CUSIPs:
06417HQ66
06427EEN7
06427EKF7
66922766 lost the leading zero
1160411628 became a number in Excel
1173103825 became a number in Excel
1206472916 became a number in Excel
1206472946 became a number in Excel
1206550558 became a number in Excel
13606ACA4
13606AKB3
13606JLK3

Appreciate any help!!
Thanks & Cheers,
Kerry

and Connor said...

It *does* all spool at text. The problem is that Excel is "smartly" deciphering the data and then choosing formats accordingly.

Check these posts for some options

http://stackoverflow.com/questions/165042/stop-excel-from-automatically-converting-certain-text-values-to-dates

http://superuser.com/questions/307496/how-can-i-set-excel-to-always-import-all-columns-of-csv-files-as-text

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

possible solution/workaround

Ankit Kumar, May 09, 2019 - 7:21 am UTC

There was need to develop report and I was also facing the same issue.i found that there is one workaround/solution. e.g. your table name --> EMPLOYEE contains 3 columns colA,colB,colC. the problem is with colB. after connecting to sqlplus you can save the the data in spool file as :

select colA|| ','''||colB||''',' ||colC from employee

sample result:

3603342979,'8938190128981938262',142796283 .

when excel opens the file it displays the result "as-it-is"
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.