Solved

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

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

840 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