English 中文(简体)
从固定格式插入文本文件忽略行终结器
原标题:Bulk Insert from fixed format text file ignores rowterminator

我有很多平面(文本)文件要每天导入到 SQLSERVER 表格中。 目前, 在我制定程序时, 我只想导入一个文件。 当然我可以为此写入 C# 代码, 但我觉得这不是正确的方式, 我想用xml 格式文件来大量插入。

我的第一个样本文件看起来像这样( 样本. 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	est01SAMPLE.DAT 
  WITH
  (
  DATAFILETYPE = CHAR ,
  FORMATFILE= C:somewhere	est01TRANSLATE02.XML ,
  ERRORFILE= C:somewhere	est01ERRORS.TXT ,
  ROWTERMINATOR= 
 
  )
  GO

When I run this script, I get an overflow error starting with row 2. (That is, row 1 appears to have translated correctly, although I do not see it in the sql table.) A hexdump of ERRORS.TXT reveals that the first error line (row 2) begins with the newline! Of course, that would cause the 4th field to overflow! So it appears the script is not understanding the ROWTERMINATOR. I tried , , , just in case it wasn t seeing the . No avail.

I also attempted a slightly different sql command as per bulk insert txt error with ROWTERMINATOR

并有相同的错误。

对我错过了什么有什么想法吗?

根据请求, 这里是样本. 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 是线性字符, 这是最后0 (hex 30) 和 开始下一行的 ZZ 字符之间唯一的事物。 希望这不会太混乱 。

My solution below works, however, this problem is also discussed here and the solution seems better to me (though I haven t confirmed it, I think I ll try it with the next file). Bulk insert rowterminator issue

最佳回答

答案(至少一个答案)简单得令人尴尬。

我刚在XML的FIELDS列表中添加了一个假的字符字段。

<?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"/>
  <FIELD ID="6" xsi:type="CharFixed" LENGTH="1"/>
 </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>

注意 我没有写下最后一个字段( 并贴上相应的 COLUMN 标签 ) 。 这读取 EOL (/ n) 进入一个假字段 。 如果这在 BULK INSERT 命令中不是 ROWTER 行为中的错误, 那么它至少是高度不直观的 。 也就是说, ROWTER 似乎是 NOOP 。

观察1:第3栏是YYYMMDD格式的日期,而相应的 SOURCE 3 实际上是小数字自动转换。

意见2:资源资源3和4被定义为小数点(14,2),我想这将使输入量以百分百的方式在相应的字段中使用最后两个字符。我可以找到一种方法自动(偏好)或b. 后处理除以100(这是另一个问题-在这里指出这一点,对我来说似乎很有意思)。

Either way, this appears to be ONE solution for the problem. Thanks for the responses.

Addendum (as an aside): I have decided to go with option b (as noted in Observation 2), using an UPDATE SET command at the end of the sql commands to divide the money fields by 100.

最后产品将是一个批量文件, 多次引用“ sqlcmd”, 然后在尾端运行一个 perl 脚本以检查各种错误文件中的条目 。

还有一件事:我注意到,在我运行此命令时, BULK INSERT 命令中列出的错误文件必须不存在; 否则, 错误本身将产生另一个错误! 我会在预处理期间处理 。

无论如何,再次感谢。

问题回答

You Must Use SSIS "SQL Server Integration Services" For Convert Data From File To Your Database. and You Can Job This Convert In SQL Server for Convert Each Day Automatically.

https://msdn.microsoft.com/en-us/library/ms187833(v=sql.110.10)aspx#importFixed Fields" rel=“nofollow”>SQL服务器关于固定格式文本XML schema文件的文章 在记录元素中指定了终止者:

<RECORD>
  <FIELD ID="1" xsi:type="CharFixed" LENGTH="10"/>
  <FIELD ID="2" xsi:type="CharFixed" LENGTH="6"/>
  <FIELD ID="3" xsi:type="CharTerm" TERMINATOR="
"
</RECORD>

(请注意上面的打字)也许这就是为什么您在查询中的规格被忽略的原因。

尝试 char( 13) , 它是 SQL 运输返回。 还有 < code>char( 13) + char(10) , 运输返回/ line feed 。





相关问题
Custom Formating a DateTimePicker using CultureInfo

I am using the DateTimePicker control in a Vb.Net Windows project. I would like the date to reflect the Regional Settings on the user s computer, but also to show the month name, rather than the ...

What settings storage format to choose?

I m writing a Qt application and will need to store the settings for the program. I want them to be easily editable by non-advanced users yet be flexible enough for advanced users (thus allow easy ...

Parsing date with timezone from an email?

I am trying to retrieve date from an email. At first it s easy: message = email.parser.Parser().parse(file) date = message[ Date ] print date and I receive: Mon, 16 Nov 2009 13:32:02 +0100 But I ...

Arduino String Formatting Issue

I m making an Arduino-powered clock, and in the process, I m trying to format integers into two-digit formatted strings for the time read-out (e.g. 1 into "01"). The following gives me "error: ...

How to get fields from such a string?

I m importing some data from a database. The data has been stored by a CMS written in php where I have no control. Here is the data (a dense report from a paypal response): a:56:{ s:8:"business";s:19:...

Nginx raises 404 when using format => js

I upload images to my App using Ajax and an Iframe. In Development everything works like a charm. But in production Nginx suddenly raises a 404 error. When I look into the log, the request never hits ...

Check if string is of SortableDateTimePattern format

Is there any way I can easily check if a string conforms to the SortableDateTimePattern ("s"), or do I need to write a regular expression? I ve got a form where users can input a copyright date (as a ...

热门标签