批量插入缺少最后一行？ [英] BULK INSERT missing last row?

问题描述

我对文本文件使用BULK INSERT。一切正常，但发现的一件事是，如果我给最后一行的最后一列赋值，它将导入。如果最后一行中该最后一列的值为空，则尽管目标列允许为空，它也会丢弃该行！文本文件使用制表符分隔符，以下是最后一行数据的示例：

I use BULK INSERT for my text files. Everything works fine but one thing that I discovered, If I give the final line's final column a value, it will import. If the value of that final column in the final line is blank, it discards the line, despite the fact that the destination column allows nulls! Text file uses tab delimiter, here is example of the last row data:

Mike    Johnson 1/29/1987   M

如果我在最后一列中有任何值，将插入字段行，例如：

if I have any value in the last column field row will be inserted, example here:

Mike    Johnson 1/29/1987   M   test

这是我的大块插入内容：

This is my BULK Insert:

BULK INSERT ##TEMP_TEXT
FROM '#uncdir#\#cffile.ServerFile#'
WITH (
    FIELDTERMINATOR = '\t', 
    ROWTERMINATOR = '\n'
)

我尝试使用 \r 代替 ＼ ，但这不能解决问题。我也在一些网站上进行了研究，但找不到任何解决方案。我想知道这是否可以在SQL中修复。如果有人知道如何解决此问题，请告诉我。

I tried to use \r instead of \n but that did not fix the problem. I also researched on some websites but could not find any solution. I'm wondering this is something that can be fixed in SQL. If someone knows how this can be fixed please let me know.

解决方案：

对于任何人，ColdFusion的用法如下，该行将在文本文件中添加换行符。

For anyone how use ColdFusion here is the line that will add newline in the text file.

exec xp_cmdshell 'echo. >> "#uncdir#\#cffile.ServerFile#"';

关键是在Coldfusion变量周围加上双引号，否则代码不起作用。

Key was to put double quotes around coldfusion variables, otherwise code does not work.

uncdir代码在这里：

uncdir code is here:

<cfset uncdir = createObject("java","java.net.InetAddress").getLocalHost().getHostName()/>

cffile.ServerFile 形成。我使用JQuery提交文本文件。我希望这有帮助。谢谢。

cffile.ServerFile you can get from the form. I used JQuery to submit the text file. I hope this helps. Thank you.

推荐答案

我在SQL Server 2008 R2上转载了您的问题。解决方案很简单，只需在文件中添加换行符，以使最后一行以换行符结束。

I reproduced your issue on SQL Server 2008 R2. The solution is as simple as adding a newline to your file so that the last row terminates with a newline.

我创建了两个文件：

然后运行以下脚本：

CREATE TABLE #t(first_name VARCHAR(128),last_name_etc VARCHAR(128),sex CHAR(1),test VARCHAR(128));

BULK INSERT #t
FROM 'C:\temp\without_newline.txt'
WITH (
    FIELDTERMINATOR='\t',
    ROWTERMINATOR='\n'
);

SELECT * FROM #t;

TRUNCATE TABLE #t;

BULK INSERT #t
FROM 'C:\temp\with_newline.txt'
WITH (
    FIELDTERMINATOR='\t',
    ROWTERMINATOR='\n'
);

SELECT * FROM #t;

DROP TABLE #t;

结果1：

first_name  | last_name_etc     | sex | test
--------------------------------------------
Tom         | Jackson 2/28/1986 | M   | test

结果2：

first_name  | last_name_etc     | sex | test
--------------------------------------------
Tom         | Jackson 2/28/1986 | M   | test
Mike        | Johnson 1/29/1987 | M   | NULL

---

解决方案应该简单到确保最后一行以 \r\n 结尾。您可以更改生成文本文件的过程，也可以在批量插入之前手动进行操作。

---

The solution should be as simple as making sure the last line terminates with \r\n. Either you change the process that generates the text file or do it manually right before you do the bulk insert.

一种手动​​执行此操作的方法是运行 EXEC xp_cmdshell'echo。 >> C：\temp\without_newline.txt ，然后再进行批量插入。

One way to do this manually would be to run EXEC xp_cmdshell 'echo. >> C:\temp\without_newline.txt' right before you do the bulk insert.

这篇关于批量插入缺少最后一行？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！