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