4

I use the following command to read in a csv:

LOAD DATA INFILE '/Users/Tyler/Desktop/players_escaped.txt' 
INTO TABLE players
FIELDS TERMINATED BY ',' 
ENCLOSED BY '^'
LINES TERMINATED BY '\n';

The csv looks like this:

^1^,^False^,^False^,^Ovie^,^Soko^,^6^,^8^,^210^,^^,^M^,^London^,^^,^^,^^,^^,^0^,^2009^,^^,^False^,^False^,^{299F909C-88D9-4D26-8ADC-3EC1A66168BB}^,^844^,^2013^,^^,^^,^0^,^^,^^,^2011-02-16 20:53:34.877000000^,^^,^2011-02-16 20:53:34.877000000^,^^,^^,^^,^^,^^,^^,^^,^^,^^,^1^,^2^,^^,^^,^^,^^,^^,^^,^^,^^,^^,^^
^2^,^False^,^False^,^Jordan^,^Swing^,^6^,^6^,^200^,^^,^M^,^Birmingham^,^AL^,^35218^,^^,^^,^0^,^2009^,^^,^False^,^False^,^{299F909C-88D9-4D26-8ADC-3EC1A66168BB}^,^844^,^2013^,^^,^^,^0^,^^,^^,^2011-02-16 20:53:34.877000000^,^^,^2011-02-16 20:53:34.877000000^,^^,^^,^^,^^,^^,^^,^^,^^,^^,^1^,^2^,^^,^^,^^,^^,^^,^^,^^,^^,^^,^^

I also tried \ as a delimiter and got the same results.

I'm only getting the odd numbered rows.

There are 250k records in the csv.

Thanks

2 Answers 2

2

You might have to check the last character of each line

Instead of this:

LOAD DATA INFILE '/Users/Tyler/Desktop/players_escaped.txt'  
INTO TABLE players 
FIELDS TERMINATED BY ','  
ENCLOSED BY '^' 
LINES TERMINATED BY '\n'; 

Try this:

LOAD DATA INFILE '/Users/Tyler/Desktop/players_escaped.txt'  
INTO TABLE players 
FIELDS TERMINATED BY ','  
ENCLOSED BY '^' 
LINES TERMINATED BY '\r\n'; 
1
  • Yep, the file came from a Windows System. Thanks! Mar 22, 2012 at 17:37
-1

enter image description here

Do not use command line, it need set up environment parameter to allow you import from local csv file.

Instead use import wizard is much easier for me. enter image description here

However I only import first 100 lines.

I solve it by choose latin1 instead of default utf-8

https://bugs.mysql.com/bug.php?id=89044

default utf-8 not work, latin1 works

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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