bcp ,Bulk insert 等导入文本文件不等列该如何处理

我想导入以下格式的数据，但数据格式是不固定的，但最大不超过52列，用DTS手动可以导入，但用bcp导入就会把第二行的开始接到第一行后边，然后凑够52个，但我想要一行数据就对应一行记录。请问如何处理。

数据文件如下

0~V~7~
1~G~zhangsan~500~0~0~1~0~0~0~~0~0~2~0~1~1~0~1~0~1~2~0~0~1~1~0~0~1~0~1~1~0~0~1~0~0~0~0~1~
2~G~lisi~400~0~
4~G~zhangsan~600~0~0~1~

bcp语句如下

bcp   "dbo.UNATCONF"   in   "E:\DBBAK\woc_data\UNATCONF.TXT"   /S"192.168.11.152"   /U"sa"   /P"sa"   /c    /t"~" 
 -r\n  /k -F 1 -L 2  -e   "\DBBAK\woc_data\err.TXT"   

25 个解决方案

#1

帮顶一下，说实真的不懂。关注一下。

#2

列不等?

ROWTERMINATOR [ = 'row_terminator' ]

指定对于 char 和 widechar 数据文件要使用的行终止符。默认值是 \n（换行符）。

#3

就是数据文件中列不等，有的只有三个~，有的四个，最多52个，
我使用了行终止符了啊 ，-r\n   

#4

BULK INSERT
以用户指定的格式复制一个数据文件至数据库表或视图中。

语法
BULK INSERT [ [ 'database_name'.][ 'owner' ].]{ 'table_name' FROM 'data_file' }
    [ WITH
        ( 
            [ BATCHSIZE [ = batch_size ] ]
            [ [ , ] CHECK_CONSTRAINTS ]
            [ [ , ] CODEPAGE [ = 'ACP' | 'OEM' | 'RAW' | 'code_page' ] ]
            [ [ , ] DATAFILETYPE [ =
                { 'char' | 'native'| 'widechar' | 'widenative' } ] ]
            [ [ , ] FIELDTERMINATOR [ = 'field_terminator' ] ]
            [ [ , ] FIRSTROW [ = first_row ] ]
            [ [ , ] FIRE_TRIGGERS ]
            [ [ , ] FORMATFILE = 'format_file_path' ]
            [ [ , ] KEEPIDENTITY ]
            [ [ , ] KEEPNULLS ]
            [ [ , ] KILOBYTES_PER_BATCH [ = kilobytes_per_batch ] ]
            [ [ , ] LASTROW [ = last_row ] ]
            [ [ , ] MAXERRORS [ = max_errors ] ]
            [ [ , ] ORDER ( { column [ ASC | DESC ] } [ ,...n ] ) ]
            [ [ , ] ROWS_PER_BATCH [ = rows_per_batch ] ]
            [ [ , ] ROWTERMINATOR [ = 'row_terminator' ] ]
            [ , [ TABLOCK ] ] 
        ) 
    ] 

参数
'database_name'

是包含指定表或视图的数据库的名称。如果未指定，则系统默认为当前数据库。

'owner'

是表或视图所有者的名称。当执行大容量复制操作的用户拥有指定的表或视图时，owner 是可选项。如果没有指定 owner 并且执行大容量复制操作的用户不拥有指定的表或视图，则 Microsoft® SQL Server™ 将返回错误信息并取消大容量复制操作。

'table_name'

是大容量复制数据于其中的表或视图的名称。只能使用那些所有的列引用相同基表所在的视图。有关向视图中复制数据的限制的更多信息，请参见 INSERT。 

'data_file'

是数据文件的完整路径，该数据文件包含要复制到指定表或视图的数据。BULK INSERT 从磁盘复制数据（包括网络、软盘、硬盘等）。 

data_file 必须从运行 SQL Server 的服务器指定有效路径。如果 data_file 是远程文件，则请指定通用命名规则 (UNC) 名称。

BATCHSIZE [ = batch_size ]

指定批处理中的行数。每个批处理作为一个事务复制至服务器。SQL Server提交或回滚（在失败时）每个批处理的事务。默认情况下，指定数据文件中的所有数据是一个批处理。

