Solved

How do I import data from several Excel files into MySQL database?

Posted on 2010-09-20
26
2,808 Views
Last Modified: 2012-05-10
I have approximately 3000 excel files, invoices to be exact. I am looking to import specific cells from these invoices (name, address, telephone numbers, etc) into a MySQL database. They are the same set of cells on every invoice. What is the best way to automate the process of what would be opening each excel file, and manually entering the names, addresses, and phone numbers into the database I've created?

Thanks in advance,
Charles
0
Comment
Question by:chazzzle
  • 12
  • 7
  • 6
  • +1
26 Comments
 
LVL 7

Expert Comment

by:haijerome
ID: 33714428
Hi,

   This is the best solution for you ....

   Kindly go Through  the link below..

  http://rpbouman.blogspot.com/2006/03/importing-xml-data-into-mysql-using.html

If it helps then its cool else let me know..

Regards,
Jerome Dennis D
0
 
LVL 7

Expert Comment

by:haijerome
ID: 33714456
Hi..

   Am really sorry for posting an irrelevant answer to your question (which in turn an answer for another post in the same zone) ...Anyway i'll get back to you with what you need ..


Regards,
Jerome Dennis D
0
 
LVL 7

Expert Comment

by:haijerome
ID: 33715662
Hi,

   Here's the one exactly what you need. With this i've attached a sample code for Excel Reader. The attachment contains three files :

   1.example.php
       
          It is the main file we should run

  2. excel_reader2.php

         It is the important file which contains all the methods to manipulate an Excel sheet...

 3. import.xls

       A sample Excel sheet for importing...

   In this example i've covered an excel sheet import into mysql Table ... If you need it for multiple excel sheets for multiple excel workbooks then you have to add some lines of code to get it right. If you need the later one i am ready to help you again...

 If it helps then its cool else let me know..

Regards,
Jerome Dennis D
excel-reader.zip
0
 

Author Comment

by:chazzzle
ID: 33715809
I think this is close to what I need, allow me to clarify the original question -

On each excel sheet I only need cells D13, D14, F15, H15, D16, F17, G17, and M13

The rest of the rows and columns in the excel spreadsheet contain irrelevant data. How do I get just the cells I need from the spreadsheet using your excel reader?
0
 
LVL 7

Expert Comment

by:haijerome
ID: 33716264
Hi

  Glad to help you once again :-)

     Its pretty simple... just make a little change in example.php thats enough..

    Since You need only the specific cells then there is no need for looping throught all rows and columns just directly give away the row and column numbers to proceed ...

   I assume that the first row of the excel sheet contains the field names but it is the first row in our code it is considered as row no : 1 i.e $row = 1... And 'A' column is col.No : 1 i.e. $col = 1...

   So    D13 means => $data->val(4,13,$sheet);             F14 means => $data->val(6,14,$sheet);

   and similar for other cells

 It will get you what you need ....

  And if you have so many worksheets in your workbook then you can loop through easily .. i have also attached the code for it here....

  So Go thru the Code attached and get back to me if you need further assistance..

 I hope this time you got it right !!!!  :-)


 If it helps then its cool else feel free to get back to me again... I am willing to help you  :-)

Regards,Jerome Dennis D                            
 
   

   
<html>
<head>

    <title>Excel Reader</title>

</head>

<body>

 <?php

error_reporting(E_ALL ^ E_NOTICE);
require_once 'excel_reader2.php';

//Specify your Excel File Here
$data = new Spreadsheet_Excel_Reader("import.xls");

/*Creating Database Connection*/
/*Remove the mysql_error() once you're running in production mode*/

$cn = mysql_connect("localhost","root","") or die("Cannot connect to MySQL ! See error below : <br/>".mysql_error());
mysql_select_db("excel",$cn) or die("Cannot Select Databse ! See error below : <br/>".mysql_error());



?>

