import csv data into database according to header

[IreneLing]

Hello all.

 

Here is my script...

 

if (isset($_POST['submit'])) {
if (is_uploaded_file($_FILES['filename']['tmp_name'])) {
	echo "<h1>" . "File ". $_FILES['filename']['name'] ." uploaded successfully." . "<br></h1>";
	echo "<h2>Displaying contents:</h2>";
	readfile($_FILES['filename']['tmp_name']);
	echo "<br>";
	echo $headers;
}


$handle = fopen($_FILES['filename']['tmp_name'], "r");

$header = fgetcsv($handle);

while(! feof($handle)){

while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
	$import="INSERT into CSVtest($header[0],$header[1],$header[2],$header[3],$header[4]) 
                         values
                         ('$data[0]','$data[1]','$data[2]','$data[3]','$data[4]')";

	mysql_query($import) or die(mysql_error());
}

}

fclose($handle);

echo "Done";

}

 

This is the way how I insert data into database according to header even it's not arranged same as the table column.

If the csv has only "cell,firstname,lastname,," (and my table has only these 3 columns as well)  then it still working...but if there are "cell,address,company,firstname,lastname" then it will show me unknown column error.

 

Actually I wish to have a function that even there are many columns in csv that not exist in the table it still can just pick the right column and insert into table.

Can anyone give me hints on how to change my script?I know there are many errors...it's really embarrassed to show my low standard script...

 

Thanks in advance.

[requinix]

You need a list of columns in the table. You can hardcode that into your script or do something dynamic using a SHOW COLUMNS FROM table query.

Then make sure you only use columns in that list. As long as you put the names into the INSERT like you're doing now the order of those columns doesn't matter. (Or you could use the INSERT... SET syntax.)

[IreneLing]

Thanks for your reply.

So now I try something like this...

 

if (isset($_POST['submit'])) {

if (is_uploaded_file($_FILES['filename']['tmp_name'])) {
	echo "<h1>" . "File ". $_FILES['filename']['name'] ." uploaded successfully." . "<br></h1>";
	echo "<h2>Displaying contents:</h2>";
	readfile($_FILES['filename']['tmp_name']);
	echo "<br>";
	echo $headers;
}


$handle = fopen($_FILES['filename']['tmp_name'], "r");

$header = fgetcsv($handle);


while(! feof($handle)){


while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {

                    $num = count($data);

                   mysql_select_db("EMC", $db);

                   $sql = "SELECT * from CSVtest";
                   $result = mysql_query($sql,$db);

                       while ($user_table_property = mysql_fetch_field($result)) 
                                {

                      for($i=0; $i<$num; $i++){
                         
                                      if($header[$i] == $user_table_property->name )
                                                   {

                                                    $import = "insert into CSVtest ( `" . $header[$i] . "`) values ('" . $data[$i] . "')";

                                                   }



                                               }

                                  mysql_query($import) or die(mysql_error()) ; 
                                }
                                               
                            



                                                       } 

    
                   }

 

The column is correct , but each data will inserted into different rows,and some will duplicate....

[jcbones]

Here is a user supplied function from the php manual page (slightly modified), and it should fit your use.

 

<?php

function csv_file_to_mysql_table($source_file, $target_table, $max_line_length=10000) {
    if (($handle = fopen($source_file, "r")) !== FALSE) {
        $columns = fgetcsv($handle, $max_line_length, ",");
        foreach ($columns as &$column) {
            $column = str_replace(".","",$column);
        }
        while (($data = fgetcsv($handle, $max_line_length, ",")) !== FALSE) {
            while(count($data) < count($columns)) {
			array_push($data, NULL);
		}
		$c = count($data);
		for($i = 0; $i < $c; $i++) {
			$data[$i] = "'{$data[$i]}'";
		}
		$sql[] = '(' . implode(',',$data) . ')';
        }
	$query = "INSERT INTO $target_table (".implode(",",$columns).")VALUES " . implode(',',$sql) . "\n";
	//mysql_query($query) or trigger_error(mysql_error());
	echo $query;
        fclose($handle);
    }
} 

$file = 'test.csv';
$table = 'test';

csv_file_to_mysql_table($file,$table);

 

Right now, it just prints to the page to show you what the query will look like, just remove the comment marks on the query line to insert it.

[IreneLing]

Here is a user supplied function from the php manual page (slightly modified), and it should fit your use.

 

Right now, it just prints to the page to show you what the query will look like, just remove the comment marks on the query line to insert it.

 

jcbones , I really wish can treat you to lunch if I live near your place , yes it fit my use , and it works like a charm.

 

Thank you so much , I really appreciate your help.