mysql loading csv file plus additional columns into a table

Question

I am reading articles and thread on how to load a csv file into a table, but I also need to add additional columns. Is this possible?

Below is my table:

+-----------+-------------+------+-----+---------+----------------+
| Field     | Type        | Null | Key | Default | Extra          |
+-----------+-------------+------+-----+---------+----------------+
| uid       | int(11)     | NO   | PRI | NULL    | auto_increment |
| lid       | int(11)     | YES  |     | NULL    |                |
| uDate     | date        | YES  |     | NULL    |                |
| cID       | int(11)     | YES  |     | NULL    |                |
| active    | int(1)      | YES  |     | 0       |                |
| user_ID   | varchar(32) | YES  |     | NULL    |                |

above are additional columns I need to pass in, below are all the columns in the CSV file

| fName     | varchar(32) | YES  |     | NULL    |                |
| lName     | varchar(32) | YES  |     | NULL    |                |
| lAddress1 | varchar(64) | YES  |     | NULL    |                |
| lAddress2 | varchar(16) | YES  |     | NULL    |                |
| lCity     | varchar(64) | YES  |     | NULL    |                |
| lState    | varchar(64) | YES  |     | NULL    |                |
| lZip      | varchar(10) | YES  |     | NULL    |                |
| lPhone    | varchar(15) | YES  |     | NULL    |                |
| lEmail    | varchar(96) | YES  |     | NULL    |                |
| field1    | varchar(32) | YES  |     | NULL    |                |
| field2    | varchar(64) | YES  |     | NULL    |                |
| field3    | varchar(64) | YES  |     | NULL    |                |
| regCode   | varchar(32) | YES  |     | NULL    |                |
+-----------+-------------+------+-----+---------+----------------+

And to load the file:

LOAD DATA LOCAL INFILE '/path/to/file/myfile.csv' INTO TABLE myTable
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"' 
LINES TERMINATED BY '\r\n'
(fName,lName,lAddress1,lAddress2,lCity,lState,lZip,field1,field2,field3,regCode) ;

UPDATE

How can I also pass in lid,uDate,cID,active,user_ID ?

These fields are:

lid = listID 
cID = customerID 
active = defaults to inactive, but entrycan be later activated  
user_ID = initially null, but later updated with a properly assigned userID.

In my php script, they are already collected as $lid,$cID,0,$userID

Are they already in the table, and you want to update those rows with the values from the CSV? Does the CSV include the unique ID of the row so we can know which row to update? — Barmar
– Barmar, Commented Sep 12, 2022 at 15:54
@barmar - i update last question to better clarify what the other columns are and where they are coming from — rolinger
– rolinger, Commented Sep 12, 2022 at 15:59

Barmar · Accepted Answer · 2022-09-12 16:07:56Z

1

You can add a SET clause to set additional columns other than the ones in the CSV.

$stmt = $conn->prepare("
    LOAD DATA LOCAL INFILE '/path/to/file/myfile.csv' INTO TABLE myTable
    FIELDS TERMINATED BY ',' 
    ENCLOSED BY '\"' 
    LINES TERMINATED BY '\r\n'
    (fName,lName,lAddress1,lAddress2,lCity,lState,lZip,field1,field2,field3,regCode)
    SET lid = ?, cID = ?, active = 0, user_id = ?");
$stmt->bind_param("iis", $lid, $cID, $userID);
$stmt->execute();

answered Sep 12, 2022 at 16:07

Barmar

789k57 gold badges554 silver badges669 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

rolinger Over a year ago

I did some more indepth reading on LOAD DATA. I can use it for more than just my initial OP. One of which is REPALCE. Using that, is there a way to do a conditional for LOAD DATA LOCAL INFILE $file REPLACE INTO TABLE myTable ... to test if active=1 to not update it or if user_ID != NULL to not update it?

Barmar Over a year ago

You would have to change all the column names in the column list to @fName, @lName, ... format. Then everything gets set in the SET clause:

SET fName = IF(active = 0 AND user_id IS NULL, @fName, fName), SET lName = IF(active = 0 AND user_id IS NULL, @lName, lName), ...

rolinger Over a year ago

Sorry, I meant to say....update everything in the row but the active and userID columns IF those columns have specific values. update row regardless, but only update active and userID columns if conditions are met for each of those columns. if active=1, don't update it. if userID != null don't update it.

Barmar Over a year ago

That's even easier. SET lid = ?, cID = ?, active = IF(active = 1, active, 0), userID = IF(userID IS NULL, ?, userID)

Collectives™ on Stack Overflow

mysql loading csv file plus additional columns into a table

UPDATE

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

UPDATE

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related