怎么把一个300万以上的CSV文件导入到SQL数据库表中

zhaowei303 2013-10-10 10:09:13

CSV文件的内容是以逗号来分隔，然后有6个字段大概是
aa,bb,cc,dd,ee,ff
42222222223432432432,32432432432432432432,2332432432,32432432432,32432432,23432432
42222222223432432432,32432432432432432432,2332432432,32432432432,32432432,23432432
42222222223432432432,32432432432432432432,2332432432,32432432432,32432432,23432432
42222222223432432432,32432432432432432432,2332432432,32432432432,32432432,23432432
这样的数据。
我通过bcp命令导进去
create table xxdd
(
aa nvarchar(1000)
)
bcp sqlwygl..xxdd in D:\datatest\result.csv -c -S(local) -Usa -P123
这样可以导进去只一列以逗号分隔，如果导成多列的话创建表多个字段，就导不进去。

我想问一下有没有办法直接导csv的文件，如果是转成EXCEL的文件的话就算了，光是打开这个文件都会很慢的。

...全文

502 14 打赏收藏转发到动态举报

写回复

用AI写文章

14 条回复

切换为时间正序

请发表友善的回复…

发表回复

LongRui888 2013-10-11

打赏
举报

引用 13 楼 zhaowei303 的回复:

select * from openrowset(bulk 'c:\wc.csv', --要读取的文件路径和名称 formatfile='c:\wc.fmt', --格式化文件的路径和名称 firstrow = 2, --要载入的第一行,由于第一行是标题,所以从2开始 --lastrow = 1000, --要载入的最后一行,此值必须大于firstrow maxerrors = 10, --在加载失败之前加载操作中最大的错误数 --errorfile ='c:\wc_error1.txt', --存放错误的文件 rows_per_batch = 10000 --每个批处理导入的行数 ) as t 我怎么提示服务器: 消息 156，级别 15，状态 1，行 3 在关键字 'bulk' 附近有语法错误。我的数据库是sql2000