<?php /*UnComment the below line to just print your Excel Sheet As it is :)*/?>
<?php //echo $data->dump(true,true); ?>
   

    <?php
        /*
        
         Give your Worksheet Number Here.
         Below i just gave my worksheet number as 0 for the first worksheet of the excel file

        */

        /*
         

         I think this is close to what I need, allow me to clarify the original question -

         On each excel sheet I only need cells D13, D14, F15, H15, D16, F17, G17, and M13

         The rest of the rows and columns in the excel spreadsheet contain irrelevant data.

         How do I get just the cells I need from the spreadsheet using your excel reader?


         1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26
         A B C D E F G H I J  K  L  M  N  O  P  Q  R  S  T  U  V  W  X  Y  Z

         

         */

         /* If you have so many worksheets in your Workbook then create  a loop : For example
          *  If you have 3 worksheets then code like this
         $sheet = 0; while($sheet>2) {
         $sheet = 0;
         $data->val(4,13,$sheet);
         $data->val(4,14,$sheet);
         $data->val(6,14,$sheet);
         $data->val(8,15,$sheet);
         $data->val(4,16,$sheet);
         $data->val(6,17,$sheet);
         $data->val(7,17,$sheet);
         $data->val(13,13,$sheet);

         $ins_query = "INSERT INTO yourtablename values('".$data->val(4,13,$sheet)."','".$data->val(4,14,$sheet)."','"."','".$data->val(6,14,$sheet)."','"."','".$data->val(8,15,$sheet)."','"."','".$data->val(4,16,$sheet)."','"."','".$data->val(6,17,$sheet)."','"."','".$data->val(7,17,$sheet)."','"."','".$data->val(13,13,$sheet)."')";
         mysql_query($ins_query,$cn) or die("Query Failed !!!".mysql_error());
         echo "<h2>Your Excel File has been successfully imported into your MySQL TABLE :)</h2>`";
         mysql_close();
               } // End of While
             */

         $sheet = 0;
         $data->val(4,13,$sheet);
         $data->val(4,14,$sheet);
         $data->val(6,14,$sheet);
         $data->val(8,15,$sheet);
         $data->val(4,16,$sheet);
         $data->val(6,17,$sheet);
         $data->val(7,17,$sheet);
         $data->val(13,13,$sheet);

         $ins_query = "INSERT INTO yourtablename values('".$data->val(4,13,$sheet)."','".$data->val(4,14,$sheet)."','"."','".$data->val(6,14,$sheet)."','"."','".$data->val(8,15,$sheet)."','"."','".$data->val(4,16,$sheet)."','"."','".$data->val(6,17,$sheet)."','"."','".$data->val(7,17,$sheet)."','"."','".$data->val(13,13,$sheet)."')";
         mysql_query($ins_query,$cn) or die("Query Failed !!!".mysql_error());
         echo "<h2>Your Excel File has been successfully imported into your MySQL TABLE :)</h2>`";
         mysql_close();

		

    ?>
</body>
</html>

Open in new window

example1.php
0
 
LVL 33

Expert Comment

by:Norie
ID: 33717444
Jerome

Sorry to bother you, but I downloaded your files but can't seem to get them to work properly.

I keep on getting a 500 error with example.php and just a blank screen with excel_reader2 (I expected that though).

I've also got a simple script that I created to test the server and MySQL and it doesn't work.

Can you see anything obvious I'm doing wrong?
0
 
LVL 7

Expert Comment

by:haijerome
ID: 33722212
Hi imnorie,

       Glad to help you.. Before i figure out your problem (500 internal server error )... I have to know  
what web server are you using Apache / IIS ....

    Because the code works fine for me am using xampp latest stable version ...

   And if you are ultimate beginner to PHP then kindly place your files inside htdocs with folder structure
c:\\xampp\htdocs\excel reader\  (In case of XAmpp)... In case of IIS place the folder excel reader inside your c:\\inetpub\wwwroot\excel reader .....

     And i have attached the Sample files again with the database files ... Kindly find it ...
   I hope this time you got it right !!!!  :-)

    Just For Clarification : Kindly unzip the files and place the excel reader folder inside your webroot and run example.php ... :-)  
If it helps then its cool else feel free to get back to me again... I am willing to help you  :-)

Regards,Jerome Dennis D                            
 

     
       

   
excel-reader.zip
0
 

Author Comment

by:chazzzle
ID: 33722883
I am currently in the process of moving all my workbooks into one giant workbook so I can use the php files on them. I'm just over 1/3rd of the way done (1200 of 3000 moved so far) and will post an update as soon as I can.
0
 
LVL 33

Expert Comment

by:Norie
ID: 33726979
Jerome

Somehow my php.ini file had become 'corrupt', well one of the paths was missing/wrong anyway.

Got it working now though, fingers crossed.

I remember having a bit of a nightmare installing Apache, MySQL, PHP when I did it originally but I got things working.

That was awhile ago so something must have happened to the php.ini file in the meantime.

Anyway all sorted now.:)

One thing I was thinking about before I saw your solution was whether it would be possible to import everything from Excel in one go.

I know that's possible using VBA, ADO etc but do you think it would be possible with php.

