MySQL Database

ID #253

How do I import delimited data into MySQL?

Applies to: Grid System

If you have data that you need to bring into your MySQL database, there are a few ways to do it. Exporting data out of mysql is another topic, described here.

1. Using the LOAD DATA INFILE SQL statement

For security reasons, no one has the mysql FILE priv, which means you cannot "LOAD DATA INFILE". You can, however, use a "LOAD DATA LOCAL INFILE" statement as long as you have a mysql prompt on our system and have uploaded the data file to your account here first.

The "LOAD DATA LOCAL INFILE" statement will only work from a MySQL prompt on our local system. It will not work from any web-based tool such as phpMyAdmin, and will never pull a file in directly off your own computer.

To import a file this way, first upload your data file to your home directory on our system with FTP or SCP. Then get a shell prompt on our system, and then a MySQL Monitor prompt so that you can issue the SQL that will import your file.

For example, suppose you have a data file named importfile.csv that contains 3 comma separated columns of data on each line. You want to import this textfile into your MySQL table named test_table, which has 3 columns that are named field1, field2 and field3.

To import the datafile, first upload it to your home directory, so that the file is now located at /importfile.csv on our local system. Then you type the following SQL at the mysql prompt:

LOAD DATA LOCAL INFILE '/importfile.csv'
INTO TABLE test_table
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
(field1, filed2, field3);

The above SQL statement tells the MySQL server to find your INFILE on the LOCAL filesystem, to read each line of the file as a separate row, to treat any comma character as a column delimiter, and to put it into your MySQL test_table as columns field1, field2, and field3 respectively. Many of the above SQL clauses are optional and you should read the MySQL documentation on the proper use of this statement.

2. Using a script to parse and import the file

You can also write a script in any programming language that can connect to MySQL (such as PHP) to open your data file, break it up into an array of lines that each represent a row of data, split each line up by the delimiter character (such as a comma ',', tab '\t', semicolon ';', space ' ', etc.), and then perform invididual MySQL INSERT queries (one INSERT for each line) to insert all your data from the file into the appropriate table fields.

Such scripts are not difficult to write in less than 15 lines and can import data from text files just as effectively as a LOAD DATA LOCAL INFILE command. A working example script written in PHP appears below in the Annotations.

3. Importing a mysqldump

If your data file actually comes from another MySQL database, and not from Excel or any other source, then the most direct way to export and import your data would be to dump out your table or entire MySQL database on the original database server using the mysqldump command, FTP the resulting dump file to your account here, and then import the dump file at a shell prompt.

For instructions on creating the dumpfile using the mysqldump command, see this FAQ. For instructions on how to import a dump made with mysqldump, see this FAQ.


Last update: 2010-10-03 17:00
Author: FAQ Admin
Revision: 1.2

Digg it! Share on Facebook Print this record Send FAQ to a friend Show this as PDF file
Please rate this FAQ:

Average rating: 3.66 (189 Votes)

completely useless 1 2 3 4 5 most valuable

You can comment this FAQ

Comment of Anonymous:
If you need to take a csv or other delimited data file and import it INTO MySQL, here is a php script that can do it for you:


Added at: 2002-06-14 01:44

Comment of Anonymous:
Here is an example of creating a data file and then importing it with LOAD DATA syntax.

mulder@modwest:/$ echo 'a b c d' > testfile
mulder@modwest:/$ mysql

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3979754 to server version: 4.0.15-Max-log

mysql> CREATE TABLE ´test´ (
-> ´fe´ VARCHAR( 2 ),
-> ´fi´ VARCHAR( 2 ),
-> ´fo´ VARCHAR( 2 ),
-> ´fum´ VARCHAR( 2 )
-> );
Query OK, 0 rows affected (0.00 sec)

mysql> load data LOCAL infile '/testfile' into table test
fields terminated by ' ' lines terminated by '\n';
Query OK, 1 row affected (0.02 sec)
Records: 1 Deleted: 0 Skipped: 0 Warnings: 0

mysql> select * from test;
+------+------+------+------+
| fe | fi | fo | fum |
+------+------+------+------+
| a | b | c | d |
+------+------+------+------+
1 row in set (0.00 sec)

mysql> load data LOCAL infile '/testfile' into table test
fields terminated by ' ' lines terminated by '\n' (fum, fo, fi, fe);

Query OK, 1 row affected (0.02 sec)
Records: 1 Deleted: 0 Skipped: 0 Warnings: 0

mysql> select * from test;
+------+------+------+------+
| fe | fi | fo | fum |
+------+------+------+------+
| a | b | c | d |
| d | c | b | a |
+------+------+------+------+
2 rows in set (0.00 sec)

mysql> exit


If you don't have your .my.cnf preference file set as per:
http://www.modwest.com/help/kb.phtml?cat=6&qid=242
then you won't get into the Monitor with just a 'mysql'.

You'll need to provide all the required parameters as per:
http://www.modwest.com/help/kb6-60.html
Added at: 2004-01-04 16:38

