Load Data InFile Issues

[fry2010]

Im just after some advice and also help with a problem I have.

 

I am trying to insert around 1 million rows, in CSV format using load data infile. I am aware and can use the command to insert it into a basic table with no foreign keys, however I wish to use foreign keys for 6 of the columns.

 

The first issue is I am getting the usual 'Foreign key constraint failed'. Now, I have gone through the file creating a seperate table of data in this csv format to use as the foreign table. I then search in this table to find the resulting insert ID, and insert that into the main table . eg:

 

MAIN TABLE

IDtitleforeignId

1'test'164

 

 

FOREIGN TABLE

foreignIdtitle

164'match this please'

163'don't match this'

 

 

So the first step I take, is go through the CSV file and populate the foreign table with one of the columns data. Using a query as such:

 

LOAD DATA INFILE 'test.csv'
IGNORE INTO TABLE foreignTable
  FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
  LINES TERMINATED BY '\r\n'
  IGNORE 1 LINES
    (@dummy, @dummy,title);

 

This goes through all 1 million rows ok, and only inserts unique rows.

 

 

 

Next step I want to insert the data into the main table using a similair query to above, but selecting from the foreign table for the correct ID:

 

LOAD DATA INFILE 'test.csv'
IGNORE INTO TABLE foreignTable
  FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
  LINES TERMINATED BY '\r\n'
  IGNORE 1 LINES
    (@dummy,title, @foreignId)
SET 
foreignId = (SELECT foreignId FROM foreignTable WHERE title = @foreignId)

 

This is when I get a foreign key constraint failure.

 

So what are my options, ideally keeping the foreign key?

 

I imagine what is happening is it finds a row that does not contain a match to the foreignTitle, so it does not provide a foreignId for the insert to use. Therefore it fails the entire query.

 

So is there a way I can get it to use a different value if it failed to find a foreign key id? I have thought of using this:

 

(SELECT foreignId FROM foreignTable WHERE title = @foreignId OR '1' = '1')

 

This does work, but it does not attempt to find the real value first before going to the OR query. SSo my foreignIds are all the same, even though they shudnt be.

 

 

I realise this kind of question is a real long shot here. Im open to suggestions.

 

cheers.

[fry2010]

also, is there a way to LIMIT a load data infile?

 

It appears that it doesnt insert all 1 million rows in one go, so I am using IGNORE XXX LINES to skip them.

 

Is there a way to see how many of those rows in the CSV file that had been read?

[fry2010]

I have got around the problem now, by setting the foreign fields to allow NULL values, instead of setting to NOT NULL.

I also discovered that you cant limit a load data infile, but it is a high requested feature in the mysql boards.

 

Just thought I would record that here in case anyone else runs into a similair problem.