Loading data into mysql LOAD DATA INFILE difficulty

Question

I am executing the following query to load the data into MySQL.

LOAD DATA LOCAL INFILE '/Users/ramjiseetharaman/Desktop/UTA/CSE\ 5330\ -\ Database\ Systems/Projects/Project\ 1/DEPARTMENT.txt'
INTO TABLE DEPARTMENT
FIELDS TERMINATED BY ','
ENCLOSED BY "'"
LINES TERMINATED BY "\n";

And I am getting the following warnings,

| Level   | Code | Message                                                               |
+---------+------+-----------------------------------------------------------------------+
| Warning | 1366 | Incorrect integer value: ' '333445555'' for column 'Mgr_ssn' at row 1 |
| Warning | 1366 | Incorrect integer value: ' '987654321'' for column 'Mgr_ssn' at row 2 |
| Warning | 1366 | Incorrect integer value: ' '888665555'' for column 'Mgr_ssn' at row 3 |
| Warning | 1366 | Incorrect integer value: ' '111111100'' for column 'Mgr_ssn' at row 4 |
| Warning | 1366 | Incorrect integer value: ' '444444400'' for column 'Mgr_ssn' at row 5 |
| Warning | 1366 | Incorrect integer value: ' '555555500'' for column 'Mgr_ssn' at row 6 |
| Warning | 1366 | Incorrect integer value: ' '112244668'' for column 'Mgr_ssn' at row 7 |
| Warning | 1366 | Incorrect integer value: ' '110110110'' for column 'Mgr_ssn' at row 8 |
| Warning | 1366 | Incorrect integer value: ' '913323708'' for column 'Mgr_ssn' at row 9 |
+---------+------+-----------------------------------------------------------------------+

I want to load the following data:

'Research', 5, '333445555', '22-MAY-1978'
'Administration', 4, '987654321', '01-JAN-1985'
'Headquarters', 1, '888665555', '19-JUN-1971'
'Software', 6, '111111100', '15-MAY-1999'
'Hardware', 7, '444444400', '15-MAY-1998'
'Sales', 8, '555555500', '01-JAN-1997'
'HR', 9, '112244668', '01-FEB-1989'
'Networking', 3, '110110110', '15-MAY-2009'
'QA', 11, '913323708', '2-FEB-2010'

What am I missing in the query? Why is that the Manager_Ssn doesn't load? Any help is appreciated.

Well the error message is pretty clear, at least to me. The third column of your data is text but apparently the DEPARTMENT table is expecting an int/numeric data type there. You should post your table definition. — Tim Biegeleisen
– Tim Biegeleisen, Commented Jul 18, 2017 at 15:28
@TimBiegeleisen - The third column is a BIGINT Tim. The weird part is that, I was able to insert with the same query, the data similar with another table and its working good! — Ramji Seetharaman
– Ramji Seetharaman, Commented Jul 18, 2017 at 15:35
DESC DEPARTMENT; | Field | Type | Null | Key | Default | Extra | | Dnumber | tinyint(4) | NO | PRI | NULL | | | Mgr_ssn | bigint(20) | YES | | NULL | | This is the schema and if you can see the Dnumber is TINYINT — Ramji Seetharaman
– Ramji Seetharaman, Commented Jul 18, 2017 at 15:36
And the data for the Dnumber gets inserted. This is the result of the query Dname | Dnumber | Mgr_ssn | Mgr_start_date | | 'Headquarters' | 1 | 0 | '19-JUN-1971' | |'Networking' | 3 | 0 | '15-MAY-2009' |'Administration' | 4 | 0 | '01-JAN-1985' |'Research' | 5 | 0 | '22-MAY-1978' |'Software' | 6 | 0 | '15-MAY-1999' — Ramji Seetharaman
– Ramji Seetharaman, Commented Jul 18, 2017 at 15:36

wchiquito · Accepted Answer · 2017-07-18 17:18:52Z

Change:

FIELDS TERMINATED BY ',' by FIELDS TERMINATED BY ', '.
Format the Mgr_start_date column.

Try:

File: /path/to/file/DEPARTMENT.txt:

'Research', 5, '333445555', '22-MAY-1978'
'Administration', 4, '987654321', '01-JAN-1985'
'Headquarters', 1, '888665555', '19-JUN-1971'
'Software', 6, '111111100', '15-MAY-1999'
'Hardware', 7, '444444400', '15-MAY-1998'
'Sales', 8, '555555500', '01-JAN-1997'
'HR', 9, '112244668', '01-FEB-1989'
'Networking', 3, '110110110', '15-MAY-2009'
'QA', 11, '913323708', '2-FEB-2010'

MySQL Command-Line:

mysql> DROP TABLE IF EXISTS `DEPARTMENT`;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE IF NOT EXISTS `DEPARTMENT` (
    ->   `Dname` VARCHAR(255),
    ->   `Dnumber` TINYINT(4),
    ->   `Mgr_ssn` BIGINT(20),
    ->   `Mgr_start_date` DATE
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql> LOAD DATA LOCAL INFILE '/path/to/file/DEPARTMENT.txt'
    ->   INTO TABLE DEPARTMENT
    ->   FIELDS TERMINATED BY ', '
    ->   ENCLOSED BY "'"
    ->   LINES TERMINATED BY "\n"
    ->   (`Dname`, `Dnumber`, `Mgr_ssn`, @`Mgr_start_date`)
    ->   SET `Mgr_start_date` = STR_TO_DATE(@`Mgr_start_date`, '%d-%M-%Y');
Query OK, 9 rows affected (0.00 sec)
Records: 9  Deleted: 0  Skipped: 0  Warnings: 0

mysql> SELECT
    ->   `Dname`,
    ->   `Dnumber`,
    ->   `Mgr_ssn`,
    ->   `Mgr_start_date`
    -> FROM
    ->   `DEPARTMENT`;
+----------------+---------+-----------+----------------+
| Dname          | Dnumber | Mgr_ssn   | Mgr_start_date |
+----------------+---------+-----------+----------------+
| Research       |       5 | 333445555 | 1978-05-22     |
| Administration |       4 | 987654321 | 1985-01-01     |
| Headquarters   |       1 | 888665555 | 1971-06-19     |
| Software       |       6 | 111111100 | 1999-05-15     |
| Hardware       |       7 | 444444400 | 1998-05-15     |
| Sales          |       8 | 555555500 | 1997-01-01     |
| HR             |       9 | 112244668 | 1989-02-01     |
| Networking     |       3 | 110110110 | 2009-05-15     |
| QA             |      11 | 913323708 | 2010-02-02     |
+----------------+---------+-----------+----------------+
9 rows in set (0.00 sec)

Collectives™ on Stack Overflow

Loading data into mysql LOAD DATA INFILE difficulty

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related