Question

I m trying to load data from a csv file and keep getting these errors. Am I missing some params in the bulk insert script or do I need to modify the file before I attempt this?

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)".

Here s the script

BULK
INSERT BrowseNotes
FROM  C:UsersJarekrowseNotes2.csv 
WITH
(
FIELDTERMINATOR =  , 
, ROWTERMINATOR =  
 
)

这里是从档案中抽取的浏览量,在试图装货之前删除第一行。浏览量以“、/n”结束。我尝试用/r/n取代/n,删除最后的 com。仍有同样的错误。

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,

页: 1

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
)

Answer 1

表中有哪些数据库? 完全符合您的表格名称,即:

`mydb.dbo.BrowseNotes`

虽然这肯定与不承认《职权范围》一样。

Answer 2

我知道,这是很晚才来到瓦阿阿阿艾,但我知道如何这样做。

DECLARE @sql varchar(1000)

set @sql =  
BULK
INSERT BrowseNotes
FROM "C:UsersJarekrowseNotes2.csv"
WITH (
  FIELDTERMINATOR = ",",
  ROWTERMINATOR = "  + char(10) +  "
) 

exec(@sql)
GO

This script works by forcing the rowterminator to a literal 0A (linefeed). This works for both and terminated data. I would also suggest using a pipe character (or anything not contained in your data) for a fieldterminator. BULK INSERT is not very tolerant of embedded field terminators in the data.

Also, adding FIRSTROW to the statement does not skip field validation for the first row. So you have to strip the headers before import, not just skip them.

友情链接