Skip first row of csv file in sql insert

[systm]

Hello,

I have the following script that imports my csv file into sql, the problem I am having is trying to skip the first row (which in excel is the column names) any idea how to do this?

here is the code I am using,

<?php
if(isset($_REQUEST['submit'])) {
/********************************/
/* Code at http://legend.ws/blog/tips-tricks/csv-php-mysql-import/
/* Edit the entries below to reflect the appropriate values
/********************************/
$databasehost = "localhost";
$databasename = "XXXXXX";
$databasetable = "XXXXXXX";
$databaseusername ="XXXXXXXX";
$databasepassword = "XXXXXXXX";
$fieldseparator = ",";
$lineseparator = "\n";
$csvfile = $_FILES["file"]["tmp_name"];
/********************************/
/* Would you like to add an ampty field at the beginning of these records?
/* This is useful if you have a table with the first field being an auto_increment integer
/* and the csv file does not have such as empty field before the records.
/* Set 1 for yes and 0 for no. ATTENTION: don't set to 1 if you are not sure.
/* This can dump data in the wrong fields if this extra field does not exist in the table
/********************************/
$addauto = 1;
/********************************/
/* Would you like to save the mysql queries in a file? If yes set $save to 1.
/* Permission on the file should be set to 777. Either upload a sample file through ftp and
/* change the permissions, or execute at the prompt: touch output.sql && chmod 777 output.sql
/********************************/
$save = 0;
$outputfile = "output.sql";
/********************************/
	$port    	  = $_REQUEST['port'];
	$dept    	  = $_REQUEST['dept'];
	$status       = $_REQUEST['status'];
	$contact      = $_REQUEST['contact'];


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 = 1;
$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);
$date = date("Y-m-d");
$linemysql = implode("','",$linearray);

if($addauto)
	$query = "insert into $databasetable values('$date','$port','$dept','$linemysql','$status','$contact');";
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";
}

?>

Thanks,
Systm

[bradgrafelman]

For one, is there any reason why you're doing all of this processing at all rather than just using MySQL's LOAD DATA INFILE syntax? Seems like that would make things much, much easier.

Failing that, is there any reason why you aren't using [man]fgetcsv/man to read in the CSV data properly?

Finally, if you don't go with the LOAD DATA INFILE query, then you can simply check if $lines is 1 (indicating you're on the first line, obviously) inside the foreach() loop and, if so, simply [man]continue[/man] on to the next loop iteration without doing anything.

[systm]

I am sure there are better ways to do this, I did not write this script. It was the only one that worked for what I needed, this is inserting form variables from another page as well so I had to use this long approach to combine the two.

In the foreach loop? Doesnt the $lines = 0; do this, not sure how I'd add the check into the code... sorry my php isn't my strongest (as you've seen I am sure 🙂)

[bradgrafelman]

systm wrote:

this is inserting form variables from another page as well so I had to use this long approach to combine the two

No, you didn't have to. Since the "variables from another page" is just constant data inserted in each row, you could still use a LOAD DATA INFILE query and set the appropriate columns equal to these values. Sorry if I sound like a broken record, but it seems necessary for me to do so since you could reduce all of that PHP code down to a single SQL query and let MySQL take care of everything on its own. shrug To each his own, I s'pose.

systm wrote:

In the foreach loop? Doesnt the $lines = 0; do this

Erm... well first of all there is no "$lines = 0;" anywhere in your code. So no, it doesn't do that. 🙂

Even if there were, "$lines = 0;" assigns 0 to the variable $lines... not really sure how that sounds like it's doing anything that I mentioned in the 'Finally, ...' part of my post since "$lines = 0;" doesn't contain any of the following: [man]if/man, == (test for equality), [man]continue[/man].

systm wrote:

not sure how I'd add the check into the code

Well, making an attempt would be the first step. 🙂 Really, though, what I suggested above requires only two lines of code (one if you don't care about whitespace/indentation, a few if you're one of those sticklers who always uses {} brace's with every if() statement), of which I've already given you the pieces you'd need to use.

[systm]

Ok so I am looking at rewriting this to use the LOAD DATA INFILE method as I do see it would make things ALOT easier. However I need to know how I can use this in a query with my variables?

right now the user uploads a template csv file with the data for columns 3-7 and my script insert the remaining columns.

from the W3 tutorials and php.net manual I don't see anything that says or shows you can LOAD DATA INFILE with $variables?

So how can this work? I understand the sql query is doing a mass dump of values into the database but the user isn't uploading a csv with all the info, the script is adding this data with the csv on insert.

I would think the script would need to rewrite the csv file and combine the two and then LOAD DATA INFILE? which doesn't seem like 2 lines of code.

Can you show me what this query would look like with the two together, because it I can't seem to find it.

Thanks,
Systm

[bradgrafelman]

On the manual page for LOAD DATA INFILE, there is a comment that shows how you can supply values that aren't in the file and how you can ignore certain columns in the CSV file:

MySQL Manual wrote:

The SET clause can be used to supply values not derived from the input file. The following statement sets column3 to the current date and time:

LOAD DATA INFILE 'file.txt'
  INTO TABLE t1
  (column1, column2)
  SET column3 = CURRENT_TIMESTAMP;

You can also discard an input value by assigning it to a user variable and not assigning the variable to a table column:

LOAD DATA INFILE 'file.txt'
  INTO TABLE t1
  (column1, @dummy, column2, @dummy, column3);

If you combine these two concepts, you should be able to achieve what you're after - loading some data from a CSV file into certain columns, while supplying data for the other columns from the query itself (e.g. the constant data you receive from the other page).