How to fix missing rows/data when exporting tables from DBeaver to MySQL database?

0
votes

I want to import a large csv file into MySQL and I use DBeaver to do so (faster). However data/records are missing when I finish exporting the table from DBeaver to MySQL database.

What could be the reason for the missing data and how can I fix it?

This is on macOS Mojave. I've imported the csv file to DBeaver (add connection) and ran the count(*) codes - there are 48744 records.

However, when I exported the table to MySQL database and ran it again in MySQL - there are only 9021 records.

SQL codes: "=select count(*) from table name"

I expect the MySQL reads the same amount of records as that in DBeaver, but it only reads less than 20% of total records.

Error messages during table export from DBeaver to MySQL database:

"Error occurred during data load Reason: SQL Error [1406] [22001]: Data truncation: Data too long for column 'NAME_EDUCATION_TYPE' at row 1"

The column type are all VARCHAR(20). I don't know where to change the data type to fit in more characters.

Update 10/16: Here's the create table codes. The csv columns match the content.

CREATE TABLE TEST.application_test(

SK_ID_CURR VARCHAR(20),

NAME_CONTRACT_TYPE VARCHAR(20),

CODE_GENDER VARCHAR(20),
...

AMT_REQ_CREDIT_BUREAU_QRT VARCHAR(20),

AMT_REQ_CREDIT_BUREAU_YEAR VARCHAR(20))
1
mysqlcsvdbeaver
Take a look at the table definition on MySQL (SHOW CREATE TABLE tablename). And, take a look at the first few rows of your CSV file. Do the column definitions in your table match the stuff in your CSV? If you were to edit your question to show those things we might be able to help you better. - O. Jones
Hi I just updated my question to show the things per my understanding of what you mentioned - Abby Lin

1 Answers

0
votes

At least one of your mysql columns dont have the min necessary size. I sugest revision about size of your VARCHARS vs columns of your CSV. Is possible to create one formula on excel to help you to discover the larger size data inside each column of your CSV. Take care about scape string characters (' /), sometimes it's hard work to be all right.