How to import and export data in Oracle [Without Coding!]
Check our new tutorial about how to import and export data in Oracle database with dbForge Studio. Get step-by-step instruction for importing and exporting tables in Oracle by our GUI tool. dbForge will help you to fully customize import and export operations and even do it automatically by schedule.
Timestamps:
0:00 Introduction
01:17 How to import data from Excel .xls file
0:42 Import Data Wizard Options
01:13 Data formats page
01:22 Mapping Page
01:46 Modes Page
02:02 Output and Errors handling tabs
02:47 Two ways to start Export Data Wizard
03:08 Source page
03:15 Options page
03:22 Table Columns page
03:29 Exported rows page
03:45 Exporting of selected rows example
04:17 How to automate import and export tasks
05:00 Creating .bat file to exporting data to .CSV file
05:43 How to solve access error
06:08 How to generate commands for PowerShell
06:38 Task Scheduler
07:25 Summary
Do you have any questions or ideas about our importing/exporting features? Feel free to write about it in the comment section!
👉 Take a look at dbForge Studio for Oracle:
https://www.devart.com/dbforge/oracle/studio/
âś… Check a full list of other our import/export features:
https://www.devart.com/dbforge/oracle/studio/oracle-export-data.html
🔥 Download a 30-days trial version of dbForge Studio for Oracle:
https://www.devart.com/dbforge/oracle/studio/download.html
đź”” Subscribe to our channel and get dozens of tutorials about Devart tools:
https://www.youtube.com/c/DevartSoftware
Thank you for watching!
#Oracle #OraclesSQLtutorialForBeginners #dbForge #Oracle19c #Oracle18c
Closed Caption:
Welcome to dbForge Studio for Oracle tutorials.
The tool offers a quick and convenient way
to import and export data, as well as schedule
and automate these operations.
First, let's consider importing data from
an .xls file.
Right-click the database in Database Explorer
and select Tools->Import Data on the shortcut
menu.
Then, select a file to import and specify
the file format.
On the Destination page, choose a target table.
You can either import data to the existing
table or create a new table for the imported
data.
On the Options page, select the range of cells
for import.
dbForge Studio for Oracle allows transposing
data from rows to columns while importing.
Just click to select "Data In" rows or columns.
On this tab, you can also configure headers
for the imported data columns.
For example, you can select to use the first
row in a range as a header, specify a row
to be used as a header, or select not to use
headers at all.
On the Data formats page, you can configure
a data format for all columns or set a format
for each column separately.
On the Mapping page, you can modify the target
table structure, add new columns, change the
existing ones, etc.
Let’s change the columns.
The wizard allows using different modes when
processing data: appending, modifying, or
updating.
Click “Use a single transaction” if you
might need to roll back the data import afterwards,
since is better to perform it within one transaction
in this case.
On the next page, you can set error processing
rules.
Let’s import data again.
Select to add new data to the existing table,
enter its name in the Filter field, and select
the first table in the list.
The range of data for import can be selected
automatically or manually.
As you can see, there are no changes on the
Mapping page.
Select the import mode “Append/update”,
enable the Use a single transaction option,
and start importing.
Now, let’s take a look at the result.
dbForge Studio for Oracle allows exporting
data directly from the Data Editor.
For this, click Export – SQL.
Alternatively, you may select the option from
the shortcut menu.
The settings can be saved.
You can also download templates that can be
used to work with the command line.
On the Source page, you can select tables
or views to be exported to separate files
or to a single file.
On the Options page, select a SeQuel statement
type to be created: insert, update, delete,
or merge.
On the Table columns page, you can exclude
specific columns from export, set keys, and
change the column names.
On the Exported rows page, you can set a range
of rows to be exported: all rows, the selected
rows, or a specific range of rows.
And here is the result.
Now, let's consider the export of the selected
columns.
We have filtered data and selected rows to
export.
As you can see, the file name matches the
table name.
Select the MERGE statement type
and then, select Export Selected Rows Only.
Let’s take a look at the result.
To automate the data import and export tasks,
you can use the command line.
To view the startup commands, type the following
in the command line:
And the list of import commands to be used
for setup will be displayed on the screen.
To view the export commands, run the following:
As mentioned earlier, to simplify the customization
of data export and import operations, you
can make the necessary configurations visually
in the Command Line Wizard and immediately
receive a command with arguments to run from
the command line.
Furthermore, in the Command Line Wizard, you
can check the settings and save the generated
command to a .bat file.
For example, let's create a .bat file to export
data from the payment table to a .csv file.
Right-click the table and select Export Data
from the shortcut menu.
As a field separator, enter a semicolon.
Then, go to the Command-Line wizard and check
the result.
As you can see, we’ve got an error – there
is no template.
Let’s create and save it.
In the Command-Line wizard, you can see a
path to the template in the Template File
field.
Now the command is valid.
When running it in the terminal, we receive
an error – access denied.
Go back to the Command-Line wizard and in
the Password field, set a password to access
the database.
Click Validate to make sure that the command
is correct.
Now, execute it in the terminal.
We see the export has been completed successfully.
The generated commands can be executed both
in a command line terminal and in PowerShell.
Just select the corresponding checkbox and
the command will be generated for PowerShell.
Save the generated command to a .bat file
and then run this file.
You can also schedule a data import or export
task in the Task Scheduler.
Just add the path to the batch file when configuring
the scheduled task.
For complex batch files, dbForge Studio provides
a useful option to return exit codes to indicate
whether a command has been successfully executed
or not.
It allows you to create a specific sequence
of steps in the batch file and immediately
understand their execution status.
To output exit codes, add the echo return
code (@echo %ERRORLEVEL%) to the file or command.
Just download a 30-day free trial version
of dbForge Studio for Oracle and find out
how easy and painless data import and export
can be.
Video Length: 07:41
Uploaded By: Devart
Published: 1/25/2022
View Count: 3,435