Solved

Exporting a table with clobs to a csv-file or similar

Posted on 2014-11-14
4
214 Views
Last Modified: 2014-11-19
I need to extract two columns from an oracle-table. The first one is a reference number and the second is a multi-line clob.
The size of each of the clobs is typically about 600 lines, and the table has 3500 rows.

The format of the text inside each clob is like this
arbeidspesifikasjon => (
  linjetekst => D/M lakk Takplate H
  periode => 0
  listeid => 3133
  tiltak => D
  pos => 21
  kortnr2 => 0
  yta => 0
  svgrad => 0
)
arbeidspesifikasjon => (
  linjetekst => Sum perioder i følge ovenstående
  periode => 586
  listeid => 3139
  tiltak => 
  pos => 00
  kortnr2 => 0
  yta => 0
  svgrad => 0
)

...and so on for another 5-600 lines

Open in new window


When I try to export this to Excel, it cuts the text in half and only part of the text is exported. So I thought I'd export the data to a text file and use a program to pick the data apart and convert it into something sensible that can be opened in Excel.

But when I export it, it looks like this:
"2997";"";"<CLOB>"
"2998";"";"<CLOB>"
"2999";"";"<CLOB>"
"3000";"";"<CLOB>"
"3001";"";"<CLOB>"
"3002";"";"<CLOB>"
"3003";"";"<CLOB>"

Open in new window

Can you please suggest how I can achieve what I'm looking for?

Thx!
0
Comment
Question by:IverErling
  • 2
  • 2
4 Comments
 
LVL 34

Assisted Solution

by:johnsone
johnsone earned 500 total points
ID: 40442468
Are you hitting a limitation of Excel?  From what I can find, you can only hold 32,767 characters in a cell.  And, if you have 2003, only the first 1,024 are displayed (in 2010 this limitation appears to be removed).

At 600 lines, that would be an average line length of 55 characters, which doesn't seem like a whole lot.  Seems like you could easily exceed the limits of Excel with this project.

Limitations of Excel 2003
http://office.microsoft.com/en-us/excel-help/excel-specifications-and-limits-HP005199291.aspx

Limitations of Excel 2010
http://office.microsoft.com/en-us/excel-help/excel-specifications-and-limits-HP010342495.aspx
0
 

Accepted Solution

by:
IverErling earned 0 total points
ID: 40442560
Hi johnsone!

Actually, after looking more at this it turns out it is a display-problem in Excel and not a problem with the data. It is all there, even though it looks as if it's not. So when I export the Excel book as a .csv file I get all the data after all.

Both the text displayed in the cells and the text displayed in the formula field (if click on the bottom border and drag it downwards so it becomes multi line) seem to have limitations when it comes to what is shown on screen.

Thanks for your help, and all the best from
IVer in Oslo, Norway
0
 
LVL 34

Expert Comment

by:johnsone
ID: 40443448
Yes, I believe that is the 1,024 character limitation that I pointed out in the documentation.
0
 

Author Closing Comment

by:IverErling
ID: 40451904
Well, johnsone didn't really come up with a solution. Rather he (or she) was pointing something out to me and I did find that the problem and was just a display thing and not a data problem. Hence the "solution" was shared. You need to have a solution to close the question, right?
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now