使用BULK INSERT SQL SERVER的乐趣-为指定的代码页键入不匹配或无效字符 [英] Fun with BULK INSERT SQL SERVER - type mismatch or invalid character for the specified codepage
问题描述
我正在插入看起来像这样的数据：
i am inserting data that looks like this:
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
由于某种原因，它不喜欢我的日期字段。我不理解为什么。我在做什么错？
it does not like my date field for some reason. i do not understand why. what am i doing wrong?
推荐答案
创建一个临时表以将日期字段指定为varchar而不是a日期，看看是否可以批量插入。您在字段中可能有某种不可打印的字符，因此它不是批量插入的日期。
Create a staging table to play with where the date field is specified as a varchar and not a date and see if you can bulk insert into that. It is possible that you have some sort of nonprintable character in the field and thus it isn't a date to the bulk insert.
它是一个表。做这样的选择
Once it is a table. do a select like this
选择'XX'+ field4 +'XX'
Select 'XX'+field4+'XX'
如果看到空格， X和日期，您将拥有一个无法打印的字符。
如果您想知道什么字符，可以使用ASCII（）函数。
If you see a space bewteen the the Xs and the date, you have an unprintable character. If you want to know what the character is you can use the ASCII () function.
这篇关于使用BULK INSERT SQL SERVER的乐趣-为指定的代码页键入不匹配或无效字符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
