Home Backend Development PHP Tutorial PHP import excel (xls) file into mysql database_PHP tutorial

PHP import excel (xls) file into mysql database_PHP tutorial

Jul 13, 2016 am 10:46 AM
excel mysql php xls import I Bundle database tidy document

I have sorted out two ways to import excel into mysql database, one is to use PHP-ExcelReader to import, the other is to convert excel into a csv file and directly import it using PHP related functions.

Last night a customer contacted me to build a website. The request was to import the data in the excel file provided by the customer into the mysql database. The most common method is to first export the xls file into a csv format file and then parse it. Files in csv format are imported into the mysql database. The method is relatively redundant and is divided into several steps, which is very inconvenient. Today Broken Bridge Canxue introduced a method that directly skips the intermediate link of csv and directly imports the excel file into the mysql database.

First we need to download PHP-ExcelReader, which is an open source project. It is mainly used to parse excel files. Download address: http://sourceforge.net/projects/phpexcelreader. After downloading, unzip it and mainly use the excel folder. There are two files inside, reader.php and oleread.php (the default of this file is oleread.inc, I don’t know why, it’s a bunch of e-texts, I haven’t read them, just change the name).

Find the following similar code in the reader.php file (the first line is) and change it to the correct oleread.php path: require_once 'oleread.php';

Then create a new php file and import reader.php, the code is as follows:

echo "n"; }
The code is as follows
 代码如下 复制代码

require_once 'Excel/reader.php';
$data = new Spreadsheet_Excel_Reader();
$data->setOutputEncoding('gbk');//此处设置编码,一般都是gbk模式

$data->read('Book1.xls');//文件路径bKjia.c0m

