I have a CSV file that I need to insert into a MySQL table. However the CSV file has a different number of columns to the table, and the columns are in a different order.
The table has these columns:
ID, Product_ID, Order_ID, Shipping_Name, Shipping_Postcode, Tracking_Number
And the CSV has:
Tracking_Number, Order_ID, Name, Postcode
(The Product ID is the name of the file and I can set this as a variable in PHP then update the column, so that’s okay)
I know that you can create fake columns using @ in the query to skip certain columns in the table, but I am not sure how to link columns that are in a different order, or if it is even possible.
Here is my query that I use for when the columns match up:
LOAD DATA INFILE '/var/www/html/imports/tracking/$file' IGNORE INTO TABLE tracking FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY 'n' IGNORE 1 LINES (@product_id, @order_id, @shipping_name, @shipping_postcode, @tracking_number) SET [email protected]_id, [email protected]_id, [email protected]_name, [email protected]_postcode, [email protected]_number";
Can I adjust this for what I need to acheive?