使用 .fmt 文件批量插入:操作系统错误代码(空) [英] Bulk Insert w/ .fmt file: Operating system error code (null)

问题描述

我正在尝试导入由 .fmt 格式定义的文本文件.这个文本文件不是逗号分隔，而是我所说的列分隔(即前 8 个字符是第一个字段，接下来的 3 个字符是第二个，等等).当我运行下面的查询时，我得到一个操作系统错误代码(null)".消息，这很奇怪，因为我使用的是带有 Vista 的 SQL Server 2008r2.

请向我解释此错误消息的含义以及如何解决它?我已经在 Google 上搜索过它并在其他论坛上发现了类似的问题，但从未真正得到解答.p>

这是我的查询:

BULK INSERT LoadTable FROM '\Dev2QueriesTest.txt'与(第一道 = 2，FORMATFILE = '\Dev2fmtTest.fmt',保持空值)

这是我得到的错误:

无法批量加载，因为文件\Dev2fmt	est.fmt"无法读取.操作系统错误代码(空).

Test.fmt 包含以下内容:

9.071 SQLCHAR 0 8 "1 Record_Control_Data SQL_Latin1_General_CP1_CI_AS2 SQLCHAR 0 3 "2 填充物"3 SQLCHAR 0 1 "3 Member_Code SQL_Latin1_General_CP1_CI_AS4 SQLCHAR 0 2 "4 Member_Sequence_Number SQL_Latin1_General_CP1_CI_AS5 SQLCHAR 0 9 "5 参与者_SSN SQL_Latin1_General_CP1_CI_AS6 SQLCHAR 0 20 ""6 姓 SQL_Latin1_General_CP1_CI_AS7 SQLCHAR 0 250 
"7 Filler10 SQL_Latin1_General_CP1_CI_AS

(这个 test.fmt 和 test.txt 是一个有 120 列的数据集的简化版本.但是错误信息是一样的.一旦我可以运行这个测试版本，我会将修复应用到真实数据)

编辑

为了回应网络上的其他半答案，我应该对这些文件夹拥有完整的读/写权限和文件.

解决方案

.fmt 文件最后需要一个空行.

I'm trying to import a text file defined by a .fmt format. Instead of being comma-delimited, this text file is what I would call column-delimited (i.e. the first 8 characters is the first field, the next 3 characters is the second, etc). When I run the query below, I get a "Operating system error code (null)" message, which is odd since I'm using SQL Server 2008r2 with Vista.

Please explain to me what this error message means and how do I get around it? I've googled it and found similar questions on other forums, but they're never really answered.

Here's my query:

BULK INSERT LoadTable FROM '\Dev2QueriesTest.txt'
WITH (FIRSTROW = 2,
      FORMATFILE = '\Dev2fmtTest.fmt',
      KEEPNULLS)

Here's the error I get:

Cannot bulk load because the file "\Dev2fmt	est.fmt" could not be read. 
Operating system error code (null).

Here's what Test.fmt contains:

9.0
7
1   SQLCHAR 0   8   ""  1   Record_Control_Data SQL_Latin1_General_CP1_CI_AS
2   SQLCHAR 0   3   ""  2   Filler  ""
3   SQLCHAR 0   1   ""  3   Member_Code SQL_Latin1_General_CP1_CI_AS
4   SQLCHAR 0   2   ""  4   Member_Sequence_Number  SQL_Latin1_General_CP1_CI_AS
5   SQLCHAR 0   9   ""  5   Participant_SSN SQL_Latin1_General_CP1_CI_AS
6   SQLCHAR 0   20  ""  6   LastName    SQL_Latin1_General_CP1_CI_AS
7   SQLCHAR 0   250 "
"  7   Filler10    SQL_Latin1_General_CP1_CI_AS

(This test.fmt and test.txt is a simplified version of a dataset with 120 columns. But the error message is the same. Once I can get this test version running, I'll apply the fix to the real data)

Edit

In response to the other half-answers around the web, I should have complete read/write permission to these folders & files.

解决方案

The .fmt file needs a blank line at the very end.

这篇关于使用 .fmt 文件批量插入:操作系统错误代码(空)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！