Solved

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

Posted on 2014-11-14
4
239 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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Jaspersoft Studio is a plugin for Eclipse that lets you create reports from a datasource.  In this article, we'll go over creating a report from a default template and setting up a datasource that connects to your database.
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

776 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