Exporting Multiple Tables To A Single Excel File…Using SQL Developer’s Cart

thatjeffsmith SQL Developer 7 Comments

Tell Others About This Story:

I’ve talked about the Cart a few times. It’s interesting to describe what the Cart is for, because it can be used or so many different things. But in general, it allows you do perform a set of operations against a collection of database objects.

In this case I’m going to show you how to export several views and tables to an Excel file.

And the rest of this blog post will be done using just pictures – it’s that easy.

Note: I’m using version 17.4 in this example.

Step 1: Open the Cart

Open the Cart.

Step 2: Add your objects

Add your objects.

Step 3: Tell us what you want

Uncheck DDL, check Data. Add any filters you’d like.

Step 4: Export

Click the export button.

Step 5: Set your export options

Set your options.

Step 6: Open the Excel file

Ta-da!

Tell Others About This Story:

Comments 7

  1. It would be nice if we can export multiple sql result into single excel.
    Understand it is not supported by now.
    Look forward to it.

    1. thatjeffsmith Post
      Author
  2. Is there any equivalent process for importing?

    Once a week, we’re supplied with an Excel spreadsheet with six tabs. Each tab needs to be imported to a specific table (which needs clearing down first). At the moment, we truncate all the tables, then use the Import Data option on each of the tables in turn, selecting the same file, but the next tab, which is kind of boring.

    Unfortunately, it’s further complicated by the fact that three of the tabs require attention in the Column Definition step of the Import Wizard (to clean up column names, or date values). Which doesn’t make it any more exciting 😉

    1. thatjeffsmith Post
      Author

      Change your process.

      Instead of doing this manually with Excel and SQLDev – automate it.

      Send those CSVs to the server, and create External Tables. Then create a job that weekly purges the source tables and does a INSERT AS SELECT from the externals.

      In SQLDev, you can setup the import scenarios, and save them to be re-used later, either in the UI or our CLI.

      1. I’m not sure we get to change the start of our process, so it’ll still mean I get the spreadsheet, and would then need to manually (or macro) split it to different CSV files, and upload them. And then run the job to load the externals. It’s not much of an improvement (I may not need to keep reconfiguring the column definitions). So it sounds like the current method isn’t too sub-optimal, given the conditions.

        When/if we can improve the mechanism which gives me the spreadsheet in the first place (such as having the underlying database connection, and raw queries), we can fix the whole thing and do direct database-to-database transfers. That’s the goal anyway 🙂

      2. thatjeffsmith Post
        Author

Leave a Reply

Your email address will not be published. Required fields are marked *