skipping lines in a text file

General PHP Help
#1

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

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

}[/php]

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

#2

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.

#3

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

#4
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.

#5

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

#6

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

#7

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.

#8

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.

#9

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’.

#10

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.

#11

if it’s just 2 lines u need to skip use a counter and continue
[php]
$fh=fopen(‘blah.txt’);
$lines=0;
while(!feof($fh))
{
$row=fgetcsv($fh,0,"\t");
if($lines++ < 2) continue;
// More processing code
}
fclose($fh);
[/php]

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]
$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);
[/php]

#12

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

#13

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]
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);

}[/php]

#14

I’m sure he meant $row[0] and not $line[0]…

#15

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

#16

look back to his original post. He uses $line as a counter and that’s where you skip the first two lines.

#17

yea, i found that. I ended up not using it though, i got it. i haven’t tried the sql insert yet, so its commented out. this is what i ended up with.
[php]
$file = $path.$_FILES[‘file’][‘name’];
$fcontents = file($file, FILE_IGNORE_NEW_LINES);

if($companyTbl == “venzo_spotify_sales”) {

for($i = 3; $i < count($fcontents); $i++) {
					
	$line = $fcontents[$i];
	$arr = explode("\t", $line);
											//$sql = "INSERT INTO {$companyTbl}(start_date, end_date, " . implode(', ', $sqlFields) . ") VALUES ('$start_date, '$end_date', '" . implode("', '", $row) . "')";
	//$ins = mysql_query($sql);
						
	if(!ins) {
		unlink($file);
		//die(mysql_error());
	}
}	
				
$qry = mysql_query("SHOW FIELDS FROM `{$companyTbl}`") or die(mysql_error());

while($field = mysql_fetch_assoc($qry)) {
	$sqlFields[] = strtolower($field['Field']);
}

}[/php]

#18

Now its telling me that the columns don’t match, but i think i know what’s causing that. I need to find a way to add the start and stop dates at the beginning of an array, then it should be fixed.

#19

The only thing I might add is to verify that $fcontents is not empty

#20

While I was looking at your code you snuck one in first…

You query string does not match your database table column labels. Spelling or order. Your previous code did not show.

Sponsor our Newsletter | Privacy Policy | Terms of Service