0

I have Googled this issue and have looked at previous Stackoverflow questions. I am trying to figure out how to copy and paste from Oracle SQL Developer into Ms Excel. I have tried right clicking my results and clicking Export and following the steps to Export to clipboard and I have also tried highlighting all rows and pressing Ctrl + Shift + C and pasting into Excel. In both cases, when a field has a Null value in SQL Developer, it will just skip the cell in Excel. For example if Field1 has a value of 'Oracle', Field2 has a value of Null, and Field3 has a value of 200, when I paste into Excel cell A2 (first row is headers) will have a value of 'Oracle', then B2 instead of being blank will have a value of 200. How do I account for Null values when pasting? The other issue is that I am working through Citrix so I am not able to easily save work from SQL Developer to my desktop (in fact it is damn near impossible).

Chuck0185
  • 531
  • 3
  • 15
  • 36
  • Is [this](https://stackoverflow.com/questions/47807931/how-to-copy-the-records-from-output-of-oracle-sql-developer-to-excel-sheet) what you're looking for? – Jeff Holt Feb 19 '20 at 01:22
  • no, that is what I have tried but I am still having an issue with Null values being skipped as opposed to be left as empty cells – Chuck0185 Feb 19 '20 at 17:28

2 Answers2

1

I'm not sure why you're having trouble because I am not. True, I am not using the Export feature but you haven't said that you must use that feature. To continue the conversation in comments will be a bad idea.

First, I am using SQL Developer 19.2.1.247 and Excel 16.24 on macOS (but the OS should not matter).

Here is the query that I am using to test the copy & paste capability:

select 1, null, 2 from dual
union all
select null, 3, 4 from dual
union all
select 5, 6, null from dual
union all
select null, null, null from dual

If you reproduce good results with this query but bad results with your query, then I suggest you edit your post and include an MCV example query that produces bad results.

I select the query and press [Run Statement]. This creates a Query Result window that looks like this:

enter image description here

Then I put my cursor in the Query Result window and select all rows displayed by pressing Meta-A (⌘A or ^A) and then copy the selection by pressing Meta-C (⌘C or ^C). Then I see this:

enter image description here

Then I place my cursor in cell A1 in an Excel sheet and paste the clipboard by pressing Meta-V (⌘V or ^V). Then I see this:

enter image description here

Notice all four row's cells in the Excel sheet are correct w.r.t. the query. I did nothing special, not even invoking Menu->Data->Text to Columns. It was all done "automatically".

Jeff Holt
  • 2,940
  • 3
  • 22
  • 29
  • Since you're using the operating system's clipboard for the transfer, of course it matters that you're not using the same OS. – Johan Boulé Jun 22 '21 at 13:12
1

This is a known issue in the newest SQL developer version 19.2:

A BUG with Excel exports containing NULL DATE values causes the Excel file to be truncated, use CSV as a workaround or fall back to version 19.1

user2864740
  • 60,010
  • 15
  • 145
  • 220
Mark de Groot
  • 11
  • 1