mysql load data infile examples

I’ve recently built a database that requires data to be imported from many different sources. The porject is a centralised database of products offered by varing suppliers all who keep their individual product lists in their own format.

Now one ‘long winded ‘way to do this would be to write a PHP script (I’m using the Zend Framework here) to import each file, which I may have to do at some point. However I’ve found that because most product lists are supplied in some form of spreadsheet, exporting the list as a csv and importing into mysql using LOAD DATA INFILE syntax works best, and is super quick.

The basic structure of a LOAD DATA INFILE is displayed on the MySQL manual page, however it leaves alot to the imagination with the naff examples. So here’s one I’ve created.

Firstly, our sql table structure looks like this:

create table products (
  id int not null auto_increment primary key,
  product_unique varchar(40),
  name text,
  description text,
  price double,
  stock_number int,
  owner_product_code varchar(5),
  product_type varchar(25),
  supplier_id int
);

Our Data set looks like this:

Reference;Price;Product
AL-3511;48;Alcatel one touch 311 / 511 data cable
AL-525;39;Data Cable for Alcatel OT525
AL-DB;39;Alcatel DB data cable
AL-DB357;43;Alcatel DB/30x/501/701 data cable
AL-EA;39;Alcatel Easy data cable
AL-TD5;108;Data Kit for Alcatel 501 / 701
AL2-3511;39;Alcatel one touch 311 / 511 dual data cable
BO-67;39;Bosch 607/738 data cable
BO-98;39;Bosch 908/909 data cable
BOUC-5;39;Bosch 508/509 (C-BUS) data cable
BOUC-98;39;Bosch 908/909 (C-BUS) data cable
C55-COM;39;Siemens C55 Serial Data Cable
C55-USB;39;USB Siemens Data Cable for C55 and S55
CM-30;45;Siemens Data Cable C30 / M30
CS-25;39;Siemens C25/S25/C35/S35 data cable
DAE-8x8;39;Ericsson 8x8 data cable for data transmission only
DAF-567;39;Nokia 51xx/61xx data cable DAU-9P with FBUS
DI-27;162;Ericsson Mobile Office DI-27
DI-28;168;DI-28 Infrared Modem for Ericsson T28 and compatibles
DKU-2;42;Nokia DKU-2 USB cable for Nokia 3300 / 6650
DKU-5;60;Nokia DKU-5 Data Cable 7210, 6610, 3570 and 3585
DRS-11;48;Ericsson DRS-11 / RS232 data cable

LOAD DATA LOCAL INFILE 'D:\\data-directory\products.csv' INTO TABLE products 
FIELDS TERMINATED BY ';'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(@type, price, @name) 
SET product_unique = MD5(UUID()), name = @name, description = @name, owner_product_code = @type, product_type = @type, supplier_id = 123456

Now breaking that statement down we can see the file path is a Windows file path. So use a backslashe to escape a backslash. *nix based file systems use the forward path. I always find it’s easiest to add an absolute path.
We tell MySQL that the separator in our case is a semi-colon, it can be a comma. You should always add this in.
Then specifiy your line terminator character. I did this example on windows and a line-feed character was enough. No need for ‘\r\n’. I would always add this in too.
Next we know that line 1 is a row of headings, so we tell it to ignore that line. We could have modified our data list slightly to remove the first line and then we would no longer need the ignore x lines command.
Now the column list. This specifies the list of columns in the import file. This took me a while to get my head around because this list will take either a products table column name or a MySQL variable. So providing a column name will store the imported value at that line and cell in the column.
By saving an imported value to a variable, we can re-use it and if necessary store it in multiple columns.
The final line is where you can set individual columns to any value you like. Here you can specifiy expressions or simply constant values such as the supplierId.

I hope this example is clear. If not leave a comment 🙂