CSV file upload to MySql Help!

[edlentz]

I have a small PHP script that I need help with. It goes something like this:

?


//connect
$db = mysql_pconnect($dbhost,$dbuser,$dbpass);
mysql_select_db("$dbname",$db);

$fcontents = file ('./tawas.csv');
# expects the csv file to be in the same dir as this script

for($i=0; $i<sizeof($fcontents); $i++) {
$line = trim($fcontents[$i]);
$arr = explode(",", $line);
$sql = "insert into temp_proposal_parts values ('".
implode("','", $arr) ."')";
mysql_query($sql);
echo $sql ."<br>\n";
if(mysql_error()) {
echo mysql_error() ."<br>\n";
}
}
?>

So here's my problems:

1. The csv file will have many lines with 5 columns which matches the table I am importing to. But, there will be several lines that only have one column. Those lines I can do without. How can I modify the csv file to get rid of those lines in my PHP script.

2. Within one of the lines/columns similar to this one:

580.8001 ,Administrator's Guide Inter-Tel 5000,1,25.00,25.00

The ' in the second column stops the input for that line. If I manually remove the ' and run the script again it goes fine.

3. Finally, the csv file has just a couple of dozen lines, and then I manually remove or change the items above the import runs fine with one exception. Sometimes the ORDER of the lines are goofed up. Mostly the first line will have a Kit information, and for some undetermined # of lines below it the items in the kit are listed. If the order is goofed up, the listing of all the items in the list will make no sense.

I am running PHP 4 and MySql 4.1

Can someone give me a hand here??

Thanks Alot!

[Yesideez]

I'm not sure if I understand what you're requesting properly but I'll have a crack...

1. Sounds like you are in need of an if() statement to see if the data has 5 columns - if it does, add it into the database.

2. Use addslashes() to change ' into \'

3. No idea what you mean here - sorry!

[edlentz]

Thanks for the reply!

I thought the third one would be tough to explain, I'll try again with an example:

This is the way I get it, and the way I want it:

580.9001 ,Inter-Tel CS-5200 System with 5 Model 8520 Digital Endpoints,1,,
, - Digital Endpoint Module (DEM16) (580.2200),1, ,
, - Inter-Tel 5000 BVM License Single Port (840.0411),4, ,
, - Inter-Tel 5000 Base System Chassis (580.1000),1, ,
, - Inter-Tel CS-5200/5400 Compact Flash Card - 512 MB (841.0274),1, ,
, - Inter-Tel DE-5200 Digital Expansion Interface (DEI) (580.1001),1, ,
, - License Inter-Tel 5000 Software v1.2 (840.0456),1, ,
, - License Inter-Tel 5200 Base (840.0238),1, ,
, - License Key for Inter-Tel 5000 (841.0219),1, ,
, - License for First Digital Endpoint Interface to Operate (840.0409),1, ,
, - Model 8520 - 2 Line Display Digital Endpoint (550.8520),5, ,
, - Processor Module 1 (PM-1) (580.2000),1, ,
, - Software CD for Inter-Tel 5000 v1.2 (841.0361),1, ,
, - Software Site License Kit (999.9000),1, ,
813.1814 ,DEM Cable Adapter (quad RJ-45 to Amp) 15ft,1,,
550.8400 ,Model 8400 - Personal Computer Data Port Module (PCDPM),,


This is the way it "sometimes gets into the MySql table"
, - Model 8520 - 2 Line Display Digital Endpoint (550.8520),5, ,
, - Processor Module 1 (PM-1) (580.2000),1, ,
, - Software CD for Inter-Tel 5000 v1.2 (841.0361),1, ,
, - Software Site License Kit (999.9000),1, ,
813.1814 ,DEM Cable Adapter (quad RJ-45 to Amp) 15ft,1,,
550.8400 ,Model 8400 - Personal Computer Data Port Module (PCDPM),,
580.9001 ,Inter-Tel CS-5200 System with 5 Model 8520 Digital Endpoints,1,,

In the first line of the first example, the line describes the "Kit" that is comprised of the items following without a first column entry. So if the table has the info in the second example then when I need to use it, then it makes no sense.

[Barand]

If I undesrstand correctly I suggest 2 tables

item - contains the main item record
kit - contains the kit items belonging to the item (contains the item id)

