HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMinor

MySQL import csv only gets half the lines

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
thegetshalfcsvmysqllinesonlyimport

Problem

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

Solution

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

Code Snippets

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

Context

StackExchange Database Administrators Q#15423, answer score: 2

Revisions (0)

No revisions yet.