CODEWITHSUNDEEP
mysql
Courtney
Courtney

Reputation: 43

MySQL 5.6 - load data infile chopping out data (no nulls involved)

I came across this issue while working on a project, but here is a simple example that replicates the "bug" ...

First create a table with the final column being some sort of varchar:

Create table testtable(
ProductName varchar(100),
Color varchar(50),
CategoryID varchar(5)
);

Create a tab delimited text file with data:

Shirt   Red 1111
Pants   Blue    2222
Shoes   Green   3333

Upload data into table:

load data infile 'testtable.txt' into table testtable;

I get no errors or warnings or anything. But, when I try to query the data, it looks rather odd:

select * from testtable;
    ProductName Color   CategoryID
                Red     1111
                Blue    2222
        Shoes   Green   3333

Queries that would join on ProductName won't work.

Next, create a table with an int as the last column:

Create table fixedtable(
ProductName varchar(100),
Color varchar(50),
CategoryID varchar(5),
Inventory int
);

Create a tab delimited text file with data:

Shirt   Red 1111    10
Pants   Blue    2222    15
Shoes   Green   3333    20

Upload data into table:

load data infile 'fixedtable.txt' into table fixedtable;

And now the table looks fine:

    select * from fixedtable;
ProductName Color   CategoryID  Inventory
    Shirt   Red     1111        10
    Pants   Blue    2222        15
    Shoes   Green   3333        20

I haven't tried replicating this using "Insert Into" or anything like that; for this project I specifically need to use the "load data infile" statement.

This might be easier to explain with screenshots, but I lack the proper reputation... I wonder if anyone else gets this result on their machine? For the record, I'm using Windows 7.

Am I doing something wrong? Any idea what is causing this?

Upvotes: 4

Views: 248

Answers (1)

madebydavid
madebydavid

Reputation: 6527

I can reproduce this. The behaviour is strange, I assume that MySQL's import can "guess" a little better when it knows to expect a number at the end of the line.

I believe that the issue is happening because you are on windows and so the text files have \r\n at the end of each line rather than \n. When you use LOAD DATA INFILE with no settings then it uses the defaults, where lines are terminated with a \n. Source - http://dev.mysql.com/doc/refman/5.6/en/load-data.html search for "If you specify no FIELDS or LINES clause".

To fix it, just specify the LINES TERMINATED BY "\r\n" part. Example:

mysql> Create table testtable(
-> ProductName varchar(100),
-> Color varchar(50),
-> CategoryID varchar(5)
-> );
Query OK, 0 rows affected (0.12 sec)

mysql> load data infile 'testtable.txt' into table testtable;
Query OK, 3 rows affected (0.01 sec)
Records: 3  Deleted: 0  Skipped: 0  Warnings: 0

mysql> SELECT * FROM testtable;
+-------------+-------+------------+
| ProductName | Color | CategoryID |
+-------------+-------+------------+
  |       | Red   | 1111
  |       | Blue  | 2222
| Shoes       | Green | 3333       |
+-------------+-------+------------+
3 rows in set (0.00 sec)

mysql> DELETE FROM testtable;
Query OK, 3 rows affected (0.02 sec)

mysql> load data infile 'testtable.txt' into table testtable
    -> LINES TERMINATED BY '\r\n';
Query OK, 3 rows affected (0.01 sec)
Records: 3  Deleted: 0  Skipped: 0  Warnings: 0

mysql> SELECT * FROM testtable;
+-------------+-------+------------+
| ProductName | Color | CategoryID |
+-------------+-------+------------+
| Shirt       | Red   | 1111       |
| Pants       | Blue  | 2222       |
| Shoes       | Green | 3333       |
+-------------+-------+------------+
3 rows in set (0.00 sec)

So, you would use this SQL:

load data infile 'testtable.txt' into table testtable LINES TERMINATED BY '\r\n';

Upvotes: 1

Related Questions