Jump to content

CSV file upload to MySql Help!


edlentz

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!
Link to comment
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!
Link to comment
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.
Link to comment
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]
Link to comment
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]
Link to comment
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]
Link to comment
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!
Link to comment
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?
Link to comment
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.
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

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.