SQL 错误：“第 2 行第 1 列（日期）的批量加载数据转换错误（指定代码页的类型不匹配或无效字符）。”
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5011302/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Error: "Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 2, column 1 (Date)."
提问by Contango
For SQL Server 2008 R2, is it possible to import a .csv file with a date in "MM/DD/YYYY" format, using "bulk insert"? Or do I have to alter my .csv file to have a format that SQL Server 2008 R2 expects?
对于 SQL Server 2008 R2，是否可以使用“批量插入”导入带有“MM/DD/YYYY”格式日期的 .csv 文件？或者我是否必须将我的 .csv 文件更改为 SQL Server 2008 R2 期望的格式？
I execute the following in Microsoft SQL Server 2008 R2:
我在 Microsoft SQL Server 2008 R2 中执行以下操作：
BULK
INSERT dbo.TCsvOptionContracts
FROM 'C:\x.csv'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n',
FORMATFILE = 'C:\format.xml',
FIRSTROW = 2
)
GO
And I get this error:
我得到这个错误：
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 2, column 1 (Date).
The date in this column of the .csv file is in the form MM/DD/YYYY, i.e. "02/16/2011".
.csv 文件此列中的日期格式为 MM/DD/YYYY，即“02/16/2011”。
p.s. I generated a format file with the following command, however, I didn't see any option to specify the MM/DD/YYYY date format for the offending column:
ps 我使用以下命令生成了一个格式文件，但是，我没有看到任何选项可以为违规列指定 MM/DD/YYYY 日期格式：
bcp Engine..TCsvOptionContracts format nul -c -x -f C:\format.xml -t, -T
采纳答案by Contango
Configured the "date" column to be "smalldatetime" instead of "datetime". Tried it, and everything imported 100%.
将“日期”列配置为“smalldatetime”而不是“datetime”。试过了，所有东西都是 100% 进口的。
There are two potential methods to do this:
有两种可能的方法可以做到这一点：
- Method 1: Discard the formatting file, it will read it in fine without it (this definitely works).
- Method 2: If LINQ-to-Entities insists on generating a "datetime" in this column, then change the formatting file to specify "smalldatetime" so it reads in properly (havn't tried this but I imagine it might work).
- 方法 1：丢弃格式化文件，它会在没有它的情况下正常读取（这绝对有效）。
- 方法 2：如果 LINQ-to-Entities 坚持在此列中生成“datetime”，则更改格式文件以指定“smalldatetime”，以便正确读取（尚未尝试过，但我想它可能会起作用）。
回答by Jason Williams
This is a little late, but if you still can't bulk insert your data into a date column in SQL 2008 R2, check out this hotfix: http://support.microsoft.com/kb/968215That fixed it for me.
这有点晚了，但如果您仍然无法将数据批量插入 SQL 2008 R2 中的日期列，请查看此修补程序：http: //support.microsoft.com/kb/968215为我修复了它。
回答by Rus
FROM 'C:\x.csv' when bulk loading it is important to make sure that the file is in the same directory as the SQL server. if the SQL server is in c:\, then file should be in C:. if the SQL server is in D:\, then file should be in D:. I have found that it helps alot
FROM 'C:\x.csv' 批量加载时，务必确保该文件与 SQL 服务器位于同一目录中。如果 SQL 服务器在 c:\ 中，则文件应在 C: 中。如果 SQL 服务器位于 D:\，则文件应位于 D:。我发现它有很大帮助
