Data transfer

Overview

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.

Supported formats

Export	Import
Database	CSV
CSV	XLSX
DbUnit	XML
HTML	Table
JSON
Markdown
SQL
Source code
TXT
XML
Parquet
XLSX

Transferring data between database tables

DBeaver provides data migration capabilities, allowing you to both export and import data between database tables. You can transfer data from one table to another within the same database or between different databases.

For more detailed instructions, see Data Migration.

Export data

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.

Note: Each format may have specific configuration settings that can be adjusted to meet your needs.

1. 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.

2. In the window that appears, choose CSV and click Next.

   

3. 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:

   

   Setting name	Description
   Maximum threads	Number of threads used for data extraction.
   Extract type	Defines the method of data extraction (Single query or Multiple queries).
   Segment size	The size of data segments to be extracted at a time (active while Multiple queries are selected as an Extract type).
   Open new connection(s)	Opens a new physical connection for data reading, recommended if you plan to continue working with the database during the export process.
   Select row count	Queries row count before performing export to track progress, but may cause performance issues in some cases.
   Fetch size	Number of rows the driver retrieves in one batch from the database.

   Tip: The Maximum threads option is active when exporting data from multiple tables. It is recommended to set the number of threads to match the number of cores in your computer for optimal performance.

4. Configure the format settings.

   At this stage, you configure the format settings for the output file. These settings determine how the data will be presented and encoded in the exported file. For more details on exporter settings, see Export parameters.

   

   Setting name	Description
   Formatting	Selects the formatting settings for the exported data. Reed more in the related article
   Binaries	Specifies how binary data is represented in the export.
   Encoding	Sets the character encoding for the exported file.
   Value display format	Chooses the format for displaying values in the export.
   Configure columns	Allows you to set which columns to export. Configure the columns to export by clicking Configure. In the Action column, use the down arrow on a row to toggle between skip or export for that column/table.

   Note: The options within the Exporter settings section will vary and provide specific controls tailored to the format selected for export.

5. Set options for output files or clipboards.

   

6. 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:

   

7. 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

The process of importing data follows a similar procedure for the supported formats. Therefore, in this guide, we will focus on the data exporting process using the XLSX format.

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, then click Next. For more details on importer settings, see Import parameters.

   

4. In the next window, set XLSX-to-table mappings. Please refer to our mapping process guide for more detailed information.

   

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 article.

   

6. 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.

   

7. 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.

   

Export parameters

In this section, you will find specific configuration settings for each supported export format. These settings allow you to customize the export process according to the requirements of the data format you choose.

CSV

