CSV File Upload to MySQL

General PHP Help
#1

Hi All,

I have a bit of a problem I was hoping for some help. I have a script that takes a CSV file and adds fields to the database. What I need is instead of having to write the values over and over, just have the data go in without the headers. The fields (Columns) are static at this point and I want to see if I can just take a whole file and upload it to the database.

[php] //get the csv file
$file = $_FILES[csv][tmp_name];
$handle = fopen($file,“r”);

//loop through the csv file and insert into database
do {
    if ($data[0]) {
        $mysql_query = mysql_query("INSERT INTO carbon (VALUE1, VALUE2, VALUE3, VALUE4, VALUE5, VALUE6, VALUE7, VALUE8, VALUE9, VALUE10, VALUE11, VALUE12, VALUE13, VALUE14) VALUES
            (
                '".addslashes($data[0])."',
                '".addslashes($data[1])."',
                '".addslashes($data[2])."',
                '".addslashes($data[3])."',
                '".addslashes($data[4])."',
                '".addslashes($data[5])."',
                '".addslashes($data[6])."',
                '".addslashes($data[7])."',
                '".addslashes($data[8])."',
                '".addslashes($data[9])."',
                '".addslashes($data[10])."',
                '".addslashes($data[11])."',
                '".addslashes($data[12])."',
                '".addslashes($data[13])."'
            )
        ");
    }
} while ($data = fgetcsv($handle,10000,",","'"));
//

//redirect[/php]

Here is all of the Code I am using:

[php]<?php

include ‘_inc/include.php’;
if ($_FILES[csv][size] > 0) {

//get the csv file
$file = $_FILES[csv][tmp_name];
$handle = fopen($file,"r");

//loop through the csv file and insert into database
do {
    if ($data[0]) {
        $mysql_query = mysql_query("INSERT INTO carbon (VALUE1, VALUE2, VALUE3, VALUE4, VALUE5, VALUE6, VALUE7, VALUE8, VALUE9, VALUE10, VALUE11, VALUE12, VALUE13, VALUE14) VALUES
            (
                '".addslashes($data[0])."',
                '".addslashes($data[1])."',
                '".addslashes($data[2])."',
                '".addslashes($data[3])."',
                '".addslashes($data[4])."',
                '".addslashes($data[5])."',
                '".addslashes($data[6])."',
                '".addslashes($data[7])."',
                '".addslashes($data[8])."',
                '".addslashes($data[9])."',
                '".addslashes($data[10])."',
                '".addslashes($data[11])."',
                '".addslashes($data[12])."',
                '".addslashes($data[13])."'
            )
        ");
    }
} while ($data = fgetcsv($handle,10000,",","'"));
//

//redirect

}

?>

Import a CSV File with PHP & MySQL <?php if (!empty($_GET[success])) { echo "Your file has been imported by import 55.

"; } //generic success notice ?> Choose your file:

Display Data Contents here

[/php]
#2

change to a for loop so you can skip the first line. instead of starting the counter at 0, you would start it at 1. This is how i did the same thing (except mine is delimited with a tab instead of a comma)
[php]
for($i = 1; $i < count($fcontents); $i++) {

$line = $fcontents[$i];
$arr = explode("\t", $line);
					
foreach($arr as $key => $value) {
	$arr[$key] = trim(mysql_real_escape_string($value));
}
unset($arr[36]);
					
mysql_query("INSERT INTO {$companyTbl} VALUES ('', '" . implode("', '", $arr) . "')");
					
unlink($file);

}[/php]

#3

I would like to propose an alternative using PDO.

[php]
$data = array(
‘value1’,
‘value2’,
‘value3’,
‘value4’,
‘value5’,
‘value6’,
‘value7’,
‘value8’,
‘value9’
);

$dbh->prepare(“INSERT INTO table VALUES (” . implode(",", array_fill(0, count($data), “?”)) . “)”);
$dbh->execute($data);
[/php]

#4

Wow This is Great! I can’t believe I could not figure that out! Thanks HUGE!. I will try both of these first chance I get. Thank You Both!!!

#5

Just note that my example is using PDO and will not work with mysql_* functions.

Sponsor our Newsletter | Privacy Policy | Terms of Service