Oh, I so DO love hearing about things that ‘we cannot do.’ This morning I read that in an article that Oracle doesn’t allow you to copy data from Excel and paste directly to a table.
AHEM.
In the video you see me selecting the cells in the Oracle table editor before pasting the new ones in, but you don’t actually need to do that. Just make sure you start the paste from the first new cell, and that you have enough new rows to paste into.
You can also use the following keystroke to add new rows, just hold down Ctrl+I until you have what you need.
If you’re going to be dealing with interesting data types or a LARGE amount of data, then please use our official data importer.
I’m always a fan of easy and fast. Under the right circumstances, this definitely qualifies.
For those of you already fans of our Import Wizard, you can look forward to some enhancements in an upcoming version of SQL Developer. Stay tuned 🙂
32 Comments
Neither importing data from excel, nor copy-pasting data into manually inserted rows, appear to be options in the VS Code SQL Developer extension (which I am otherwise fondly enjoying).
Apologies if you have covered this elsewhere on your site.
Importing from Excel, not there, YET
I’m using this method to paste from Excel or a text file that has tabs in it for indentation. When I paste into the SQL Dev table only the unindented rows are pasted. Is there any way to design the table so that it ignores the tabs when I paste the text in? I can use the Import tool, but I’m trying to create a quick way to paste in some text and then analyze it using Oracle.
I don’t have a good answer for you…this would probably require a code change.
Thanks for the quick answer Jeff. I figured it might not work, but worth a shot!
does the number of SQL Developer ctrl+I rows you create have to exactly match the number of Excel rows you’re pasting? Or can you intentionally create too many ctlr+I rows, then paste Excel, and not worry about the extra empy rows cuz SQL Developer maybe takes care of them, not sure?
Cuz it would be nice just to hold down ctrl+I a few seconds, not worrying about counting the exact number of empty table rows you’re creating
Did you try?
i think extras will sort themselves out, assuming you don’t have a table with no required fields/keys, otherwise you’ll get new rows filled with NULLs
no didnt try cuz I don’t have OSD installed yet. New employer forcing me to move away from my old preferred tool. I am anticipating this issue, so I thought I’d ask first, and thought it was a valuable piece of info, not present until I asked just now, to have answered in a thorough way on your blog.
I think it will work as you expect. After you get it installed let me know if you require any assistance.
Hello,
Thanks for the article, very useful! But not as useful as the closest PL/SQL competitor tool…
In that tool you could create the lines as many as you want without the need of creating it just by doing a “FOR UPDATE” before pasting the excel content.
sure, some tools offer more flexility in some spaces than others, we continue to make improvements, all while charging you $0
Hi Jeff,
How to fill an enhacement request?
Best regards,
Renan.
You can open an Service Request with My Oracle Support. Or unofficially you can add your idea to sqldeveloper.oracle.com – although that one might already be there. Search first, and if it’s there, you can vote it up.
Try import feature (right mouse button click on the table name) …
Yes, cool!
I had no idea you could do this! I was actually coming to your blog to look into the Excel importing. I think this is great. I do think it automatically inserting the rows needed would be awesome.
I tried the cut and paste from excel and received a very unhelpful error:
One error saving changes to table xxx
Where do I look for a more complete explanation?
Thank you for your posts, I find them most helpful and entertaining.
That does sound unhelpful. Did the data appear to paste in correctly when looking at in in the SQL Developer table grid?
Did the log panel that showed the INSERT commands running show any additional information?
Ok, just figured out the cause of the error with no information.. My date format was incorrect.. There should be some indication that this is the issue though… Nice blog.. Thanks!
did you see the INSERTS/ORA’s written to the log panel by any chance?
No, there was nothing in the log window other than the generic error. No inserts or ORA errors were ever logged.
So after having this happen for months, it looks like by going to Preferences -> Database -> Object Viewer and selecting Post Edits on Row Change gets rid of this error.. This is for errors with no description- “One error saving changes to table *.*” followed by a blank line. Sometimes it’s not the date format that causes this… The option seems to fix it.. Could be a bug. Just keeping people who search for this posted.
I also get this error.. There is no helpful information. Before I tried restarting SQL Developer, and then the errors I got were more descriptive.. But currently I only get the generic error- “One error saving changes to table *.*” with no further information… I happen to be pasting info into a table when I get this error every time.
A very handy enhancement of the import wizard would be if he would acknowledge the fact that CSV files in countries where the decimal separator is a comma usually use a semicolon as separator. At the moment we have to rename every .csv into .dsv and afterwards back if we want to use it further with e.g. Excel
You can open your csv in the importer – although I’m confused, if it’s a csv, why are the delimiters semicolons – and then on Step 1 of the wizard:
So no need to re-save the file to a different file extension.
There is a world outside the USA where they use commas as decimal separators. In this world it does not make sense to use commas as field delimiter. Therefore we use semicolons as standard delimiter and we spell CSV as Character Separated Values.
You can use the delimited format, works swell.
Hi,
I’m using SQL Developer 4.0.2.15.
But the shortcuts does not work. I’ve already loaded and reloaded all the shortcut keys schemas and nothing happens.
I really need my “ctrl-+/” back )=
it’s Ctrl+I, as defined by the defaults.
Open the preferences, go to the keyboard shortcuts page, and search on ‘insert row’ – that will tell you what it is mapped to on your install
Hmm, that works fine with 3 rows, but how about 20? 100? 3000? Thanks, but I value my index finger enough to not click gazillion times before pasting the data 🙂
Jokes aside, it would be nice if SQLDev could automatically create enough new rows and paste all the data in there, when user is attempting to paste all the data into the single new row. I think I even saw that suggestion being raised already in the forum and/or exchange sometime before.
20, 100…use Ctrl+I – just hold it down until you have as many as you need.
3000 – use the formal import wizard.
Next time I talk to the developer, I’ll throw your suggestion his way.
Oh, that’s cool, thanks!
Also, you’re right, I totally forgot about shortcuts. Should revisit the preferences from time to time to find new useful things.