A post came up on the board that discussed importing an Excel file into a database. This is a very common task in application development, so I figured I'd show a quick and easy way to do this.
I am going to use an example of a school who wishes to import it's student id numbers and the first, middle, and last names of the student into the database. This hits close home to me because I just did it today. Ha ha.
Lets assume you have a table named students that has the fields 'id', 'firstname', 'middleinitial', 'lastname'. You also have an Excel file with the data you want inputted into the database. My Excel file contains four columns, each with it's corresponding data. I have about 350 fields.
The first thing you want to do is save your Excel in tab delimited format. This is super easy. Just select File > Save As . . . > Text (tab-delimited). I'm using Excel 2003. I imagine the same would apply for the new Office Suite Open Office. Go ahead and save your file as students.txt.
If you view your file in your favorite text editor it should look like the following
CODE
12345 Bob B Johnson
65458 Joe L Schmoe
98741 Jane D Davidson
32179 Sue R Lastname
68423 Ramone Q Smith
It'll probably look neater, but it will be separated by tabs, with a record on each line. Remember that each tab is actually read as a '\t' character.
Next we are going to write the PHP that will process the file. It looks like the following:
php
<?php
// Connect to your database
$fp = fopen("students.txt", "r") // Open the file for reading
while ($line = fgets($fp)) { // Loop through each line
list ($id, $first, $middle, $last) = split("\t", $line, 4); // Split the line by the tab delimiter and store it in our list
$sql = "insert into students (id, firstname, middlename, lastname) values ('$id', '$first', '$middle', '$last')"; // Generate our sql string
mysql_query($sql) or die ( mysql_error() ); // Execute the sql
}
?>
That should just about do it. Just realize that this is meant to be a basic example. If you have 300 rows in your file, you're going to be executing the sql 300 times. If optimization is important to you you'll need to optimize the code.
Pretty short and simple tutorial. I hope it's of some help to anybody out there trying to import Excel data. There are many applications to being able to do this, so I think it's a very important tool in your PHP belt. Leave your comments, suggestions, and criticisms at the beep. * beep *
Importing Excel data into MySQL, Hooray for delimiting!
11 Jun, 2008 - 01:57 PM
| 