We love getting enhancement requests from our users. A good deal of the development in SQL Developer is guided by what our users are asking for.
It does pain me a bit though when someone asks for a feature what we already have.
Example:
We frequently have a requirement to export working data (especially for data modeling purposes) to spreadsheets. It would be helpful if SQL Developer export had an option to export all tables to individual spreadsheets, creating one spreadsheet per table. So exporting a schema of 50 tables would create 50 spreadsheets in a single export operation. The spreadsheet name should include the table name. Today the export function does not allow me to export more than one table at a time. For a large schema with many tables, the export schema function would be very handy. The new dialog should allow the user to select one or more tables to export (including an all tables option).
So..
- export more than 1 table
- to Excel
- one file per table
- default to the table name
I read this as a spreadsheet = an Excel file. If you read it as a one or more worksheets in a single workbook (single Excel FILE), then skip to the bottom, because we support THAT too.
Tools > Database Export
The Filters
You can ‘filter’ out the columns you don’t want to export for each table. Again, we default to grabbing everything. You can add WHERE clause filters for each table – and also preview what that data will look like.
There’s a global where input as well – that will add a ‘WHERE XYZ’ clause to every SELECT we use to grab the data. So if you want to only grab records that have an ID in (1,2,3,4,5) add THAT to your global where.
But Wait, What About Multiple Worksheets in a Single Workbook??
In other words, just one file, and a tab per table?
Yeah, it’ll do that too.
29 Comments
I noticed that when exporting multiple tables to Excel separate workbooks, SQL developer appends _DATA_TABLE to the end of each Excel file. Do you know of how to get rid of this feature? Also, when exporting to a single workbook, each tab or worksheet has the schema Owner pre-appended to the tab name. This reduces my ability to create meaningful worksheet names, as there is a limitation on the number of characters in the name.
DATA, because the Cart lets you export DDL and/or Data, and TABLE, because you could also do this for a VIEW. Once the files have been generated, you’re free to renamed them or modify them to your required spec.
how we can do import to other db by using cart if we have exported data of multiple tables in multiple excel files (one exl files for each tables)
Add each Excel file to the cart, then map the file to a table and do the import, but I stand by my original question, WHY are you using Excel in the first place?
so what is best way if i need to export and import of data plus ddl of multiple objects (120 plus includes tables ,procedures,functions,triggers, sequecnces etc) by using sql developer .
Data Pump, no contest. You should be using Data Pump.
Hi Jeff
Is there a way to exclude empty tables from the export, please?
Thanks
John
Empty boxes?
Sorry, I’m not sure what you mean by “empty boxes”…
To explain – we have a database with more than 2200 tables. We need to analyse the changes to the data that result from running certain business transactions, so we have CDC configured to capture the data changes into a matching set of 2200 “CDC tables”. After the business transactions run there are typically 150 – 200 CDC tables that have records in them – we need to export those tables into Excel files (the rest of the CDC tables are empty).
So I’m hoping there might be a way to automatically exclude the empty tables from the export – obviously it’s an onerous task to select the 150-odd out of 2200 manually in the “Specify Data” pane – so is it possible to (for example) apply a global where clause that would do the trick? Or any other ideas?
You should look at change data notification and dbms_comparison features in the database – could be much easier than what you’re doing today
If you only want to export tables to Excel that have records…I would in SQLcl, write a script that loops through the tables gathering row counts, and if > 0 do a spool to CSV
It would be nice if we could export multiple SQL query results in one go instead of multiple table result.
Looking forward to this feature in next version ~
Oracle is always doing the best I believe
Cheers
Jeff,
This is great!
Once I choose the options and export it, is there a way the steps can be stored as a single task? Then I would be able to execute that task without having to specify the options again.
Thanks,
Ramana
Default export options are in the preferences. Database, utilities, export.
i want to export multiple executed query result in to csv file at a time .. please help me..
set sqlformat csv
spool file.csv
select 1;
select 2;
select 3;
spool off;
execute w/F5
I tried this out but instead of the query results, I am getting the sql statement itself in the generated csv file. Any idea what might be causing this ?
there’s an option to include the SQL, uncheck that – but, is it Exce XLSX or CSV files?
Thanks Jeff for the quick reply. I tried to look for the option to disable the SQL but could not find it. Can you help me with that ?
Also, the column headers are missing in the output. Any suggestions to fix that would be awesome. I intend to have dat (text) files as the output, one for each query in the script. SQLDev Version 4.0.3.16
>> SQLDev Version 4.0.3.16
Upgrade dude. We’ve released 4.1.0, 4.1.1, 4.1.2, 4.1.3, 4.1.5, & 4.2.0 since your version.
Would be even better if you could tables from another schema.
Maybe allow you run something like “ALTER SESSION SET CURRENT_SCHEMA=other_schema;” before searching for objects to export?
you can pull tables from any schema you have access to
Just found that you can export tables from another schema.
Multi-select “Other Users” objects in the browser, right-mouse-click, and select “Export”
Sorry if this caused you any pain Jeff … I do feel stupid now for asking for a feature what you already have.
Tools > Database > Export..walk the wizard and pull as many tables as you want from one or more schemas. You can also apply filters.
Wow!
Your mailing list is SO HELPFUL, and this tip in particular. Some of us don’t have training budgets (or time), and don’t have knowledgeable co-workers to learn from.
You are the best!
john3
So my training is free. Get a group at your org there, and I’ll be happy to deliver a webinar on the content area of your choice. Just drop me a note at [email protected]
Dang Jeff!
I just found this and passed it up the chain of command. You may regret the offer! haha
john3
We had taken a separate exl files data for multiple tables by using cart in sqldeveloper now we want to move this data to prd environment how multiple exl files can be done
I think you should mention the export to multiple sheets in one Excel file too. As far as i understand this was what was described in the request, and it’s done simply by choosing “single file” instead of “seperate files” 🙂
I think you just did that for me, Peter. Thanks!