Solved

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

Posted on 2014-11-14
4
265 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 35

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 35

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
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 in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

733 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