Load data infile not affecting any rows

Question

I have been trying to upload my user.txt file in mysql workbench but its not working. It run successfully but no row or column gets updated. It returns

LOAD DATA INFILE 'C:\\ProgramData\\MySQL\\MySQL Server 8.0\\Uploads\\user.txt' 
 INTO TABLE main.users
 FIELDS TERMINATED BY '|'
 LINES TERMINATED BY '{CR}{LF}'
 IGNORE 1 LINES 0 row(s) affected
 Records: 0  Deleted: 0  Skipped: 0  Warnings: 0    24.860 sec 

The code i am trying to execute is

LOAD DATA INFILE 'C:\\ProgramData\\MySQL\\MySQL Server 8.0\\Uploads\\user.txt' 
INTO TABLE main.users
FIELDS TERMINATED BY '|'
LINES TERMINATED BY '{CR}{LF}'
IGNORE 1 LINES;

If i give path name as C:\ProgramData\MySQL\MySQL Server 8.0\Uploads\user.txt It throw error

 The MySQL server is running with the --secure-file-priv option so it cannot execute this statement 0.000 sec

I also tried changing Lines Termiated by '\r\n' and also with '\r\n' but nothing works. I also tried to followed all the stackoverflow question on this problem such as How should I tackle --secure-file-priv in MySQL?

import text file into mysql workbench?

My user.txt has over 10,000 records in the following manner

UserId|FirstName|LastName|Email|Password|Address
1|John|Doe|johndoe@gmail.com|password|Address
2|John|Doe|johndoe@gmail.com|password|Address

Can anyone please tell me where i am doing wrong and how can i fix this issue. Thank you

Answer 1

I suspect that LOAD DATA is seeing only a single line, and the IGNORE 1 LINES is causing the entire file to be read as a single line.

I recommend you figure out what the line terminator actually is.

On PC/DOS/Windows, the line end is usually carriage return + newline characters, which is represented in MySQL as '\r\n'

On Unix/Linux, the line end is is usually just '\n' character.

I suggest you try running with

LINES TERMINATED BY '\n'

And see how big a smokeball that makes.

If you get carriage return characters at the end of the address field, that means the line terminator is probably PC/DOS/Windows style

LINES TERMINATED BY '\r\n'

---

I haven't a clue where you came up with using '{CR}{LF}', that sounds like something we'd find on w3fools

---

FOLLOWUP

Q: " I tried with \n and also with \r and \r\n. These gives me error saying column is too big its truncated. Only when i do \\r or \\n then only the query runs. But after 30 second it says 0 rows effected. "

Q: " It says in row 1 truncated because it contain more data then input field. But i have exact amout of input fields as it required . So i am guessing line break is not working. When i do '\\r\\n' then the query run but the mysql server connection get lost. Could it be because of my big file size? "

A: The Data truncated for column warning means that a value didn't "fit" into the allotted storage for the column in the table.

Did that happen on every column, or some particular columns? The first column? Was that warning thrown for the first row and every row? Note that the warning message actually includes the name of the column and identifies the row.

(I've never actually encountered a "column is too big" error with LOAD DATA.)

What is the actual warning message? Is it an error or a warning? (The output returned by MySQL isn't unintelligible gibberish. The warning message includes some specific information. Translating the text of the error message into "column is too big its truncated" is unnecessary obfuscation. That's not helping you to actually diagnose the problem.

(I'm wondering if your client is handling warnings as errors? From the mysql command line client, we can issue a SHOW WARNINGS statement.)

If you are getting MySQL warning 1262 Row n was truncated; it contained more data than there were input columns, then that means LOAD DATA is seeing more fields in the line from the file than the number of columns in the table.

Consider explicitly listing the columns in the LOAD DATA statement; and that those "line up" with the fields in the file. Note that we can include a user defined variable(s) in the list

LINES TERMINATED BY '\r\n'
IGNORE 1 LINES
( `userid`
, `firstname`
, `lastname`
, `email`
, `password`
, `address`
, @field7
, @field8 
)

The first field will be loaded into the first listed identifier, in this case, userid as a reference to a column named userid in main.users table. The second field will be loaded into the second listed column firstname.

Identifiers prefixed with an at sign symbol @ are references to user-defined variables, not columns in the table.

---

Do not specify '\\n' as the line delimiter; that is going to cause LOAD DATA to search for a literal backslash character immediately followed by an "n".

The newline character is represented in MySQL string literal as just '\n'.

And that should work as far as identifying the end of the line. The problem we have is that if its a DOS/Windows PC formatted file, there's going to be a carriage return character, and if we don't specify that as part of the line terminator, that carriage return character is going to be a character included as part of the last field, which we probably don't want.

If we're not sure, and can't tell, then get this tested specifying just the newline character as the line terminator.

If we know the line terminator is DOS/Windows PC-style '\r\n', then we can go ahead and specify that.

(We would only need to escape that backslash character if that string is being passed through a bash shell, or other programming language that interprets a string and swallows backslash characters before they get passed to MySQL. We don't encounter that issue in the MySQL command line client.)

I strongly suggest we create a smaller file to test with, maybe containing just five lines... the header line and four data lines.