There is a way to import CSV files to MySQL via a query using LOADFILE, and obviously the Excel file could be saved as CVS.

Do you think that would be viable?

Might not even need the excel_reader module.
0
 
LVL 7

Expert Comment

by:haijerome
ID: 33731385
Hi imnorie,

       Its glad to hear that you've got it working....
     
       And if you want to export everything from an Excel sheet into MySQL directly then there are some tools available Excel To MySQL tool (Sadly not freeware ) ... Because still now we don't have any native support from MySQL to import Excel File Directly without converting it to .CSV files ... And Loading .CSV files into MySQL is really a viable one of course... And obviously there is no need for Excel Reader Module...



Regards,
Jerome Dennis D

         
0
 
LVL 33

Expert Comment

by:Norie
ID: 33731590
jerome

I think I'd prefer to do it myself if you like.

I'm not too experienced with PHP - think the syntax and lack of development tools has put me off.

That's just being lazy though and relying on MS or whoever to provide IDEs etc, on their terms.

Anyway, slight rant over so a simple question.

Do you think it would be possible/worthwhile to use Excel Reader to create a CSV file from an Excel worksheet?

It does a pretty good job of displaying them in a browser.

I suppose I could find the answer in the code but there's a fair amount of that to go through.

Perhaps somebody has already tried it???
0
 
LVL 7

Expert Comment

by:haijerome
ID: 33733001
Hi

      Because the solutions what i've given here are the ones i used with ... And importing excel directly into MySQL is really an  Out of Box feature ....And i would really appreciate and glad if you can...
Regards,Jerome Dennis D  

0
 
LVL 33

Expert Comment

by:Norie
ID: 33733144
Jerome

I might take a look at it, but considering the trouble I had just getting PHP to even work I wouldn't
hold your breath.

I've searched on the topic quite a few times and always it's the same answer - convert to CSV then import to SQL.

Cheers

Norie
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 21

Expert Comment

by:theGhost_k8
ID: 33733264
Have u tried kettle! kettle.pentaho.org
0
 

Author Comment

by:chazzzle
ID: 33735454
I am performing a test using a spreadsheet with 5 invoices, it is successfully putting the customer number into the database but the rest of the columns are remaining empty, am I missing something? Below are the errors I get and the modified example1.php I've made:


Deprecated: Assigning the return value of new by reference is deprecated in C:\xampp\htdocs\excel_reader2.php on line 916

Deprecated: Function split() is deprecated in C:\xampp\htdocs\excel_reader2.php on line 844

Deprecated: Function split() is deprecated in C:\xampp\htdocs\excel_reader2.php on line 844

Deprecated: Function split() is deprecated in C:\xampp\htdocs\excel_reader2.php on line 844

Deprecated: Function split() is deprecated in C:\xampp\htdocs\excel_reader2.php on line 844

Deprecated: Function split() is deprecated in C:\xampp\htdocs\excel_reader2.php on line 844

Deprecated: Function split() is deprecated in C:\xampp\htdocs\excel_reader2.php on line 844

Deprecated: Function split() is deprecated in C:\xampp\htdocs\excel_reader2.php on line 844

Deprecated: Function split() is deprecated in C:\xampp\htdocs\excel_reader2.php on line 844

Deprecated: Function split() is deprecated in C:\xampp\htdocs\excel_reader2.php on line 844

Deprecated: Function split() is deprecated in C:\xampp\htdocs\excel_reader2.php on line 844

Deprecated: Function split() is deprecated in C:\xampp\htdocs\excel_reader2.php on line 844

Deprecated: Function split() is deprecated in C:\xampp\htdocs\excel_reader2.php on line 844

Warning: array_key_exists() expects parameter 2 to be array, null given in C:\xampp\htdocs\excel_reader2.php on line 348

Warning: array_key_exists() expects parameter 2 to be array, null given in C:\xampp\htdocs\excel_reader2.php on line 348

Warning: array_key_exists() expects parameter 2 to be array, null given in C:\xampp\htdocs\excel_reader2.php on line 348

Warning: array_key_exists() expects parameter 2 to be array, null given in C:\xampp\htdocs\excel_reader2.php on line 348

Warning: array_key_exists() expects parameter 2 to be array, null given in C:\xampp\htdocs\excel_reader2.php on line 348

Warning: array_key_exists() expects parameter 2 to be array, null given in C:\xampp\htdocs\excel_reader2.php on line 348

Warning: array_key_exists() expects parameter 2 to be array, null given in C:\xampp\htdocs\excel_reader2.php on line 348

