Check for header in csv file

candice · September 18, 2008

Hi all, I have a csv file that i wish to upload to the database, and i was wondering what is the code snippet that can help me check whether csv file has a header. Thanks!!

Barand · September 18, 2008

If you know that one of the data items in each row should always be a number then you can test for a header row using is_numeric() on that item. eg

suppose the csv file is

"ID","FNAME","LNAME"
1,"John","Doe"
2,"Mary","Smith"

then

$fp = fopen('my.csv', 'r');
while ($data = fgetcsv($fp, 1024))
{
    if (!is_numeric($data[0])) continue; // skip header
    // process data
}
fclose($fp);

candice · September 18, 2008

Thanks a million! I'll give me a try

candice · September 21, 2008

Hi, I tried your method, but i getting this error.

Warning: fopen('Subject Schedule2 - Excel (extracted 31 July).csv') [function.fopen]: failed to open stream: No such file or directory in C:\wamp\www\web portal (staff)v1\import2.php on line 121

Warning: fgetcsv() expects parameter 1 to be resource, boolean given in C:\wamp\www\web portal (staff)v1\import2.php on line 123

SQL ERROR:Query was empty

do you know what is the problem?

attached below is my current codes

<?php 
session_start();
echo $name;
ob_start();


?>

<?php require_once('staffSql.php');
?>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">

<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Staff Signage: Web Portal</title>

<link href="style.css" rel="stylesheet" type="text/css" />
</head>

<body>
<table width="800" border="0" align="center" cellpadding="0" cellspacing="0">
  <tr>
    <td colspan="4"><?php include("./include/headerAdmin.php"); ?></td>
  </tr>
  <tr>
    <td colspan="4" bgcolor="#FFFFFF" height="6"></td>
  </tr>
  <tr>
    <td width="5" valign="top" bgcolor="#FFFFFF"> </td>
    <td width="175" align="center" valign="top" bgcolor="#FFFFFF"><?php include("./include/leftBar.php");?></td>
    <td width="7" bgcolor="#FFFFFF"> </td>
    <td width="614" valign="top" bgcolor="#FFFFFF"><table width="605" border="0" align="center" cellspacing="0">
      <tr>
        <td height="20" valign="middle" bgcolor="#437AB6"><span class="contentHeader">   Import Staff Timetable</span></td>
      </tr>
      <tr>      </tr>
      <tr>
        <td valign="top" bgcolor = "#FAFAF5"><form action="import2.php" method="post" enctype="multipart/form-data">
            <div align="center">
              <p align="left" class="contentText">Here, you can upload a new staff timetable into the database with each new semester. The old timetable would be replaced once the new one has been uploaded into the database. Please note that <strong>ONLY CSV </strong>files are allowed to be uploaded to the database. There is however, no file size limit.</p>
              <p align="center" class="contentText">File Path
                <input type="file" name="fileupload" id="fileupload" />
              </p>
              <p align="center">
                <input type="submit" name="upload" id="upload" value="Submit" />
              </p>
              <p align="center"><strong>NOTE: You do not have to remove the header at the csv file</strong></p>
              <p align="center">
              
              <?php 

//FUNCTION TO GET THE EXTENSION OF THE FILE THAT IS BEING UPLOADED
function getExtension($str) {
$i = strrpos($str,".");         
if (!$i) { 
return ""; 
}         
$l = strlen($str) - $i;         
$ext = substr($str,$i+1,$l);         
return $ext; 
}


function GetFilename($file) {

    $filename = substr($file, strrpos($file,'/')+1,strlen($file)-strrpos($file,'/'));

    return $filename;

}


//UPLOADING FILE
if(isset($_POST['upload']))
{

$filetype = $_FILES['fileupload']['type'];

if(empty($filetype)){

	echo'<font color="red"><span class = "contentText"><b>Please click on the browse button to locate your file</b></span></font>';
	//echo "<script language=\"JavaScript\">";
	//echo "alert('Please click on the browse button to locate your file');";
}

if($filetype){

	$csvfile = stripslashes($_FILES['fileupload']['name']); 	

	$extension = getExtension($csvfile); 	

	$extension = strtolower($extension);

	//echo $extension;

	if (($extension != "csv") && ($extension != "CSV")){	

		echo'<font color="red"><span class = "contentText"><b>Invalid file type. Please upload only .csv files</b></span></font>';
		/*echo "<script language=\"JavaScript\">";
		echo "alert('Invalid file type. Please upload only .csv files');";
		echo "</script>"; 
		*/
	}
	else {

		$uploaddir = 'C:/wamp/www/web portal (staff)v1/uploads/';

		$uploadfile = $uploaddir . basename($_FILES['fileupload']['name']);

		echo $csvname; 

		echo $uploaddir; 

		echo $uploadfile; 

		$csvname = GetFilename($uploadfile);

		$fp = fopen("'$csvname'", "r"); 

		while ($data = fgetcsv($fp, 1000)){


    			if (!is_numeric($data[0])){ // skip header
    				// process data
					$query = 'LOAD DATA INFILE "'.$uploadfile.'" REPLACE
					INTO TABLE timetable 
					FIELDS TERMINATED BY ","
					OPTIONALLY ENCLOSED BY """"
					LINES TERMINATED BY "\r\n"
					IGNORE 1 LINES';
			}

			else{
				$query = 'LOAD DATA INFILE "'.$uploadfile.'" REPLACE
				INTO TABLE timetable 
				FIELDS TERMINATED BY ","
				OPTIONALLY ENCLOSED BY """"
				LINES TERMINATED BY "\r\n"';
			}
		}
	}


		//TRUNCATE THE FILE BEFORE INSERTING IT INTO THE DATABASE
		mysql_select_db($dbname, $root);

		mysql_select_db($dbname, $root); 

		mysql_query("TRUNCATE TABLE timetable") or die("MySQL Error: " . mysql_error()); //Delete the existing rows

		//LOAD DATA FILE INTO DATABASE


		mysql_query($query, $root) or die('SQL ERROR:'.mysql_error()); //Insert in the new values into the database




		fclose($fp);
}


}


?></p>
            </div>
        </form></td>
      </tr>
    </table>    
    <p><br />
    </p>
    <p> </p>
    <p> </p>
    <p> </p>
    <p>           </p></td>
  </tr>
  <tr>
    <td colspan="4"><?php include("include/footer.php") ?></td>
  </tr>
</table>
</body>

</html>
<?php ob_flush(); ?>

Sign In

Check for header in csv file

Recommended Posts

candice

Link to comment

Share on other sites

Barand

Link to comment

Share on other sites

candice

Link to comment

Share on other sites

candice

Link to comment

Share on other sites

Archived

Browse

Activity

Important Information