如何将Excel数据转换为SQL脚本

如何将Excel数据转换为SQL脚本

打开用户提供的excel数据，鼠标定位到第一行数据的最后一个单元格（这里是F2单元格），

输入="",在双引号里面写入INSERT语句脚本，这里举例oracle语句的insert into语句，格式如

INSERT INTO USER(USER_CODE,USER_NAME,USER_TYPE,IS_ENABLED,IN_DATE)VALUES('"&&"','"&&"','"&&"','"&&"',TO_DATE('"&&"','YYYY-MM-DD'))

鼠标定位到两个&&中间，依次选择到栏位对应的单元格，如第一列的值定位到A2，这时公式里面的值就变为了&A2&，意思就是USER_CODE的值就是A2单元格的值，第二列第三列的依次类推.

最后把鼠标光标放到公式最后面，再按Enter键，这样第一行的公式就写好了

鼠标再定位到第一行的公式，鼠标左键按住往下拖动到数据行的最后一行，然后松开按键，这样全部的公式就生成完了。

全部选中公式，右键【复制】，粘贴到记事本中，这样SQL脚本就出来了。

直接放到数据库中执行，这样不就解决问题了吗？

注意事项: SQL语句后面加""是为了多个SQL

一： 在本地PC新建一个Excel文件（例如：excel2007）

准备工作，左键选择excel文本左上角的图标，选择“Excle选项”。1. 点击“信任中心”->“信任中心设置”->“宏设置”->选择“启用所有宏...”选项。“开发人员宏设置”选项也勾选上。 2. 点击“信任中心”->“信任中心设置”->选择“个人信息选项”，将“文档特定设置”上面默认选择去掉，避免在保存脚本时报错。

二： 在本地PC新建一个excel文件（例如： D:\testdate.xlsx）

按快捷键“ALT + F11”进入宏编辑，输入如下代码后保存。summary()为目标生成代码，SQL()为生成SQL脚本文件代码，按条件生成SQL的脚本如下：

Sub summary()

Dim i As Integer

i = 2

ThisWorkbook.Worksheets(1).Columns(2).Clear

For Each sh In ThisWorkbook.Worksheets

If sh.Name <>" " Then

ThisWorkbook.Worksheets(1).Cells(i, 2).Value = sh.Name

ThisWorkbook.Worksheets(1).Cells(i, 2).Select

ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _

sh.Name + "!A1", TextToDisplay:=sh.Name

i = i + 1

End If

Next sh

ThisWorkbook.Worksheets(1).Cells.Select

With Selection.Font

.Name = "目录"

.Size = 9

.Strikethrough = False

.Superscript. = False

.Subscript. = False

.OutlineFont = False

.Shadow = False

.TintAndShade = 0

.ThemeFont = xlThemeFontNone

End With

End Sub

Sub SQL()

Dim i As Integer

i = 1

Dim ADO_Stream As Object

Dim strSQL, strDelSQL As String

Dim strTblName As String

Dim col As Long

Dim row As Long

Dim str As String

Dim PK As String

Dim cnt As Integer

PK = "PK"

Dim rowcounts As Long

rowcounts = 0

Dim filecount As Long

filecount = 0

Set ADO_Stream = CreateObject("ADODB.Stream")

ADO_Stream.Type = 2

ADO_Stream.Mode = 3

ADO_Stream.Charset = "unicode"

ADO_Stream.Open

Dim checkType As String

For Each sh In ThisWorkbook.Worksheets

cnt = 0

If sh.Name <>" " And InStr(sh.Name, "template") = 0 Then

strTblName = sh.Cells(1, 2).Value

rowcounts = 1

'Insert SQL

row = 6

Do While sh.Cells(row, 1).Value <>""

strDelSQL = "delete from " + strTblName + " where "

strSQL = "Insert into " + strTblName + " ("

col = 1

Do While sh.Cells(3, col).Value <>""

If col <>1 Then

strSQL = strSQL + ", "

End If

strSQL = strSQL + sh.Cells(3, col).Value

col = col + 1

Loop

strSQL = strSQL + ") VALUES ("

col = 1

Do While sh.Cells(3, col).Value <>""

str = Trim(CStr(sh.Cells(row, col).Value))

If InStr(Trim(CStr(sh.Cells(2, col).Value)), PK) <>0 Then

If cnt >0 Then

strDelSQL = strDelSQL + " and "

End If

strDelSQL = strDelSQL + Trim(CStr(sh.Cells(3, col).Value)) + " = '" + str + "'"

cnt = cnt + 1

End If

If col <>1 Then

strSQL = strSQL + ", "

End If

If (InStr(Trim(CStr(sh.Cells(4, col).Value)), "Integer") = 0) And (InStr(Trim(CStr(sh.Cells(4, col).Value)), "Decimal") = 0) And ((InStr(Trim(CStr(sh.Cells(4, col).Value)), "DATE") = 0) Or _

((Len(str) >0) And (InStr(Trim(CStr(sh.Cells(4, col).Value)), "DATE") >0))) Then

If (Len(str) <= 0) And (InStr(Trim(CStr(sh.Cells(5, col).Value)), "No") = 0) Then

str = "NULL"

