Importing files

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
shivam0101
Forum Contributor
Posts: 197
Joined: Sat Jun 09, 2007 12:09 am

Importing files

Post 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?
mezise
Forum Newbie
Posts: 17
Joined: Tue Sep 18, 2007 4:38 am

Post 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
User avatar
xpgeek
Forum Contributor
Posts: 146
Joined: Mon May 22, 2006 1:45 am
Location: Kyiv, Ukraine
Contact:

Post 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 ?
shivam0101
Forum Contributor
Posts: 197
Joined: Sat Jun 09, 2007 12:09 am

Post 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">
User avatar
xpgeek
Forum Contributor
Posts: 146
Joined: Mon May 22, 2006 1:45 am
Location: Kyiv, Ukraine
Contact:

Post 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>";
?>
shivam0101
Forum Contributor
Posts: 197
Joined: Sat Jun 09, 2007 12:09 am

Post 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?
User avatar
xpgeek
Forum Contributor
Posts: 146
Joined: Mon May 22, 2006 1:45 am
Location: Kyiv, Ukraine
Contact:

Post by xpgeek »

After you press submit the file moved to tmp directory.
So you can access to tmp name throught $_FILES array.
mezise
Forum Newbie
Posts: 17
Joined: Tue Sep 18, 2007 4:38 am

Post 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
Post Reply