0

I am working with MySQL 8.0 through MySQL Workbench on a Windows machine.

I am having an enormous amount of problems importing an excel file into a MySQL database. I think the problem is that my data contains blank spaces or periods . to denote the absence of values for some positions.

I have received the data in the form of an Excel spread-sheet. As I have read that using CSV format may sometimes cause problems, I have saved the spreadsheet as a tab-delimited text file. I have created a table in MySQL Workbench according to the fields of the spreadsheet and everything is ready for the import. My problem is upon typing the following code into the MySQL Workbench:

LOAD DATA INFILE 'The/path/to/my/file.txt'
INTO TABLE my_table
FIELDS TERMINATED BY '\t'
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS

I receive errors about incorrect values.

Some of the positions of the data are not of the type that the database expects. For example, there is a column that expects a DATE. The issue is that in the data, some fields corresponding to the column are blank. I think what is happening is that when MySQL tries to import that blank value, the error is caused.

Other columns have either empty spaces (no values) or indeed a single period . to denote that there is no value for that position. Depending on the column, the database may expect type Date or Int. I have searched extensively for advice on the subject. The first answer on this page had looked hopeful, but after implementing a copy of the solution I received an error of the type:

Error Code: 1366. Incorrect integer value: ''for column 'abs_2012' at row 1.

abs_2012 is of type Int and row one's value is an empty space. What is particularly strange is that abs_2012 is the last column of about 30 I haven't received any errors about the previous columns.

Does anyone have any idea at all?

Many thanks in advance

  • Let's see sample input data. – Rick James Dec 7 '18 at 22:22
0

Use SET form of LOAD DATA INFILE (load the whole line into a user-defined variable and then parse it into separate fields).

Example:

LOAD DATA INFILE 'The/path/to/my/file.txt'
INTO TABLE my_table
IGNORE 1 ROWS
( @temp )
SET @count = LENGTH(@temp) - LENGTH(REPLACE(@temp, '\t', '')),
    id = CASE WHEN @count > 0 
              THEN SUBSTRING_INDEX(@temp, '\t', 1) 
              ELSE NULL END,
    field1 = CASE WHEN @count > 1 
                  THEN SUBSTRING_INDEX(SUBSTRING_INDEX(@temp, '\t', 2), '\t', -1)  
                  ELSE DEFAULT() END,
    field2 = CASE WHEN @count > 2  
                  THEN SUBSTRING_INDEX(SUBSTRING_INDEX(@temp, '\t', 3), '\t', -1)  
                  ELSE CURRENT_TIMESTAMP END,
    /* and similar expressions for all another fields */

The problem is that a record is inserted always - ever if a line is empty. So you need to remove those "empty" records after importing, and set some correct literal values to NOT-NULL fields which do not have default value.

Depending on the column, it may be of type Date or Int.

You can easily create unique parsing statement for each separate field dependent by its data type. Like

fieldX = CASE WHEN @count > X  
              THEN some_parsing_function_or_expression(SUBSTRING_INDEX(SUBSTRING_INDEX(@temp, '\t', X+1), '\t', -1))  
              ELSE {default value proper for field's data type} END,

or the same using intermediate variable

@tmpX = CASE WHEN @count > X  
             THEN SUBSTRING_INDEX(SUBSTRING_INDEX(@temp, '\t', X+1), '\t', -1))  
             ELSE 'default value proper for field data type' END,
fieldX = CASE WHEN @tmpX = 'default value proper for field data type'
              THEN @tmpX
              ELSE some_parsing_function_or_expression(@tmpX) END,
| improve this answer | |
  • Hi Akina, I'm afraid I may not have made myself fully understood. The tab-delimited file itself is not the problem. The issue is that some fields of the data to import are not of the correct type. For example, one column that represents a date might have no value for a certain position in the data, when mySQL tries to import that value I get an error as it expects a DATE but receives a blank space. – Eric31 Dec 10 '18 at 7:40
  • It seems you have previously spoken about blank lines. My answer shows the way to solve this issue. Nevertheless the method is applicable while only some fields values are blank. You MUST parse each field value, and if parsed value is incorrect for current field datatype (considering server SQL mode) you MUST replace it with some default value correct for current datatype and server mode. – Akina Dec 10 '18 at 8:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.