The last part of a project that i’m working involves uploading files to a designated folder, then using a cron job to look those files, delete parts of the file, upload what’s left to a mysql table, then move the file somewhere else.
Currently i have the upload and import working if i manually edit the csv file to delete what’s needed.
Keep in mind that one of our other developers wrote this code, but is no longer with us. But if there’s another way of doing this, i’m all for it. I modified it to allow for different files to be imported through 1 script.
[php]
<?php require 'config.php'; ?> <h2>Upload & Import Sales and/or Trends</h2>
"; } else { if(file_exists($path . $_FILES['file']['name'])) { echo "{$_FILES['file']['name']} already exists."; } else { if(move_uploaded_file($_FILES['file']['tmp_name'], $path . $_FILES['file']['name'])) { //echo "Stored in: " . "upload/" . $_FILES["file"]["name"]; $file = $path.$_FILES['file']['name']; $fcontents = file($file, FILE_IGNORE_NEW_LINES); /*check the table exists*/ $qry = current(mysql_fetch_row(mysql_query("SELECT DATABASE()"))); $qry = mysql_query("SHOW TABLES FROM `{$qry}` LIKE '{$companyTbl}'") or die(mysql_error()); if(mysql_num_rows($qry) < 1) { unlink($file); die("Table `{$companyTbl}` for '{$company}' does not exist!"); } /*pull the field list out of the DB*/ $qry = mysql_query("SHOW FIELDS FROM `{$companyTbl}`") or die(mysql_error()); //echo $company."
"; if($company == "venzo_itunes_trends" || $company == "venzo_app_trends") { $sqlFieldNum = mysql_num_rows($qry); while($field = mysql_fetch_assoc($qry)) { $sqlFields[] = strtolower($field['Field']); } } else { $sqlFieldNum = mysql_num_rows($qry)-2; while($field = mysql_fetch_assoc($qry)) { if($field['Field'] != 'id' && $field['Field'] != 'report_paid') $sqlFields[] = strtolower($field['Field']); } } /*check the field list in the report and the field list in the database match*/ if($sqlFieldNum != count($fieldArr = explode("\t", $fcontents[0]))) { foreach($fieldArr as $key => $value) { $fields[0][$key] = trim(str_replace('/', '_', strtolower($value))); $fields[1][$key] = trim(str_replace(array('/', ' '), '_', strtolower($value))); $fields[2][$key] = trim(str_replace(array('/', ' '), array('_', ''), strtolower($value))); } $tfields = $fields[0]; /*check for fields in the DB that are not in the report*/ for($a = 0; $a < count($sqlFields); $a++) { echo "SQL: {$sqlFields[$a]} - Raw report: {$fields[0][$a]} - Underline report: {$fields[1][$a]} - Strip-spaces report: {$fields[2][$a]}
"; if($sqlFields[$a] != $fields[0][$a] && $sqlFields[$a] != $fields[1][$a] && $sqlFields[$a] != $fields[2][$a]) { $error = true; echo "Report fields do not match the table fields for `{$company}`! Field `{$sqlFields[$a]}` should not exist! Estimated expected field name: '" . $fields[0][$a] . "'.
"; } else { unset($tfields[$a]); } } /*check for fields in the report that are not in the DB*/ if(count($tfields) > 0) { $error = true; echo "Report fields do not match the table fields for `{$company}`! Field(s) `" . implode("`, `", $tfields) . "` are missing!
"; } } /*if there is an error, stop here and delete the file*/ if($error == true) { unlink($file); die(); } /*for each record in the report...*/ for($i = 1; $i < count($fcontents); $i++) { $line = $fcontents[$i]; $arr = explode("\t", $line); /*santitise the values*/ foreach($arr as $key => $value) { $arr[$key] = trim(mysql_real_escape_string($value)); } if(strtolower($arr[0]) != "total") { /*number of fields in the DB exceeds number of fields in the record*/ if($sqlFieldNum > count($arr)) { $error[$i] = true; for($a = 0; $a < count($sqlFields); $a++) { echo "{$a}) MYSQL: {$a} => {$sqlFields[$a]} - Report: {$a} => {$arr[$a]}
"; } echo "# of fields in the table (" . $sqlFieldNum . ") is greater than the # of fields in the report (" . count($arr) . ")!
"; /*number of fields in the record exceeds number of fields in the DB*/ } else if($sqlFieldNum < count($arr)) { $error[$i] = true; for($a = 0; $a < count($arr); $a++) { echo "{$a}) MYSQL: {$a} => {$sqlFields[$a]} - Report: {$a} => {$arr[$a]}
"; } echo "# of fields in the report (" . count($arr) . ") is greater than the # of fields in the table (" . $sqlFieldNum . ")!
"; } /*if there is no error insert the record into the table else continue onto the next record*/ if($error[$i] != true) { $sql = "INSERT INTO {$companyTbl}(" . implode(', ', $sqlFields) . ") VALUES ('" . implode("', '", $arr) . "')"; mysql_query($sql); if(mysql_errno()) { unlink($file); die(mysql_error()); } } else { echo "Record {$i} not inserted!
"; } } } /*if there is an error, stop here and delete the file*/ if(count($error) > 0) { unlink($file); die(); } if($sql) { $msg = basename($_FILES['file']['name']) . ' has successfully been imported. The information is ready to be displayed
'; } else { $msg = basename($_FILES['file']['name']) . ' has NOT been imported, please try again later!
'; } } } } } ?>
<br/>
Please select which report you need to upload:<br /><br />
<form method="post" action="" enctype="multipart/form-data">
<label for="company">Mobile or Music? </label>
<select name="company">
<optgroup label="Sales">
<option value="venzo_app_sales">Mobile</option>
<option value="venzo_itunes_sales">Music</option>
</optgroup>
<optgroup label="Trends">
<option value="venzo_app_trends">Mobile</option>
<option value="venzo_itunes_trends">Music</option>
</optgroup>
</select><br /><br />
<label for="report">Report: </label><input type="file" name="file" /><br /><br />
<div style="text-align:right;">
<input type="submit" name="submit_ureport" value="Import" style="padding:2px 20px 2px 20px;" />
</div>
</form>
<br />
<?=$msg?>[/php]
What i need to know is how to get it automatically look in a folder, open the fie and then delete stuff. This is the first and last line from one of our sales reports.
Start Date End Date UPC ISRC/ISBN Vendor Identifier Quantity Partner Share Extended Partner Share Partner Share Currency Sales or Return Apple Identifier Artist/Show/Developer/Author Title Label/Studio/Network/Developer/Publisher Grid Product Type Identifier ISAN/Other Identifier Country Of Sale Pre-order Flag Promo Code Customer Price Customer Currency
11/27/2011 12/31/2011 USVGG1160301 USVGG1160301 2 1.29 2.58 AUD S 465219812 DJ Ringtones Club Paradise (feat. Drake) [Ringtone] V&G PR AU 2.19 AUD
Total_Rows 352
Total_Amount 3696.84
Total_Units 2916
Total_Rows 352
Total_Amount 3696.84
Total_Units 2916
is what i need to delete. i know how to open the file, but not how to delete just those totals.
anyone have any ideas on how to accomplish this?
