Excel in Data Management: Tips for Exporting Large Datasets from DBeaver
Data management is a crucial aspect of any organization, be it small or large. One of the key responsibilities of a data analyst or a data scientist is to extract and analyze data. While there are various tools available in the market for extracting data, DBeaver is a popular open-source database management tool that many analysts and scientists choose to work with. In this article, we will discuss some tips for exporting large datasets from DBeaver.
Understanding DBeaver
Before we get into the tips, let’s take a brief look at DBeaver. DBeaver is a free, open-source database management tool that supports a wide variety of databases. It provides an intuitive and easy-to-use interface for working with databases. Some of the key features of DBeaver include:
- Support for a wide range of databases, including MySQL, Oracle, PostgreSQL, SQLite, and more.
- An intuitive interface for managing and querying databases.
- An SQL editor that supports syntax highlighting, auto-completion, and parameterized queries.
- Support for database migration and data transfer.
Tips for Exporting Large Datasets from DBeaver
Exporting large datasets from DBeaver can be a challenging task. However, with the right approach, you can make the process more manageable. Here are some tips for exporting large datasets from DBeaver.
1. Start with the Query
Before exporting the data, it’s essential to start with the right query. A query that extracts only the required data can significantly reduce the size of the dataset. Therefore, instead of selecting all the data from a table, you should focus on limiting the query to the relevant information. Additionally, make use of filters and other query functions supported by DBeaver to refine the results.
2. Use Pagination
Pagination is a useful technique for exporting large datasets, as it allows you to break the data into smaller chunks. By breaking the data into smaller chunks, you can reduce the memory footprint required to store the data. DBeaver supports pagination through the use of the limit and offset keywords. By using these keywords, you can specify how many rows to retrieve and skip.
3. Use Compression
Exporting large datasets often leads to large file sizes. To reduce the file size, you can use compression techniques such as gzip or bzip2. DBeaver supports exporting data to compressed formats, which can significantly reduce the file size. Additionally, if you plan to transfer the data over a network, using compressed formats can reduce the transfer time.
4. Use the Right Format
When exporting data, it’s essential to choose the right format. Different formats have different advantages and disadvantages. For instance, CSV is a widely supported format that is easy to import into Excel or other software. However, it does not support complex data types such as arrays or JSON. On the other hand, JSON supports complex data types but can be challenging to work with in Excel. Therefore, you should choose the format that best fits your requirements.
5. Optimize the Export Settings
DBeaver provides various configuration settings for exporting data. By optimizing these settings, you can improve the performance and speed of the export. For instance, you can adjust the buffer sizes, optimize memory usage and adjust the fetch size to reduce the number of calls to the database. These settings are available in the preferences section of DBeaver.
Exporting large datasets from DBeaver can be challenging, but by following these tips, you can make the process more manageable. Starting with the right query, using pagination, compression, choosing the right format, and optimizing the export settings are essential for extracting and analyzing data effectively. DBeaver is an excellent tool for data management and provides various features that are useful for extracting and analyzing data. By using these tips, you can take full advantage of its capabilities.
Saved searches
Use saved searches to filter your results more quickly
You signed in with another tab or window. Reload to refresh your session. You signed out in another tab or window. Reload to refresh your session. You switched accounts on another tab or window. Reload to refresh your session.
dbeaver / dbeaver Public
Data transfer
- Export data
- Import data
- Importing data from CSV file
- Importing data from Excel file
- Additional features
- Importing data from XML file
- Importing data from the database table
DBeaver Desktop Documentation
General User Guide
- Database Navigator
- Data Editor
- Managing Charts
- Using Liquibase in DBeaver
- Project security
- Team work (Git)
- DynamoDB
- DocumentDB
- Athena
- Keyspaces
- Bigtable
License management
- License Administration
- How to Import License
- How to Reassign License
- Build from sources
- Develop in Eclipse
- Develop in IDEA
- Develop in VS Code
- Resources localization
- Automatic Tests
- Unit Tests
- Code contribution
Clone this wiki locally
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.
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:
- CSV
- XLSX (Excel spreadsheet)
- HTML
- XML
- TXT
- JSON
- Markdown files
- SQL statements
- 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 CSV format.
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 CSV and 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 CSV , XLSX , and XML files.
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 CSV and click Next.
Select the input CSV file 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 CSV file for each database table column. You can skip columns (the value will be set to NULL in the target table column). You can set constant values for the table column if there is no source column in the CSV .
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
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.
In the window that appears, choose XLSX and click Next.
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.
In the next window, set XLSX-to-table mappings. Please refer to our mapping process guide for more detailed information.
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.
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.
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 XLSX file into different tables in your database.
Importing data from XML file
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.
In the window that appears, select XML and then click Next.
In the following window, select the XML file that contains the data you want to import, then click Next.
In the next window,w set XLSX-to-table mappings. Please refer to our guide for detailed information on the mapping process.
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.
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.
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.
Как select выгрузить все базы из dbeaver
Am using DBeaver 6.3.4 Community Edition and it doesn’t show XLSX ( Excel) option when Data Export feature is selected as in the screenshot. This excel option appeared in my Trial version of Enterprise Ed.
Please help me enable/install this excel option or does Community Ed support at all?
2 Answers 2
Excel export also works for the community edition (CE). Tested with Version 7.3.2.202101032114. How-to (via Google «dbeaver excel»):
An alternative to «Export data . » is to select the cells to be exported in the result grid and the right click -> Open with -> Excel.
Excel export option is available only in Enterprise edition which is a Licensed version also while Community edition is for free.
Data migration
- SSH Configuration
- Proxy configuration
- Kerberos authentication
- Network Profiles
- Separate Connections
PRO Databases support 
License management
- License Administration
- How to Import License
- How to Reassign License
- Build from sources
- Develop in Eclipse
- Develop in IDEA
- Resources localization
- Automatic Tests
- Unit Tests
- Code contribution
Clone this wiki locally
DBeaver supports data migration of tables from one database to tables of another one.
To perform a data transfer, please, follow the steps below.
Step 1: Define the data source
In the Database Navigator select one or more tables you want to export. In the context menu choose Export Data. (Note: you also can export data from the custom SQL query results. For that, choose Export data in the results context menu).
Step 2: Define data transfer target type
In the opened dialog box choose Database type as the data transfer target and press Next.
Step 3: Define data mapping
For proper table mapping, the following options are available:
- Target container — defines a database or a scheme where the data will be transferred to. Press
button and choose the container.
Source — contains names of all the tables selected at step 1. You can also see the list of columns existing in the source table by pressing
Target — contains names of the tables where the data will be transferred to.
Mapping — contains the list of actions to be applied to the source data on data transfer. The following options are available:
Create— the source data will be populated into a newly created table or column of the target container.
Skip — the source data will not be transferred to the target container.
Existing — the source data will be transferred to the table that already exists in the target container.
Unassigned — this value is set by default when there is no target defined.
If the cells are marked with 
, it means that in the target table there are no columns with matching names, otherwise the names will be filled in automatically.
You may also want to transform the values of some columns during the transfer. To do that, define column transformers by clicking on corresponding cells in the Transform column. You can choose one of three options: 
- Set to NULL. All values in the corresponding column will be set to null.
- Constant. All values in the corresponding column will be set to the specified constant.
- Expression. This transformer uses expressions (namely, JEXL expressions) to calculate the cell’s value. You can use basic arithmetic operations and column names to construct an expression.
You can define a target table by clicking on a cell in the Target column and entering its name, or press the New button and enter a new name in the opened dialog box.
You can also choose a name for a target table from the drop-down list.
Or select a table from the existing tables in the target container by pressing the Browse button 
To define the mapping setting for a column in a target table, release the list of source table columns by pressing next to the table’s name in the Source column, then click the name of the target column and enter a new one or select one from the dropdown list. To collapse the list, press
If you want tables of the target container to be named like those of source, press the Auto assign button and the Target column will be automatically populated.
You can also define the names of target columns, as well as their data types, by clicking a row with a table name and pressing the Columns button 
The following elements are available here:
Source column -this column contains names of columns existing in the selected source table;
Source type — this column contains the list of data types assigned to the columns in the selected source table;
Target column — this column contains names of target table columns where the data from the source column will be transferred to. To change the name, click the cell and enter a new name.
Target type — this column contains the list of data types that will be assigned to the columns in the target table.
IMPORTANT: Sometimes data types that are supported on the source database are not supported on the target or vice versa.
To set a data type for a column in a target table, click the cell in the Target Type column and select one from the dropdown list of data types supported on the target.
Mapping — this column contains the list of actions to be applied to the data on data transfer.
To change the mapping type, click a cell in the Mapping column of Table mapping dialog box and select the required mapping type.
You can also view the SQL script that will be run on data transfer by pressing the Target DDL button .
If you want to see a preview of the imported data, you can select the Preview data button
The following keyboard shortcuts for easy navigation within the mapping table area of Table mapping screen are supported:
| Shortcut | Action |
|---|---|
| Up | Moves one row up. |
| Down | Moves one row down. |
| Right | Releases the list of source table columns. |
| Left | Swaps the list of source table columns. |
| Space | Auto-assigns the target. |
| Del | Sets mapping type to skip. |
Configure data mapping and press Next.
Step 4: Define export settings
Data export settings are grouped into Extraction settings and Data load settings.
Extraction settings define how the data will be pulled from the source. The following options are available:
| Option | Description |
|---|---|
| Maximum threads | Defines the number of threads to be used for data transfer. |
| Extract type | Select Single query option if your data load is not too big. Select By segments option if you need to migrate a solid amount of data. When this option is selected you can set the Segment size value, that is to define the number of rows to be transferred in each segment. |
| Open new connections | If selected, a new connection will be opened and the data transfer will not interfere with other calls to the database whose data is being transferred. |
| Select row count | If selected, a progress bar displaying data migration process will be shown. |
Data load settings
Data load settings define how the extracted data will be pushed to the target. The following options are available.
| Option | Description |
|---|---|
| Truncate data load table before load | Select this checkbox only if you want all the data to be cleared from the target table. Be very careful with this option! |
| Replace method | Select this drop-down list if you want to change the import behavior in case of a duplicate primary key value. Data Import and Replace page |
| Open new connections | Use this option to speed up data transfer. If selected, a new connection will be opened and the data transfer will not interfere with other calls to the database where data is being transferred to. |
| Use transactions | This option allows you to speed up the data transfer and to define the number of rows for each transaction by setting the Commit after insert of parameter. |
| Use multi-row insert | Use multi-row insert with extended values number for higher performance. Database-specific setting. |
| Skip bind values during insert | This option can drastically increase performance for some drivers like Redshift by skipping a process of binding values and setting them directly, but it opens up a vulnerability to SQL injections. Not recommended if you are not sure of imported file contents. |
| Disable batches | Select this checkbox if you want to disable the use of batch imports. The import will be made row by row. Enabling this function will show all import errors, but make the import process slower. |
| Open table editor on finish | If selected, the table editor is to be opened when data transfer is finished. |
| Show finish message | If selected, a notification message will be shown when the transfer is finished. |
Check out the data transfer settings and press Start or save as task.
Export result set on Dbeaver to CSV
Normally I use Dbeaver for windows and always export my result set like this:
- Run my query —> select the result —> export the result set —> select export to clipboard —> done
This step by step puts my result set in my clipboard and I can paste it wherever I want to work with it.
The problem is that now I am using dbeaver for mac and this guide is not working. I can go on until the moment that I select my result set like in the image below:
But once I go further in the process, in the last step I get: no query
Note that in «source» it was suppose to show the query that originated the result set, but instead it says just «select.
As a result it does’t select my result or anything (besides being «successful»).
Normally my query would show up there automatically and I couldn’t find any option that corrects this problem in the menus.
Как select выгрузить все базы из dbeaver
Normally I use Dbeaver for windows and always export my result set like this:
- Run my query —> select the result —> export the result set —> select export to clipboard —> done
This step by step puts my result set in my clipboard and I can paste it wherever I want to work with it.
The problem is that now I am using dbeaver for mac and this guide is not working. I can go on until the moment that I select my result set like in the image below:
But once I go further in the process, in the last step I get: no query
Note that in «source» it was suppose to show the query that originated the result set, but instead it says just «select.
As a result it does’t select my result or anything (besides being «successful»).
Normally my query would show up there automatically and I couldn’t find any option that corrects this problem in the menus.
Экспорт набора результатов на Dbeaver в CSV
- Запустить мой запрос -> выбрать результат -> экспортировать набор результатов -> выбрать экспорт в буфер обмена -> готово
Lets Analyse it!
Быстрый импорт в базу данных с помощью DBeaver
- Получить ссылку
- Электронная почта
- Другие приложения
DBeaver приходит на помощь
Export Command
The @export command allows you to open the data transfer wizard with prefilled settings.
It may be helpful in case you’re editing several SQL queries and want to quickly perform the export of the produced results without creating any additional data transfer tasks.
Disclaimer: This article describes supported settings by the @export command, their purpose, and allowed values. Generally, this article contains every setting accessible in the data transfer wizard. Settings are written in the order they appear in the wizard, so you can always look at the wizard to quickly locate any of these settings.
Usage
The body of the command consists of JSON text, which looks like this:
Due to certain limitations, it must be written on a single line, without line delimiters:
The command itself doesn’t do anything. It must be followed by any other query:
You can either execute each line separately, or execute the entire script at once.
Data transfer
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.
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.
Export data
You can transfer data from one database to another or export it in different types and formats:
- CSV
- XLSX (Excel spreadsheet)
- HTML
- XML
- TXT
- JSON
- Markdown files
- SQL statements
- 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 CSV format.
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 CSV and 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.
Import data
You can import data to your database from CSV , XLSX , and XML files.
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 CSV and click Next.
Select the input CSV file 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 CSV file for each database table column. You can skip columns (the value will be set to NULL in the target table column). You can set constant values for the table column if there is no source column in the CSV .
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.
| Setting name | Description |
|---|---|
| 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 NULL value if an empty string is encountered. |
| 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 XLSX file. This is used to clarify the date format during the import process and does not affect the output data. You can refer to the java DateTimeFormatter documentation for details on the format pattern syntax. |
| 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.
Additional features
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 XLSX file 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.