从CSV错误时4866批量插入数据

我想从一个CSV文件加载数据,并不断得到这些错误。 我缺少一些PARAMS在批量插入脚本或是否需要修改文件我尝试这之前?

Msg 4866, Level 16, State 1, Line 1 The bulk load failed. The column is too long in the data file for row 1, column 54. Verify that the field terminator and row terminator are specified correctly. 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)".

这里的脚本

BULK INSERT BrowseNotes FROM 'C:\Users\Jarek\browseNotes2.csv' WITH ( FIELDTERMINATOR = ',' , ROWTERMINATOR = '\n' )

以下是文件示例行,我尝试加载之前删除了第一行。 行结束用“,/ N”。 我试过更换/ N与/ R / N和删除最后一个逗号。 仍然得到同样的错误。

LoanType,Maturity,LoanClass,Borrower,LoanStatus,TimeLeftBeforeExpiration,MonthlyPayment,LoanMaturity,JobTenureYearsString,AmountToInvest,AmountMissingToClose,NumberOfPayments,Id,State,Type,Status,Aid,Amount,Duration,StartD,IntRate,Grade,Purpose,HousingStatus,JobTenure,Income,CreditClassId,City,UnfundedAmnt,Fico,OpenCreditLines,TotalCreditLines,Inq6Months,RevolvUtil,FundedPercentage,FundedAmount,EmpStatus,JobTitle,AppDate,AppAmount,Employer,DelinquentAmount,EarliestCreditLine,PubRecords,DTI,AppExpiration,LapStatus,IncomeVStatus,CreditReportD,RevolvCreditBal,AccntsNowDelinquent,Delinquencies2Yrs,MnthsSinceLastDelinquency,MnthsSinceLastRecord PERSONAL,60,C4,1248804,INFUNDING,279589,344.62,Year5,8 years,0,625.0,60,1020047,PA,1,1,1248804,13775.0,60,2011-11-11 11:40:18,0.1527,C,debt_consolidation,MORTGAGE,96,50000.0,124,PHILADELPHIA,625.0,679-713,10,21,2,62.2,0.9565972222222222,13775.0,EMPLOYED,"Quality Assurance Manager",2011-11-11 11:40:18,14400.0,"J. Ambrogi Food Distribution",0.0,01/27/2003,0,23.14,2011-11-25 11:40:18,APPROVED_CR,NOT_REQUIRED,11/11/2011,22906.0,0,0,null,null, PERSONAL,60,A5,1247389,INFUNDING,180323,289.94,Year5,3 years,0,1975.0,60,1018925,FL,1,1,1247389,12025.0,60,2011-11-10 08:05:52,0.089,A,house,MORTGAGE,36,150000.0,105,orange park,1950.0,750-779,9,25,0,62.9,0.8607142857142858,12050.0,EMPLOYED,"Project Manager",2011-11-10 08:05:52,14000.0,"Scientific Research Corp.",0.0,10/01/1984,0,14.02,2011-11-24 08:05:52,APPROVED_CR,VERIFIED,11/09/2011,43069.0,0,0,null,null,

以下是我正在试图加载表

CREATE TABLE [dbo].[BrowseNotes]( [LoanType] [nvarchar](25) NULL, [Maturity] [tinyint] NULL, [LoanClass] [nvarchar](2) NULL, [Borrower] [int] NULL, [LoanStatus] [nvarchar](25) NULL, [TimeLeftBeforeExpiration] [int] NULL, [MonthlyPayment] [smallmoney] NULL, [LoanMaturity] [nvarchar](10) NULL, [JobTenureYearsString] [nvarchar](15) NULL, [AmountToInvest] [smallmoney] NULL, [AmountMissingToClose] [smallmoney] NULL, [NumberOfPayments] [tinyint] NULL, [Id] [int] NULL, [State] [char](2) NULL, [Type] [tinyint] NULL, [Status] [tinyint] NULL, [Aid] [int] NULL, [Amount] [smallmoney] NULL, [Duration] [tinyint] NULL, [StartD] [datetime] NULL, [IntRate] [decimal](18, 0) NULL, [Grade] [char](1) NULL, [Purpose] [nvarchar](25) NULL, [HousingStatus] [nvarchar](25) NULL, [JobTenure] [tinyint] NULL, [Income] [money] NULL, [CreditClassId] [smallint] NULL, [City] [nvarchar](255) NULL, [UnfundedAmnt] [smallmoney] NULL, [Fico] [nvarchar](10) NULL, [OpenCreditLines] [tinyint] NULL, [TotalCreditLines] [tinyint] NULL, [Inq6Months] [tinyint] NULL, [RevolvUtil] [decimal](18, 0) NULL, [FundedPercentage] [decimal](18, 0) NULL, [FundedAmount] [smallmoney] NULL, [EmpStatus] [nvarchar](25) NULL, [JobTitle] [nvarchar](255) NULL, [AppDate] [datetime] NULL, [AppAmount] [money] NULL, [Employer] [nvarchar](255) NULL, [DelinquentAmount] [money] NULL, [EarliestCreditLine] [datetime] NULL, [PubRecords] [tinyint] NULL, [DTI] [decimal](18, 0) NULL, [AppExpiration] [datetime] NULL, [LapStatus] [nvarchar](25) NULL, [IncomeVStatus] [nvarchar](25) NULL, [CreditReportD] [datetime] NULL, [RevolvCreditBal] [money] NULL, [AccntsNowDelinquent] [tinyint] NULL, [Delinquencies2Yrs] [tinyint] NULL, [MnthsSinceLastDelinquency] [nvarchar](10) NULL, [MnthsSinceLastRecord] [nvarchar](10) NULL )

--------------解决方案-------------

什么是数据库中的表? 尝试完全限定的表名即

`mydb.dbo.BrowseNotes`

虽然它肯定听起来像它无法识别ROWTERMINATOR。

我知道这是在未来waaaaaay晚了,但我想通了如何做到这一点。

DECLARE @sql varchar(1000)

set @sql = '
BULK
INSERT BrowseNotes
FROM "C:\Users\Jarek\browseNotes2.csv"
WITH (
FIELDTERMINATOR = ",",
ROWTERMINATOR = "' + char(10) + '"
)'

exec(@sql)
GO

这个脚本的工作原理是迫使ROWTERMINATOR以文字'0A'(换行)。 这既适用于\ r \ n和\ n终止的数据。 我也建议使用管道字符(或任何不包含在你的数据)的FIELDTERMINATOR。 BULK INSERT是不是很宽容数据嵌入式领域终结器。

此外,加入FIRSTROW的声明不跳过字段验证的第一行。 所以,你必须导入之前剥去头,不只是跳过它们。

分类:SQL服务器 时间:2015-03-15 人气:1
分享到:

相关文章

Copyright (C) 55228885.com, All Rights Reserved.

55228885 版权所有 京ICP备15002868号

processed in 2.073 (s). 10 q(s)