主页 / user-217479

Wilmar's questions

Martin Hope
Wilmar
Asked: 2020-10-22 08:30:59 +0800 CST
  • 1

我正在尝试将一个简单CSV文件导入 SQL Server 2017 上的表中。CSV 文件和表在列数和数据类型方面相互匹配。这是CSV文件的摘录：

UID,customerID,DateT,TypeID,AssociatedID,AttributeID,LandingPage,jsonT,ClientIPAddress
9150,2345,2020-09-01T00:00:36Z,1,,859,campaign01.html,{'product': 'string01', 'type': '', 'string011'},192.168.200.2
9151,2356,2020-09-01T00:02:30Z,1,,640,campaign02.html,{'product': 'string02', 'type': '', 'string022'},192.168.200.1

这是我的桌子：

CREATE TABLE [dbo].[activity](
    [UID] [bigint] NOT NULL,
    [customerID] [bigint] NOT NULL,
    [DateT] [datetime] NULL,
    [TypeID] [bigint] NULL,
    [AssociatedID] [float] NULL,
    [AttributeID] [bigint] NULL,
    [LandingPage] [varchar](256) NULL,
    [jsonT] [varchar](1024) NULL,
    [ClientIPAddress] [varchar](64) NULL,
)
GO

这是我的导入声明：

BULK INSERT dbo.activity
FROM 'C:\tmp\activity.csv'
WITH (
    FIRSTROW = 2,
    FIELDTERMINATOR = ',',
    ROWTERMINATOR = '0x0a',
    BATCHSIZE = 1000,
    MAXERRORS = 2
)
GO

我不断收到一条错误消息，说要截断最后一列（客户端 IP）。我检查了所有值，它们只有 IP。此外，错误并非在所有行上，我一生都看不到一种 IP 格式或另一种 IP 格式之间的任何区别，它们只是 IP！我尝试简单地使 ClientIPAddress 字段更大（只是为了它），但我只是不断收到相同的错误。

Msg 4863, Level 16, State 1, Line 1
Bulk load data conversion error (truncation) for row 2, column 9 (ClientIPAddress).
Msg 4863, Level 16, State 1, Line 1
Bulk load data conversion error (truncation) for row 5, column 9 (ClientIPAddress).
Msg 4863, Level 16, State 1, Line 1
Bulk load data conversion error (truncation) for row 6, column 9 (ClientIPAddress).
Msg 4865, Level 16, State 1, Line 1
Cannot bulk load because the maximum number of errors (2) was exceeded.
Msg 7399, Level 16, State 1, Line 1
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 1
Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".

任何关于我可以检查什么或我做错了什么的想法都将受到高度赞赏。

谢谢！

sql-server csv