skipping lines in a text file

[richei]

Time for another round.  I don't know why these music companies have to make things so damn difficult on me.  A couple months ago, we started submitting our clients' music to spotify and now we're getting the sales reports back, but instead of giving it to us in a way that's easy to import, they do something like

Code: [Select]

Format version Start date End date Sender Recipient Label Aggregator Disclaimer
4 5/1/2012 5/31/2012 Spotify xxxxxxxxx #N/A #N/A Confidential
Country Product URI UPC EAN ISRC Track name Artist name Composer name Album name Quantity Label
So, now i'm left to figure out a way to completely skip the first two rows of the file so that i'm left with Country, Product, etc.  Since this is part of another import file, i already have set up to read the file into an array using $fcontents = file($file, FILE_IGNORE_NEW_LINES);, then i loop through it with a for loop.  Below is the entire php part of the script.

PHP Code: [Select]

if(isset($_POST['submit_ureport'])) {

$companyArr = array('venzo_app_sales', 'venzo_app_trends', 'venzo_itunes_sales', 'venzo_itunes_trends', 'spotify');

$company = mysql_real_escape_string(strtolower($_POST['company']));

if(in_array($company, $companyArr)) {

if($company == "venzo_app_sales") {

$path = "sales/app/";

} elseif($company == "venzo_app_trends") {

$path = "trends/app/";

} elseif($company == "venzo_itunes_sales") {

$path = "sales/itunes/";

} elseif($company == "venzo_itunes_trends") {

$path = "trends/itunes/";

} elseif($company == "venzo_spotify_sales") {

$path = "sales/spotify/";

$start = $_POST['start_date'];

$end = $_POST['end_date'];

}

$companyTbl = $company;

}

if($_FILES['file']['error'] > 0) {

echo "Return Code: {$_FILES['file']['error']}<br />";

} 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."<br />";

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]}<br />";

if($sqlFields[$a] != $fields[0][$a] && $sqlFields[$a] != $fields[1][$a] && $sqlFields[$a] != $fields[2][$a]) {

$error = true;

echo "<b>Report fields do not match the table fields for `{$company}`! Field `{$sqlFields[$a]}` should not exist! Estimated expected field name: '" . $fields[0][$a] . "'.</b><br /><br />";

} else {

unset($tfields[$a]);

}

}
          

/*check for fields in the report that are not in the DB*/

if(count($tfields) > 0) {

$error = true;

echo "<b>Report fields do not match the table fields for `{$company}`! Field(s) `" . implode("`, `", $tfields) . "` are missing!</b><br /><br />";

}

}

/*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]}<br />";

}

echo "<b># of fields in the table (" . $sqlFieldNum . ") is greater than the # of fields in the report (" . count($arr) . ")!</b><br /><br />";
              

/*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]}<br />";

}

echo "<b># of fields in the report (" . count($arr) . ") is greater than the # of fields in the table (" . $sqlFieldNum . ")!</b><br /><br />";

}
            

/*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 "<b>Record {$i} not inserted!</b><br /><br />";

}

}

}

/*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<br />';

} else {

$msg =  basename($_FILES['file']['name']) . ' has NOT been imported, please try again later!<br />';

}

}

}

}
}

Any help would be greatly appreciated.  I've been racking what few brain cells i have left for a week now.

[aztech4mac]

Are these items separated by tabs? chr(9)

How about doing an explode... this dumps everything into an array, then just grab the array items you need.

[richei]

yea, is a tab delimited file.  Don't you loose the association when you explode though?  Basically, each row in the file represents a row in the db table

[aztech4mac]

Don't you loose the association when you explode though?

This is a tab del string, it doesn't have any association to the database. You can only know what indexes you need into the exploded array. If the data order or number of items are change you need to adjust your code.

Testing using count($myarray) is marginal validation, and maybe test a number field for a number adds some additional testing.

[richei]

There has to be an easier way of getting rid to 2 lines in that file lol.

[aztech4mac]

First you need to make sure the file has a line break to divide into the three lines or is all data separated by tabs and what you see is wrap around.

$lines = explode("\n",$mystring) // using line breaks (maybe (\r\n)

$mystring = $lines[2]; The string you want

or

$lines = explode("\t",$mystring) // using tabs

[richei]

Well, its gotta have line breaks, hadn't thought of that.  ok, so if i explode it and use a for loop, i'd just start the loop at 2 instead of 0 (for $i = 2; $i < count($lines); $i++) ?  The first 2 lines should be $line[0] and $line[1] right?

Actually, if i do it this way, i can skip the 3rd line too because i won't need the headers on the 3rd line.

[richei]

Well, i had a look at how it imports other files and it explodes it on the \t.  Now i'm stuck trying to get the script to ignore all the field checks.

[aztech4mac]

well if that info you provided (three lines) is a header, it's most likely ALL tabs, you just said it's a header. A header by nature can only be one line, unless items wrap within each element.

array_diff() might help...

$a1=array("Cat", "Dog", "Snake");
$a2=array("Snake", "Dog", "Rabbit");

print_r(array_diff($a1, $a2));

count($a1) = 1
$a1[0] = Cat

Any array element in $a1 that matches an element in $a2 will be removed, reducing the array to only non-matches.

Please explain 'field checks'.

[richei]

The middle bit of code in my first post (all that sqlfield stuff) looks at the first row and compares that against the column names in the db table.  if one does't exist or if something is out of place, it stops the script and tells me what's going on.

[Laffin]

if it's just 2 lines u need to skip use a counter and continue

PHP Code: [Select]


  $fh=fopen('blah.txt');
  $lines=0;
  while(!feof($fh))
  {
    $row=fgetcsv($fh,0,"\t");
    if($lines++ < 2) continue;
    // More processing code
  }
  fclose($fh);
 

if you need to start reading after the country header than a quick ok flag to signal when $line[0] is country to begin processing. About the same as above
 

PHP Code: [Select]


  $fh=fopen('blah.txt');
  $ok=FALSE;
  while(!feof($fh))
  {
    $row=fgetcsv($fh,0,"\t");
    if($line[0] == 'Country) $ok=TRUE;
    if(!$ok) continue;
    // More processing code
  }
  fclose($fh);
 

[richei]

Ill give it a try asap, looks like it should work. I knew there had to be a way.

[richei]

ok, i'm making progress here on this thing, but i have another question.  Where is $line[0] coming from? is it supposed to be $row[0]?  below is what i have so far.

PHP Code: [Select]

if($companyTbl == "venzo_spotify_sales") {

$fh=fopen($file, "r");

$ok=FALSE;

while(!feof($fh))  {

$row=fgetcsv($fh,0,"\t");

if($line[0] == 'Country') $ok=TRUE;

if($ok) continue; // More processing code

$qry = mysql_query("SHOW FIELDS FROM `{$companyTbl}`") or die(mysql_error());

while($field = mysql_fetch_assoc($qry)) {

$sqlFields[] = strtolower($field['Field']);

}

for($i = 3; $i < count($line); $i++) {

$sql = "INSERT INTO {$companyTbl}(start_date, end_date, " . implode(', ', $sqlFields) . ") VALUES ('$start_date, '$end_date', '" . implode("', '", $row) . "')";

mysql_query($sql) or die(mysql_error());

}

}

fclose($fh);
}

[aztech4mac]

I'm sure he meant $row[0] and not $line[0]...

[richei]

Well i see where $line came from, but i can't get it to skip those first to lines, no matter what i change.