Jump to content

CSV aka Comma Separated Values - error checking, iterating through data

etnastyles

By etnastyles
in PHP Coding Help

 Share

Recommended Posts

etnastyles Member

etnastyles

Posted
Posted

Things i need to know asap.......links or tutorial would be sweet..

 

*Read csv file and search for column headers.

*loop through csv rows and run a function that checks for dupilcates.

*output csv into table and then commit into sql db.

 

heres all the code i have to help you... i think im mainly getting confused with the loops.

 

 

------------------------------------------------------------------------------------------------------

<?php

session_start();

require_once("scripts/defs.php");

require_once("scripts/debug.php");

require_once("scripts/database.php");

require_once("scripts/routines.php");

require_once("scripts/func__file.php");

 

 

/*****************************************************************************/

/* Restrict access to page functions return Admin and User Sessions.     */

/*****************************************************************************/

//if ( !Admin() )

// exit("Only Administrators have the ability to add csv files.");

 

define("FORM_NAME",    "csvInputForm");

define("FORM_ACTION",  "file_csv.php");

define("HEADER_IMG",  "<img src=\"./images/redhaloCSV.png\" alt=\"RedHalo CSV Input Logo\" />");

define("DOWNLOAD",    "./help_pages/csv_help.doc");

define("DOWNLOAD_IMG", "<img src=\"./images/download.png\" border=\"0\" alt=\"Download CSV Template\" />");

 

 

//$errors = NULL;

//$file_input = NULL;

//print_r($_FILES);

$tmp_name = $_FILES['file_input']['tmp_name'];

$name   = $_FILES['file_input']['name'];

debug("name = [".$name."]");

debug("tmp name = [".$tmp_name."]");

 

if (isset($_POST['file_input']))

{

$file_input = $_POST['file_input'];

debug("file submitted: [".$file_input."]");

}

 

if (isset($_POST['submit_btn']))

{

$submit_btn = $_POST['submit_btn'];

debug("submit_btn: [".$submit_btn."]");

}

 

 

 

/*****************************************************************************/

/* Database connection parameters and Admin specific variables initialised.  */

/*****************************************************************************/

if (($connection_account = getAccountsConnection()) != NULL)

{

$results__admin_info = getAccountDetailsByAdminID(Admin(), $connection_account);

$adminID            = GetResultsValue($results__admin_info, TABLE_ACCOUNT__ADMINISTRATOR_ID);

$restrictionsID      = GetResultsValue($results__admin_info, TABLE_ACCOUNT__RESTRICTIONS_ID);

$accountTypeID      = GetResultsValue($results__admin_info, TABLE_ACCOUNT__ACCOUNT_TYPE_ID);

$org_id              = GetResultsValue($results__admin_info, TABLE_ACCOUNT__ORGANISATION_ID);

$expiryID = GetResultsValue($results__admin_info, TABLE_ACCOUNT__EXPIRY_ID);

 

debug("adminID = [".$adminID."]");

debug("restrictionsID = [".$restrictionsID."]");

debug("accountTypeID = [".$accountTypeID."]");

debug("org_id = [".$org_id."]");

debug("expiry_id = [".$expiryID."]");

 

    mysql_close($connection_account);

    }

 

 

 

 

 

/*****************************************************************************/

/*****************************************************************************/

//if (isset($submit_btn))

// {

// if ($file_input == '')

// {

// $errors["ERROR"] = "Please Browse for a CSV file.";

// }

// else

// {

// if (strstr($file_input, '.csv') != TRUE)

// {

// $errors["ERROR"] = "Only CSV files are allowed.";

// }

// }

// }

 

/*****************************************************************************/

/* getcsvAndDisplay($csv)

/*****************************************************************************/

function getcsvAndDisplay($csv, $name, $org_id, $expiryID, $admin_id, $restrictionsID, $AccountTypeID,  $connection)

