headers and blank lines in csv file

General PHP Help
#1

I am working on inserting data into mysql database. I am able to do this except I cannot figure out how to remove the header and blank lines from the csv file. Here is my script.
[php]<?php
if(isset($_POST[“Import”]))
{
include("…/dbcc.php");
$host=“localhost”; // Host name.
$db_user=“xxxxx”;
$db_password=“xxxxx”;
$db=‘xxxx’; // Database name.
$con=@new mysqli($db_s,$db_us,$db_p,$db_n);
$co = mysql_connect(“localhost”,“xxxxx “,” xxxxx”) or die (mysql_error());
mysql_select_db(“databasename”) or die(mysql_error());

	echo $filename=$_FILES["file"]["tmp_name"];
	//echo $ext=substr($filename,strrpos($filename,"."),(strlen($filename)-strrpos($filename,".")));
if($_FILES["file"]["size"] > 0)
{
	$file = fopen($filename, "r");
		
	
		while (($emapData = fgetcsv($file, 10000, ",")) !== FALSE)
		{
			//print_r($emapData);
			$sql = "INSERT into schedule(CRN,CRS,NBR,SEC,BILL_HRS,CR_HRS,LINK,CRS_TITLE,XLST_CD,XLST_CRN,XLST_CRS,MAX_ENROLL,ACT_ENROLL,
	SCHD_CD,INST_METH,1_MTYP,1_DAY,1_TIME,1_BLDG,1_RM,2_MTYP,2_DAY,2_TIME,2_BLDG,2_RM,PRIMARY_INSTR_ID,PRIMARY_INSTR_NAME,INSTR_ID,
	INSTR_NAME,SUBTITLE,NOTES,CRS_START_DTE,CRS_END_DTE) values('$emapData[0]','$emapData[1]','$emapData[2]','$emapData[3]',
	'$emapData[4]','$emapData[5]','$emapData[6]','$emapData[7]','$emapData[8]','$emapData[9]','$emapData[10]',
	'$emapData[11]','$emapData[12]','$emapData[13]','$emapData[14]','$emapData[15]','$emapData[16]','$emapData[17]',
	'$emapData[18]','$emapData[19]','$emapData[20]','$emapData[21]','$emapData[22]','$emapData[23]','$emapData[24]',
	'$emapData[25]','$emapData[26]','$emapData[27]','$emapData[28]','$emapData[29]','$emapData[30]','$emapData[31]','$emapData[32]')";
			mysql_query($sql);
		}
     fclose($file);
		echo "CSV File has been successfully Imported";
}	
else
	echo "Invalid File:Please Upload CSV File";

}

?>[/php]

#2

I figured out how to remove the headers, if anyone can direct me to a site that tells me how to remove the blank lines, that would be awesome.

#3

Wrap your query and INSERT code in:

[php]if(!empty($emapData)) {
//$sql and mysql_query()
}[/php]

Alternately you could forgo the fopen() and use something like:

[php]$lines = file($filename, FILE_IGNORE_NEW_LINES | FILE_SKIP_EMPTY_LINES);
unset($lines[0]); //if headings are there

foreach($lines as $line) {
$emapData = str_getcsv($line);
//$sql and mysql_query()
}[/php]

Also, assuming all columns in the CSV are being used you can just implode() them:

[php]$values = “’” . implode("’,’", $emapData) . “’”;
$sql = “INSERT into schedule(CRN,CRS,NBR,SEC,BILL_HRS,CR_HRS,LINK,CRS_TITLE,XLST_CD,XLST_CRN,XLST_CRS,MAX_ENROLL,ACT_ENROLL,
SCHD_CD,INST_METH,1_MTYP,1_DAY,1_TIME,1_BLDG,1_RM,2_MTYP,2_DAY,2_TIME,2_BLDG,2_RM,PRIMARY_INSTR_ID,PRIMARY_INSTR_NAME,INSTR_ID,
INSTR_NAME,SUBTITLE,NOTES,CRS_START_DTE,CRS_END_DTE) values($values)”;[/php]

Depending upon whether you are specifying all columns or not you could also remove the column names from the query.

And to put it all together (except the previous comment) you can do it in one query:

[php]foreach($lines as $line) {
$emapData = str_getcsv($line);
$values[] = “(’” . implode("’,’", $emapData) . “’)”;
}
$values = implode(’,’, $values);

$sql = “INSERT into schedule(CRN,CRS,NBR,SEC,BILL_HRS,CR_HRS,LINK,CRS_TITLE,XLST_CD,XLST_CRN,XLST_CRS,MAX_ENROLL,ACT_ENROLL,
SCHD_CD,INST_METH,1_MTYP,1_DAY,1_TIME,1_BLDG,1_RM,2_MTYP,2_DAY,2_TIME,2_BLDG,2_RM,PRIMARY_INSTR_ID,PRIMARY_INSTR_NAME,INSTR_ID,
INSTR_NAME,SUBTITLE,NOTES,CRS_START_DTE,CRS_END_DTE) values $values”;[/php]

Sponsor our Newsletter | Privacy Policy | Terms of Service