frank110 :
我有一批数据，大约几百个.xls、.csv文件,相批量导入数据库。
数据库内有：name、sex、age、birth、education、positionname、marrystatus、areaname、income、TelNo、mobileno、email这些字段。
文件字段是：姓名、性别、年龄、出生日期、学历、职位、婚姻状况、地区、月收入、电话、手机、EMAIL
现在的问题是：1.每个文件内的字段并不全，例如：01文件内只有姓名、性别、年龄、出生日期、学历。02文件内只有姓名、性别、年龄、出生日期、职位、婚姻状况、地区、月收入
2.如何把这些文件批量导入到数据库中，并且自动对应到相应的字段。
navy887 :
给你参考一下
-
SQL code
--
excel数据导入SQL SERVER
insert Test(col1,col2,col3)
Select col1,col2,col3
From
OpenRowSet ( ' MICROSOFT.JET.OLEDB.4.0 ' , ' EXCEL 8.0;HDR=YES;IMEX=2;DataBase=c:/test.xls ' , [ sheet1$ ] )
SQL code
-- 给你一串代码 SQL SERVER 和EXCEL的数据导入导出 1 、在SQL SERVER里查询Excel数据: -- ====================================================== SELECT * FROM OpenDataSource ( ' Microsoft.Jet.OLEDB.4.0 ' , ' Data Source="c:/book1.xls";User ID=Admin;Password=;Extended properties=Excel 5.0 ' )... [ Sheet1$ ] 下面是个查询的示例，它通过用于 Jet 的 OLE DB 提供程序查询 Excel 电子表格。 SELECT * FROM OpenDataSource ( ' Microsoft.Jet.OLEDB.4.0 ' , ' Data Source="c:/Finance/account.xls";User ID=Admin;Password=;Extended properties=Excel 5.0 ' )...xactions -- ----------------------------------------------------------------------------------------------- 2 、将Excel的数据导入SQL server : -- ====================================================== SELECT * into newtable FROM OpenDataSource ( ' Microsoft.Jet.OLEDB.4.0 ' , ' Data Source="c:/book1.xls";User ID=Admin;Password=;Extended properties=Excel 5.0 ' )... [ Sheet1$ ] 实例: SELECT * into newtable FROM OpenDataSource ( ' Microsoft.Jet.OLEDB.4.0 ' , ' Data Source="c:/Finance/account.xls";User ID=Admin;Password=;Extended properties=Excel 5.0 ' )...xactions -- ----------------------------------------------------------------------------------------------- 3 、将SQL SERVER中查询到的数据导成一个Excel文件 -- ====================================================== T - SQL代码： EXEC master..xp_cmdshell ' bcp 库名.dbo.表名out c:/Temp.xls -c -q -S"servername" -U"sa" -P"" ' 参数：S 是SQL服务器名；U是用户；P是密码 说明：还可以导出文本文件等多种格式 实例: EXEC master..xp_cmdshell ' bcp saletesttmp.dbo.CusAccount out c:/temp1.xls -c -q -S"pmserver" -U"sa" -P"sa" ' EXEC master..xp_cmdshell ' bcp "SELECT au_fname, au_lname FROM pubs..authors ORDER BY au_lname"
queryout C:/ authors.xls -c -Sservername -Usa -Ppassword ' 在VB6中应用ADO导出EXCEL文件代码： Dim cn As New ADODB.Connection cn. open "Driver = {SQL Server};Server = WEBSVR; DataBase = WebMis;UID = sa;WD = 123 ;" cn. execute "master..xp_cmdshell ' bcp "SELECT col1, col2 FROM 库名.dbo.表名" queryout E:/DT.xls -c -Sservername
-Usa -Ppassword ' " -- ---------------------------------------------------------------------------------------------- 4 、在SQL SERVER里往Excel插入数据: -- ====================================================== insert into OpenDataSource ( ' Microsoft.Jet.OLEDB.4.0 ' , ' Data Source="c:/Temp.xls";User ID=Admin;Password=;Extended properties=Excel 5.0 ' )...table1 (A1,A2,A3) values
( 1 , 2 , 3 ) T - SQL代码： INSERT INTO OPENDATASOURCE ( ' Microsoft.JET.OLEDB.4.0 ' , ' Extended Properties=Excel 8.0;Data source=C:/training/inventur.xls ' )... [ Filiale1$ ] (bestand, produkt) VALUES ( 20 , ' Test ' )
navy887 :
--将某个目录上的Excel表,导入到数据库中
--将所有的Excel文件放到一个目录中,假设为c:/test/,然后用下面的方法来做
create table #t(fname varchar(260),depth int,isf bit)
insert into #t exec master..xp_dirtree 'c:/test',1,1
declare tb cursor for select fn='c:/test'+fname from #t
where isf=1 and fname like '%.xls' --取.xls文件(EXCEL)
declare @fn varchar(8000)
open tb
fetch next from tb into @fn
while @@fetch_status=0
begin
--下面是查询语句,需要根据你的情况改为插入语句
--插入已有的表用:insert into 表 selct * from ...
--创建表用:select * into 表 from ...
set @fn='select * from
OPENROWSET(''MICROSOFT.JET.OLEDB.4.0'',''Excel 5.0;HDR=YES;DATABASE='+@fn+''',全部客户$)'
exec(@fn)
fetch next from tb into @fn
end
close tb
deallocate tb
drop table #t
访问excel
1)、inert into A SELECT * FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0','Data Source="E:/联系方式.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')... [Sheet1$]
但此时查询结果得顺序是按照列名得顺序排列的，而不是按照电子表格中原有的顺序。这是 SQL Server 2000 行集函数 OpenRowSet 和 OpenDataSource 本身的问题，与访问接口引擎无关，也与 Excel 版本无关。SQL Server 2005 的 OpenRowSet 和 OpenDataSource 不存在这个问题。
2)、inert into A select * from OpenRowSet('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;HDR=Yes;Database=E:/联系方式.xls', 'select * from [Sheet1$]')
此种方法可以解决opendatasurce得问题，即查询结果列得顺序与电子表格中原有顺序一致
3)、使用链接服务器：
exec sp_addlinkedserver @server='xlsserver',@srvproduct='jet4.0',@provider='microsoft.jet.oledb.4.0',@datasrc='E:/联系方式.xls',@provstr='excel 8.0'
inert into A select * from xlsserver...[Sheet1$]
此时查询结果中列得顺序是按照列名得顺序排列的。
htl258 :
SQL code
要把文本数据导入到数据库，本文只有3个字段，数据库表有7个字段， 怎么把文本字段的对应到表的字段，如何用bulk insert来实现? 数据库表 userinfo id identity ，userName，pass，address，phone，email，registerTime 文本格式是 userName，address，phone hua，湖南， 5971898 -- SQL2005处理方式: 先在G盘存放一个格式化文件 G:/format.xml XML code : < ?xml version = " 1.0 "? > < BCPFORMAT xmlns = "http: // schemas.microsoft.com / sqlserver / 2004 / bulkload / format" xmlns:xsi = "http: // www.w3.org / 2001 / XMLSchema - instance" > < RECORD > < FIELD ID = " 1 " xsi:type = "CharTerm" TERMINATOR = "," MAX_LENGTH = " 20 " COLLATION = "Chinese_PRC_CI_AS" /> < FIELD ID = " 2 " xsi:type = "CharTerm" TERMINATOR = "," MAX_LENGTH = " 100 " COLLATION = "Chinese_PRC_CI_AS" /> < FIELD ID = " 3 " xsi:type = "CharTerm" TERMINATOR = "/r/n" MAX_LENGTH = " 20 " COLLATION = "Chinese_PRC_CI_AS" /> </ RECORD > < ROW > < COLUMN SOURCE = " 1 " NAME = "userName" xsi:type = "SQLVARYCHAR" /> < COLUMN SOURCE = " 2 " NAME = "address" xsi:type = "SQLVARYCHAR" /> < COLUMN SOURCE = " 3 " NAME = "phone" xsi:type = "SQLVARYCHAR" /> </ ROW > </ BCPFORMAT > SQL code : G:/test.txt userName,address,phone hua,湖南, 5971898 -- SQL SERVER -- 创建表 CREATE TABLE userinfo(id INT identity ,userName varchar ( 20 ), pass varchar ( 20 ),address varchar ( 100 ),phone varchar ( 20 ), email varchar ( 128 ),registerTime datetime ) -- 导入 INSERT INTO userinfo(userName,address,phone) SELECT * FROM OPENROWSET ( BULK ' G:/test.txt ' ,FORMATFILE = ' G:/format.xml ' ,FIRSTROW = 2 ) AS T; -- 查看数据 SELECT * FROM userinfo; /* id userName pass address phone email registerTime ----------- --------- ---------- ----------- ---------- ---------- ------------- 1 hua NULL 湖南 5971898 NULL NULL (1 行受影响) */-- SQL2000处理方式:先在G盘存放一个格式化文件G:/format.fmt FMT code : 8.0 3 1 SQLCHAR 0 20 "," 2 userName Chinese_PRC_CI_AS 2 SQLCHAR 0 100 "," 4 address Chinese_PRC_CI_AS 3 SQLCHAR 0 20 "/r/n" 5 phone "" SQL code G:/test.txt userName,address,phone hua,湖南, 5971898 -- SQL SERVER -- 建表 CREATE TABLE userinfo(id int identity ,userName varchar ( 20 ), pass varchar ( 20 ),address varchar ( 100 ),phone varchar ( 20 ), email varchar ( 128 ),registerTime datetime ) -- 导入 BULK INSERT userinfo FROM ' G:/test.txt ' WITH ( FORMATFILE = ' G:/format.fmt ' , FIRSTROW = 2 ) -- 查看数据 SELECT * FROM userinfo; /* id userName pass address phone email registerTime ----------- --------- ---------- ----------- ---------- ---------- ------------- 1 hua NULL 湖南 5971898 NULL NULL (1 行受影响) */-- ----------------------------------------------------------------------------------------------- 8.0 -- 这个8,表示版本是8.0 3 -- 这个3.表示数据文件中有几列. 第一列的1, 2 ,3表示数据文件的列的序号 而后面的第六列的2, 4 ,5表示userName,Address,phone在表中.是第几列 1 SQLCHAR 0 20 "," 2 userName Chinese_PRC_CI_AS 2 SQLCHAR 0 100 "," 4 address Chinese_PRC_CI_AS 3 SQLCHAR 0 20 "/r/n" 5 phone "" -- ------------------------------------------------------------------------------------------------ 原贴地址: http: // topic.csdn.net / u / 20090913 / 15 / fa2e7e65 - 73d8 - 4b64 - b6e0 - bd583f564d86.html? 95717
扫一扫
2170
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