Warning: array_key_exists() expects parameter 2 to be array, null given in C:\xampp\htdocs\excel_reader2.php on line 348
Your Excel File has been successfully imported into your MySQL TABLE :)
`
<html>
<head>

    <title>Excel Reader</title>

</head>

<body>

 <?php

error_reporting(E_ALL ^ E_NOTICE);
require_once 'excel_reader2.php';

//Specify your Excel File Here
$data = new Spreadsheet_Excel_Reader("Book1.xls");

/*Creating Database Connection*/
/*Remove the mysql_error() once you're running in production mode*/

$cn = mysql_connect("localhost","root","password") or die("Cannot connect to MySQL ! See error below : <br/>".mysql_error());
mysql_select_db("bandg",$cn) or die("Cannot Select Databse ! See error below : <br/>".mysql_error());



?>

<?php /*UnComment the below line to just print your Excel Sheet As it is :)*/?>
<?php //echo $data->dump(true,true); ?>
   

    <?php

         $sheet = 0; while($sheet<6) {
         $data->val(4,13,$sheet);
         $data->val(4,14,$sheet);
         $data->val(6,14,$sheet);
         $data->val(8,15,$sheet);
         $data->val(4,16,$sheet);
         $data->val(6,17,$sheet);
         $data->val(7,17,$sheet);
         $data->val(13,13,$sheet);
	 $sheet++;
         $ins_query = "INSERT INTO invoices (`name`, `address`, `citystate`, `zip`, `phone1`, `phone2`, `phone3`, `customernumber`) values ('".$data->val(4,13,$sheet)."','".$data->val(4,14,$sheet)."','".$data->val(6,14,$sheet)."','".$data->val(8,15,$sheet)."','".$data->val(4,16,$sheet)."','".$data->val(6,17,$sheet)."','".$data->val(7,17,$sheet)."','".$data->val(13,13,$sheet)."')";
         mysql_query($ins_query,$cn) or die("Query Failed !!!".mysql_error());
               } // End of While
         echo "<h2>Your Excel File has been successfully imported into your MySQL TABLE :)</h2>`";
         mysql_close();
		

    ?>
</body>
</html>

Open in new window

0
 
LVL 33

Expert Comment

by:Norie
ID: 33740694
Are you trying to import more than 1 worksheet?

Does it work if you only import 1 worksheet?

Apart from the errors does it actually insert the data correctly?
0
 
LVL 7

Expert Comment

by:haijerome
ID: 33741161
Hi,

   Glad to Help you Again !
 
   Kindly Follow the instructions below to debug it successfully  :-)

   1. Echo the Cell Values and check whether our excel Reader is pulling it from the Excel Sheets successfully...

