批量插入行终止符问题 [英] Bulk insert rowterminator issue

问题描述

我将这个csv命名为test.csv，其内容如下

I have this csv named test.csv with the content below

1,"test user",,,4075619900,[e-mail],"Aldelo for Restaurants","this is my deal",,"location4"
2,"joe johnson",,"32 bit",445555519,[e-mail],"Restaurant Pro Express","smoe one is watching u",,"some location"

这是我的SQL FILE，用于批量插入

Here is my SQL FILE to do the BULK insert

USE somedb
GO

CREATE TABLE CSVTemp
(id INT,
name VARCHAR(255),
department VARCHAR(255),
architecture VARCHAR(255),
phone VARCHAR(255),
email VARCHAR(255),
download VARCHAR(255),
comments TEXT,
company VARCHAR(255),
location VARCHAR(255))
GO

BULK
INSERT CSVTemp
FROM 'c:\test\test.csv'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '''+CHAR(124)+''+CHAR(10)+'''
)
GO
--Check the content of the table.
SELECT *
FROM CSVTemp
GO

但是发生的是它仅插入了一条记录，而第二条记录中的所有信息都被插入到第一条记录中的位置字段中

but whats happening is its only inserting one record and all the info from the second record is getting inserted in the location field on the first record

  id,name,department,architecture,phone,email,download,comments,company,location
  1,"test user",NULL,NULL,4075619900,[e-mail],"Aldelo for Restaurants","this is my deal",NULL,"""location4""2,""joe johnson"",,""32 bit"",445555519,[e-mail],""Restaurant Pro Express"",""smoe one is watching u"",,""some location"""

我认为问题出在ROWTERMINATOR，但是我尝试了所有这些

I assume the problem is the ROWTERMINATOR but i tried all these

ROWTERMINATOR = '\n'
ROWTERMINATOR = '\r\n'
ROWTERMINATOR = '\r'

和所有相同的结果...有关如何解决此问题的任何想法

and all the same results ...any ideas on how to FIX this

我正在通过PHP创建此的csv

I am creating the csv like this via PHP

推荐答案

我认为问题是您的csv文件使用\n作为EOL(unix方式). SQL Server中的BULK INSERT是智能"的，即使将ROWTERMINATOR指定为\n(理论上应该可以解决您的问题)，它也会在\r之前添加它，因此最终将\r\n作为行终止符.

I think problem is that your csv file uses \n as EOL (unix way). BULK INSERT in SQL Server is "smart" and even if you specify ROWTERMINATOR as \n, which in theory should resolve your problem, it prepends it with \r so you end up with \r\n as row terminator.

尝试使用ROWTERMINATOR='0x0A'.在这种情况下，SQL Server不会执行任何魔术，而只会使用您设置为行终止符的值.
为我工作. :)

Try using ROWTERMINATOR='0x0A'. In this case SQL Server doesn't perform any magic tricks and just uses the value you've set as row terminator.
Works for me. :)

这篇关于批量插入行终止符问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！