CHECK_CONSTRAINTS

指定在大容量复制操作中检查 table_name 的任何约束。默认情况下，将会忽略约束。

CODEPAGE [ = 'ACP' | 'OEM' | 'RAW' | 'code_page' ]

指定该数据文件中数据的代码页。仅当数据含有字符值大于 127 或小于 32 的 char、varchar 或 text 列时，CODEPAGE 才是适用的。

CODEPAGE 值 描述 
ACP char、varchar 或 text 数据类型的列从 ANSI/Microsoft Windows® 代码页 ISO 1252 转换为 SQL Server 代码页。 
OEM（默认值） char、varchar 或 text 数据类型的列被从系统 OEM 代码页转换为 SQL Server 代码页。 
RAW 并不进行从一个代码页到另一个代码页的转换；这是最快的选项。 
code_page 特定的代码页号码，例如 850。 


DATAFILETYPE [ = {'char' | 'native' | 'widechar' | 'widenative' } ]

指定 BULK INSERT 使用指定的默认值执行复制操作。

DATAFILETYPE 值 描述 
char（默认值） 从含有字符数据的数据文件执行大容量复制操作。 
native 使用 native（数据库）数据类型执行大容量复制操作。要装载的数据文件由大容量复制数据创建，该复制是用 bcp 实用工具从 SQL Server 进行的。 
widechar 从含有 Unicode 字符的数据文件中执行大容量复制操作。 
widenative 执行与 native 相同的大容量复制操作，不同之处是 char、varchar 和 text 列在数据文件中存储为 Unicode。要装载的数据文件由大容量复制数据创建，该复制是用 bcp 实用工具从 SQL Server 进行的。该选项是对 widechar 选项的一个更高性能的替代，并且它用于使用数据文件从一个运行 SQL Server 的计算机向另一个计算机传送数据。当传送含有 ANSI 扩展字符的数据时，使用该选项以便利用 native 模式的性能。 


FIELDTERMINATOR [ = 'field_terminator' ]

指定用于 char 和 widechar 数据文件的字段终止符。默认的字段终止符是 \t（制表符）。

FIRSTROW [ = first_row ]

指定要复制的第一行的行号。默认值是 1，表示在指定数据文件的第一行。

FIRE_TRIGGERS

指定目的表中定义的任何插入触发器将在大容量复制操作过程中执行。如果没有指定 FIRE_TRIGGERS，将不执行任何插入触发器。

FORMATFILE [ = 'format_file_path' ]

指定一个格式文件的完整路径。格式文件描述了含有存储响应的数据文件，这些存储响应是使用 bcp 实用工具在相同的表或视图中创建的。格式文件应该用于以下情况： 

数据文件含有比表或视图更多或更少的列。


列使用不同的顺序。


列分割符发生变化。


数据格式有其它的改变。通常，格式文件通过 bcp 实用工具创建并且根据需要用文本编辑器修改。有关更多信息，请参见 bcp 实用工具。 
KEEPIDENTITY

指定标识列的值存在于导入文件中。如果没有指定 KEEPIDENTITY，在导入的数据文件中此列的标识值将被忽略，并且 SQL Server 将根据表创建时指定的种子值和增量值自动赋给一个唯一的值。假如数据文件不含该表或视图中的标识列，使用一个格式文件来指定在导入数据时，表或视图中的标识列应被忽略；SQL Server 自动为此列赋予唯一的值。有关详细信息，请参见 DBCC CHECKIDENT。 

KEEPNULLS

指定在大容量复制操作中空列应保留一个空值，而不是对插入的列赋予默认值。

KILOBYTES_PER_BATCH [ = kilobytes_per_batch ]

指定每个批处理中数据的近似千字节数（KB）。默认情况下，KILOBYTES_PER_BATCH 未知。

LASTROW [ = last_row ]

指定要复制的最后一行的行号。默认值是 0，表示指定数据文件中的最后一行。

MAXERRORS [ = max_errors ]

指定在大容量复制操作取消之前可能产生的错误的最大数目。不能被大容量复制操作导入的每一行将被忽略并且被计为一次错误。如果没有指定 max_errors，默认值为 0。