[edlentz]

Hi barand,

I would use two tables if Icould. The original csv file is generated by a quote system that automatically generates all that is needed. I am trying to get it into my Db and then manipulate it for my own purposes.

[ypirc]

php has csv commands [a href=\"http://us2.php.net/manual/en/function.fgetcsv.php\" target=\"_blank\"]http://us2.php.net/manual/en/function.fgetcsv.php[/a]

Also, MySQL has a function to import a full table from a csv file...check out this reference using the LOAD DATA FROM INFILE
[a href=\"http://dev.mysql.com/doc/refman/5.0/en/load-data.html\" target=\"_blank\"]http://dev.mysql.com/doc/refman/5.0/en/load-data.html[/a]

[Barand]

try

[code]$fcontents = file ('./tawas.csv');
# expects the csv file to be in the same dir as this script

for($i=0; $i<sizeof($fcontents); $i++) {
    $line = addslashes(trim($fcontents[$i]));  // << added addslashes
    $arr = explode(",", $line);

    $prodcode = $arr[0] != '' ? $arr[0] : $prodcode;  // << added 2 lines
    $arr[0] = $prodcode;

    $sql = "insert into temp_proposal_parts values ('".
    implode("','", $arr) ."')";
    mysql_query($sql);
    echo $sql ."<br>\n";
    if(mysql_error()) {
       echo mysql_error() ."<br>\n";
    }
}[/code]

[edlentz]

Thanks for the reply Barry.

I tried your code with no results at all. Any other suggestions?

Thanks

[Yesideez]

Hopefully this is generating more of an idea of the contents:
[code]<?php
  $thehtml="";
  if ($buffer=file("myfile.csv")) {
    for ($i=0;$i<count($buffer);$i++) {
      $temp=explode(",",$buffer[$i]);
      $templine="<tr>";
      for ($k=0;$k<count($temp);$k++) {
        $temp[$k]=str_replace("\n","",$temp[$k]);
        if (empty($temp[$k])||$temp[$k]==" ") {
          $templine.='<td>&nbsp;</td>';
        } else {
          $templine.='<td>'.trim($temp[$k]).'</td>';
        }
      }
      $thehtml.=$templine.'</tr>'."\n";
    }
  } else {echo "Error opening file";}
?>
<html>
<head>
  <title>Testing</title>
</head>
<body>
  <table border="1">
<tr><td>CODE</td><td>DESCRIPTION</td><td>QTY</td><td>?</td><td>?</td></tr>
<?=$thehtml?>
  </table>
</body>
</html>[/code]
See this working here: [a href=\"http://www.zeboliver.co.uk/test/test.php\" target=\"_blank\"]http://www.zeboliver.co.uk/test/test.php[/a]

[edlentz]

I appreciate what you have done Yesideez, but not sure why. I am wanting to show the table info with OpenOffice Base forms once I get the csv into MySql. Getting it there is the issue. I have or gould have lines that would only have 1 column which could be ignored, and sometimes a description that has an apostrophe , and also I need to strip the all the $ signs out of the file.

Thanks!

[Barand]

When I ran my code (suppressing the MySql calls) but echoing the SQL I got this, the blanks at the begining of the kit lines being filled with the code from the preceding main item record

insert into temp_proposal_parts values ('580.9001 ',' - Digital Endpoint Module (DEM16) (580.2200)','1',' ','')
insert into temp_proposal_parts values ('580.9001 ',' - Inter-Tel 5000 BVM License Single Port (840.0411)','4',' ','')
insert into temp_proposal_parts values ('580.9001 ',' - Inter-Tel 5000 Base System Chassis (580.1000)','1',' ','')
insert into temp_proposal_parts values ('580.9001 ',' - Inter-Tel CS-5200/5400 Compact Flash Card - 512 MB (841.0274)','1',' ','')
insert into temp_proposal_parts values ('580.9001 ',' - Inter-Tel DE-5200 Digital Expansion Interface (DEI) (580.1001)','1',' ','')

Or isn't that what you want?

[edlentz]

Strange
When I ran your code I got a blank screen and nothing in the table. The first line item the xxx.9001 is the kit number that those individual parts are a part of. I think I can live with that, but not what I was looking for. But close except that on my machine it didn't work at all. wierd.