Inserting CSV file into a MySql Database - HELP!!!!

[asaschool]

Hey everybody,

I am stuck working with a csv file that needs to be inserted into a mysql database. This one is driving me crazy. Can anybody tell me where I am messing up here. Maybe my CSV file has some errors in it or my script is wrong. Here is what I am trying to use:

<?php 
// connect to DB
require_once('Datafile.php');
mysql_select_db($database_database, $database);

//Move through a CSV file, and output an associative array for each line 
ini_set("auto_detect_line_endings", 1); 
$current_row = 1; 
$matches = glob('*.csv'); 
$name = basename($matches[0]);
$handle = fopen($name, "r"); 
while ( ($data = fgetcsv($handle, 10000, ",") ) !== 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]; 
        } 

// Sort through thte data and clean up the data.
//Code removed to shorten the script.

// Loop through the csv file and enter the data into the MySql database.
$insertSQL = sprintf("INSERT INTO data_sheet (one, two, three, four, five, six, seven, eight, nine, ten, eleven, twelve, thirteen, fourteen, fifteen, sixteen, seventeen, eighteen, nineteen, twenty, twentyone) VALUES ($dataOne, $dataTwo, $dataThree, $dataFour, $dataFive, $dataSix, $dataSeven, $dataEight, $dataNine, $dataTen, $dataEleven, $dataTwelve, $dataThirteen, $dataFourteen, $dataFifteen, $dataSixteen, $dataSeventeen, $dataEighteen, $dataNineteen, $dataTwenty, $dataTwentyOne)");

mysql_select_db($database_database, $database);
$Result1 = mysql_query($insertSQL, $database) or die(mysql_error());

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

 

When I run the code I get the following error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near.........

 

I am cleaning up the data making sure to remove all single quotes, ticks and other bad items. When I look at the csv data, it doesnt look like it should be doing this. PLEASE HELP - ANYTHING HELPS

 

[PFMaBiSmAd]

A) If you were to post the part of the actual mysql error message that comes after the single-quote in the right syntax to use near ', someone could directly help you because that is point in the query where something that was found that could not be figured out,

B) All string data must be escaped to prevent any possible special SQL characters in the data from breaking the syntax of the query, use the mysql_real_escape_string() function on each piece of string data, and

C) Each piece of string data must be enclosed in single-quotes so that it is treated as a string instead of an identifier or a reserved keyword.

[marksie1988]

try this one, you will need to adapt it slightly but it should work wonders for you works great for me you will need to add your sql connection and if you use mysql change the mssql bits to mysql, put ignore on csv fields you dont want to import in the array and put the colum title in the DB in the array if you want it importing

 

<?php
// define table name and csv file location and name

$csvupload = "../advent/pricelist.csv";

mssql_query($sql2) or die("Failed to insert to db ");

// Define DB mapping (Fill in rest of the mapping in order of appearance in CSV)
$fields = array(
"supp_part_no",
"manf_part_no",
"description",
"avail_qty",
"cost_price",
"ignore",
"ignore"
);
// Open the CSV file
$handle = fopen($csvupload, "r") or die("Unable to open $csvupload for reading");

// Get each row's values to an array 
$i = 1;
// define tab delimited or comma
while (($data = fgetcsv($handle, 512, ",")) !== FALSE) {
	// First row's headers and not included, otherwise row field count must match wanted field count
	if(count($data)==count($fields) && $i!=1){
		$rows[] = $data;
	}
	elseif(count($data)!=count($fields)) {			
		die("Erroneus CSV file: incorrect field count on row $i");
	}
	$i++;
}

// Close file handle
fclose($handle);

// Create SQL
if(count($rows)==0) die("No rows found in user file");

 // Remove last comma
foreach($rows as $i => $row){
	$sql = "INSERT INTO ".$table." (";
foreach($fields as $field)
	if($field!="ignore")
		$sql .= "$field,";
$sql = substr($sql,0,-1).") VALUES ";
	$sql .= "(";
	foreach($row as $j => $value){
		if($fields[$j]!="ignore"){
			if(empty($value))
				$value = "NULL";
			// Quote strings, try to remove existing quotes
			elseif(!is_numeric($value))
				$value = "\"".str_replace("\"","",$value)."\"";
			$sql .= "$value,";
		}
	}

	$sql = substr($sql,0,-1).")"; // Remove last comma
	//echo "SQL became: $sql";

	mssql_query($sql) or die("Failed to insert to db ");

}
?>