好像这个openrowset(bulk是2005后才引入的，sql server 2000不支持。修改成这样试试：


bulk insert xxdd
from 'c:\wc.csv'
with  
(
  FIRSTROW = 2,
  FORMATFILE = 'c:\wc.fmt',
  KILOBYTES_PER_BATCH=5000,
  batchsize=1000 )

zhaowei303 2013-10-11

打赏
举报

select * from openrowset(bulk 'c:\wc.csv', --要读取的文件路径和名称 formatfile='c:\wc.fmt', --格式化文件的路径和名称 firstrow = 2, --要载入的第一行,由于第一行是标题,所以从2开始 --lastrow = 1000, --要载入的最后一行,此值必须大于firstrow maxerrors = 10, --在加载失败之前加载操作中最大的错误数 --errorfile ='c:\wc_error1.txt', --存放错误的文件 rows_per_batch = 10000 --每个批处理导入的行数 ) as t 我怎么提示服务器: 消息 156，级别 15，状态 1，行 3 在关键字 'bulk' 附近有语法错误。我的数据库是sql2000

唐诗三百首 2013-10-10

打赏
举报

引用 5 楼 zhaowei303 的回复:

[quote=引用 1 楼 ap0405140 的回复:] 用bulk insert,


bulk insert [目标表]
from '[csv文件]'
with  
( FIELDTERMINATOR =',',  --> 指定分隔符
  ROWTERMINATOR ='\n',
  KILOBYTES_PER_BATCH=5000 )

数据太大了好像不行，有300多万条记录。180多M。服务器: 消息 4832，级别 16，状态 1，行 1 大容量插入: 在数据文件中遇到意外的文件结尾 (EOF)。服务器: 消息 7399，级别 16，状态 1，行 1 OLE DB 提供程序 'STREAM' 报错。提供程序未给出有关错误的任何信息。语句已终止。 [/quote] --> 加batchsize参数试试, 再不行,检查一下文件格式是否正确.


bulk insert [目标表]
from '[csv文件]'
with  
( FIELDTERMINATOR =',',  --> 指定分隔符
  ROWTERMINATOR ='\n',
  KILOBYTES_PER_BATCH=5000,
  batchsize=5000 )

zhaowei303 2013-10-10

打赏
举报

我看了有些文件里有一个特殊符号，在DOS下边不能替换掉findstr /v/b "" Result.csv > Result.temp

zhaowei303 2013-10-10

打赏
举报

引用 1 楼 ap0405140 的回复:

用bulk insert,


bulk insert [目标表]
from '[csv文件]'
with  
( FIELDTERMINATOR =',',  --> 指定分隔符
  ROWTERMINATOR ='\n',
  KILOBYTES_PER_BATCH=5000 )

数据太大了好像不行，有300多万条记录。180多M。服务器: 消息 4832，级别 16，状态 1，行 1 大容量插入: 在数据文件中遇到意外的文件结尾 (EOF)。服务器: 消息 7399，级别 16，状态 1，行 1 OLE DB 提供程序 'STREAM' 报错。提供程序未给出有关错误的任何信息。语句已终止。

奔四在望 2013-10-10

打赏
举报

用自带的任务里面的导入数据选择平面文件源导入即可

Rotel-刘志东 2013-10-10

打赏
举报

用导入导出工具即可。

發糞塗牆 2013-10-10

打赏
举报

用自带的导入导出工具更方便

唐诗三百首 2013-10-10

打赏
举报

用bulk insert,


bulk insert [目标表]
from '[csv文件]'
with  
( FIELDTERMINATOR =',',  --> 指定分隔符
  ROWTERMINATOR ='\n',
  KILOBYTES_PER_BATCH=5000 )

LongRui888 2013-10-10

打赏
举报

我按照你的那个xxdd表，把上面的试验，重新做了一遍，完全可以导入数据的，你可以看看：



--修改高级参数
sp_configure 'show advanced options',1
go

--允许即席分布式查询
sp_configure 'Ad Hoc Distributed Queries',1
go

--如果配置的值不在合理范围（在最小值最大值范围内），那么可以强制覆盖
reconfigure with override  
go

sp_configure 'xp_cmdshell',1
go
reconfigure
go


--创建数据库
create database wc 
go


use wc
go

--建表
create table xxdd
(
aa nvarchar(1000),
bb nvarchar(1000),
cc nvarchar(1000),
dd nvarchar(1000),
ee nvarchar(1000),
ff nvarchar(1000)
)
go


/* 这里建立一个c:\wc.csv  文件,内容如下：
aa,bb,cc,dd,ee,ff
42222222223432432432,32432432432432432432,2332432432,32432432432,32432432,23432432
42222222223432432432,32432432432432432432,2332432432,32432432432,32432432,23432432
42222222223432432432,32432432432432432432,2332432432,32432432432,32432432,23432432
42222222223432432432,32432432432432432432,2332432432,32432432432,32432432,23432432

*/

--导出格式文件，这个是关键，数据库名称，表名称，用户名和密码，服务器ip和端口
--都改成你自己的
exec xp_cmdshell 'bcp wc.dbo.xxdd format nul -t "," -f c:\wc.fmt -c -Usa -Pyupeigu -S 192.168.1.106,1433'
go


--先查看要导入的数据
select *
from 
openrowset(bulk 'c:\wc.csv',             --要读取的文件路径和名称 
                formatfile='c:\wc.fmt',  --格式化文件的路径和名称
                
                firstrow = 2,            --要载入的第一行,由于第一行是标题,所以从2开始
                --lastrow  = 1000,       --要载入的最后一行,此值必须大于firstrow
                
                maxerrors = 10,          --在加载失败之前加载操作中最大的错误数
                --errorfile ='c:\wc_error1.txt', --存放错误的文件
                
                rows_per_batch = 10000                    --每个批处理导入的行数
          ) as t 

/*
aa	bb	cc	dd	ee	ff
42222222223432432432	32432432432432432432	2332432432	32432432432	32432432	23432432
42222222223432432432	32432432432432432432	2332432432	32432432432	32432432	23432432
42222222223432432432	32432432432432432432	2332432432	32432432432	32432432	23432432
42222222223432432432	32432432432432432432	2332432432	32432432432	32432432	23432432
*/

--最后可以 insert into 表 (列)  select * from openrowset...插入数据即可
insert into xxdd (aa,bb,cc,dd,ee,ff)
select *
from 
openrowset(bulk 'c:\wc.csv',             --要读取的文件路径和名称 
                formatfile='c:\wc.fmt',  --格式化文件的路径和名称
                
                firstrow = 2,            --要载入的第一行,由于第一行是标题,所以从2开始
                --lastrow  = 1000,       --要载入的最后一行,此值必须大于firstrow
                
                maxerrors = 10,          --在加载失败之前加载操作中最大的错误数
                --errorfile ='c:\wc_error1.txt', --存放错误的文件
                
                rows_per_batch = 10000                    --每个批处理导入的行数
          ) as t 


select *
from xxdd

LongRui888 2013-10-10

打赏
举报

下面，做个试验，你可以参考一下的哈，其中最关键的是格式文件：

--修改高级参数
sp_configure 'show advanced options',1
go

--允许即席分布式查询
sp_configure 'Ad Hoc Distributed Queries',1
go

--如果配置的值不在合理范围（在最小值最大值范围内），那么可以强制覆盖
reconfigure with override  
go

sp_configure 'xp_cmdshell',1
go
reconfigure
go


--创建数据库
create database wc 
go


use wc
go

--建表
create table wc(a int,b int,c int)
go


/* 这里建立一个c:\wc.csv  文件,内容如下：
a,b,c
1,2,3
2,3,4
*/

--导出格式文件，这个是关键，数据库名称，表名称，用户名和密码，服务器ip和端口
--都改成你自己的
exec xp_cmdshell 'bcp wc.dbo.wc format nul -t "," -f c:\wc.fmt -c -Usa -Pyupeigu -S 192.168.1.106,1433'
go


--先查看要导入的数据
select *
from 
openrowset(bulk 'c:\wc.csv',             --要读取的文件路径和名称 
                formatfile='c:\wc.fmt',  --格式化文件的路径和名称
                
                firstrow = 2,            --要载入的第一行,由于第一行是标题,所以从2开始
                --lastrow  = 1000,       --要载入的最后一行,此值必须大于firstrow
                
                maxerrors = 10,          --在加载失败之前加载操作中最大的错误数
                errorfile ='c:\wc_error.txt', --存放错误的文件
                
                rows_per_batch = 10000                    --每个批处理导入的行数
          ) as t 

/*
a	b	c
1	2	3
2	3	4

--最后可以 insert into 表 (列)  select * from openrowset...插入数据即可

PB菜鸟 2013-10-10

打赏
举报

sql 自带的导入工具不行吗？自带的工具使用的导入方法应该是导入的最优方案吧。

zhaowei303 2013-10-10

打赏
举报

如果用BCP的话应该怎么弄呢，感觉那个更快。

zhaowei303 2013-10-10

打赏
举报

引用 7 楼 ap0405140 的回复:

[quote=引用 5 楼 zhaowei303 的回复:] [quote=引用 1 楼 ap0405140 的回复:] 用bulk insert,
bulk insert [目标表]
from '[csv文件]'
with  
( FIELDTERMINATOR =',',  --> 指定分隔符
  ROWTERMINATOR ='\n',
  KILOBYTES_PER_BATCH=5000 )


bulk insert [目标表]
from '[csv文件]'
with  
( FIELDTERMINATOR =',',  --> 指定分隔符
  ROWTERMINATOR ='\n',
  KILOBYTES_PER_BATCH=5000,
  batchsize=5000 )

[/quote] 多谢，我自已解决了，这样的话只是每次传输不会回滚，但是还是要报错误。我最终是把合并的那个文件的特殊字符去掉就解决了。

数据量达到几亿的大批量CSV文件，快速同步至数据库中方案

ExcelToSQLServer：批量导入Excel文件到SQL Server数据库的自动化工具

将几百万条数据导入到数据库中，怎么样高效率的导入？下面我就介绍一个高效率的方法： 1、将数据库文件(DB.csv)导入到DataTable中： /// <summary> /// 将CSV文件的数据读取到DataTable中 /// </summary> /// <param name="fileName"...

作为数据分析师的角色，数据库的作用是帮助存储数据和需要时可以导出所需数据的用途，这个用途在数据量一大时，不采用数据库方案是没有办法做到一个完美效果的，所以就算不深入了解数据库其他功能，单单数据导入导出功能，对一名合格的数据分析师来说，是必备的本领。鉴于笔者所接触到的读者群体偏向Excel用户的业务导向的群体，此篇可能部分内容对熟悉数据库的群体过于简单，同时为了完整性的方案介绍，某些方法对Excel...

PHP 导入导出excel、csv百万数据到数据库 待解决: wamp下导入导出百万数据没有问题 lamp下导入10W条数据没问题，导入50W及以上会出现nginx504报错代码包地址测试数据表地址表中有大概110W+条数据，下载时可能会慢一些下载完成后，导入到数据库中，然后用Navicat修改表为MYISAM类型，把触发器删除掉即可环境 WAMP和LAMP PHP版本: 7.3 目...

疑难问题

22,300

社区成员

121,734

社区内容

发帖

与我相关

我的任务

社区管理员

加入社区

近7日
近30日
至今

加载中

查看更多榜单

社区公告

暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章