MySQL import csv only gets half the lines

Question

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

RolandoMySQLDBA · Accepted Answer · 2012-03-22 17:30:54Z

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';

Yep, the file came from a Windows System. Thanks!

Tyler DeWitt
– Tyler DeWitt

2012-03-22 17:37:38 +00:00
Commented Mar 22, 2012 at 17:37 — Tyler DeWitt
– Tyler DeWitt, Commented Mar 22, 2012 at 17:37

handler · Accepted Answer · 2024-07-10 04:29:18Z

Here is a work around for this.

Go to this website: https://www.convertcsv.com/csv-to-sql.htm and upload your csv into it. It will convert your csv to .sql file with create, and insert queries and other queries if you want them too. Then open the sql script normally as you would do with any other .sql file. With this, no need to go through any hassle of cleaning data or checking every row of csv.

hoogw · Accepted Answer · 2022-03-22 17:10:37Z

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.

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

Stack Exchange Network

MySQL import csv only gets half the lines

3 Answers 3

Linked

Hot Network Questions

MySQL import csv only gets half the lines

3 Answers 3

Linked

Related

Hot Network Questions