Setting name	Description	Available options
Characters escape	Bad characters escaping model (surrounded with quotes or escaped with '\' character).	quotes/escape
Delimiter	Column delimiter.	You can use special characters like \t, \n, and \r.
File extension	The default file extension for the exported file.	csv
Format numbers	Format numeric values using locale settings.	true/false
Header	CSV header settings.	none/top/bottom
Header case	You can choose lower or upper case for column names or descriptions in the header.	as is/upper/lower
Header format	Defines the formatting of the header.	label/description/both
NULL string	String which will be used instead of NULL values.
Quote always	Quote all cell values. Cannot be used with "Quote Never".	disabled/all/strings/all but numbers/all but nulls
Quote character	Character which will be used to quote strings (space means no quote).
Quote never	Do not quote cell values. Cannot be used with "Quote Always".	true/false
Row delimiter	Row delimiter. Default is system-specific line feed delimiter.	default/\n/\r/\r\n/\n\r

DBUnit

Setting name	Description	Available options
File extension	The default file extension for the exported file.	xml
Force upper case column names	Convert all column names to upper case.	true/false
Force upper case table name	Convert the table name to upper case.	true/false
Include NULL values in export	Include NULL values in the exported data.	true/false
Replace NULL values with	Specify a string to replace NULL values in the export.

JSON

Setting name	Description	Available options
File extension	The default file extension for the exported file.	json
Format dates in ISO 8601	Convert all date values to ISO 8601 format.	true/false
Print table name	Include the table name in the exported file.	true/false
Export JSON values as	Export values as text or as JSON objects.	string/JSON

Markdown

Setting name	Description	Available Options
Confluence format	Enable Confluence-specific Markdown formatting.	true/false
File extension	The default file extension for the exported file.	md
Format numbers	Format numeric values using locale settings.	true/false
NULL string	String to represent NULL values in the export.
Show header separator	Include a separator line below the header.	true/false

Source code

Setting name	Description	Available options
File extension	The default file extension for the exported file.	php
Format dates in ISO 8601	Convert all date values to ISO 8601 format.	true/false
Language	Specifies the PHP version compatibility for the generated code.	PHP < 5.4, PHP 5.4+
Quote character	Character which will be used to quote strings.	", '
Row delimiter	Row delimiter. Default is system-specific line feed delimiter.	default, \n, \r, \r\n, \n\r

SQL

Setting name	Description	Available options
Data rows per statement	Specifies the number of data rows in a single insert statement.	integer
File extension	The default file extension for the exported file.	sql
Identifier case	Allows selection of lower or upper keyword case for table and column names.	as is/upper/lower
Include generated columns	Specifies whether to include auto-generated columns (e.g., auto-increment) in SQL INSERT statements.	true/false
Insert line before rows	Specifies inserting a line feed before values in multi-row inserts.	true/false
Keyword case	Allows selection of lower or upper keyword case.	upper/lower
Native date/time format	Specifies using native date/time format in INSERT statements.	true/false
Omit schema name	Specifies omitting schema/catalog name in INSERT statements.	true/false
On conflict expression	Provides an expression for the end of the statement. This setting is specific to the database.
Target table name	Allows specification of the target table name to generate an INSERT statement.
Upsert keyword	Allows selection of different upsert keywords.	INSERT/INSERT ALL/UPDATE OR/UPSERT INTO/REPLACE INTO/ON DUPLICATE KEY UPDATE/ON CONFLICT

TXT

Setting name	Description	Available options
Batch size	Specifies the number of records per batch.	integer
File extension	Specifies the file type for output.	txt
In-between delimiter	Adds a custom character between data values.
Max column length	Specifies the maximum length of data in a column; longer values will be cropped.	integer
Min column length	Specifies the minimum length of data in a column; shorter values will be padded with spaces.	integer
Print header	Specifies whether to print column names at the top of the file.	true/false
Show header delimiter	Adds hyphen characters either in the first row without a header or between the header and data.	true/false
Show leading delimiter	Adds a pipe character at the start of the row.	true/false
Show NULLs	Controls the display of NULL values in the output.	true/false
Show trailing delimiter	Adds a pipe character at the end of the row.	true/false

XML

Setting name	Description	Available options
File extension	Specifies the file type for output.	xml
Include DOCTYPE declaration	Specifies whether to include the DOCTYPE declaration in the XML file export.	true/false

XLSX

Setting name	Description	Available options
Append strategy	Strategy used when appending data to an existing file.	create new sheets/use existing sheets
Boolean string FALSE	String that replaces FALSE boolean values in the exported file.	true/false
Boolean string TRUE	String that replaces TRUE boolean values in the exported file.	true/false
Border style	Style of cell borders in the exported file.	NONE/THIN/THICK
Excel date format	Date and time format used in the Excel file (modifiable in Excel application).	m/d/yy / d-mmm-yy / d-mmm / mmm-yy / h:mm AM/PM / h:mm:ss AM/PM / h:mm / h:mm:ss / m/d/yy h:mm
Column group	Column number used for grouping rows on a sheet by column value.	integer
Export SQL	Specifies whether to export SQL to a second sheet in the Excel file.	true/false
File extension	File extension for the exported document.	xlsx
Header format	Format of the header in the exported file.	label/description/both/none
Header row font	Font styling for the first row in the exported file.	NONE/BOLD/ITALIC/STRIKEOUT/UNDERLINE
Max row on sheet	Maximum number of rows allowed on a single sheet, after which data will split into another sheet.	integer
NULL string	String used instead of displaying NULL values in the exported file.
Row number(s)	Includes the row index as the first column in the exported file.	true/false
Split SQL Text	Splits exported SQL into rows by CR (Carriage Return) in the exported file.	true/false
Trim strings	Removes extra leading and trailing spaces from all string values in the exported file.	true/false

HTML

Setting name	Description	Available options
File extension	File extension for the exported document.	html
Images	Extracts images to graphic files.	true/false
Output column headers	Outputs column names as an additional row in the generated table.	true/false
Output table header	Outputs the query or table name as the first row in the generated table.	true/false

Import parameters

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 section Patterns for data import 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.

Patterns for data and time format

When specifying the date/time format for data import, ensure that you use the correct format specifiers. A common issue involves confusing format codes, such as using MM (for months) when mm (for minutes) is intended.

Refer to the table below for a list of common format symbols and their meanings.

Symbol	Meaning	Presentation	Examples
G	era	text	AD; Anno Domini; A
u	year	year	2004; 04
y	year-of-era	year	2004; 04
D	day-of-year	number	189
M/L	month-of-year	number/text	7; 07; Jul; July; J
d	day-of-month	number	10
Q/q	quarter-of-year	number/text	3; 03; Q3; 3rd quarter
Y	week-based-year	year	1996; 96
w	week-of-week-based-year	number	27
W	week-of-month	number	4
E	day-of-week	text	Tue; Tuesday; T
e/c	localized day-of-week	number/text	2; 02; Tue; Tuesday; T
F	week-of-month	number	3
a	am-pm-of-day	text	PM
h	clock-hour-of-am-pm (1-12)	number	12
K	hour-of-am-pm (0-11)	number	0
k	clock-hour-of-am-pm (1-24)	number	0
H	hour-of-day (0-23)	number	0
m	minute-of-hour	number	30
s	second-of-minute	number	55
S	fraction-of-second	fraction	978
A	milli-of-day	number	1234
n	nano-of-second	number	987654321
N	nano-of-day	number	1234000000
V	time-zone ID	zone-id	America/Los_Angeles; Z; -08:30
z	time-zone name	zone-name	Pacific Standard Time; PST
O	localized zone-offset	offset-O	GMT+8; GMT+08:00; UTC-08:00;
X	zone-offset 'Z' for zero	offset-X	Z; -08; -0830; -08:30; -083015; -08:30:15;
x	zone-offset	offset-x	+0000; -08; -0830; -08:30; -083015; -08:30:15;
Z	zone-offset	offset-Z	+0000; -0800; -08:00;

For more information on valid patterns, see the Java DateTimeFormatter Patterns.