{

if (isset($csv))

{

$fp = fopen($csv, 'r');

$num = 1;

echo "[".$name."]   Loaded: "; echo dateAndTime(); echo "   <input class=\"goCSV\" type=\"submit\" name=\"submit_btn\" value=\"Submit Users\" />";

echo "<br/><br/>";

echo "<table width=\"100%\"/><th>Record No</th><th>Firstname</th><th>Lastname</th><th>UPN</th><th>DisplayName</th><th>UserName</th><th>Password</th><th>Email</th><th>Errors</th>";

 

$line_count = 1;

while (!feof($fp))

{

 

// $herro = checkLoginUnique($line[4], $line[5], $connection);

// debug("$line[4], $line[5] => ".$herro);

 

$line = fgetcsv($fp, 4096);

if ($line != '')

{

echo "<tr><td>".$num."</td><td>";

echo implode('</td><td>', $line);findFirstnameRowNumber($line[0], $line[1], $line[3], $line[4], $line[5], $line[6], $line[2], $num, $connection);

echo "</td></td><td align=\"center\" class=\"inputWhite\"></td></tr></font>";

 

// if(isset($_POST['submit_btn']))

// {

// foreach ($line as $key_line => $value)

// {

$firstname = $line[0];

$lastname  = $line[1];

$upn      = $line[2];

$display  = $line[3];

$username  = $line[4];

$password  = $line[5];

$email    = $line[6];

 

// $new_UserID  = createUserRecord($firstname, $lastname, $display, $email, $upn, $connection);

// $new_LoginID = createUserLoginRecord($username, $password, $connection);

// $CreateWasSuccessful = createUserAccountRecord($new_LoginID, $new_UserID, $org_id, $expiryID, $admin_id, $restrictionsID, $AccountTypeID, $connection);

  //debug("debug CreateWasSuccessful = ".$CreateWasSuccessful);

 

$csvArray[$line_count++] = array(RecordNo=>$num ,Firstname=>$firstname, Lastname=>$lastname, UPN=>$upn, Displayname=>$display, Username=>$username, Password=>$password, Email=>$email);

 

doWhileLoop($username, $password, $items_in_array);

// }

// }

 

 

$num++;

} // close if !=''

} // close while loop

$items_in_array = count($csvArray);

debug("items in array = ".$items_in_array);

//print_r($csvArray);

 

 

fclose($fp); // close file

echo "</table>";

}

else

{

echo "N.B. Before submitting a file please read the CSV documentation...";

}

} // close function - returns echo data + array

 

 

 

/*****************************************************************************/

/*

/*****************************************************************************/

function doWhileLoop($username, $password, $number_of_rows)

{

for($i=1; $i<$number_of_rows-1; $i++)

{

for($x=$i+1; $x<=$number_of_rows; $x++)

{

//$res = checkLoginUnique($username, $password, $connection);

checkloginNames($username);

}

}

return($res);

}

 

 

 

function checkloginNames(loginname)

{

$num = 0;

if ($loginname !== $loginname[$num++])

{

debug("not ==");

}

else

{

debug("=<>");

}

}

 

 

 

/*****************************************************************************/

/*

/*****************************************************************************/

function findFirstnameRowNumber($firstname, $lastname, $displayname, $username, $password, $email, $upn, $rowNumber, $connection)

