XLS CSV TAB (they all break rows) -> MySQL

[Kudose]

hello all.

I am having some problems with an Excel .xls file. It is a file in which certain columns contain HTML for product descriptions and such. I've tried converting it to a CSV and a Tab Delimited file (although excel wouldn't let me save as .tab, only .txt).

What happens when I try the TAB or CSV is that most of the rows are inserted properly, then something happens, like a character starts destroying all order. inserting titl's into SKU's and desciptions to image names and all hell breaks lose. Then, magically everything goes to normal for the last few hundred rows.

Now, I know for a fact that this script I am using, which I create, works with .csv and .tab files with HTML in them. I've used it plenty of times.

So now that you've read thus far and are wondering what my questions are:

A. What special characters could possibly be inserted to mess this up?

B. What do you think is wrong?

C. Is there anyway to rip break down a .xls file into an 2 dimensional array ($rows[$row][$data])?

Here's some of my code:

if($_POST['delimiter'] == "comma"){
		$delimiter = ",";
	} else {
		$delimiter = "\t";
	}

if(!$delimiter){ $delimiter = "\t"; }

$file = $site_dir.'/admin/'.$_FILES['csv']['name'];
$handle = fopen($file, "r");
$rows = array();
$row_cnt = 0;

while(($data = fgetcsv($handle, filesize($file), $delimiter)) !== FALSE){

	echo '<pre>';
		print_r($data);
	echo '</pre>';

	$rows[$row_cnt]['prod_sku'] = mysql_real_escape_string(strip_tags($data[0]));
	$rows[$row_cnt]['prod_name'] = mysql_real_escape_string(strip_tags($data[1]));
	$rows[$row_cnt]['category'] = mysql_real_escape_string(strip_tags($data[2]));
	$rows[$row_cnt]['prod_category'] = $data[3];
	$rows[$row_cnt]['prod_description'] = mysql_real_escape_string($data[4]);
	$rows[$row_cnt]['prod_price'] = strip_tags($data[5]);
	$rows[$row_cnt]['prod_weight'] = mysql_real_escape_string(strip_tags($data[6]));
	$rows[$row_cnt]['prod_referb'] = strip_tags($data[7]);
	$data[8] = "http://www.xyz.com/graphics/products/".$data[8];
	$rows[$row_cnt]['prod_image'] = strip_tags($data[8]);

	$row_cnt++;
}

//unset the first row, we don't need it
unset($rows[0]);

foreach($rows as $row){
	$sql = "INSERT INTO table_stock (prod_sku, prod_name, category, prod_category, prod_description";
	$sql .= ", prod_price, prod_weight, prod_referb, prod_image, added) VALUES ('".implode("', '", $row)."', NOW())";

	if(mysql_query($sql)){
		echo $row['prod_name']." entered into the xyz database.<br>";
	} else {
		echo mysql_error().'<br>';
	}

}

if(!@unlink($site_dir.'/admin/'.$_FILES['csv']['name'])){
	echo '<br>Unable to delete the uploaded file.<br>';
} else {
	echo '<br>The uploaded file was successfully deleted.<br>';
}

Any help would be appreciated. 🙂

edit
When I view the file to be uploaded in excel, all of the columns are displayed properly.
/edit

[Kudose]

I helped a few people and knocked myself down, so I think this is a legal move.

bump

[mr__why]

I'm just taking a shot here, but did you try stripping quotes out of the CSV file? I'm not terribly familiar with the [man]fgetcsv/man function so I don't know if it requires quotes around the fields, but it simplifies my life when I deal with CSVs to remove quotes.

[AstroTeg]

My bet is on a stray carriage return (or several) is/are causing you problems. [man]fgetcsv[/man] only handles data on a per-line basis. But legally, carriage returns can be in a comma delimited file (I don't think they can be in a tab delimited, but that won't stop Excel from trying).

Options:

You write your own CSV importer. It wouldn't be difficult. But would probably take more time then you're willing to spend.

You could try checking for carriage returns in a text editor and manually editing them so your PHP import works.

Another option, which might work well is to use Pear's Excel functions. I haven't tried them, but I poked around and they look pretty handy. You might just ditch the CSV/tab approach and use the XLS file as is.

[Kudose]

Thanks for both your input.

AstroTeg, I think PEAR sounds like my best bet. Good to try new things anyways.

🙂

I'll check it out and report back.

Thanks.