csv importer tweeks

ali_p

I am tweeking a csv import to mysql script and I'm a little stuck. Firstly all of my database entries are wrapped in double quotes (" ") which I don't want and secondly I would like to not include the first line of the csv as this is just cell names. The things that I tweek to achieve both of these results always seem to be incorrect. Here is the script that I am using. Is there something that I am doing wrong?

Thanks in advance



$databasehost = "dbase";
$databasename = "name";
$databasetable = "table";
$databaseusername ="user";
$databasepassword = "password";
$fieldseparator = ",";
$lineseparator = "\n";
$csvfile = "name.csv";


$addauto = 0;


if(!file_exists($csvfile)) {
	echo "File not found. Make sure you specified the correct path.\n";
	exit;
}

$file = fopen($csvfile,"r");

if(!$file) {
	echo "Error opening data file.\n";
	exit;
}

$size = filesize($csvfile);

if(!$size) {
	echo "File is empty.\n";
	exit;
}

$csvcontent = fread($file,$size);

fclose($file);

$con = @mysql_connect($databasehost,$databaseusername,$databasepassword) or die(mysql_error());
@mysql_select_db($databasename) or die(mysql_error());

$lines = 0;
$queries = "";
$linearray = array();

foreach(split($lineseparator,$csvcontent) as $line) {

$lines++;

$line = trim($line," \t");

$line = str_replace("\r","",$line);

/************************************
This line escapes the special character. remove it if entries are already escaped in the csv file
************************************/
$line = str_replace("'","\'",$line);
/*************************************/

$linearray = explode($fieldseparator,$line);

$linemysql = implode("','",$linearray);

if($addauto)
	$query = "insert into $databasetable values('','$linemysql');";
else
	$query = "insert into $databasetable values('$linemysql');";

$queries .= $query . "\n";

@mysql_query($query);
}

@mysql_close($con);

if($save) {

if(!is_writable($outputfile)) {
	echo "File is not writable, check permissions.\n";
}

else {
	$file2 = fopen($outputfile,"w");

	if(!$file2) {
		echo "Error writing to the output file.\n";
	}
	else {
		fwrite($file2,$queries);
		fclose($file2);
	}
}

}

echo "Found a total of $lines records in this csv file.\n";

bradgrafelman

ali_p wrote:
Is there something that I am doing wrong?

First thing I notice that's wrong is that you're using [man]fread/man to read the entire file and not [man]fgetcsv/man to read each entry in the CSV one-by-one.

ali_p wrote:
I would like to not include the first line of the csv as this is just cell names.

You can throw away the header line in a CSV simply by calling [man]fgets/man and ignoring the data (e.g. no need to store it in a variable or actually do anything with it).

Then again, if the names of the columns in the CSV have any correlation to the column names in the DB, it might be a lot easier to use [man]fgetcsv/man to retrieve the header row and use that array to keep the "names" of the cells when you retrieve the actual data. That way, you could ignore things like this:

    $linearray = explode($fieldseparator,$line); 

$linemysql = implode("','",$linearray);

which gives you no control over how the data is inserted into the SQL query.

EDIT: Also, there's no reason you should be using the '@' error suppressor (ever, IMHO). If you don't want errors, either write intelligent code that can handle error conditions or else turn off display_errors.