HYGEN

PHP - Load Plain Text CSV Data Into MySQL / Convert CSV To MySQL

January 26th, 2010 | by admin |

The resulting MySQL table after loading CSV data into MyDQL

For a while now, possibly like many other developers, I’ve been coding in PHP using CSV related functions such as fgetcsv() and explode(). This usually works out fine until you want to read data from a CSV file and the store the data inside a MySQL database. Using mysql_query inside a loop is not fun for your servers processor!

As usual, the best way to do things when it comes to PHP & MySQL is to wherever possible get MySQL to do most of the work. Here is an example PHP script for effectively converting a CSV format database into a MySQL table. It presumes you already have a working connection and have selected a database.

The queries being executed here by PHP could equally be be given as commands directly to MySQL on the command line. However the real power and reasoning for using PHP is that you can then work with web applications.

Most of the hard work is done for you by MySQL with this part of the SQL:

LOAD DATA LOCAL INFILE ‘drug.txt ‘
INTO TABLE QRYM_DRUG_PRODUCT
FIELDS TERMINATED BY ‘,’ ENCLOSED BY ‘\”‘
LINES TERMINATED BY ‘\r\n’

This tells MySQL to load the external CSV format text file from the current working directory (in this case where the php code is being run from). It then gives the name of the table in the MySQL database (QRYM_DRUG_PRODUCT) for the data to be stored in and explains that fields end with a comma (change this to a tab or pipe as necessary) and that filed data is enclosed in ” marks. End of lines (records) are marked by new lines - “\r\n”. Change any of these to suit the format of the CSV file you are working with before running the code.

// ------------------------------------------------------------
/*
drug.txt     QRYM_DRUG_PRODUCT
 
ENTITY:   QRYM_DRUG_PRODUCT;
 Name                                   Null?     Type
 ------------------------------- -------- ----
 DRUG_CODE                              NOT NULL  NUMBER(8)
 PRODUCT_CATEGORIZATION                           VARCHAR2(80)
 CLASS                                            VARCHAR2(40)
 DRUG_IDENTIFICATION_NUMBER                       VARCHAR2(8)
 BRAND_NAME                                       VARCHAR2(200)
 PEDIATRIC_FLAG                                   VARCHAR2(1)
 ACCESSION_NUMBER                                 VARCHAR2(5)
 NUMBER_OF_AIS                                    VARCHAR2(10)
 LAST_UPDATE_DATE                                 DATE
 AI_GROUP_NO					  VARCHAR2(10)
*/
$Query = "drop table if exists QRYM_DRUG_PRODUCT;";
mysql_query ($Query);
echo mysql_error();
 
$Query = "create table QRYM_DRUG_PRODUCT (
id int not null auto_increment primary key, 
drug_code int, 
product_categorization text, 
class text, 
drug_identification_number text, 
brand_name text, 
pediatric_flag text, 
accession_number text, 
number_of_ais text, 
last_update_date text, 
ai_group_number text);";
mysql_query ($Query);
echo mysql_error();
 
$Query = "LOAD DATA LOCAL INFILE 'drug.txt '
INTO TABLE QRYM_DRUG_PRODUCT
FIELDS TERMINATED BY ',' ENCLOSED BY '\"'
LINES TERMINATED BY '\r\n'
(drug_code, product_categorization, class, drug_identification_number, brand_name, pediatric_flag, accession_number, number_of_ais, last_update_date, ai_group_number);";
mysql_query ($Query);
echo mysql_error();

Using this method to create 10 MySQL tables being created and populated with about 600,000 records in total the PHP script took only about 5 seconds to execute on my workstation. Compare that to loading and looping through rows of the CSV files and making SQL insert statements if you dare