ORDER ( { column [ ASC | DESC ] } [ ,...n ] ) 

指定数据文件中的数据如何排序。如果装载的数据根据表中的聚集索引进行排序，则可以提高大容量复制操作的性能。如果数据文件基于不同的顺序排序，或表中没有聚集索引，ORDER 子句将被忽略。给出的列名必须是目的表中有效的列。默认情况下，大容量插入操作假设数据文件未排序。

n

是表示可以指定多列的占位符。

ROWS_PER_BATCH [ = rows_per_batch ]

指定每一批处理数据的行数（即 rows_per_bacth）。当没有指定 BATCHSIZE 时使用，导致整个数据文件作为单个事务发送给服务器。服务器根据 rows_per_batch 优化大容量装载。默认情况下，ROWS_PER_BATCH 未知。

ROWTERMINATOR [ = 'row_terminator' ]

指定对于 char 和 widechar 数据文件要使用的行终止符。默认值是 \n（换行符）。

TABLOCK

指定对于大容量复制操作期间获取一个表级锁。如果表没有索引并且指定了 TABLOCK，则该表可以同时由多个客户端装载。默认情况下，锁定行为是由表选项 table lock on bulk load 决定的。只在大容量复制操作期间控制锁会减少表上的锁争夺，极大地提高性能。

注释
BULK INSERT 语句能在用户定义事务中执行。对于一个用 BULK INSERT 语句和 BATCHSIZE 子句将数据装载到使用多个批处理的表或视图中的用户定义事务来说，回滚它将回滚所有发送给 SQL Server 的批处理。

权限
只有 sysadmin 和 bulkadmin 固定服务器角色成员才能执行 BULK INSERT。

示例
本例从指定的数据文件中导入订单详细信息，该文件使用竖杠 (|) 字符作为字段终止符，使用 |\n 作为行终止符。

BULK INSERT Northwind.dbo.[Order Details]
   FROM 'f:\orders\lineitem.tbl'
   WITH 
      (
         FIELDTERMINATOR = '|',
         ROWTERMINATOR = '|\n'
      )

本例指定 FIRE_TRIGGERS 参数。

BULK INSERT Northwind.dbo.[Order Details]
   FROM 'f:\orders\lineitem.tbl'
   WITH
     (
        FIELDTERMINATOR = '|',
        ROWTERMINATOR = ':\n',
        FIRE_TRIGGERS
      )

-----------------------------------

上为bulk insert的帮助说明,如果解决不了,就是bulk,insert不支持不等列的文件操作.

帮顶.

#5

up

#6

可以呀

create table tt_text(t1 varchar(20),t2 varchar(20))
BULK INSERT  tt_text from 'e:\py.txt' 

   WITH  
      ( 
         FIELDTERMINATOR = ' ', 
         ROWTERMINATOR = '\n' 
      ) 
/*文本文件
掯ken 
狠ken hen 
珢ken
*/
select * from tt_text
/*结果
t1                   t2                   
-------------------- -------------------- 
掯ken                 NULL
狠ken                 hen 
珢ken                 NULL
*/
drop table tt_text

#7

不行啊，报错


服务器: 消息 4832，级别 16，状态 1，行 1
大容量插入: 在数据文件中遇到意外的文件结尾 (EOF)。
服务器: 消息 7399，级别 16，状态 1，行 1
OLE DB 提供程序 'STREAM' 报错。提供程序未给出有关错误的任何信息。
OLE DB 错误跟踪［OLE/DB Provider 'STREAM' IRowset::GetNextRows returned 0x80004005:  提供程序未给出有关错误的任何信息。］。
语句已终止。

#8

设定一个行终止符
可用；
同时数据库中相应列要可为null

#9

结束符号就是回车换行，列也设为可空了啊

#10

文本文件的最后一行需要处理。
最后一行要用空格补齐数据，并且删除后面多余的空行。

#11

忘记说你要为null的地方需要列分割符

如
1，，，，，，；
而不能是1，；

#12

我的数据文件就是这种样式
如 
1，，，，，，； 
2，，；
3，，，，；

#13