Comment of Anonymous:
From the example above:


Added at: 2005-05-07 22:01

Comment of Anonymous:
The code was not doing what I had in mind, so I modified the code untill I was finally able to accomplish what I needed to do.

the test.csv files contains data llike:
"name","userid"
"john","jon"

there were only two lines that I had to modify from the original code:

$arr = explode("\"", $line);
and
$sql = "insert into test values (". implode("'", $arr) .")";

Full code:

Added at: 2005-05-17 11:39

Comment of Anonymous:
Have a look at http://www.php.net/fgetcsv for an easier way to import CSV files
Added at: 2005-10-17 20:10

Comment of Anonymous:
How to do this from a php script:
http://www.webmasterworld.com/forum88/10592.htm
Added at: 2005-10-28 14:39

Comment of Anonymous:
Hi
I have PHP 5 and MYSQL set up on localhost Win XP Home.
Do what you want with this in PHP it imports a csv file. Cheers

LOAD DATA INFILE "./ImportData.csv"
INTO TABLE table1
FIELDS TERMINATED BY ","
OPTIONALLY ENCLOSED BY """"
LINES TERMINATED BY "\r\n";

OPTIONALLY ENCLOSED is optional.
Added at: 2006-02-17 23:11

Comment of Anonymous:
The fgetcsv PHP function was able to save me in this case. Here's a code snippet:

$handle = fopen ('./file.csv', 'r');
while (($data = fgetcsv($handle, 1000, ',', '"')) !== FALSE)
{
$query = "INSERT INTO services VALUES ('". implode("','", $data) ."')";
$query = @mysql_query($query);
}

This certainly got me out of a fix
Added at: 2006-11-02 11:02

Comment of Anonymous:
To load data from Delimited textfiles to MySQL you can try Data Loader 2.0 tool. I find this tool quite good. website http://www.dbload.com
Added at: 2006-11-27 12:13

Comment of Anonymous:
See http://snippets.dzone.com/posts/show/3508 for example on how to load CSV data into a database with open source Scriptella ETL tool.
Added at: 2007-02-14 10:19

Comment of Anonymous:
In response to anonymous -at- example.com 02-Nov-2006 11:02 extremely helpful post, I have an amendment to his query. Problems arise if an apostrophe is contained in any of the csv fields, say a name like O'Rourke. Normally you would use the handy mysql_real_escape_string function, but as this is an array it does not apply. So, instead of enclosing the fields in the Values bit of the query with an apostrophe, try:

$query = "INSERT INTO Exhibitors VALUES (\"". implode("\",\"", $data) ."\")";

Hope this helps someone out.
Added at: 2008-09-16 15:08

Comment of Anonymous:
Don't forget to add slashes before making your SQL query. Simply add...

array_map('addslashes', $arr);


Added at: 2008-11-04 00:33

Comment of Anonymous:
If your database will not accept '' as a blank, and you need a NULL instead, add this code before mysql_querty($sql);

$sql = str_replace("' '", mysql_escape_string("NULL"), $sql);








Added at: 2009-05-06 12:41

Comment of Anonymous:
Be mindful of the text encoding. I was doing this kind of project and entering thousands of blank rows, it took me a while to realize the text encoding of the source document was UTF-16.

I created a second array after each line of the document to hold the converted contents of the original. Then did the INSERT INTO with the contents of the converted arrary.
Added at: 2009-05-08 14:30

Comment of Anonymous:
I needed to be able to set up a process that imported & replaced 2 tables with .csv files that were uploaded every 8 hours.

I created a cron job to call a php url where I house the following code. I also require a key & a command to start the following code. The following code also assumes you have a db connection & $table1 & $table2 $inputfile1 & inputfile2 are defined:



// Empty the tables before importing updated records
mysql_query("TRUNCATE TABLE $table1");
mysql_query("TRUNCATE TABLE $table2");

// Load all records into table 1
mysql_query("LOAD DATA LOCAL INFILE '$inputfile1' REPLACE INTO TABLE $table1 FIELDS TERMINATED BY ',' ".
" OPTIONALLY ENCLOSED BY '\"' LINES TERMINATED BY '\\n' IGNORE 1 LINES");

// Load all records into table 2
mysql_query("LOAD DATA LOCAL INFILE '$inputfile2' REPLACE INTO TABLE $table2 FIELDS TERMINATED BY ',' ".
" OPTIONALLY ENCLOSED BY '\"' LINES TERMINATED BY '\\n' IGNORE 1 LINES");

// Count how many records imported
$Result = @mysql_query("SELECT count(*) FROM $table1");
$Row = @mysql_fetch_row($Result);
$cnt1 = $Row[0];

// Count how many records imported
$Result = @mysql_query("SELECT count(*) FROM $table2");
$Row = @mysql_fetch_row($Result);
$cnt2 = $Row[0];

// Display a success message indicating how many records imported
echo ''.$cnt1.' records imported from table 1';
echo ''.$cnt2.' records imported from table 2';
mysql_close();
//
Added at: 2009-11-24 13:44