sql-server-2008 - 从固定格式文本文件批量插入忽略行终止符

Question

我有很多平面(文本)文件，我想每天将它们导入到 SQLSERVER 表中。现在，当我制定我的程序时，我只想导入一个文件。当然我可以编写 c# 代码来做到这一点，但我觉得这不是正确的方法，我想使用诸如批量插入 xml 格式文件之类的东西。

我的第一个示例文件看起来像这样(sample.dat):

Q     RR201110010000000002000000000000232000
N     X4201110010000000001500000000000160000

注意:此文件上的十六进制转储显示，每一行都以换行符结束——不多也不少。

我的 xml 翻译文件如下所示:

<?xml version="1.0"?>
<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" 
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
 <RECORD>
  <FIELD ID="1" xsi:type="CharFixed" LENGTH="6"/>
  <FIELD ID="2" xsi:type="CharFixed" LENGTH="2"/>
  <FIELD ID="3" xsi:type="CharFixed" LENGTH="8"/>
  <FIELD ID="4" xsi:type="CharFixed" LENGTH="14"/>
  <FIELD ID="5" xsi:type="CharFixed" LENGTH="14"/>
 </RECORD>
 <ROW>
  <COLUMN SOURCE="1" NAME="c1" xsi:type="SQLNCHAR"/>
  <COLUMN SOURCE="2" NAME="c2" xsi:type="SQLNCHAR"/>
  <COLUMN SOURCE="3" NAME="c3" xsi:type="SQLCHAR"/>
  <COLUMN SOURCE="4" NAME="c4" xsi:type="SQLINT" />
  <COLUMN SOURCE="5" NAME="c5" xsi:type="SQLINT" />
 </ROW>
</BCPFORMAT>

我的查询如下所示:

SET LANGUAGE us_english;
GO
SET DATEFORMAT ymd;
go
BULK INSERT 
  PROJ.dbo.Costs
  FROM 'C:\somewhere\test01\SAMPLE.DAT'
  WITH
  (
  DATAFILETYPE ='CHAR',
  FORMATFILE='C:\somewhere\test01\TRANSLATE02.XML',
  ERRORFILE='C:\somewhere\test01\ERRORS.TXT',
  ROWTERMINATOR='\n'
  )
  GO

当我运行这个脚本时，我得到一个从第 2 行开始的溢出错误。(也就是说，第 1 行似乎已正确翻译，尽管我在 sql 表中没有看到它。) ERRORS.TXT 的十六进制转储显示第一个错误行(第 2 行)以换行符开头!当然，那会导致第4个字段溢出!所以看起来脚本不理解 ROWTERMINATOR。我试过 '\n', '\r', '\r\n', '\n\r' 以防万一它没有看到\r。徒劳无功。

我还尝试了一个稍微不同的 sql 命令
bulk insert txt error with ROWTERMINATOR

并得到同样的错误。

对我缺少的东西有什么想法吗？

根据要求，这里是 sample.dat 的经过处理的十六进制转储:

000000: 41 20 20 20  20 20 XX XX  32 30 31 31  31 30 30 31  Q     RR20111001
000010: 30 30 30 30  30 30 30 30  30 31 35 30  30 30 30 30  0000000001500000
000020: 30 30 30 30  30 30 31 35  30 30 30 30  0A ZZ 20 20  000000150000.N
000030: 20 20 20 XX  XX 32 30 31  31 31 30 30  31 30 30 30     X420111001000
000040: 30 30 30 30  30 30 32 30  30 30 30 30  30 30 30 30  0000002000000000
000050: 30 30 30 32  33 32 30 30  30 0A ZZ 20  20 20 20 20  000232000.Y

请注意，XX 和 ZZ 被屏蔽(不是真实数据)，0A 是换行符，它是最后一个零(十六进制 30)和开始下一行的 ZZ 字符之间的唯一内容。希望这不会太令人困惑。

我下面的解决方案有效，但是，这里也讨论了这个问题，我觉得这个解决方案更好(虽然我还没有确认，我想我会在下一个文件中尝试一下)。
Bulk insert rowterminator issue

Answer 1

您必须使用 SSIS“SQL Server 集成服务”将数据从文件转换到您的数据库。
您可以在 SQL Server 中执行此转换，以便每天自动转换。