Page 1 of 1

Importing files

Posted: Fri Sep 21, 2007 6:16 am
by shivam0101

Code: Select all

$numbers = file("../Book1.csv");
   foreach ($numbers as $number)
   {
      list($data) = explode(",", $number);
      $query_insert=mysql_query("INSERT INTO members SET member_id='$data'");
   }
The above script works well. But if i wanted the csv file to be dynamic, should i have to copy the file to local folder and read it from there?

Posted: Fri Sep 21, 2007 7:11 am
by mezise
Hi,

what do you mean by local folder? For me "../Book1.csv" is the local folder (in WWW server). So if you change the file Book1.csv and run the script, data will be imported.

If you have access to MySQL command "LOAD DATA INFILE" a very fast way to import CSV data is:

Code: Select all

LOAD DATA INFILE 'Book1.csv' INTO TABLE members
  FIELDS TERMINATED BY ','
  LINES TERMINATED BY '\n';
By the way: executing INSERT command one by one for every record is not very efficient if it concerns you, specially if you have indexes on a table you are importing to. On every INSERT MySQL rebuilds index data and when the table is big it may take some time to finish the job. More data may be inserted in one INSERT query, then index data are rebuilt once (check a manual). Of course you also need to consider scalability issues. When you import much data, an other option are these commands:

Code: Select all

ALTER TABLE members DISABLE KEYS;
ALTER TABLE members ENABLE KEYS;
Michal

Posted: Fri Sep 21, 2007 7:18 am
by xpgeek
2 mezise

Code: Select all

LOAD DATA INFILE 'Book1.csv' INTO TABLE members
  FIELDS TERMINATED BY ','
  LINES TERMINATED BY '\n'; 
To work with file from mysql you need a FILE previlegies, but it is not recommended on the security reason.


2 shivam0101
The above script works well. But if i wanted the csv file to be dynamic, should i have to copy the file to local folder and read it from there?
What you mean ?

Posted: Fri Sep 21, 2007 7:20 am
by shivam0101
thanks.

What i meant was, importing csv file means, the user will browse for the file and select a csv file and submit. So how to give the path

Code: Select all

$numbers = file("../Book1.csv");
for this,

in html i have given,

Code: Select all

<input type="file" name="csv_file">

Posted: Fri Sep 21, 2007 7:27 am
by xpgeek
:D
It is simple, after user select a file and press submit - see $_FILES array.
You can find all information what you need to get file.

Simple example:

Code: Select all

<?php
$uploaddir = '';
$uploadfile = $uploaddir . basename($_FILES['csv_file']['name']);
echo '<pre>';
if (move_uploaded_file($_FILES['csv_file']['tmp_name'], $uploadfile)) {
   echo "File is valid, and was successfully uploaded.\n";
} else {
   echo "Possible file upload attack!\n";
}
echo 'Here is some more debugging info:';
print_r($_FILES);
print "</pre>";
?>

Posted: Fri Sep 21, 2007 7:48 am
by shivam0101
thanks once again. Is it possible to copy the contents to database without uploading the file by just showing the path. Is it necessary to copy the file?

Posted: Fri Sep 21, 2007 8:14 am
by xpgeek
After you press submit the file moved to tmp directory.
So you can access to tmp name throught $_FILES array.

Posted: Fri Sep 21, 2007 8:18 am
by mezise
shivam0101 wrote:thanks once again. Is it possible to copy the contents to database without uploading the file by just showing the path. Is it necessary to copy the file?
Aah, that's what you mean! Yes it is necessary, but it is done automatically after user press Submit button. Then $_FILES array contains information about uploaded file copied by a Web Server to a temporary directory. Just look at $_FILES array as xpgeek suggested and you will find path to this uploaded file which you may read and do what you want.

Michal