Jump to content

Archived

This topic is now archived and is closed to further replies.

edlentz

CSV file upload to MySql Help!

Recommended Posts

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!

Share this post


Link to post
Share on other sites
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!

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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)

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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]

Share this post


Link to post
Share on other sites
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]

Share this post


Link to post
Share on other sites
Thanks for the reply Barry.

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

Thanks

Share this post


Link to post
Share on other sites
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]

Share this post


Link to post
Share on other sites
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!

Share this post


Link to post
Share on other sites
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?

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.