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

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!
Iver Erling ArvaSenior consultantAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

johnsoneSenior Oracle DBACommented:
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
Iver Erling ArvaSenior consultantAuthor Commented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
johnsoneSenior Oracle DBACommented:
Yes, I believe that is the 1,024 character limitation that I pointed out in the documentation.
0
Iver Erling ArvaSenior consultantAuthor Commented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.