ElseIf InStr(Trim(CStr(sh.Cells(4, col).Value)), "DATE") >0 Then

str = "to_date('" + str + "','yyyy-mm-dd hh24:mi:ss')"

Else

str = "'" + str + "'"

End If

strSQL = strSQL + str

ElseIf (Len(str) <= 0) And (InStr(Trim(CStr(sh.Cells(4, col).Value)), "DATE") >0) Then

strSQL = strSQL + "NULL"

Else

If (Len(str) <= 0) And (InStr(Trim(CStr(sh.Cells(5, col).Value)), "No") = 0) Then

str = "NULL"

End If

strSQL = strSQL + str

End If

col = col + 1

Loop

strDelSQL = strDelSQL + "" + vbCrLf

ADO_Stream.WriteText strDelSQL

strSQL = strSQL + ")" + vbCrLf

ADO_Stream.WriteText strSQL

row = row + 1

Loop

End If

i = i + 1

rowcounts = 0

filecount = 0

Next sh

ADO_Stream.SaveToFile ThisWorkbook.Path &"\MstSQL(delete by condition).txt", 2

ADO_Stream.Close

Set ADO_Stream = Nothing

End Sub

点击“保存”宏脚本，主要的一步完成。

三： 在excel文件（test.xlsx）的首页创建两个图标，分别选择右键指定宏，一个指定上面的summary(),一个指定上面的SQL()。然后就可以在后续的sheet页创建自己需要生成SQL脚本的表结构名称了，记得每个sheet页面对应一个表结构及数据，使用方法如下：

A,将需要更新的对象表数据整个sheet拷进工具中，数据只保留需要更新的数据。

B,在第2行标出主键字段，填上“PK”即可。

看看代码

http://www.51testing.com/html/41/195041-831346.html

第一步：建立数据库和数据表（按照自己的Excel数据设立字段）。

[sql] view plain copy print?

CREATE DATABASE php_excel

USE php_excel

CREATE TABLE IF NOT EXISTS php_excel(

id int(20) NOT NULL AUTO_INCREMENT PRIMARY KEY,

gid varchar(20) NOT NULL,

stu_no varchar(20) NOT NULL,

name varchar(45) NOT NULL,

age int(4) NOT NULL

)ENGINE=MyISAM DEFAULT CHARSET=utf8

第二步：前台index.php文件。

[html] view plain copy print?

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">

<head>

<meta http-equiv="Content-Type" content="text/htmlcharset=utf-8" />

<title>phpexcel导入excel数据到MYSQL数据库</title>

</head>

<body>

<form name="frm1" action="insertdb.php" method="post" enctype="multipart/form-data">

<input name="filename" type="file" />

<input name="submit" type="submit" value="import" />

</form>

</body>

</html>

第三步：向数据库插入数据的insertdb.php文件。

[php] view plain copy print?

session_start()

header("Content-type:text/htmlcharset:utf-8")

//全局变量

$succ_result=0

$error_result=0

$file=$_FILES['filename']

$max_size="2000000"//最大文件限制（单位：byte）

$fname=$file['name']

$ftype=strtolower(substr(strrchr($fname,'.'),1))

//文件格式

$uploadfile=$file['tmp_name']

if($_SERVER['REQUEST_METHOD']=='POST'){

if(is_uploaded_file($uploadfile)){

if($file['size']>$max_size){

echo "Import file is too large"

exit

}

if($ftype!='xls'){

echo "Import file type is error"

exit

}

}else{

echo "The file is not empty!"

exit

}

}

require("./conn.php") //连接mysql数据库

//调用phpexcel类库

require_once 'PHPExcel.php'

require_once 'PHPExcel\IOFactory.php'

require_once 'PHPExcel\Reader\Excel5.php'

$objReader = PHPExcel_IOFactory::createReader('Excel5')//use excel2007 for 2007 format

$objPHPExcel = $objReader->load($uploadfile)

$sheet = $objPHPExcel->getSheet(0)

$highestRow = $sheet->getHighestRow()// 取得总行数

$highestColumn = $sheet->getHighestColumn()// 取得总列数

$arr_result=array()

$strs=array()

for($j=2$j<=$highestRow$j++)

{

unset($arr_result)

unset($strs)

for($k='A'$k<= $highestColumn$k++)

{

//读取单元格

$arr_result .= $objPHPExcel->getActiveSheet()->getCell("$k$j")->getValue().','

}

$strs=explode(",",$arr_result)

$sql="insert into php_excel(gid,stu_no,name,age) values ($strs[0],'$strs[1]','$strs[2]',$strs[3])"

echo $sql."<br/>"

mysql_query("set names utf8")

$result=mysql_query($sql) or die("执行错误")

$insert_num=mysql_affected_rows()

if($insert_num>0){

$succ_result+=1

}else{

$error_result+=1

}

}

echo "插入成功".$succ_result."条数据！！！<br>"

echo "插入失败".$error_result."条数据！！！"

其中conn.php代码如下：

[php] view plain copy print?

$mysql=mysql_connect("localhost","root","") or die("数据库连接失败！")

mysql_select_db("php_excel",$mysql)

mysql_query("set names utf8")

我的导入效果如下：

至此，从Excel文件读取数据批量导入到Mysql数据库完成。

---