我有一个名为 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'
和所有相同的结果...关于如何解决这个问题的任何想法
我正在创建 csv 就像this通过 PHP