SQL server导入数据Excel、Access、Csv

转载

mob604756e65292 2021-07-22 18:36:00

-----------------------------------------------------#导入EXCEL存储过程#--------------------------------------------------------

USE [ShiXiao]
GO
/****** Object: StoredProcedure [dbo].[usp_DaoRuExcel] Script Date: 2021-7-22-星期四 18:31:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[usp_DaoRuExcel]
@Url nvarchar(800),
@Path nvarchar(800)=0,
@Tb nvarchar(800),
@Sheet nvarchar(800),
@InsertSQL nvarchar(1000)=0
AS
BEGIN
SET NOCOUNT ON;
---#开启服务#---
exec sp_configure 'show advanced options',1
reconfigure
exec sp_configure 'Ad Hoc Distributed Queries',1
reconfigure

---#拼接Excel路径与传入参数#---
set @Path = '''Microsoft.ACE.OLEDB.12.0'',''Data Source=' + cast(@URL as varchar(800)) + ';Extended Properties=Excel 12.0'''
set @InsertSQL =N'SELECT * into ##'+@Tb+' FROM OpenDataSource('+@Path+')...['+@Sheet+'$]'
exec (@InsertSQL)

---#关闭服务#---
exec sp_configure 'Ad Hoc Distributed Queries',0
reconfigure
exec sp_configure 'show advanced options',0
reconfigure

END

-----------------------------------------------------#导入ACCESS存储过程#--------------------------------------------------------

USE [ShiXiao]
GO
/****** Object: StoredProcedure [dbo].[usp_DaoRuAccess] Script Date: 2021-7-22-星期四 18:31:03 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[usp_DaoRuAccess]
@Url_A nvarchar(800),
@Path_A nvarchar(800)=0,
@Tb_A nvarchar(800),
@Sheet_A nvarchar(800),
@InsertSQL_A nvarchar(1000)=0,
@ZhuanYun_1 nvarchar(800)=0,
@ZhuanYun_2 nvarchar(800)=0,
@ZhuanYun_3 nvarchar(800)=0
AS
BEGIN
SET NOCOUNT ON;
---#开启服务#---
exec sp_configure 'show advanced options',1
reconfigure
exec sp_configure 'Ad Hoc Distributed Queries',1
reconfigure

---#拼接Access路径与传入参数#---
set @Path_A = '''Microsoft.ACE.OLEDB.12.0'',''Data Source=' + cast(@URL_A as varchar(800)) + ';User ID=Admin;Password='''
set @InsertSQL_A =N'SELECT * into ##'+@Tb_A+' FROM OpenDataSource('+@Path_A+')...['+@Sheet_A+']WHERE 始发转运='+@ZhuanYun_1+' or 始发转运='+@ZhuanYun_2+' or 始发转运='+@ZhuanYun_3
exec (@InsertSQL_A)

---#关闭服务#---
exec sp_configure 'Ad Hoc Distributed Queries',0
reconfigure
exec sp_configure 'show advanced options',0
reconfigure

END

-----------------------------------------------------#导入CSV数据#--------------------------------------------------------

bulk insert DaoRuCSV
from 'D:\SQL\1.csv' ----时效群文件路径与文件名
with
(
codepage='65001',
DATAFILETYPE='char',
fieldterminator=',',
rowterminator='0x0A',
FIRSTROW=2)

-----------------------------------------------------#导入ACCESS传参#--------------------------------------------------------

DROP TABLE ##SFDJCB_A                                           ---先删除全局临时表内容
exec [ShiXiao].[dbo].[usp_DaoRuAccess]                           ---调用存储过程导入Excel数据
@Url_A=N'D:\SQL\2021071902.accdb',           ---传入Excel文件路径给存储过程
@Tb_A=N'SFDJCB_A',                                      ---存储过程中全局临时表名称
@Sheet_A=N'基础资料',
@ZhuanYun_1=N'''沈阳''',
@ZhuanYun_2=N'''盘锦''',
@ZhuanYun_3=N'''大连'''

-----------------------------------------------------#导入EXCEL传参#--------------------------------------------------------

DROP TABLE ##JJBZD                                   ---先删除全局临时表内容
exec [dbo].[usp_DaoRuExcel]                           ---调用存储过程导入Excel数据
@Url=N'd:\sql\0718.xlsx',         ---传入Excel文件路径给存储过程
@Tb =N'JJBZD',                                     ---存储过程中全局临时表名称
@Sheet=N'明细'