Uploading large files to your database

Topic:	Uploading large files to your database
Slugger Professional Posts: 1185 Posted: 4.Apr 2006 - 07:51	Sometimes you want to upload a lot of data. If you're sharing a server, it's likely your host has put a limit on the size of your upload file. Maybe you can only use ftp and not command shell...whatever. There is a solution that's as easy as 1-2-3: 1. Upload your file, in CSV format (that's comma separated value or, as I call it, comma delimited) 2. Save it with the .txt extension, and 3. Use the following PHP script to populate your data base. Now before doing anything, of course, backup your files. This script will drop the table and any data it contains...so beware. All you have to do is copy the sript, below, and paste it into a text file which you can name whateveryouwant.php. And, of course, don't forget to personalize username, password, database name, table name, and the name of your csv file to suit your case. Upload the file to the same directory as your csvtext file and call the PHP file from your browser...that would be http://www.yourdomain.com/yourdatadirectory/whateveryouwant.php. So here it is: Code <?php # MySQL database User ID, Password, Database name, and Table Name $sql_id = "Your database username"; $sql_pwd = "Your database password"; $sql_db = "Your database's name"; $sql_tbl = "Your table's name"; # Connect to the database mysql_connect('',$sql_id,$sql_pwd); # Delete the current content of the table $result = mysql_db_query($sql_db,"DELETE FROM $sql_tbl") or die ("Invalid DELETE query"); # Optimize the current table (recover empty space) $result = mysql_db_query($sql_db,"OPTIMIZE TABLE $sql_tbl") or die ("Invalid OPTIMIZE query"); # Load local comma separated, fields enclosed by quotes text database - File has to be in the same directory of this file $result = mysql_db_query($sql_db,"LOAD DATA LOCAL INFILE 'yourcsvtextfile.txt' INTO TABLE $sql_tbl FIELDS TERMINATED BY ',' ENCLOSED BY '\"' LINES TERMINATED BY '\r'") or die ("Invalid DATA LOAD query"); # Get how many records are present in the table now $result = mysql_db_query($sql_db,"SELECT * from $sql_tbl") or die ("Invalid SELECT query"); $rows_count = mysql_num_rows($result); echo "Records: $rows_count"; mysql_free_result($result); ?> If it doesn't work for you, try removing Code LINES TERMINATED BY '\r' Good luck and sweet dreams. Slugger

Topic:

Uploading large files to your database

Slugger

Professional

Posts: 1185

Posted:
4.Apr 2006 - 07:51

Sometimes you want to upload a lot of data. If you're sharing a server, it's likely your host has put a limit on the size of your upload file. Maybe you can only use ftp and not command shell...whatever.

There is a solution that's as easy as 1-2-3:

1. Upload your file, in CSV format (that's comma separated value or, as I call it, comma delimited)
2. Save it with the .txt extension, and
3. Use the following PHP script to populate your data base.

Now before doing anything, of course, backup your files. This script will drop the table and any data it contains...so beware.

All you have to do is copy the sript, below, and paste it into a text file which you can name whateveryouwant.php. And, of course, don't forget to personalize username, password, database name, table name, and the name of your csv file to suit your case. Upload the file to the same directory as your csvtext file and call the PHP file from your browser...that would be http://www.yourdomain.com/yourdatadirectory/whateveryouwant.php.

So here it is:

Code

<?php

# MySQL database User ID, Password, Database name, and Table Name
$sql_id = "Your database username";
$sql_pwd = "Your database password";
$sql_db = "Your database's name";
$sql_tbl = "Your table's name";

# Connect to the database
mysql_connect('',$sql_id,$sql_pwd);

# Delete the current content of the table
$result = mysql_db_query($sql_db,"DELETE FROM $sql_tbl") or die ("Invalid DELETE query");

# Optimize the current table (recover empty space)
$result = mysql_db_query($sql_db,"OPTIMIZE TABLE $sql_tbl") or die ("Invalid OPTIMIZE query");

# Load local comma separated, fields enclosed by quotes text database - File has to be in the same directory of this file
$result = mysql_db_query($sql_db,"LOAD DATA LOCAL INFILE 'yourcsvtextfile.txt' INTO TABLE $sql_tbl FIELDS TERMINATED BY ',' ENCLOSED BY '\"' LINES TERMINATED BY '\r'") or die ("Invalid DATA LOAD query");

# Get how many records are present in the table now
$result = mysql_db_query($sql_db,"SELECT * from $sql_tbl") or die ("Invalid SELECT query");
$rows_count = mysql_num_rows($result);

echo "Records: $rows_count";
mysql_free_result($result);
?>

If it doesn't work for you, try removing

Code

LINES TERMINATED BY '\r'

Good luck and sweet dreams.

Slugger