CODEWITHSUNDEEP
mysqlcsv
cerides
cerides

Reputation: 13

How to end the LOAD DATA INFILE upon encountering a certain field

Here is the SQL query I have so far:

LOAD DATA INFILE 'filename.csv'
INTO TABLE zt_accubid
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 4 LINES

I need to be able to end the process once a field with value="xyz" is encountered. Is this possible?

Upvotes: 1

Views: 135

Answers (2)

Eugen Rieck
Eugen Rieck

Reputation: 65334

LOAD DATA INFILE has no such option. There are a couple of workaraounds, though

  • You could solve the problem outside MySQL by manipulating the file first
  • As Alain Collins mentioned, if the column containing you marker has only unique values, and you don't have the LOAD DATA inside a transcation, you can use a unique key as a stopper.
  • You can use a trigger on the table as a stopper
  • If the marker is near the end of the table or the overhead is not important to you, you can do a full LOAD DATA into an interims table, then use INSERT INTO ... SELECT to move only the relevant data into your final table
  • Similarily you can load all data, then delete the irellevant part

Upvotes: 1

Alain Collins
Alain Collins

Reputation: 16362

Sure - put a row in the database before the load, and put a unique key on it. The LOAD should fail when it hits the duplicate.

Upvotes: 0

Related Questions