BULK INSERT SQL SERVER 的乐趣 - 指定代码页的类型不匹配或无效字符

Question

我正在插入如下所示的数据：

   AA00000111   PSNH-OT J, SMITH    03/01/2011  10/11/1957  42  Male    Hartford    NH      Lorazepam   Benzodiazepines C
AA00000151  PSNH-OT BEN, HARRY  03/06/2011  07/18/1969  42  Male    Hartford    NH      Fentanyl    Synthetic Opioids   C
AA00000151  PSNH-OT URA, HARRISON   03/06/2011  07/18/1969  29  Male    Hartford    NH      Norfentanyl Synthetic Opioids   C
AA00000181  PSNH-OT WAYNE, GRIFFON  03/06/2011  09/01/1982  75  Female  Hartford    NH      cTHC (Marijuana metabolite) Illicits    C

使用此批量插入：

use RadarDataMining
go

BULK INSERT tblRadarsAC
FROM 'C:\PerfLogs\radars.txt'
WITH
(
FIELDTERMINATOR = '\t',
ROWTERMINATOR = '\n'
)

并且我收到这些错误：

Msg 4864, Level 16, State 1, Line 2
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 4 (date_tested).
Msg 4864, Level 16, State 1, Line 2
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 2, column 4 (date_tested).
Msg 4864, Level 16, State 1, Line 2
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 3, column 4 (date_tested).
Msg 4864, Level 16, State 1, Line 2
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 4, column 4 (date_tested).
Msg 4864, Level 16, State 1, Line 2
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 5, column 4 (date_tested).
Msg 4864, Level 16, State 1, Line 2
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 6, column 4 (date_tested).
Msg 4864, Level 16, State 1, Line 2
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 7, column 4 (date_tested).
Msg 4864, Level 16, State 1, Line 2
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 8, column 4 (date_tested).
Msg 4864, Level 16, State 1, Line 2
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 9, column 4 (date_tested).
Msg 4864, Level 16, State 1, Line 2
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 10, column 4 (date_tested).
Msg 4864, Level 16, State 1, Line 2
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 11, column 4 (date_tested).
Msg 4865, Level 16, State 1, Line 2
Cannot bulk load because the maximum number of errors (10) was exceeded.
Msg 7399, Level 16, State 1, Line 2
The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.
Msg 7330, Level 16, State 2, Line 2
Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".

第四个字段类型是 date

由于某种原因，它不喜欢我的日期字段。我不明白为什么。我做错了什么？

Answer 1

创建一个临时表来处理日期字段指定为 varchar 而不是日期的位置，并查看是否可以批量插入其中。该字段中可能有某种不可打印的字符，因此它不是批量插入的日期。

一旦它是一张桌子。执行这样的选择

Select 'XX'+field4+'XX'

如果您看到 X 和日期之间有空格，则说明您有一个无法打印的字符。
如果你想知道字符是什么，可以使用 ASCII() 函数。