Page 1 of 1

Importing Excel data into MySQL Hooray for delimiting! Rate Topic: ****- 3 Votes

#1 akozlik  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 90
  • View blog
  • Posts: 797
  • Joined: 25-February 08

Post icon  Posted 11 June 2008 - 02:57 PM

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

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

// 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 *

Is This A Good Question/Topic? 2
  • +

Replies To: Importing Excel data into MySQL

#2 chrisman  Icon User is offline

  • New D.I.C Head
  • member icon

Reputation: 1
  • View blog
  • Posts: 46
  • Joined: 22-March 08

Posted 14 June 2008 - 08:03 PM

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. ;)
Was This Post Helpful? 0
  • +
  • -

#3 akozlik  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 90
  • View blog
  • Posts: 797
  • Joined: 25-February 08

Posted 14 June 2008 - 09:45 PM

Quote

PS: No "Thanks" link on this page. ;)


Ha ha, no problem. I'm glad you enjoyed the tutorial. Let us know on the PHP board if you have any more issues and we'll try to clear them up. I don't mind writing more tutorials if they're needed.
Was This Post Helpful? 0
  • +
  • -

#4 MrsQ  Icon User is offline

  • New D.I.C Head

Reputation: 2
  • View blog
  • Posts: 29
  • Joined: 20-September 08

Posted 20 September 2008 - 08:07 AM

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!
Was This Post Helpful? 0
  • +
  • -

#5 akozlik  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 90
  • View blog
  • Posts: 797
  • Joined: 25-February 08

Posted 20 September 2008 - 08:20 AM

View PostMrsQ, on 20 Sep, 2008 - 11:07 AM, said:

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!


Awesome, I'm glad it really helped you out. Definitely come visit the forums if you have any specific questions you can't find answers to. Everybody's really helpful in there.

Out of curiosity, how did you end up finding this tutorial? I've written several for DIC, but a few of them have had a lot more exposure than the rest. This is one of those.

Glad you like it and welcome to the PHP world!
Was This Post Helpful? 0
  • +
  • -

#6 faiz1  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 16
  • Joined: 17-September 08

Posted 14 July 2009 - 02:59 AM

thx man i have been looking for this thing for the last 18 days and it was right under my nose thx . i will updated u . whether it worked or not . thx agian
Was This Post Helpful? 0
  • +
  • -

#7 Guest_tjosephads*


Reputation:

Posted 24 August 2010 - 01:10 PM

Why do I have a problem when there is an apostrophe in my data??
Was This Post Helpful? 0

#8 akozlik  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 90
  • View blog
  • Posts: 797
  • Joined: 25-February 08

Posted 24 August 2010 - 01:24 PM

View Posttjosephads, on 24 August 2010 - 03:10 PM, said:

Why do I have a problem when there is an apostrophe in my data??


The apostrophe will break the sql. Something like

$sql = "select * from users where last_name like 'O'Hare'";
$result = mysql_query($sql);



will break the sql because of the quote after the "O". The sql engine sees that as the end of the statement. This easily fixed with

$last_name = mysql_real_escape_string("O'Hare");
$sql = "select * from users where last_name like 'O'Hare'";
$result = mysql_query($sql);



The mysql_real_escape_string function will fix the problem you're having.
Was This Post Helpful? 1
  • +
  • -

#9 noprobz09  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 9
  • Joined: 01-September 09

Posted 23 September 2010 - 09:05 PM

:chinese: nice codes, I hope it can be a great help for my project about OGS, thanks a lot for your codes...I will post soon, if this code will it work or not...
Was This Post Helpful? 0
  • +
  • -

#10 cameltech  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 4
  • Joined: 26-February 11

Posted 28 February 2011 - 10:36 PM

View Postchrisman, on 15 June 2008 - 08:33 AM, said:

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. :helpsmilie:...Thanks .
Was This Post Helpful? 0
  • +
  • -

#11 akozlik  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 90
  • View blog
  • Posts: 797
  • Joined: 25-February 08

Posted 01 March 2011 - 08:13 AM

View Postcameltech, on 01 March 2011 - 01:36 AM, said:

View Postchrisman, on 15 June 2008 - 08:33 AM, said:

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. :helpsmilie:...Thanks .


Just do the same, but in reverse. Select * from your table and echo the results separated by either tabs or commas, adding a new line terminator after each record.

Alternatively, if you have PHPMyAdmin you can use their export tools to export directly to xls format.
Was This Post Helpful? 2
  • +
  • -

Page 1 of 1