[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 248
  • Last Modified:

How do I adjust the output on this XLS to CSV script

Experts:

This script works perfectly, however, all of the results of a row are inserted into the first field on that row (part_num).   How do I eliminate the " " in each result, and how do I populate the results correctly in the respective cells?

The results are all populated in the first field of each row (part_num):  

00 83104|"21in. Cross Bar"|""|"42.00"|""|"32.15"            
0002V413|"Canon Case for XL1S"|"10.00"|"325.00"|"285.00"|"229.76"            
000-6775|"Amphenol 000-6775 BNC Male Cable End for RG8/U"|"0.00"|"27.50"|""|"20.31"            
001-008|"Calzone 001-008 Escort AT Series 42inch Plasma Case"|""|"899.95"|""|"560.30"            
0015276|"Mackie 0015276 Replacement Subwoofer Driver for SW1501"|"1.00"|"172.62"|"139.80"|"107.89"            
003-000884-01|"Replacement Bulb for Christie Digital DS65 - DS650 - DS655"|"2.00"|"495.00"|""|"419.88"            
003-120242-01|"Replacement Lamp For Christie LX380"|""|"475.00"|""|"397.95"      


Here are the database fields:

$import="INSERT into `1product_excel_download` (part_num,description,weight,List,MAP,Cost) values ('$data[0]','$data[1]','$data[2]','$data[3]','$data[4]','$data[5]')";



<?php
require_once 'reader.php';
$excel = new Spreadsheet_Excel_Reader();
$excel->setOutputEncoding('CP1251');
$excel->read('pricelist.xls');
$x=8;
$sep = "|";
ob_start();
while($x<=$excel->sheets[0]['numRows']) {
$y=1;
$row="";
while($y<=$excel->sheets[0]['numCols']) {
$cell = isset($excel->sheets[0]['cells'][$x][$y]) ? $excel->sheets[0]['cells'][$x][$y] : '';
$row.=($row=="")?"\"".$cell."\"":"".$sep."\"".$cell."\"";
$y++;
}
echo $row."\n"; 
$x++;
}
$fp = fopen("products.csv",'w');
fwrite($fp,ob_get_contents());
fclose($fp);
ob_end_clean();
?>

Open in new window

     






mysql_query("TRUNCATE TABLE `1product_excel_download`");

//
// Include the mysql connect page
//include("connect.php");
// Set the filename that you want to import
$filename="products.csv";
//open the file
$handle = fopen($filename, "r");
//this is what causes it to skip
fgetcsv($handle,"|");


//begin looping through the lines
while (($data = fgetcsv($handle, "|")) !== FALSE)
{

// loop all column values and escape special characters
foreach ($data as $key => $value){
  $data[$key] = mysql_real_escape_string($value);
}
$import="INSERT into `1product_excel_download` (part_num,description,weight,List,MAP,Cost) values ('$data[0]','$data[1]','$data[2]','$data[3]','$data[4]','$data[5]')"; 


//execute the mysql query
mysql_query($import) or die(mysql_error());
}
//close the file
fclose($handle);
//output a message saying its done.

$msgprod="Product Import done";
echo $msgprod;
echo "<BR>";

Open in new window

0
rlb1
Asked:
rlb1
  • 4
1 Solution
 
Ray PaseurCommented:
How are these rows of pipe-delimited text strings created?
0
 
rlb1Author Commented:
Please clarify your question...  do not understand exactly what you mean.

I am downloading a feed from a distributor, then converting the xls feed to csv, then uploading to the server.  I am creating the feeds with script 2.  

0
 
rlb1Author Commented:
uploading to the mysql database not server
0
 
rlb1Author Commented:
disregard.  changed delimiter to comma and it worked fine.  
0
 
rlb1Author Commented:
I was running pipe delimited because of quotes and commas within the data.  Data is not perfect, but is usable using comma as delimiter.

Thanks!
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now