Customer Support

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:

<?
   # first get a mysql connection as per the FAQ

  $fcontents = file ('./spreadsheet.xls'); 
  # expects the csv file to be in the same dir as this script

  for($i=0; $i<sizeof($fcontents); $i++) { 
      $line = trim($fcontents[$i]); 
      $arr = explode("\t", $line); 
      #if your data is comma separated
      # instead of tab separated, 
      # change the '\t' above to ',' 
     
      $sql = "insert into TABLENAME values ('". 
                  implode("','", $arr) ."')"; 
      mysql_query($sql);
      echo $sql ."<br>\n";
      if(mysql_error()) {
         echo mysql_error() ."<br>\n";
      } 
}
?>

Upload a spreadsheet file into the same directory as this script. Then
you edit this script to put in the correct table name instead of 
"TABLENAME".

So long as your xls file is tab delimited and has 1 row per line with
all columns in the same order that your mysql database columns are, then
this script will pull all the data out of your XLS file and insert it
all into mysql. Don't forget to connect to mysql in the script before
anything else with a mysql_connect() and mysql_select_db() as shown here
http://www.modwest.com/help/kb6-60.html

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

From the example above:

<?
   # first get a mysql connection as per the FAQ

  $fcontents = file ('./spreadsheet.xls'); 
  # expects the csv file to be in the same dir as this script

  for($i=0; $i<sizeof($fcontents); $i++) { 
      $line = trim($fcontents[$i]); 
      $arr = explode("\t", $line); 
      #if your data is comma separated
      # instead of tab separated, 
      # change the '\t' above to ',' 
     
      $sql = "insert into TABLENAME values ('". 
                  implode("','", $arr) ."')"; 
      mysql_query($sql);
      echo $sql ."<br>\n";
      if(mysql_error()) {
         echo mysql_error() ."<br>\n";
      } 
}
?>

Many feeds have blank fields. To maintain the correct field count in
each row for the fields in the database, change this line:     $line =
trim($fcontents[$i]);   TO      $line = trim($fcontents[$i], '\t');  for
tab delimited files or  ',' for comma delimited, etc.

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:
<?php
connection = mysql_connect("localhost", "test", "test") or die ("Unable
to connect to server");
      3 $db = mysql_select_db("test", $connection) or die ("Unable to
select database");
      4
      5  #mysql connection as per the FAQ
      6
      7    $fcontents = file ('./test.csv');
      8      # expects the csv file to be in the same dir as this script
      9
     10        for($i=0; $i<sizeof($fcontents); $i++) {
     11        $line = trim($fcontents[$i]);
     12        echo "$line<BR>";
     13        $arr = explode("\"", $line);
     14        echo "$arr";
     15        #if your data is comma separated
     16        # instead of tab separated,
     17        # change the '\t' above to ','
     18
     19        $sql = "insert into test values (". implode("'", $arr)
.")";
     20        #$sql = "insert into test values ('". $arr ."')";
     21        mysql_query($sql);
     22        echo $sql ."<br>\n";
     23        if(mysql_error()) {
     24        echo mysql_error() ."<br>\n";
     25        }
     26    }
     27  ?>

I'm sure there is a better way to do the above, but for now this will
do.

Have a look at http://www.php.net/fgetcsv for an easier way to import
CSV files

How to do this from a php script:
http://www.webmasterworld.com/forum88/10592.htm

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.

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

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

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.

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.    

Don't forget to add slashes before making your SQL query. Simply add...

 array_map('addslashes', $arr);

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);







<?php

 # first get a mysql connection as per the FAQ

 include "database.php";

 $fcontents = file ('./operators.csv');

 # expects the csv file to be in the same dir as this script

 for($i=0; $i<sizeof($fcontents); $i++) {

         $line = trim($fcontents[$i],',');   //   '\t' for tab delimeted
        $arr = explode(',', $line);         //   '\t' for tab delimeted

        #  if your data is comma separated
        #  instead of tab separated
        #  change the '\t' above to ','

        $sql = "insert into operators values ('". implode("','", $arr)
."')";

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

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

        mysql_query($sql); echo $sql ."<br>\n";

        if(mysql_error()) {
                echo mysql_error() ."<br><b><font color=red>\n</b>";
        }

 } ?>

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.