{

$num = 1;

if (empty( $firstname ))

{

$msg0 = "<font color=\"#FF0000\"/>".$num++."<a href=\"#\" title=\"The Firtsname should not be blank.\"/>firstname</a><br/></font>";

}

 

if (empty( $lastname ))

{

$msg1 = "<font color=\"#FF0000\"/>".$num++."<a href=\"#\" title=\"The Lastname should not be blank.\"/>lastname</a><br/></font>";

}

 

if (!preg_match(REG_SCREENNAME, $displayname) || ($displayname == '') )

{

$msg2 = "<font color=\"#FF0000\"/>".$num++."<a href=\"#\" title=\"The Displayname should not be blank or contain any foriegn characters.\"/>display</a><br/></font>";

}

 

if (!preg_match(REG_USERNAME, $username) || ($username == '') )

{

$msg3 = "<font color=\"#FF0000\"/>".$num++."<a href=\"#\" title=\"The Username should not be blank or contain any foriegn characters.\"/>username</a><br/></font>";

}

 

if (!preg_match(REG_PASSWORD, $password) || ($password == '') )

{

$msg4 = "<font color=\"#FF0000\"/>".$num++."<a href=\"#\" title=\"The Password should not be blank or contain any foriegn characters.\"/>password</a><br/></font>";

}

 

if(isset($email))

{

if (!preg_match(REG_EMAIL, $email))

{

$msg5 = "<font color=\"#FF0000\"/>".$num++."<a href=\"#\" title=\"The Email address is not a vaild email address.\"/>email</a><br/></font>";

}

}

 

if(isset($upn))

{

if (!preg_match(REG_UPN, $upn))

{

$msg6 = "<font color=\"#FF0000\"/>".$num++."<a href=\"#\" title=\"The UPN Length is not vaild.\"/>UPN</a><br/></font>";

}

}

 

/* if (isset($username, $password))

{

if ( checkLoginUnique($username, $password, $connection) == 0 )

{

$msg7 = "<font color=\"#FF0000\"/>".$num++."<a href=\"#\" title=\"The Login Details must be unique within the system.  Please chnage the Loginname and Password.\"/>Login</a><br/></font>";

}

}

*/

 

echo "<td><div id=\"container\"/>".$msg0.$msg1.$msg2.$msg3.$msg4.$msg5.$msg6/*.$msg7*/."</div></td>";

}

 

 

/*****************************************************************************/

/* dateAndTime()                                                            */

/*****************************************************************************/

function dateAndTime()

{

$date = date('l dS \of F Y h:i:s A');

return($date);

}

 

/*****************************************************************************/

/* Output_MessageError()                                                    */

/*****************************************************************************/

function Output_MessageError()

    {

    global $errors;

    if(isset($errors['ERROR']))

        {

        echo "<tr><td colspan=\"2\"><div align=\"left\"/><font style=\"font:11px;verdana\" color=\"#EF2020\"><strong>".$errors['ERROR']."</strong></font></div></td></tr>";

        }

    }

 

/*****************************************************************************/

/* createUserRecord($firstname, $lastname, $displayname, $email, $upn,      */

/*                                                              $connection) */

/* Return:                                                                  */

/* Returns UserID if record is added. or Failed if record add fails.        */

/*****************************************************************************/

function createUserRecord($firstname, $lastname, $displayname, $email, $upn, $connection)

{

debug("debugging createUserRecord [ $firstname, $lastname, $displayname, $email, $upn ]");

return(AddRecord(TABLE__USER, "Values(NULL, '$firstname', '$lastname', '$displayname', '$email', '$upn')", $connection));

}

 

/*****************************************************************************/

/* createUserLoginRecord($loginname, $password, $connection)

 

/* Return:                                                                  */

/* Returns LoginID if record is added. or Failed if record add fails.        */

/*****************************************************************************/

function createUserLoginRecord($loginname, $password, $connection)

{

debug("debugging createUserLoginRecord [ $loginname, $password ]");

return(AddRecord(TABLE__LOGIN, "Values(NULL, '$loginname', '$password')", $connection));

}

 

/*****************************************************************************/

/* createUserAccountRecord($loginname, $password, $connection)

 

/* Return:                                                                  */

/* Returns AccountID if record is added. or Failed if record add fails.      */

/*****************************************************************************/

function createUserAccountRecord($login_id, $user_id, $org_id, $expiryID, $admin_id, $restrictionsID, $AccountTypeID, $connection)

{

debug("debugging createUserLoginRecord [ $login_id, $user_id, $org_id, $expiryID, $admin_id, $restrictionsID, $AccountTypeID ]");

return(AddRecord(TABLE__ACCOUNT, "Values(NULL, NULL, 1, $login_id, $user_id, $org_id, $expiryID, $admin_id, 0, $restrictionsID, $AccountTypeID, 'www.redhalo.com', NULL)", $connection));

}

 

 

 

 

?>

<!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=iso-8859-1" />

<title><?php echo companyTitle(); ?></title>

