为指定的代码页键入不匹配或无效字符

为指定的代码页键入不匹配或无效字符

本文介绍了使用BULK INSERT SQL SERVER的乐趣-为指定的代码页键入不匹配或无效字符的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我正在插入看起来像这样的数据:

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的乐趣-为指定的代码页键入不匹配或无效字符的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

08-30 05:51