error_reporting(E_ALL ^ E_NOTICE);
//这里我就只循环输出excel文件的内容了,要入库,只要把输出的地方,写一段mysql语句即可~
for ($i = 1; $i <= $data->sheets[0]['numRows']; $i++) {
 for ($j = 1; $j <= $data->sheets[0]['numCols']; $j++) {
  echo """.$data->sheets[0]['cells'][$i][$j]."",";
 }
 echo "n";
}
?>

Copy code

require_once 'Excel/reader.php';

$data = new Spreadsheet_Excel_Reader();

$data->setOutputEncoding('gbk');//Set the encoding here, usually in gbk mode


$data->read('Book1.xls');//File path bKjia.c0m

error_reporting(E_ALL ^ ​​E_NOTICE);

//Here I will only loop the contents of the excel file. To store it in the database, just write a mysql statement in the output place~

for ($i = 1; $i <= $data->sheets[0]['numRows']; $i++) {
for ($j = 1; $j <= $data->sheets[0]['numCols']; $j++) {

echo """.$data->sheets[0]['cells'][$i][$j]."",";
 代码如下 复制代码


//连接数据库文件 www.bKjia.c0m
$connect=mysql_connect("localhost","admin","admin") or die("链接数据库失败!");
//连接数据库(test)
mysql_select_db("testcg",$connect) or die (mysql_error());

$temp=file("test.csv");//连接EXCEL文件,格式为了.csv
for ($i=0;$i {
$string=explode(",",$temp[$i]);//通过循环得到EXCEL文件中每行记录的值
//将EXCEL文件中每行记录的值插入到数据库中
$q="insert into ceshi (name,num,dom) values('$string[0]','$string[1]','$string[2]');";
mysql_query($q) or die (mysql_error());

if (!mysql_error());
{
echo " 成功导入数据!";
}
echo $string[4]."n";
unset($string);
}
?>

}
?> Note: Please do not use the xls in the PHP-ExcelReader compressed package for testing. Broken Bridge Canxue found that the file cannot be opened even with excel, so it is wrong. Broken Bridge Canxue used the above method to parse a 1.4M data, and it all showed normal, so everyone can use it with confidence phpexcel is relatively resource intensive, but not all excel can be read. We can convert it to csv for operation First confirm what encoding your database is in, take utf-8 as an example, You first open the excel file, then save it and choose to save as a .csv file. Then open the .csv file with a text editor and save it as utf-8 csv Then when you write PHP, you can use PHP's getcsv to open it (this ensures that the fields you have contain it, which will cause parsing errors), and then import the parsed results into the database. If it were in csv format, I wouldn’t have to go through so much trouble
The code is as follows Copy code
//Connect to the database file www.bKjia.c0m<🎜> $connect=mysql_connect("localhost","admin","admin") or die("Failed to connect to database!"); <🎜> //Connect to database (test) <🎜> mysql_select_db("testcg",$connect) or die (mysql_error());<🎜> <🎜>$temp=file("test.csv");//Connect the EXCEL file, the format is .csv <🎜> for ($i=0;$i { <🎜> $string=explode(",",$temp[$i]);//Get the value of each row record in the EXCEL file through loop <🎜> //Insert the value of each row of records in the EXCEL file into the database <🎜> $q="insert into ceshi (name,num,dom) values('$string[0]','$string[1]','$string[2]');"; <🎜> mysql_query($q) or die (mysql_error());<🎜> <🎜>if (!mysql_error()); <🎜> { <🎜> echo "Data imported successfully!"; <🎜> } <🎜> echo $string[4]."n"; <🎜> unset($string); <🎜> } <🎜> ?>

www.bkjia.comtruehttp: //www.bkjia.com/PHPjc/632909.htmlTechArticleI have sorted out two ways to import mysql database in excel, one is to use PHP-ExcelReader to import, the other Just convert excel into a csv file and import it directly using PHP related functions. Last night...
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

Hot AI Tools

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress AI Tool

Undress images for free

Clothoff.io

Clothoff.io

AI clothes remover

AI Hentai Generator

AI Hentai Generator

Generate AI Hentai for free.

Hot Article

R.E.P.O. Energy Crystals Explained and What They Do (Yellow Crystal)
2 weeks ago By 尊渡假赌尊渡假赌尊渡假赌
Repo: How To Revive Teammates
4 weeks ago By 尊渡假赌尊渡假赌尊渡假赌
Hello Kitty Island Adventure: How To Get Giant Seeds
4 weeks ago By 尊渡假赌尊渡假赌尊渡假赌

Hot Tools

Notepad++7.3.1

Notepad++7.3.1

Easy-to-use and free code editor

SublimeText3 Chinese version

SublimeText3 Chinese version

Chinese version, very easy to use

Zend Studio 13.0.1

Zend Studio 13.0.1

Powerful PHP integrated development environment

Dreamweaver CS6

Dreamweaver CS6

Visual web development tools

SublimeText3 Mac version

SublimeText3 Mac version

God-level code editing software (SublimeText3)

PHP 8.4 Installation and Upgrade guide for Ubuntu and Debian PHP 8.4 Installation and Upgrade guide for Ubuntu and Debian Dec 24, 2024 pm 04:42 PM

PHP 8.4 brings several new features, security improvements, and performance improvements with healthy amounts of feature deprecations and removals. This guide explains how to install PHP 8.4 or upgrade to PHP 8.4 on Ubuntu, Debian, or their derivati

Discuss CakePHP Discuss CakePHP Sep 10, 2024 pm 05:28 PM

CakePHP is an open-source framework for PHP. It is intended to make developing, deploying and maintaining applications much easier. CakePHP is based on a MVC-like architecture that is both powerful and easy to grasp. Models, Views, and Controllers gu

How to fix mysql_native_password not loaded errors on MySQL 8.4 How to fix mysql_native_password not loaded errors on MySQL 8.4 Dec 09, 2024 am 11:42 AM

One of the major changes introduced in MySQL 8.4 (the latest LTS release as of 2024) is that the &quot;MySQL Native Password&quot; plugin is no longer enabled by default. Further, MySQL 9.0 removes this plugin completely. This change affects PHP and other app

How To Set Up Visual Studio Code (VS Code) for PHP Development How To Set Up Visual Studio Code (VS Code) for PHP Development Dec 20, 2024 am 11:31 AM

Visual Studio Code, also known as VS Code, is a free source code editor — or integrated development environment (IDE) — available for all major operating systems. With a large collection of extensions for many programming languages, VS Code can be c

CakePHP Quick Guide CakePHP Quick Guide Sep 10, 2024 pm 05:27 PM

CakePHP is an open source MVC framework. It makes developing, deploying and maintaining applications much easier. CakePHP has a number of libraries to reduce the overload of most common tasks.

How do you parse and process HTML/XML in PHP? How do you parse and process HTML/XML in PHP? Feb 07, 2025 am 11:57 AM

This tutorial demonstrates how to efficiently process XML documents using PHP. XML (eXtensible Markup Language) is a versatile text-based markup language designed for both human readability and machine parsing. It's commonly used for data storage an

PHP Program to Count Vowels in a String PHP Program to Count Vowels in a String Feb 07, 2025 pm 12:12 PM

A string is a sequence of characters, including letters, numbers, and symbols. This tutorial will learn how to calculate the number of vowels in a given string in PHP using different methods. The vowels in English are a, e, i, o, u, and they can be uppercase or lowercase. What is a vowel? Vowels are alphabetic characters that represent a specific pronunciation. There are five vowels in English, including uppercase and lowercase: a, e, i, o, u Example 1 Input: String = "Tutorialspoint" Output: 6 explain The vowels in the string "Tutorialspoint" are u, o, i, a, o, i. There are 6 yuan in total

7 PHP Functions I Regret I Didn't Know Before 7 PHP Functions I Regret I Didn't Know Before Nov 13, 2024 am 09:42 AM

If you are an experienced PHP developer, you might have the feeling that you’ve been there and done that already.You have developed a significant number of applications, debugged millions of lines of code, and tweaked a bunch of scripts to achieve op

See all articles