CODEWITHSUNDEEP
mysql
NR03
NR03

Reputation: 41

LOAD DATA LOCAL INFILE custom value

How to add a custom value using LOAD DATA LOCAL INFILE? The column time_added is the 7th column and the file has only 2 values for the first and the second column. For the 7th column, time_added I want to use the unix timestamp when loading from file.

This code isn't working:

$result = mysql_query("LOAD DATA LOCAL INFILE '{$myFile}' INTO TABLE {$table} FIELDS TERMINATED BY ':' LINES TERMINATED BY '\n' SET `time_added`=unix_timestamp()");

Upvotes: 1

Views: 1324

Answers (2)

wingr
wingr

Reputation: 2630

The answer given by @iouri indicates the key element to address your question, namely the explicit listing of the columns populated by the .csv file, (column1, column2). This line informs the LOAD function to only consider these columns when loading data from the .csv file and avoids an error similar to Row 1 doesn't contain data for all columns.

You will still need to list all columns, including custom columns, in the table definition. Also, the column names listed in the parentheses should match the names of the columns defined in the table definition. For example, if the table definition specifies two columns named user and id then you would need to have the line (user, id) above the SET column7 = unix_timestamp() line.

You may also want to double check that you want LOAD DATA LOCAL INFILE instead of LOAD DATA INFILE (no LOCAL). As specified in the documentation for load-data, the LOCAL keyword affects the expected location of the file and both the server and client must be configured properly to allow for using the LOCAL option.

Upvotes: 2

iouri
iouri

Reputation: 2929

Why wouldn't this work?

LOAD DATA INFILE 'file.txt'
  INTO TABLE t1
  (column1, column2)
  SET column7 = unix_timestamp();

Upvotes: 2

Related Questions