你把表建为散列试试
create table tt_text(t1 varchar(20),t2 varchar(20)),t3 varchar(20))
BULK INSERT  tt_text from 'e:\py.txt' 

   WITH  
      ( 
         FIELDTERMINATOR = ' ', 
         ROWTERMINATOR = '\n' 
      ) 
/*文本文件
掯ken 
狠ken hen 
珢ken
*/
select * from tt_text
/*结果
t1                   t2                   
-------------------- -------------------- 
掯ken                 NULL
狠ken                 hen 
珢ken                 NULL
*/
drop table tt_text

#14

引用 6 楼 cson_cson 的回复:

可以呀
SQL codecreatetablett_text(t1varchar(20),t2varchar(20))BULKINSERTtt_textfrom'e:\py.txt'WITH( 
         FIELDTERMINATOR='', 
         ROWTERMINATOR='\n')/*文本文件
掯ken 
狠ken hen 
珢ken*/select*fromtt_text/*结果
t1                   t2                   
-------------------- -------------------- 
掯ken                 NULL
狠ken                 hen 
珢ken                 NULL*/droptable…

照你的方式做了下
消息 4832，级别 16，状态 1，第 1 行
大容量加载: 在数据文件中遇到意外的文件结尾。
消息 7399，级别 16，状态 1，第 1 行
链接服务器 "(null)" 的 OLE DB 访问接口 "BULK" 报错。提供程序未给出有关错误的任何信息。
消息 7330，级别 16，状态 2，第 1 行
无法从链接服务器 "(null)" 的 OLE DB 访问接口"BULK"提取行。
难道是设置问题？

文本内容是：
掯ken 
狠ken hen 
珢ken

#15

链接服务器??

#16

你把表建为三列试试 
create table tt_text(t1 varchar(20),t2 varchar(20)),t3 varchar(20)) 

#17

就楼主的问题设计了个实验

对于没有写全数据的导入问题,若存在create table a12345 (a int,b int,c int,d int,e int)而要导入的数据格式为
1    
1 2   
1 2 3  
1 2 3 4 
1 2 3 4 5
bcp "test..a12345" in "C:\Documents and Settings\Iamme\px.txt" -c -t " " -T 
以此格式导入数据时 会存在只能导入一行数据的问题，若在每行后添加一个~则会使下一行添加到上一行后，
此时可以使用BULK INSERT来进行数据导入，如下语句
BULK INSERT  tt_text from 'C:\Documents and Settings\Iamme\aa.txt' 
   WITH  
      ( 
         FIELDTERMINATOR = ' ', 
         ROWTERMINATOR = '\n' 
      ) 
但是必须是有几列没写要补几个空格，且最后一行要用空格补齐数据，并且删除后面多余的空行。否则即便是成功插入数据库也会出现少插几列等的错误,若以其余符号分割字符比如：~几乎无解。
若使用bcp也可以通过与BULK INSERT相同的方式用如下语句实现,但是对于其余分割符目前同样无解。
bcp "test..a12345" in "C:\Documents and Settings\Iamme\aa.txt" -c -t " " -T -r\n

#18

引用 15 楼 cson_cson 的回复:

链接服务器??

错误我已经知道了没有补齐空格的原因

#19

小发现

注意:bcp导入的数据未必加在表最后一行，而BULK INSERT导入的数据必定加在最后（有identity的例外）

#20

你的意思是必须把后边的分割符，也就是空格必须补齐了和列数相等？是吗

#21

引用 20 楼 zhuixing 的回复:

你的意思是必须把后边的分割符，也就是空格必须补齐了和列数相等？是吗

正解

关于你的问题可以先导入到EXCEL中再选满52列粘贴出来，应该将上述语句中的 改成TAB键。
比如:
BULK INSERT  tt_text from 'C:\Documents and Settings\Iamme\aa.txt' 
   WITH  
      ( 
         FIELDTERMINATOR = ' ', 
         ROWTERMINATOR = '\n' 
      ) 
粘帖出来的数据都会自动补齐TAB键的

#22

关注 接分

#23

支持搂主，收藏

#24

解决了用DTS解决

#25

我也要学习，就是看不懂啊，难难难！！！！！！！！