Eg :   echo $data->val(4,13,$sheet);
         echo $data->val(4,14,$sheet);
         echo $data->val(6,14,$sheet);
         echo $data->val(8,15,$sheet);

   2. If So copy your Database query and run in your PHPmyadmin  / MySQL to conform whether the SQL Query runs fine...

        INSERT INTO invoices (`name`, `address`, `citystate`, `zip`, `phone1`, `phone2`, `phone3`, `)



  3.  

0
 
LVL 33

Expert Comment

by:Norie
ID: 33741179
Jerome

I tested the OP's code with a workbook with appropriately structured worksheets and it worked find.

I only made one change - I moved the incrementation of $sheet after the execution of the insert.

When it was before it one of the worksheets was being missed.

I even added another field to the destination table, 'sheetsource', and altered the SQL to put the sheet number in it.

Everything worked fine.

Is it possible that there is another module involved somewhere that also has a split function?
0
 
LVL 7

Expert Comment

by:haijerome
ID: 33741221
[Continued]

  Even though there are no syntax errors in the query there may be something ..... which MySQL couldn't get / found

 Again for your reference before running your query ... Echo it your browser and run the display query in your MySQl / PHPmyadmin to find it out....
 
I also suspect whether zip,Phone1,Phone2,Phone3 are all declared varchar / Int type


        3. If the Query runs successfully then come to our Code part again...Find the attached Code below and replace your code with this modified one...


If still it makes some problem ... Then (If you don't mind) upload your sample table structure and sample excel sheet here so that i can get it right for you..


 If it helps then its cool else feel free to get back to me again... I am willing to help you  :-)Regards,Jerome Dennis D                            
 
     

           
<html>
<head>

    <title>Excel Reader</title>

</head>

<body>

 <?php

error_reporting(E_ALL ^ E_NOTICE);
require_once 'excel_reader2.php';

//Specify your Excel File Here
$data = new Spreadsheet_Excel_Reader("Book1.xls");

/*Creating Database Connection*/
/*Remove the mysql_error() once you're running in production mode*/

$cn = mysql_connect("localhost","root","password") or die("Cannot connect to MySQL ! See error below : <br/>".mysql_error());
mysql_select_db("bandg",$cn) or die("Cannot Select Databse ! See error below : <br/>".mysql_error());



?>

<?php /*UnComment the below line to just print your Excel Sheet As it is :)*/?>
<?php //echo $data->dump(true,true); ?>
   

    <?php

         $sheet = 0; 
		 while($sheet<6) {
         $name = $data->val(4,13,$sheet);
         $address = $data->val(4,14,$sheet);
         $citystate = $data->val(6,14,$sheet);
         $zip = $data->val(8,15,$sheet);
         $phone1 = $data->val(4,16,$sheet);
         $phone2 = $data->val(6,17,$sheet);
         $phone3 = $data->val(7,17,$sheet);
         $customernumber = $data->val(13,13,$sheet);
		 
         $ins_query = "INSERT INTO invoices (`name`, `address`, `citystate`, `zip`, `phone1`, `phone2`, `phone3`, `customernumber`) values ('".$name."','".$address."','".$citystate."','".$zip."','".$phone1."','".$phone2."','".$phone3."','".$customernumber."')";
         
		 /* Kindly Echo your Query To Check whether it holds all the variables and run it in the MySQL / PHPMYADMIN to check 
			I also suspect whether zip,Phone1,Phone2,Phone3 are all declared varchar / Int type
		 
		 */
		 
		 mysql_query($ins_query,$cn) or die("Query Failed !!!".mysql_error());
		 
		 $sheet++;
		 
               } // End of While
         echo "<h2>Your Excel File has been successfully imported into your MySQL TABLE :)</h2>`";
         mysql_close();
		

    ?>
</body>
</html>

Open in new window

0
 
LVL 7

Expert Comment

by:haijerome
ID: 33741252
Hi imnorie:,

  Exactly ... Its really a Good catch .. I too found that and added the increment line after the query execution and updated in the last sample code attached ....

  I don't think so that there is a chance for another module / class which holds Split function ... sometimes PHP parser redirects to some  different angles rather than what exactly is...

 Anyway its really a good discussion with this good topic and helped to gain really good insights about MySQL and PHP With Excel Files  :-)  for all the particpants and viewers and no doubt good feast to all the ones who visited thjis link.... Cheers EE ... :-)
 

Regards,
Jerome Dennis D
0
 

Author Comment

by:chazzzle
ID: 33745005
I commented out the while loop for the sake of debugging. My MySQL table is made correctly. Below are the files requested.
Book2.xls
example1.php
0
 

Author Comment

by:chazzzle
ID: 33745041
I should also add that when I echo the query it doesn't display data for any variables other than the customernumber. When I check my table it successfully has entered the customer number into the database. I am wondering why this isn't grabbing any of the other variables?
0
 
LVL 33

Assisted Solution

by:Norie
Norie earned 25 total points
ID: 33750444
You've got the row and columns the wrong way round.
0
 
LVL 7

Accepted Solution

by:
haijerome earned 475 total points
ID: 33751058
Hi chazzzle,

 
      Its just a typo, the rows and columns nos are interchanged with their positions..
And it was my oversight....    

    Kindly read below to correct the mistake....
       
   If you want to get the value of CELL D5

       Since D's column no is 4

       
    so give it in this way to get the value of  CELL D5

         
SYNTAX : $dat->val(row,column,Sheet)      

EG :
                         
                   
$data->val(5,4,$sheet);   => this will get you D5                    $data->val(9,4,$sheet);   => this will get you D9                   $data->val(6,7,$sheet);     => this will get you G6

                           
I have made these corrections in your excel sheet and attached ... Find the attachments and get your script running as expected...


If it helps then its cool else let me know..

Regards,Jerome Dennis D

example2.php
Book2.xls
0
 

Author Closing Comment

by:chazzzle
ID: 33751220
Thank you all so much! I have extracted the data from all 3,048 worksheets in my workbook. Now onto correcting the spelling errors! You have saved me months of time.
0
 
LVL 7

Expert Comment

by:haijerome
ID: 33751283
Hi chazzzle,

     Its glad to hear that my solution helped you save lots of time. :-) .. I hope you come back to EE for your further queries ....

    @imnorie Nice discussion with you ...
   
     Cheers EE :-)    

Regards,
Jerome Dennis D
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now