How to import large amounts of data in excel

How DBeaver imports large amounts of data in excel

I have also imported large quantities of excel data before, and forgot how to import it at that time. Today I have also asked me to import it again. I feel that manual import should not work. It is too manual. I still think of some solutions. So I asked Wen Xin for a word and the operation is as follows.

Importing data in Excel in DBeaver. Since DBeaver itself does not directly support the import of Excel file format, it usually needs to be implemented by converting Excel files into CSV (comma-separated value) format as an intermediary step. The summary is the problem of saving the excel file as a .csv suffix, and then ensuring that the column name of the data to be imported in Excel corresponds to the fields in the database table.

The following are the detailed import steps:

Step 1: Convert Excel file to CSV format

1. Open Excel file: First, use Microsoft Excel or other spreadsheet software to open your Excel file.

2. Save as CSV：

Click the "File" menu in the upper left corner of the Excel interface.
Select the "Save As" option.
In the Save dialog box that pops up, select the save type as CSV (comma-separated value) (.csv) or CSV UTF-8 (comma-separated value) (.csv)". It is recommended to select "CSV UTF-8" to ensure the correct display of Chinese characters.
Click the "Save" button.

Step 2: Create a database table in DBeaver (if not created yet)

1. Connect to the database: First, make sure you have established a connection to the target database in DBeaver.

2. Create a table：

Right-click on the database name and select New > Table.
According to the data structure in the Excel file, enter the table name and field name and set the corresponding data type.
Make sure that the fields in the table correspond to the column names in the CSV file one by one.

Step 3: Import CSV files to database table

1. Right-click on the table name: In DBeaver's database navigator, find and right-click the table name you just created.

2. Select "Import Data": Select the "Import Data" option in the pop-up menu.

3. Configure import settings：

In the pop-up import wizard, select "CSV" as the data source.
Click the "Browse" button to select the CSV file you saved.
To set the encoding method, usually select "UTF-8" to avoid Chinese garbled code.
Set the column separator, usually a comma (,).
If the CSV file contains headers, make sure the header position is set correctly (usually the top).
Set other import options as needed, such as date/time format, etc.

4. Mapping columns：

In the Import Wizard, check and confirm that the mapping relationship between the columns of the CSV file and the fields of the database table is correct.
If necessary, the mapping relationship can be adjusted manually.

5. Start importing：

Click "Next" to preview the data to ensure that the data is not garbled or incorrect in format.
If everything is OK, click the "Done" button to start importing the data.

Things to note

During the import process, make sure the database connection is stable to avoid import failures.
If the amount of data in the CSV file is large, the import process may take some time, please be patient.
If you encounter any problems during the import process (such as garbled code, format errors, etc.), please check whether the encoding method, column separator, etc. of the CSV file are set correctly, and refer to DBeaver's official documentation or community forum for help.

Through the above steps, you should be able to successfully import data from Excel into the database tables connected to the DBeaver.

Summarize

The above is personal experience. I hope you can give you a reference and I hope you can support me more.