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

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 code2.

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 *

Editor’s comment
/1/ - Creating the connection variable $pdo can be looked up in the PHP Manual.
/2/ - Using Prepared Statements—as shown—is such an optimisation. Effectively, you’re only pushing data to MySQL inside the loop, instead of running the insert query each time. With regards to the original author this paragraph was intentionally left in place.

This post has been edited by astonecipher: 03 February 2015 - 09:29 AM
Reason for edit:: Added PDO connection

Thank you for a simple and straightforward explanation! I've been trying to figure out how to do that for quite some time now. I knew it was possible, but being a bit of a newbie to php and web design in general, I wasn't sure how. Although I tried to do searches for instruction, the answers were all a bit over my head. I look forward to learning more from DIC!

Thanks! I'm new to PHP, especially MySQL, and I knew you could do this, I just wasn't sure how.

PS: No "Thanks" link on this page.

Alright.... this is good. But can u do the same in a reverse manner ? i mean.. can u show us how to export MySQL data to Excel sheet ? It would be really helpful if u show us how to do that. ...Thanks .