<script type="text/javascript" src="BubbleTooltips.js"></script>

<script type="text/javascript">

window.onload=function(){enableTooltips()};

</script>

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

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

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

<style>

/* bubble tooltips */

 

.tooltip{

width: 200px; /*color:#000;*/

font:lighter 11px/1.3 Arial,sans-serif;

text-decoration:none;text-align:center

}

 

.tooltip span.top{

padding: 5px 8px 0;

    background: url(./images/bt.gif) no-repeat top

}

 

.tooltip b.bottom{

padding:13px 8px 15px;

color: ;

    background: url(./images/bt.gif) no-repeat bottom

}

 

/* bubble tooltips */

 

input.goCSV {

  background:#00FF66;

  font:Verdana, Arial, Helvetica, sans-serif;

  color:#000000;

  }

 

td.inputWhite {

  border: 1px solid;

  color: white;

  background-color:#ffffff;

  padding: 5px;

  font-family:Verdana, Arial, Helvetica, sans-serif;

  font-size:10px;

}

 

td.input {

  border: 1px solid;

  color: black;

  background-color:#ffffff;

  padding: 5px;

  font-family:Verdana, Arial, Helvetica, sans-serif;

  font-size:10px;

}

 

/* page specific styling == inputed csv table styling */

 

td {

  border: 1px solid black DDDDDD;

  color: black;

  background-color:#CCCCCC;

  padding: 5px;

  font-family:Verdana, Arial, Helvetica, sans-serif;

  font-size:10px;

}

th {

  border: 1px solid yellow;

  color: white;

  background: black;

  padding: 5px;

  font-family:Verdana, Arial, Helvetica, sans-serif;

  font-size:11px;

}

</style>

</head>

<body>

<?php echo HEADER_IMG ?>

<table width="100%" border="1" cellpadding="2" cellspacing="0" bordercolor="#CCCCCC">

<tr>

<td class="input">

<form enctype="multipart/form-data" name="<?php echo FORM_NAME ?>" method="post" action="<?php echo FORM_ACTION ?>"/>

<input type="file" name="file_input"/>

<input style="font-size:9px; height:20px;" type="submit" name="submit_btn" value="Submit CSV"/>

</form>

</td>

<td class="input"><font style="font-family:Verdana, Arial, Helvetica, sans-serif; font-size:10px;"/>Download the RedHalo CSV documentation file now and for further information. - (97kb  - *.doc) <a href="<?php echo DOWNLOAD ?>"/><?php echo DOWNLOAD_IMG ?></a> </font></td>

</tr>

</table><br/>

<?php if (($connection_account = getAccountsConnection()) != NULL){getcsvAndDisplay($tmp_name, $name, $org_id, $expiryID, $adminID, $restrictionsID, $accountTypeID, $connection_account);mysql_close($connection_account);}?>

</body>

</html>

 

Link to comment
Share on other sites
BenInBlack Newbie

BenInBlack

Posted
Posted

well, in CSV the header are supposed to be the 1st row, if there are any. About the only way you could tell is the headers should be wrapped in quotes on each column, but with data only the text column will be wrapped in quotes, but if the csv is all text columns, this could be fun. Another way to tell is the headers might be bigger.

 

As for the help can you include a snippet of the top few row of a CSV file you are attempting to read in?

Link to comment
Share on other sites
etnastyles Member

etnastyles

Posted
  • Author
Posted

sure np;;

 

"Firstname","Lastname","DisplayName","LoginName","Password","UPN","Email"

"Olly","Dalton","Odalton","Odalton","Password",,"saintd@gmail.com"

"Dave","Allen","Dallen","Dallen","Password",,

"Neil ","Critchell","Ncritchell","Ncritchell","Password",,

"Matt","Hilbery","Mhilbery","Mhilbery","Password",,"m.hillbery@here.com"

 

 

 

ok heres a csv form openoffice.. great ... not

 

but some of the fields are empty as i was going to try to error check the file to see if anything was missing before i committed to my _db...... thkss

Link to comment
Share on other sites
This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
 Share
Go to topic listing
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.