• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 520
  • Last Modified:

PHP - CSV file import routine

Dear all,

Wrote an import routine, can't get it to work. Mind taking a quick look at the code and feeding back any ideas? Thanks.
<?php require_once('_config.php'); ?>

<?php
## import existing database ##
// 

    if(isset($_POST['submit']))
    {
         $fname = $_FILES['sel_file']['name'];
        
         $chk_ext = explode(".",$fname);
		 
		 var_dump($chk_ext);
        
         if(strtolower($chk_ext[1]) == "csv")
         {
        
             $filename = $_FILES['sel_file']['tmp_name'];
             $handle = fopen($filename, "r");
       
             while ($data = fgetcsv($handle, 1000, ",") != FALSE)
             {
                $import_query = "INSERT INTO clients (membershipNo, firstname, lastname, dob, email, telephone, contactable, shopID, issueruserID, idproof, type) VALUES ('$data[0]','$data[1]','$data[2]','$data[3]','$data[4]','$data[5]','$data[6]','$data[7]','$data[8]','$data[9]','$data[10]') ";
                mysql_query($import_query) or die(mysql_error());
             }
       
             fclose($handle);
             echo "Successfully Imported";
         }
         else
         {
             echo "Invalid File";
         }   
    }

?>

    <form action='<?php echo $_SERVER["PHP_SELF"]; ?>' method='post'>

        Import File : <input type='text' name='sel_file' size='99'>
        <input type='submit' name='submit' value='submit'>

    </form>
<body>
</body>
</html>

Open in new window

0
BenthamLtd
Asked:
BenthamLtd
2 Solutions
 
hernst42Commented:
the query fails if the data contains a '. Also you for is not useing the corrct enctype. See http://de.php.net/manual/en/features.file-upload.post-method.php   enctype="multipart/form-data"
Try:
$import_query = sprintf("INSERT INTO clients (membershipNo, firstname, lastname, dob, email, telephone, contactable, shopID, issueruserID, idproof, type) VALUES ('%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s')", mysql_real_escape_string($data[0]), mysql_real_escape_string($data[1]), mysql_real_escape_string($data[2]), mysql_real_escape_string($data[3]), mysql_real_escape_string($data[4]), mysql_real_escape_string(data[5]), mysql_real_escape_string($data[6]), mysql_real_escape_string($data[7]), mysql_real_escape_string($data[8]), mysql_real_escape_string($data[9]), mysql_real_escape_string($data[10]));

Open in new window

0
 
nepaluzCommented:
is your file separated by a fullstop?
if not change:

$chk_ext = explode(".",$fname);

(just a stab in the dark ....)
0
 
Ray PaseurCommented:
Line 9 says this:  $fname = $_FILES['sel_file']['name'];

But see the form on line 38.  There is no enctype attribute nor any input type=file statement.

If you want to upload a file this is a good place to start the learning process:
// MANUAL REFERENCE PAGES
// http://docs.php.net/manual/en/features.file-upload.php
// http://docs.php.net/manual/en/features.file-upload.common-pitfalls.php
// http://docs.php.net/manual/en/function.move-uploaded-file.php

0
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!

 
Ray PaseurCommented:
If you want to post a link to the actual URL of the CSV file you're trying to process we may be able to show you how it could be done.  Standing by, ~Ray
0
 
BenthamLtdAuthor Commented:
Ok thanks for the ideas so far everyone. The .csv file I'm trying to upload is in the root of the site where the upload.php is running. The CSV file is called 'camborne.csv'.

We can probably strip out all the error checking in this script to make it easier if needs be. It's a one-time shot for uploading a group of CSV files. When they are on the database, we can disregard this script!

Many thanks guys, really appreciate the excellent advice on here.
0
 
BenthamLtdAuthor Commented:
Ok as an update to this, I've commented out all the rubbish error checking. It seems to work but is only inserting blank rows. Any ideas? Code attached (also tried experimenting with sprintf)


<?php
## import existing database ##
// 

  #  if(isset($_POST['submit']))
  #  {
  #       $fname = $_FILES['sel_file']['name'];
        
  #       $chk_ext = explode(".",$fname);
		 
#		 var_dump($chk_ext);
        
 #        if(strtolower($chk_ext[0]) == "csv")
 #        {
        
  #           $filename = $_FILES['sel_file']['tmp_name'];
  			$filename = $_FILES['sel_file']['name'];
      
	     #    $handle = fopen($filename, "r");
       $handle = fopen('camborne.csv', "r");
             while (($data = fgetcsv($handle, 1000, ",") !== FALSE))
             {
                $import_query = sprintf("INSERT INTO clients (membershipNo, firstname, lastname, dob, email, telephone, contactable, shopID, issueruserID, idproof, type) VALUES ('$data[0]','$data[1]','$data[2]','$data[3]','$data[4]','$data[5]','$data[6]','$data[7]','$data[8]','$data[9]','$data[10]') ");
                mysql_query($import_query) or die(mysql_error());
             }
       
             fclose($handle);
             echo "Successfully Imported";
   #      }
     #    else
    #     {
      #       echo "Invalid File";
      #   }   
  #  }

?>

    <form action='<?php echo $_SERVER["PHP_SELF"]; ?>' method='post'>

        Import File : <input type="file" name='sel_file' size='99'>
        <input type='submit' name='submit' value='submit'>

    </form>

Open in new window

0
 
BenthamLtdAuthor Commented:
OK just got it to work, basically stripped out all error checking. Posting code, thanks for the help guys.

Think I will whack a mysql_real_escape_string in there just in case though (as it will indeed, f--k up the query).


I'll split points for the info that worked.
0
 
BenthamLtdAuthor Commented:

<?php

$filename = $_FILES['sel_file']['name'];
      
$handle = fopen('test.csv', "r");

while ($data = fgetcsv($handle, 1000, ","))
   {
     $import_query = "INSERT INTO clients (membershipNo, firstname, lastname, dob, email, telephone, contactable, shopID, issueruserID, idproof, type) VALUES ('$data[0]','$data[1]','$data[2]','$data[3]','$data[4]','$data[5]','$data[6]','$data[7]','$data[8]','$data[9]','$data[10]') ";
     mysql_query($import_query) or die(mysql_error());
				
     echo $import_query;   // just to see if it worked, not many lines per CSV luckily so shouldnt kill the browser
   }
       
fclose($handle);
echo "Successfully Imported";

?>

Open in new window

0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

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