设为首页收藏本站Access中国

Office中国论坛/Access中国论坛

 找回密码
 注册

QQ登录

只需一步,快速开始

返回列表 发新帖
查看: 38688|回复: 4
打印 上一主题 下一主题

批量导入(bulk insert)csv文件到SQL server

[复制链接]
跳转到指定楼层
1#
发表于 2020-7-4 16:27:22 | 只看该作者 回帖奖励 |倒序浏览 |阅读模式
前不久在某个论坛(ExcelHome)上有人问,能否批量导入数据到SQL server?虽然之前也有数据同步的情况,但需要导入文件的情况极少,所以也就手动按向导完成罢了。
这么一问,倒是把我问到了。终于今天得闲,就抽空去微软官网看看。导入大概有三种方式。一种是bcp,一种是bulk insert,另一种则是openrowset。

bcp只能对最简单的格式进行导入,不能指定表头(或许我没找到这方面的例子吧)。而openrowset则通常需要格式化文件(fmt)或者指定数据库链接引擎(例如,access)等参数,对于文本文件,bulk insert似乎更简单一些。所以这里只谈谈bulk insert。
按惯例,先上代码。
  1. CREATE PROC [dbo].[import_data] @start_date date='2020-07-01', @end_date date='2020-07-31'
  2. AS
  3. BEGIN
  4.         DECLARE @file_name varchar(100)--文件名
  5.         DECLARE @day_num int--天数
  6.         DECLARE @date date--日期变量
  7.         DECLARE @i int--序号变量
  8.         DECLARE @tmp_table table(is_file_exists int,is_path int,is_parent_exists int)--文件信息变量表,字段1:是否存在文件。字段2:是否文件夹,字段3:父级路径是否存在
  9.         DECLARE @str_sql varchar(500)--SQL语句拼接
  10.         --初始化起止日期天数,用于确定循环上限
  11.         SET @day_num=DATEDIFF(day,@start_date,@end_date)
  12.         --初始化日期变量。
  13.         SET @date=@start_date
  14.         SET @i=0

  15.         WHILE @i<=@day_num

  16.         BEGIN
  17.                 SET @file_name='E:\AppData\Local\'+convert(varchar(8),@date,112)+'.csv'
  18.                 INSERT INTO @tmp_table EXEC [MASTER]..XP_FILEEXIST @file_name --把文件信息插入文件信息变量表

  19.                 IF EXISTS(SELECT * FROM @tmp_table WHERE is_file_exists=1) --如果文件存在,则拼接SQL语句
  20.                         SET @str_sql= 'BULK INSERT v_test FROM '''+@file_name
  21.                                                         +''' WITH (FIRSTROW=2,FIELDTERMINATOR='','')'
  22.                 ELSE --否则就跳出循环
  23.                         BREAK
  24.                
  25.                 delete from test where import_date=@date --如果已经导入,则删除数据
  26.                 EXEC(@str_sql) --导入数据
  27.                 update test set import_date=@date where import_date is null --添加导入日期
  28.                 DELETE FROM @tmp_table --删除文件信息变量表。注意,这里可不能truncate。
  29.                 SET @i=@i+1 --循环累计
  30.                 SET @date=DATEADD(day,@i,@start_date)  --重设日期变量
  31.         END

  32. END
复制代码


先说下基本格式:
BULK INSERT 表名(或视图名)'C:\测试.csv' WITH (FIRSTROW=2,FIELDTERMINATOR=',')
说明:
FIRSTROW=2:可选(默认情况下从第一行导入)。这里表示从第二行导入。
FIELDTERMINATOR=',':必选,表示csv分割符为逗号。SQL server 2017版本【即版本号为14以上】以后可以用FORMAT='CSV'代替这一句。

版本号查看请参考下图:

附件如下:




本帖子中包含更多资源

您需要 登录 才可以下载或查看,没有帐号?注册

x

评分

参与人数 1经验 +30 收起 理由
admin + 30 (其它)优秀教程、原创内容、以资鼓励、其.

查看全部评分

分享到:  QQ好友和群QQ好友和群 QQ空间QQ空间 腾讯微博腾讯微博 腾讯朋友腾讯朋友
收藏收藏 分享分享 分享淘帖 订阅订阅
2#
 楼主| 发表于 2020-7-4 16:27:37 | 只看该作者
其实存储过程里的注释已经写得很清楚了。不过我还是想讲下思路,便于大家理解。
在讲解之前,我们先做这么一个假设。
假定这批CSV文件都是以日期命名的,当然,你也可以加上前缀,例如,“被割韭菜20200701.csv”,不过,不建议具体到时分秒,更不建议加后缀名,这样处理难度比较大。
接下来我们大体画个流程图(看不清流程图的话,请自行下载附件):
大体需要做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)

添加完毕后,就是写存储过程了。大家可以看上面的帖子,注释应该算比较详尽的,就不一一解析了。
如果还有什么问题,也可以在后面的帖子上评论或回复。

本帖子中包含更多资源

您需要 登录 才可以下载或查看,没有帐号?注册

x

点击这里给我发消息

3#
发表于 2020-7-4 17:56:05 | 只看该作者
赞一个
回复

使用道具 举报

4#
发表于 2021-1-6 10:15:28 | 只看该作者
牛,感谢分享,大数据导入很实用,节省不少时间
5#
发表于 2023-11-6 20:36:59 | 只看该作者
这个应该非常有用,先收藏一下
您需要登录后才可以回帖 登录 | 注册

本版积分规则

QQ|站长邮箱|小黑屋|手机版|Office中国/Access中国 ( 粤ICP备10043721号-1 )  

GMT+8, 2024-12-24 00:12 , Processed in 0.077734 second(s), 31 queries .

Powered by Discuz! X3.3

© 2001-2017 Comsenz Inc.

快速回复 返回顶部 返回列表