php - MySQL LOAD DATA INFILE store line number

Question

I'm using MySQL LOAD DATA INFILE to move CSV file data into a database table. I'm looking for a way to reference the original file line number (row) in the imported record.

So that a table like this:

CREATE TABLE tableName (
  uniqueId INT UNSIGNED NOT NULL PRIMARY_KEY,
  import_file VARCHAR(100),
  import_line INT,
  import_date = DATETIME,
  fieldA VARCHAR(100),
  fieldB VARCHAR(100),
  fieldC VARCHAR(100)
);

Where import_file, import_line and import_date are meta data relevant to the specific file import. fieldA, fieldB and fieldC represent the actual data in the file.

Would be updated by a query like this:

LOAD DATA INFILE '$file' 
REPLACE
INTO TABLE '$tableName' 
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATES BY '
'
IGNORE 1 LINES # first row is column headers
(fieldA,fieldB,fieldC)
SET import_date = now(), 
import_file = '" . addslashes($file) . "', 
import_line = '???';

Is there a variable I can set 'import_line' to?

Thanks,

-M

Answer 1

You can do that by setting a user variable first, and increment this variable in your SET clause, i.e.

SET @a:=0;                                       -- initialize the line count
LOAD DATA INFILE 'c:/tools/import.csv'           -- my test import 
REPLACE
INTO TABLE tableName 
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '
'
IGNORE 1 LINES # first row is column headers
(fieldA,fieldB,fieldC)
SET import_date = now(), 
import_file = 'import.csv',               
import_line = @a:=@a+1;                          -- save the incremented line count