Quite often all of us have to get the data from some Excel table and to fill into MySql database. There is not implemented such standard converting features both on MS Excel and MySql server.
In this tutorial I will show you How to Import Excel XLS file into MySql database.
To done this task, the user has several variants.
First method is no so smart but is very simple and clear method. User have to parse the data is suitable columns. One column for each filed in the data base. Also in front of each value, in each column have to be placed ' symbol and at the end have to be place ', symbol combination. In the last column, each row have to be ended with '); symbol combination. Next have to be inserted one column foremost of all. And in each row have to enter: INSERTO INTO `TABLE` (`field1`, `field2`, `fieldN`) VALUES( . Next this have to be saved like normal text file and copy & paste in MySql console or PhpMyAdmin SQL command field.
Yes. I know that this is very dump method, but long time age this was helpful for me.
How to Import Excel XLS file into MySql database
|
;)
Second but, no so popular method is to run Apache & PHP under Windows Server. In this way you can call Excel COM in PHP and next to work directly with the data in the file. But to use this method, you have to run the Web Server under Windows, which is ... how to say ... hm ... not so popular ;-), have to be installed MS Excel and all of this is not so cheap.
<?php
$xls_file="c:\excel\data.xls";
$xlsObj = new COM("Excel.application") or Die ("Did not connect");
$xlsObj->DisplayAlerts = false;
$xlsObj->Workbooks->Open($xls_file);
$book=$xlsObj->ActiveWorkbook;
$sheets=$book->Sheets;
$sheet=$book->Worksheets(1);
// rest of the code....
?>
Other way is to use one PHP Excel Class from many which is available at Internet. But this method is for more advanced developer which works with objects and object oriented programing.
And the last and most secure and sure variant is to save Excel XLS file into CSV (Comma Separated Values) file in MS Excel and next just to import into MySql. In our next tutorial we will examine exactly how to do this - How to Import CSV file into MySql database.
And the last and most secure and sure variant is to save Excel XLS file into CSV (Comma Separated Values) file in MS Excel and next just to import into MySql. In our next tutorial we will examine exactly how to do this - How to Import CSV file into MySql database.
"1";"PalmOS"
"2";"Windows XP"
"3";"Windows Vista"
"4";"Windows Mobile"
"5";"Windows CE.NET"
"6";"Blackberry"
"9";"Windows 2000"
"7";"Windows 98"
"8";"Windows 95"
"10";"Windows 2003"
"11";"Windows XP64"
"12";"PocketPC 2000"
"13";"PocketPC 2002"
"19";"Windows 7"
Next Tutorials
Post Comment
Need a specific tutorial? Do not hesitate and submit a request!
