Take data from a csv .csv file and insert into the DB.

[ameriblog]

I have a .csv (comma delimited) file that is on my server with information like:

 

ID,Headline,Description

 

What I would like to do is write some PHP/mysql code so that when I go to the PHP code page it reads/opens/whatever the .csv and takes the content of that file and inserts it into the database.

 

$ID = first column;
$headline = second column;
$description = third column;

$sql = "UPDATE mytable SET headline = $headline, description = $description WHERE ID = $ID";
$add_rs = $conn->Execute($sql) or die ( $conn->ErrorMsg() );		

[redbullmarky]

if you have phpMyAdmin, it supports importing data from CSV files. just specify the seperator, line ending types, and data columns, and Bob's your Uncle.

 

Failing that - if it's not just a one off and a particular feature you need, then fgetcsv() will set you on your way.

[ameriblog]

I can use phpMyAdmin, that's what I'm using now, I was thinking it may be easier to do it this way, then again maybe not

[chronister]

I actually have a csv importer. It takes the CSV file and makes an associative array with the field names.

 

 

dbwrite.php  or whatever you want to name it

 

<form enctype="multipart/form-data" action="<?=$_SERVER['PHP_SELF'] ?>" method="post">
      <!-- MAX_FILE_SIZE must precede the file input field -->
      <input type="hidden" name="MAX_FILE_SIZE" value="3000000" />
      <!-- Name of input element determines name in $_FILES array -->
      Upload File:
  <input name="csv_file" type="file" id="csv_file" />
  <input type="submit" value="Send File" name="send_file" />
        </form>
<?php
/////////////////////////////////////
//     process uploaded joke file //
////////////////////////////////////
if($_POST['send_file'])

{
echo $_FILES['csv_file']['type'];
include($_SERVER['DOCUMENT_ROOT'].'/etc/get_csv.php'); // change this to the path of the csv file
	if($_POST['send_file'])
	{
		connectdb_com();

		$x=0;
	while($x < count($jokes))
	{
		$question=addslashes($jokes[$x]['question']); // change this to the field name
		$answer=addslashes(str_replace('"',"'",$jokes[$x]['answer'])); //change this to the field name

			$query="INSERT INTO jokes (question,answer) VALUES ('$question','$answer')";
			$result=mysql_query($query) or die(mysql_error());

		$x++;						
	}
   $message='<div align="center" id="message">Successfully imported '. $x .' records</div> ';
	};
};
?>

 

get_csv.php

 

<?php
if($_POST['send_file']){
$file=$_FILES['csv_file']['tmp_name'];

if (is_uploaded_file($file)) {
   
   
//Move through a CSV file, and output an associative array for each line
ini_set("auto_detect_line_endings", 1);
$current_row = 1;
$handle = fopen("$file", "r");
while ( ($data = fgetcsv($handle, 100000, ",") ) !== FALSE )
{
   $number_of_fields = count($data);
   if ($current_row == 1)
   {
   //Header line
       for ($c=0; $c < $number_of_fields; $c++)
       {
           $header_array[$c] = $data[$c];
       }
   }
   else
   {
   //Data line
       for ($c=0; $c < $number_of_fields; $c++)
       {
           $data_array[$header_array[$c]] = $data[$c];
       }
        //print_r($data_array);
	$jokes[]=$data_array; //set this var to the name you want to access the data with

   }
   $current_row++;
}
fclose($handle);
};

};
?>

 

As you can see, this will accept a csv file from an upload form and process it and add it to a mysql db.

 

Enjoy!

[gigantorTRON]

Thanks man! That helps a lot!