Data transfer is a crucial feature that enables you to export and import data in various formats as well as moving data between tables in the same or to different databases.
- Export data
- Import data
Note: The data transfer operation runs in the background, allowing you to continue working with your database during the export or import process. However, try to avoid changing data in the tables that have been selected for export or import until the process is complete.
You can transfer data from one database to another or export it in different types and formats:
- Source code arrays
The process of exporting data follows a similar procedure for the supported formats. Therefore, in this guide, we will focus on the data exporting process using the
Select the table or tables you want to export. In the context menu, choose Export Data.
Note: you can also export data from custom SQL query results. To do that, choose Export data in the results context menu.
In the window that appears, choose
CSVand click Next.
Set your data extraction options (how the data will be read from the tables). This may affect the extraction's performance. And set the export format option. They are specific to the data format you chose in step 2:
Set options for output files or clipboards. Note: Timestamp pattern is used here to target the file name pattern:
Review what you want to format and which format you will export. You can also save all your settings as a task in this step or change the task variables:
Press finish. See extraction progress. You can keep working with your database during the export process as the extraction will be performed in the background.
You can import data to your database from
Importing data from CSV file
Select one or more tables to which you want to import data. In the context menu, choose Import Data:
In the window that appears, choose
CSVand click Next.
Select the input
CSVfile for each table you want to import. You can change the Importer settings (format specific) during this step:
Set CSV-to-table mappings. You must set a column in the
CSVfile for each database table column. You can skip columns (the value will be set to
NULLin the target table column). You can set constant values for the table column if there is no source column in the
Set options for loading data in the database. These options may affect the loading's performance:
Read our guide on Data Import and Replace to learn more about the replacing method option.
Review which file(s) and to which table(s) you will import. You can also save all your settings as a task in this step:
Press finish. See extraction progress. You can keep working with your database during the export process as the data loading will be performed in the background. In the end, you will see the following status message:
Importing data from Excel file
1) Choose the database table (or tables) you want to import data into. Do this by right-clicking on the table name in the Database Navigator section and then clicking on Import Data.
2) In the window that appears, choose
XLSX and click Next.
3) In the following window, choose the file that contains the data you wish to import into the table. Select the appropriate settings described below, then click Next.
|Header position||Determines the location of the column names in the Excel table, either at the top or none. This setting specifies whether the column names are located in the first row of the Excel table or if there are no column names present.|
|Skip empty rows||If this setting is enabled, any open string values encountered during the data processing will be ignored and not inserted into the corresponding cells in the row. If the setting is disabled, all cells in the row will be filled with a |
|Import all sheets||Specifies that all sheets in the file should be imported during the data import process.|
|Specific sheet name||Enables you to choose a particular sheet from the Excel file for importing during the data transfer process.|
|Date/time format||Use this setting to specify the date format used in the |
|Timezone ID||The local machine timezone is used by default. There are three ways to specify the timezone: |
1) Local zone offset: Specify the offset from UTC in the format of either a positive or negative number (e.g., +3, -04:30).
2) Specific zone offset: Specify the offset from GMT or UTC in the format of GMT+/-X or UTC+/-X (e.g., GMT+2, UTC+01:00).
3) Region-based: Specify the timezone using a region-based identifier such as UTC, ECT, PST, etc.
|Sample rows count||Determines the number of rows that will be used as a sample to estimate the length and data types of the imported data.|
|Minimum column length||This value is used when creating a new column and, if necessary, specifying its type. It indicates the minimum number of characters or digits expected in the column. This information helps determine the appropriate data type and size for the column during the creation process.|
|Save task||Opens the Save Task window to assist in creating a task during the data transfer process. This window provides options and settings for creating and configuring a task related to the data transfer operation.|
5) In the next window, set XLSX-to-table mappings. Please refer to our mapping process guide for more detailed information.
6) Select your data load settings in the subsequent window, and then click Next. For more information, please refer to our article's section Data load settings article.
7) In the final window, you can review all the settings you selected earlier. If you missed something, you could go Back and fix it. When you are ready, finish the import by clicking Proceed.
8) If the
XLSX file is valid and there are no errors, you will see a notification window with information about the
completion of the task. You can keep working with your database during export, as the data
loading will be performed in the background.
When importing from Excel files, DBeaver provides additional features to enhance your data transfer experience:
- Allow importing multiple sheets into different tables: DBeaver allows you to import multiple sheets from a single
XLSXfile into different tables in your database.
Importing data from XML file
1) Select the database table (or tables) where you want to import data. Do this by right-clicking on the table name in the Database Navigator section, then clicking on Import Data.
2) In the window that appears, select
XML and then click Next.
3) In the following window, select the
XML file that contains the data you want to import, then click Next.
4) In the next window,w set XLSX-to-table mappings. Please refer to our guide for detailed information on the mapping process.
5) select your data load settings in the subsequent window, and then click Next. For more information, please refer to our article's section Data load settings.
6) In the final window, you can review all the settings you selected earlier. If you missed something, you can go back and adjust it. Once you are happy with everything, finish the import by clicking Proceed.
7) If the file is valid and there are no errors, you will see a notification window with information about the completion of the task. You can keep working with your database during export, as the data loading will be performed in the background.
Importing data from the database table
DBeaver offers seamless data migration capabilities, allowing you to transfer data from one database table to another. For more detailed instructions and insights on data migration, you can refer to our Data Migration guide.