Office中国论坛/Access中国论坛
标题:
批量导入(bulk insert)csv文件到SQL server
[打印本页]
作者:
roych
时间:
2020-7-4 16:27
标题:
批量导入(bulk insert)csv文件到SQL server
前不久在某个论坛(ExcelHome)上有人问,能否批量导入数据到SQL server?虽然之前也有数据同步的情况,但需要导入文件的情况极少,所以也就手动按向导完成罢了。
这么一问,倒是把我问到了。终于今天得闲,就抽空去微软官网看看。导入大概有三种方式。一种是bcp,一种是bulk insert,另一种则是openrowset。
bcp只能对最简单的格式进行导入,不能指定表头(或许我没找到这方面的例子吧)。而openrowset则通常需要格式化文件(fmt)或者指定数据库链接引擎(例如,access)等参数,对于文本文件,bulk insert似乎更简单一些。所以这里只谈谈bulk insert。
按惯例,先上代码。
CREATE PROC [dbo].[import_data] @start_date date='2020-07-01', @end_date date='2020-07-31'
AS
BEGIN
DECLARE @file_name varchar(100)--文件名
DECLARE @day_num int--天数
DECLARE @date date--日期变量
DECLARE @i int--序号变量
DECLARE @tmp_table table(is_file_exists int,is_path int,is_parent_exists int)--文件信息变量表,字段1:是否存在文件。字段2:是否文件夹,字段3:父级路径是否存在
DECLARE @str_sql varchar(500)--SQL语句拼接
--初始化起止日期天数,用于确定循环上限
SET @day_num=DATEDIFF(day,@start_date,@end_date)
--初始化日期变量。
SET @date=@start_date
SET @i=0
WHILE @i<=@day_num
BEGIN
SET @file_name='E:\AppData\Local\'+convert(varchar(8),@date,112)+'.csv'
INSERT INTO @tmp_table EXEC [MASTER]..XP_FILEEXIST @file_name --把文件信息插入文件信息变量表
IF EXISTS(SELECT * FROM @tmp_table WHERE is_file_exists=1) --如果文件存在,则拼接SQL语句
SET @str_sql= 'BULK INSERT v_test FROM '''+@file_name
+''' WITH (FIRSTROW=2,FIELDTERMINATOR='','')'
ELSE --否则就跳出循环
BREAK
delete from test where import_date=@date --如果已经导入,则删除数据
EXEC(@str_sql) --导入数据
update test set import_date=@date where import_date is null --添加导入日期
DELETE FROM @tmp_table --删除文件信息变量表。注意,这里可不能truncate。
SET @i=@i+1 --循环累计
SET @date=DATEADD(day,@i,@start_date) --重设日期变量
END
END
复制代码
先说下基本格式:
BULK INSERT 表名(或视图名)'C:\测试.csv' WITH (FIRSTROW=2,FIELDTERMINATOR=',')
说明:
FIRSTROW=2
:可选(默认情况下从第一行导入)。这里表示从第二行导入。
FIELDTERMINATOR=','
:必选,表示csv分割符为逗号。SQL server 2017版本【即版本号为14以上】以后可以用
FORMAT='CSV'
代替这一句。
版本号查看请参考下图:
[attach]63815[/attach]
附件如下:
[attach]63816[/attach]
作者:
roych
时间:
2020-7-4 16:27
其实存储过程里的注释已经写得很清楚了。不过我还是想讲下思路,便于大家理解。
在讲解之前,我们先做这么一个假设。
假定这批CSV文件都是以日期命名的,当然,你也可以加上前缀,例如,“被割韭菜20200701.csv”,不过,不建议具体到时分秒,更不建议加后缀名,这样处理难度比较大。
接下来我们大体画个流程图(看不清流程图的话,请自行下载附件[attach]63824[/attach]):
[attach]63822[/attach]大体需要做3个判断。
判断日期是否达到。这个作为条件来终止循环。
判断是否存在文件。这个用来防止导入出错。
判断是否已经导入。这个用来防止重复导入。
解决方案:
我们需要定义一个日期变量(@date),它将从开始日期,每次循环累加一天,直至达到终止日期。由此,需要衍生出一个计数变量(@i)。是否设置变量来计算起止时期差呢?见仁见智。我这里是设置了的。如果不设置,也可以直接用datediff函数处理。
这道题超纲了哈。命令
EXEC [MASTER]..XP_FILEEXIST "文件路径"
,将返回是否存在某文件的信息。为了便于处理,我们需要创建一个表变量来存储它。因此需要定义一个文件路径的字符串变量(@file_name),以及一个表变量(@tmp_table)。
如何判断重复导入?自然是在导入时添加一个日期了,因此,除了数据源所需字段之外,目标表还需要建立导入日期字段(import_date)。这样的话,流程上先删除当日已导入的数据,然后插入当日数据,再添加当日日期。就不至于多次导入重复数据了。
由于导入的文件名是变量,我们无法直接执行bulk insert,需要拼接好SQL语句再执行,因此这里需要一个拼接SQL的字符串变量(@str_sql)
综上,我们需要定义的变量有:
@date(日期变量)、@i(计数变量)、@day_num(起止时间段天数)
@file_name(文件路径变量)、@tmp_table(文件信息表变量)
@str_sql(SQL语句拼接变量)
需要添加一个字段(import_date)
添加完毕后,就是写存储过程了。大家可以看上面的帖子,注释应该算比较详尽的,就不一一解析了。
如果还有什么问题,也可以在后面的帖子上评论或回复。
作者:
admin
时间:
2020-7-4 17:56
赞一个
作者:
Excel3000
时间:
2021-1-6 10:15
牛,感谢分享,大数据导入很实用,节省不少时间
作者:
worryd1
时间:
2023-11-6 20:36
这个应该非常有用,先收藏一下
欢迎光临 Office中国论坛/Access中国论坛 (http://www.office-cn.net/)
Powered by Discuz! X3.3