批量插入行终止符问题
我有一个名为 test.csv 的 csv，其内容如下
1,"test user",,,4075619900,[email protected],"Aldelo for Restaurants","this is my deal",,"location4"
2,"joe johnson",,"32 bit",445555519,[email protected],"Restaurant Pro Express","smoe one is watching u",,"some location"
这是我的 SQL 文件来执行批量插入，
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
但发生的情况是它只插入一条记录，第二条记录中的所有信息都插入到第一条记录的位置字段中
id,name,department,architecture,phone,email,download,comments,company,location
1,"test user",NULL,NULL,4075619900,[email protected],"Aldelo for Restaurants","this is my deal",NULL,"""location4""2,""joe johnson"",,""32 bit"",445555519,[email protected],""Restaurant Pro Express"",""smoe one is watching u"",,""some location"""
我认为问题是 ROWTERMINATOR 但我尝试了所有这些
ROWTERMINATOR = '\n'
ROWTERMINATOR = '\r\n'
ROWTERMINATOR = '\r'
和所有相同的结果...关于如何解决这个问题的任何想法
我正在创建像 这个 通过 PHP
I have this csv named test.csv with the content below
1,"test user",,,4075619900,[email protected],"Aldelo for Restaurants","this is my deal",,"location4"
2,"joe johnson",,"32 bit",445555519,[email protected],"Restaurant Pro Express","smoe one is watching u",,"some location"
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,[email protected],"Aldelo for Restaurants","this is my deal",NULL,"""location4""2,""joe johnson"",,""32 bit"",445555519,[email protected],""Restaurant Pro Express"",""smoe one is watching u"",,""some location"""
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
I am creating the csv like this via PHP
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
我认为问题是您的 csv 文件使用
\n作为 EOL （unix 方式）。 SQL Server 中的 BULK INSERT 是“智能”的，即使您将 ROWTERMINATOR 指定为\n（理论上应该可以解决您的问题），它也会在其前面加上\r所以你最终会以\r\n作为行终止符。尝试使用
ROWTERMINATOR='0x0A'。在这种情况下，SQL Server 不会执行任何魔术，而仅使用您设置为行终止符的值。对我有用。 :)
I think problem is that your csv file uses
\nas EOL (unix way). BULK INSERT in SQL Server is "smart" and even if you specifyROWTERMINATORas\n, which in theory should resolve your problem, it prepends it with\rso you end up with\r\nas row terminator.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. :)
您可能需要
CHAR(13)和CHAR(10)作为行终止符http://www.techonthenet.com/ascii/chart.php
You probably need
CHAR(13)andCHAR(10)as Row Terminatorhttp://www.techonthenet.com/ascii/chart.php