程序员经验分享-hwhale

将 5 gig 文件导入表时出错

2024-06-03

我正在尝试批量插入表：

use SalesDWH

go


BULK INSERT dbo.npi
 FROM 'S:\tmp\npi.csv' WITH (FIELDTERMINATOR=',',ROWTERMINATOR='\n',lastrow=200,firstrow=2)

并收到此错误：

Msg 4866, Level 16, State 8, Line 3
The bulk load failed. The column is too long in the data file for row 1, column 329. Verify that the field terminator and row terminator are specified correctly.
Msg 7301, Level 16, State 2, Line 3
Cannot obtain the required interface ("IID_IColumnsInfo") from OLE DB provider "BULK" for linked server "(null)".

我还尝试使用sql导入和导出向导来导入文件。它是逗号分隔的，我收到以下错误：

Executing (Error)
Messages
Error 0xc02020a1: Data Flow Task 1: Data conversion failed. The data conversion for column "Other Provider Identifier Issuer_5" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".
 (SQL Server Import and Export Wizard)

Error 0xc020902a: Data Flow Task 1: The "output column "Other Provider Identifier Issuer_5" (514)" failed because truncation occurred, and the truncation row disposition on "output column "Other Provider Identifier Issuer_5" (514)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.
 (SQL Server Import and Export Wizard)

Error 0xc0202092: Data Flow Task 1: An error occurred while processing file "S:\tmp\nppes\npidata_20050523-20111114.csv" on data row 79.
 (SQL Server Import and Export Wizard)

Error 0xc0047038: Data Flow Task 1: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED.  The PrimeOutput method on component "Source - npidata_20050523-20111114_csv" (1) returned error code 0xC0202092.  The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.  There may be error messages posted before this with more information about the failure.
 (SQL Server Import and Export Wizard)

我知道我的领域绝对足够大。这是表的创建语句：

USE [SalesDWH]
GO

/****** Object:  Table [dbo].[npi]    Script Date: 12/30/2011 15:28:42 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[npi](
    [NPI] [varchar](80) NULL,
    [Entity Type Code] [varchar](80) NULL,
    [Replacement NPI] [varchar](80) NULL,
    [Employer Identification Number (EIN)] [varchar](80) NULL,
    [Provider Organization Name (Legal Business Name)] [varchar](80) NULL,
    [Provider Last Name (Legal Name)] [varchar](80) NULL,
    [Provider First Name] [varchar](80) NULL,
    [Provider Middle Name] [varchar](80) NULL,
    [Provider Name Prefix Text] [varchar](80) NULL,
    [Provider Name Suffix Text] [varchar](80) NULL,
    [Provider Credential Text] [varchar](80) NULL,
    [Provider Other Organization Name] [varchar](80) NULL,
    [Provider Other Organization Name Type Code] [varchar](80) NULL,
    [Provider Other Last Name] [varchar](80) NULL,
    [Provider Other First Name] [varchar](80) NULL,
    [Provider Other Middle Name] [varchar](80) NULL,
    [Provider Other Name Prefix Text] [varchar](80) NULL,
    [Provider Other Name Suffix Text] [varchar](80) NULL,
    [Provider Other Credential Text] [varchar](80) NULL,
    [Provider Other Last Name Type Code] [varchar](80) NULL,
    [Provider First Line Business Mailing Address] [varchar](80) NULL,
    [Provider Second Line Business Mailing Address] [varchar](80) NULL,
    [Provider Business Mailing Address City Name] [varchar](80) NULL,
    [Provider Business Mailing Address State Name] [varchar](80) NULL,
    [Provider Business Mailing Address Postal Code] [varchar](80) NULL,
    [Provider Business Mailing Address Country Code (If outside U S )] [varchar](80) NULL,
    [Provider Business Mailing Address Telephone Number] [varchar](80) NULL,
    [Provider Business Mailing Address Fax Number] [varchar](80) NULL,
    [Provider First Line Business Practice Location Address] [varchar](80) NULL,
    [Provider Second Line Business Practice Location Address] [varchar](80) NULL,
    [Provider Business Practice Location Address City Name] [varchar](80) NULL,
    [Provider Business Practice Location Address State Name] [varchar](80) NULL,
    [Provider Business Practice Location Address Postal Code] [varchar](80) NULL,
    [Provider Business Practice Location Address Country Code (If outside U S )] [varchar](80) NULL,
    [Provider Business Practice Location Address Telephone Number] [varchar](80) NULL,
    [Provider Business Practice Location Address Fax Number] [varchar](80) NULL,
    [Provider Enumeration Date] [varchar](80) NULL,
    [Last Update Date] [varchar](80) NULL,
    [NPI Deactivation Reason Code] [varchar](80) NULL,
    [NPI Deactivation Date] [varchar](80) NULL,
    [NPI Reactivation Date] [varchar](80) NULL,
    [Provider Gender Code] [varchar](80) NULL,
    [Authorized Official Last Name] [varchar](80) NULL,
    [Authorized Official First Name] [varchar](80) NULL,
    [Authorized Official Middle Name] [varchar](80) NULL,
    [Authorized Official Title or Position] [varchar](80) NULL,
    [Authorized Official Telephone Number] [varchar](80) NULL,
    [Healthcare Provider Taxonomy Code_1] [varchar](80) NULL,
    [Provider License Number_1] [varchar](80) NULL,
    [Provider License Number State Code_1] [varchar](80) NULL,
    [Healthcare Provider Primary Taxonomy Switch_1] [varchar](80) NULL,
    [Healthcare Provider Taxonomy Code_2] [varchar](80) NULL,
    [Provider License Number_2] [varchar](80) NULL,
    [Provider License Number State Code_2] [varchar](80) NULL,
    [Healthcare Provider Primary Taxonomy Switch_2] [varchar](80) NULL,
    [Healthcare Provider Taxonomy Code_3] [varchar](80) NULL,
    [Provider License Number_3] [varchar](80) NULL,
    [Provider License Number State Code_3] [varchar](80) NULL,
    [Healthcare Provider Primary Taxonomy Switch_3] [varchar](80) NULL,
    [Healthcare Provider Taxonomy Code_4] [varchar](80) NULL,
    [Provider License Number_4] [varchar](80) NULL,
    [Provider License Number State Code_4] [varchar](80) NULL,
    [Healthcare Provider Primary Taxonomy Switch_4] [varchar](80) NULL,
    [Healthcare Provider Taxonomy Code_5] [varchar](80) NULL,
    [Provider License Number_5] [varchar](80) NULL,
    [Provider License Number State Code_5] [varchar](80) NULL,
    [Healthcare Provider Primary Taxonomy Switch_5] [varchar](80) NULL,
    [Healthcare Provider Taxonomy Code_6] [varchar](80) NULL,
    [Provider License Number_6] [varchar](80) NULL,
    [Provider License Number State Code_6] [varchar](80) NULL,
    [Healthcare Provider Primary Taxonomy Switch_6] [varchar](80) NULL,
    [Healthcare Provider Taxonomy Code_7] [varchar](80) NULL,
    [Provider License Number_7] [varchar](80) NULL,
    [Provider License Number State Code_7] [varchar](80) NULL,
    [Healthcare Provider Primary Taxonomy Switch_7] [varchar](80) NULL,
    [Healthcare Provider Taxonomy Code_8] [varchar](80) NULL,
    [Provider License Number_8] [varchar](80) NULL,
    [Provider License Number State Code_8] [varchar](80) NULL,
    [Healthcare Provider Primary Taxonomy Switch_8] [varchar](80) NULL,
    [Healthcare Provider Taxonomy Code_9] [varchar](80) NULL,
    [Provider License Number_9] [varchar](80) NULL,
    [Provider License Number State Code_9] [varchar](80) NULL,
    [Healthcare Provider Primary Taxonomy Switch_9] [varchar](80) NULL,
    [Healthcare Provider Taxonomy Code_10] [varchar](80) NULL,
    [Provider License Number_10] [varchar](80) NULL,
    [Provider License Number State Code_10] [varchar](80) NULL,
    [Healthcare Provider Primary Taxonomy Switch_10] [varchar](80) NULL,
    [Healthcare Provider Taxonomy Code_11] [varchar](80) NULL,
    [Provider License Number_11] [varchar](80) NULL,
    [Provider License Number State Code_11] [varchar](80) NULL,
    [Healthcare Provider Primary Taxonomy Switch_11] [varchar](80) NULL,
    [Healthcare Provider Taxonomy Code_12] [varchar](80) NULL,
    [Provider License Number_12] [varchar](80) NULL,
    [Provider License Number State Code_12] [varchar](80) NULL,
    [Healthcare Provider Primary Taxonomy Switch_12] [varchar](80) NULL,
    [Healthcare Provider Taxonomy Code_13] [varchar](80) NULL,
    [Provider License Number_13] [varchar](80) NULL,
    [Provider License Number State Code_13] [varchar](80) NULL,
    [Healthcare Provider Primary Taxonomy Switch_13] [varchar](80) NULL,
    [Healthcare Provider Taxonomy Code_14] [varchar](80) NULL,
    [Provider License Number_14] [varchar](80) NULL,
    [Provider License Number State Code_14] [varchar](80) NULL,
    [Healthcare Provider Primary Taxonomy Switch_14] [varchar](80) NULL,
    [Healthcare Provider Taxonomy Code_15] [varchar](80) NULL,
    [Provider License Number_15] [varchar](80) NULL,
    [Provider License Number State Code_15] [varchar](80) NULL,
    [Healthcare Provider Primary Taxonomy Switch_15] [varchar](80) NULL,
    [Other Provider Identifier_1] [varchar](80) NULL,
    [Other Provider Identifier Type Code_1] [varchar](80) NULL,
    [Other Provider Identifier State_1] [varchar](80) NULL,
    [Other Provider Identifier Issuer_1] [varchar](80) NULL,
    [Other Provider Identifier_2] [varchar](80) NULL,
    [Other Provider Identifier Type Code_2] [varchar](80) NULL,
    [Other Provider Identifier State_2] [varchar](80) NULL,
    [Other Provider Identifier Issuer_2] [varchar](80) NULL,
    [Other Provider Identifier_3] [varchar](80) NULL,
    [Other Provider Identifier Type Code_3] [varchar](80) NULL,
    [Other Provider Identifier State_3] [varchar](80) NULL,
    [Other Provider Identifier Issuer_3] [varchar](80) NULL,
    [Other Provider Identifier_4] [varchar](80) NULL,
    [Other Provider Identifier Type Code_4] [varchar](80) NULL,
    [Other Provider Identifier State_4] [varchar](80) NULL,
    [Other Provider Identifier Issuer_4] [varchar](80) NULL,
    [Other Provider Identifier_5] [varchar](80) NULL,
    [Other Provider Identifier Type Code_5] [varchar](80) NULL,
    [Other Provider Identifier State_5] [varchar](80) NULL,
    [Other Provider Identifier Issuer_5] [varchar](80) NULL,
    [Other Provider Identifier_6] [varchar](80) NULL,
    [Other Provider Identifier Type Code_6] [varchar](80) NULL,
    [Other Provider Identifier State_6] [varchar](80) NULL,
    [Other Provider Identifier Issuer_6] [varchar](80) NULL,
    [Other Provider Identifier_7] [varchar](80) NULL,
    [Other Provider Identifier Type Code_7] [varchar](80) NULL,
    [Other Provider Identifier State_7] [varchar](80) NULL,
    [Other Provider Identifier Issuer_7] [varchar](80) NULL,
    [Other Provider Identifier_8] [varchar](80) NULL,
    [Other Provider Identifier Type Code_8] [varchar](80) NULL,
    [Other Provider Identifier State_8] [varchar](80) NULL,
    [Other Provider Identifier Issuer_8] [varchar](80) NULL,
    [Other Provider Identifier_9] [varchar](80) NULL,
    [Other Provider Identifier Type Code_9] [varchar](80) NULL,
    [Other Provider Identifier State_9] [varchar](80) NULL,
    [Other Provider Identifier Issuer_9] [varchar](80) NULL,
    [Other Provider Identifier_10] [varchar](80) NULL,
    [Other Provider Identifier Type Code_10] [varchar](80) NULL,
    [Other Provider Identifier State_10] [varchar](80) NULL,
    [Other Provider Identifier Issuer_10] [varchar](80) NULL,
    [Other Provider Identifier_11] [varchar](80) NULL,
    [Other Provider Identifier Type Code_11] [varchar](80) NULL,
    [Other Provider Identifier State_11] [varchar](80) NULL,
    [Other Provider Identifier Issuer_11] [varchar](80) NULL,
    [Other Provider Identifier_12] [varchar](80) NULL,
    [Other Provider Identifier Type Code_12] [varchar](80) NULL,
    [Other Provider Identifier State_12] [varchar](80) NULL,
    [Other Provider Identifier Issuer_12] [varchar](80) NULL,
    [Other Provider Identifier_13] [varchar](80) NULL,
    [Other Provider Identifier Type Code_13] [varchar](80) NULL,
    [Other Provider Identifier State_13] [varchar](80) NULL,
    [Other Provider Identifier Issuer_13] [varchar](80) NULL,
    [Other Provider Identifier_14] [varchar](80) NULL,
    [Other Provider Identifier Type Code_14] [varchar](80) NULL,
    [Other Provider Identifier State_14] [varchar](80) NULL,
    [Other Provider Identifier Issuer_14] [varchar](80) NULL,
    [Other Provider Identifier_15] [varchar](80) NULL,
    [Other Provider Identifier Type Code_15] [varchar](80) NULL,
    [Other Provider Identifier State_15] [varchar](80) NULL,
    [Other Provider Identifier Issuer_15] [varchar](80) NULL,
    [Other Provider Identifier_16] [varchar](80) NULL,
    [Other Provider Identifier Type Code_16] [varchar](80) NULL,
    [Other Provider Identifier State_16] [varchar](80) NULL,
    [Other Provider Identifier Issuer_16] [varchar](80) NULL,
    [Other Provider Identifier_17] [varchar](80) NULL,
    [Other Provider Identifier Type Code_17] [varchar](80) NULL,
    [Other Provider Identifier State_17] [varchar](80) NULL,
    [Other Provider Identifier Issuer_17] [varchar](80) NULL,
    [Other Provider Identifier_18] [varchar](80) NULL,
    [Other Provider Identifier Type Code_18] [varchar](80) NULL,
    [Other Provider Identifier State_18] [varchar](80) NULL,
    [Other Provider Identifier Issuer_18] [varchar](80) NULL,
    [Other Provider Identifier_19] [varchar](80) NULL,
    [Other Provider Identifier Type Code_19] [varchar](80) NULL,
    [Other Provider Identifier State_19] [varchar](80) NULL,
    [Other Provider Identifier Issuer_19] [varchar](80) NULL,
    [Other Provider Identifier_20] [varchar](80) NULL,
    [Other Provider Identifier Type Code_20] [varchar](80) NULL,
    [Other Provider Identifier State_20] [varchar](80) NULL,
    [Other Provider Identifier Issuer_20] [varchar](80) NULL,
    [Other Provider Identifier_21] [varchar](80) NULL,
    [Other Provider Identifier Type Code_21] [varchar](80) NULL,
    [Other Provider Identifier State_21] [varchar](80) NULL,
    [Other Provider Identifier Issuer_21] [varchar](80) NULL,
    [Other Provider Identifier_22] [varchar](80) NULL,
    [Other Provider Identifier Type Code_22] [varchar](80) NULL,
    [Other Provider Identifier State_22] [varchar](80) NULL,
    [Other Provider Identifier Issuer_22] [varchar](80) NULL,
    [Other Provider Identifier_23] [varchar](80) NULL,
    [Other Provider Identifier Type Code_23] [varchar](80) NULL,
    [Other Provider Identifier State_23] [varchar](80) NULL,
    [Other Provider Identifier Issuer_23] [varchar](80) NULL,
    [Other Provider Identifier_24] [varchar](80) NULL,
    [Other Provider Identifier Type Code_24] [varchar](80) NULL,
    [Other Provider Identifier State_24] [varchar](80) NULL,
    [Other Provider Identifier Issuer_24] [varchar](80) NULL,
    [Other Provider Identifier_25] [varchar](80) NULL,
    [Other Provider Identifier Type Code_25] [varchar](80) NULL,
    [Other Provider Identifier State_25] [varchar](80) NULL,
    [Other Provider Identifier Issuer_25] [varchar](80) NULL,
    [Other Provider Identifier_26] [varchar](80) NULL,
    [Other Provider Identifier Type Code_26] [varchar](80) NULL,
    [Other Provider Identifier State_26] [varchar](80) NULL,
    [Other Provider Identifier Issuer_26] [varchar](80) NULL,
    [Other Provider Identifier_27] [varchar](80) NULL,
    [Other Provider Identifier Type Code_27] [varchar](80) NULL,
    [Other Provider Identifier State_27] [varchar](80) NULL,
    [Other Provider Identifier Issuer_27] [varchar](80) NULL,
    [Other Provider Identifier_28] [varchar](80) NULL,
    [Other Provider Identifier Type Code_28] [varchar](80) NULL,
    [Other Provider Identifier State_28] [varchar](80) NULL,
    [Other Provider Identifier Issuer_28] [varchar](80) NULL,
    [Other Provider Identifier_29] [varchar](80) NULL,
    [Other Provider Identifier Type Code_29] [varchar](80) NULL,
    [Other Provider Identifier State_29] [varchar](80) NULL,
    [Other Provider Identifier Issuer_29] [varchar](80) NULL,
    [Other Provider Identifier_30] [varchar](80) NULL,
    [Other Provider Identifier Type Code_30] [varchar](80) NULL,
    [Other Provider Identifier State_30] [varchar](80) NULL,
    [Other Provider Identifier Issuer_30] [varchar](80) NULL,
    [Other Provider Identifier_31] [varchar](80) NULL,
    [Other Provider Identifier Type Code_31] [varchar](80) NULL,
    [Other Provider Identifier State_31] [varchar](80) NULL,
    [Other Provider Identifier Issuer_31] [varchar](80) NULL,
    [Other Provider Identifier_32] [varchar](80) NULL,
    [Other Provider Identifier Type Code_32] [varchar](80) NULL,
    [Other Provider Identifier State_32] [varchar](80) NULL,
    [Other Provider Identifier Issuer_32] [varchar](80) NULL,
    [Other Provider Identifier_33] [varchar](80) NULL,
    [Other Provider Identifier Type Code_33] [varchar](80) NULL,
    [Other Provider Identifier State_33] [varchar](80) NULL,
    [Other Provider Identifier Issuer_33] [varchar](80) NULL,
    [Other Provider Identifier_34] [varchar](80) NULL,
    [Other Provider Identifier Type Code_34] [varchar](80) NULL,
    [Other Provider Identifier State_34] [varchar](80) NULL,
    [Other Provider Identifier Issuer_34] [varchar](80) NULL,
    [Other Provider Identifier_35] [varchar](80) NULL,
    [Other Provider Identifier Type Code_35] [varchar](80) NULL,
    [Other Provider Identifier State_35] [varchar](80) NULL,
    [Other Provider Identifier Issuer_35] [varchar](80) NULL,
    [Other Provider Identifier_36] [varchar](80) NULL,
    [Other Provider Identifier Type Code_36] [varchar](80) NULL,
    [Other Provider Identifier State_36] [varchar](80) NULL,
    [Other Provider Identifier Issuer_36] [varchar](80) NULL,
    [Other Provider Identifier_37] [varchar](80) NULL,
    [Other Provider Identifier Type Code_37] [varchar](80) NULL,
    [Other Provider Identifier State_37] [varchar](80) NULL,
    [Other Provider Identifier Issuer_37] [varchar](80) NULL,
    [Other Provider Identifier_38] [varchar](80) NULL,
    [Other Provider Identifier Type Code_38] [varchar](80) NULL,
    [Other Provider Identifier State_38] [varchar](80) NULL,
    [Other Provider Identifier Issuer_38] [varchar](80) NULL,
    [Other Provider Identifier_39] [varchar](80) NULL,
    [Other Provider Identifier Type Code_39] [varchar](80) NULL,
    [Other Provider Identifier State_39] [varchar](80) NULL,
    [Other Provider Identifier Issuer_39] [varchar](80) NULL,
    [Other Provider Identifier_40] [varchar](80) NULL,
    [Other Provider Identifier Type Code_40] [varchar](80) NULL,
    [Other Provider Identifier State_40] [varchar](80) NULL,
    [Other Provider Identifier Issuer_40] [varchar](80) NULL,
    [Other Provider Identifier_41] [varchar](80) NULL,
    [Other Provider Identifier Type Code_41] [varchar](80) NULL,
    [Other Provider Identifier State_41] [varchar](80) NULL,
    [Other Provider Identifier Issuer_41] [varchar](80) NULL,
    [Other Provider Identifier_42] [varchar](80) NULL,
    [Other Provider Identifier Type Code_42] [varchar](80) NULL,
    [Other Provider Identifier State_42] [varchar](80) NULL,
    [Other Provider Identifier Issuer_42] [varchar](80) NULL,
    [Other Provider Identifier_43] [varchar](80) NULL,
    [Other Provider Identifier Type Code_43] [varchar](80) NULL,
    [Other Provider Identifier State_43] [varchar](80) NULL,
    [Other Provider Identifier Issuer_43] [varchar](80) NULL,
    [Other Provider Identifier_44] [varchar](80) NULL,
    [Other Provider Identifier Type Code_44] [varchar](80) NULL,
    [Other Provider Identifier State_44] [varchar](80) NULL,
    [Other Provider Identifier Issuer_44] [varchar](80) NULL,
    [Other Provider Identifier_45] [varchar](80) NULL,
    [Other Provider Identifier Type Code_45] [varchar](80) NULL,
    [Other Provider Identifier State_45] [varchar](80) NULL,
    [Other Provider Identifier Issuer_45] [varchar](80) NULL,
    [Other Provider Identifier_46] [varchar](80) NULL,
    [Other Provider Identifier Type Code_46] [varchar](80) NULL,
    [Other Provider Identifier State_46] [varchar](80) NULL,
    [Other Provider Identifier Issuer_46] [varchar](80) NULL,
    [Other Provider Identifier_47] [varchar](80) NULL,
    [Other Provider Identifier Type Code_47] [varchar](80) NULL,
    [Other Provider Identifier State_47] [varchar](80) NULL,
    [Other Provider Identifier Issuer_47] [varchar](80) NULL,
    [Other Provider Identifier_48] [varchar](80) NULL,
    [Other Provider Identifier Type Code_48] [varchar](80) NULL,
    [Other Provider Identifier State_48] [varchar](80) NULL,
    [Other Provider Identifier Issuer_48] [varchar](80) NULL,
    [Other Provider Identifier_49] [varchar](80) NULL,
    [Other Provider Identifier Type Code_49] [varchar](80) NULL,
    [Other Provider Identifier State_49] [varchar](80) NULL,
    [Other Provider Identifier Issuer_49] [varchar](80) NULL,
    [Other Provider Identifier_50] [varchar](80) NULL,
    [Other Provider Identifier Type Code_50] [varchar](80) NULL,
    [Other Provider Identifier State_50] [varchar](80) NULL,
    [Other Provider Identifier Issuer_50] [varchar](80) NULL,
    [Is Sole Proprietor] [varchar](80) NULL,
    [Is Organization Subpart] [varchar](80) NULL,
    [Parent Organization LBN] [varchar](80) NULL,
    [Parent Organization TIN] [varchar](80) NULL,
    [Authorized Official Name Prefix Text] [varchar](80) NULL,
    [Authorized Official Name Suffix Text] [varchar](80) NULL,
    [Authorized Official Credential Text] [varchar](80) NULL,
    [Healthcare Provider Taxonomy Group_1] [varchar](80) NULL,
    [Healthcare Provider Taxonomy Group_2] [varchar](80) NULL,
    [Healthcare Provider Taxonomy Group_3] [varchar](80) NULL,
    [Healthcare Provider Taxonomy Group_4] [varchar](80) NULL,
    [Healthcare Provider Taxonomy Group_5] [varchar](80) NULL,
    [Healthcare Provider Taxonomy Group_6] [varchar](80) NULL,
    [Healthcare Provider Taxonomy Group_7] [varchar](80) NULL,
    [Healthcare Provider Taxonomy Group_8] [varchar](80) NULL,
    [Healthcare Provider Taxonomy Group_9] [varchar](80) NULL,
    [Healthcare Provider Taxonomy Group_10] [varchar](80) NULL,
    [Healthcare Provider Taxonomy Group_11] [varchar](80) NULL,
    [Healthcare Provider Taxonomy Group_12] [varchar](80) NULL,
    [Healthcare Provider Taxonomy Group_13] [varchar](80) NULL,
    [Healthcare Provider Taxonomy Group_14] [varchar](80) NULL,
    [Healthcare Provider Taxonomy Group_15] [varchar](80) NULL
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

我确信没有一列超过 80 个字符。我也尝试将其更改为 500 个字符，但仍然收到相同的错误。

我究竟做错了什么？

为什么我无法导入该文件？

以下是其中一行的示例： "139236748349","1","","","","GRUrt3NERT","乔治","M","博士","","医学博士","","","", "","","","","","","7234900 FANNIN ST","套房 4423400","休斯顿","德克萨斯州","770523442900","美国","713334351227000"," 713g25127082","792300 范宁街","套房 423400","休斯顿","德克萨斯州","7705ef242900","美国","7132225127000","713512ff27082","05/23/2005","01/22 /2009","","","","M","","","","","","207VE0f2f102X","E02f945","TX","Y","" ,"","","","","","","","","","","","","","","",""," ","","","","","","","","","","","","","","","","", ""、""、""、""、""、""、""、""、""、""、""、""、""、""、""、""、"" ,"","","","","","82f4380J","04","TX","FT.BEND/MONT","B2f33159","02","TX","" ,"8432335J","04","TX","BRAZORIA","8dd2047G","01","TX","蓝十字与蓝盾","84sd2282J","04","TX","哈里斯县","","","","","","","","","","","","","","",""," ","","","","","","","","","","","","","","","","", ""、""、""、""、""、""、""、""、""、""、""、""、""、""、""、""、"" ,"","","","","","","","","","","","","","","",""," ","","","","","","","","","","","","","","","","", ""、""、""、""、""、""、""、""、""、""、""、""、""、""、""、""、"" ,"","","","","","","","","","","","","","","",""," ","","","","","","","","","","","","","","","","", ""、""、""、""、""、""、""、""、""、""、""、""、""、""、""、""、"" ,"","","","","","","","","","","","","","","",""," ","","","","","","","","","","","","","","","N","" ,"","","","","","","","","","","","","","","",""," ”、“”、“”、“”

只是更新...我将所有字段更改为nvarchar(max)

仍然遇到这个可怕的错误：

Operation stopped...

- Initializing Data Flow Task (Success)

- Initializing Connections (Success)

- Setting SQL Command (Success)

- Setting Source Connection (Success)

- Setting Destination Connection (Success)

- Validating (Success)
    Messages
    * Warning 0x80049304: Data Flow Task 1: Warning: Could not open global shared memory to communicate with performance DLL; data flow performance counters are not available.  To resolve, run this package as an administrator, or on the system's console.
     (SQL Server Import and Export Wizard)


- Prepare for Execute (Success)

- Pre-execute (Success)
    Messages
    * Information 0x402090dc: Data Flow Task 1: The processing of file "S:\tmp\nppes\npidata_20050523-20111114.csv" has started.
     (SQL Server Import and Export Wizard)


- Executing (Error)
    Messages
    * Error 0xc02020a1: Data Flow Task 1: Data conversion failed. The data conversion for column "Other Provider Identifier Issuer_5" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".
     (SQL Server Import and Export Wizard)

    * Error 0xc020902a: Data Flow Task 1: The "output column "Other Provider Identifier Issuer_5" (514)" failed because truncation occurred, and the truncation row disposition on "output column "Other Provider Identifier Issuer_5" (514)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.
     (SQL Server Import and Export Wizard)

    * Error 0xc0202092: Data Flow Task 1: An error occurred while processing file "S:\tmp\nppes\npidata_20050523-20111114.csv" on data row 79.
     (SQL Server Import and Export Wizard)

    * Error 0xc0047038: Data Flow Task 1: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED.  The PrimeOutput method on component "Source - npidata_20050523-20111114_csv" (1) returned error code 0xC0202092.  The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.  There may be error messages posted before this with more information about the failure.
     (SQL Server Import and Export Wizard)


- Copying to [dbo].[npidata_20050523-20111114] (Stopped)

- Post-execute (Success)
    Messages
    * Information 0x402090dd: Data Flow Task 1: The processing of file "S:\tmp\nppes\npidata_20050523-20111114.csv" has ended.
     (SQL Server Import and Export Wizard)

    * Information 0x402090df: Data Flow Task 1: The final commit for the data insertion in "component "Destination - npidata_20050523-20111114" (1326)" has started.
     (SQL Server Import and Export Wizard)

    * Information 0x402090e0: Data Flow Task 1: The final commit for the data insertion  in "component "Destination - npidata_20050523-20111114" (1326)" has ended.
     (SQL Server Import and Export Wizard)

    * Information 0x4004300b: Data Flow Task 1: "component "Destination - npidata_20050523-20111114" (1326)" wrote 58 rows.
     (SQL Server Import and Export Wizard)

我将通过外部脚本验证导致错误的行 - 比如一些快速的 powershell。例如

$data = @(Import-Csv .\partialFile.csv )
$row = $data[0]

# how many columns do we have?
$row | gm -membertype NoteProperty | Measure-Object 

# columns larger than 80 chars
$row | gm -membertype NoteProperty | ? { ($row."$($_.Name)").Length -gt 80 }
本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系:hwhale#tublm.com(使用前将#替换为@)

sql

sqlserver

sqlserver2008

将 5 gig 文件导入表时出错 的相关文章

  • 选择语句REF oracle

    我需要一些帮助来创建将使用引用的选择语句 我设法很好地插入了值 但是当我尝试使用 where 语句提取值时 输出要么是数据类型错误 要么会输出两个表以及它们都包含的数据 这只是一个例子 Create or replace table1 Ty

  • ssis将N个表从源服务器加载到目标服务器的最佳实践

    我需要将 N 个 大约 50 个 表从源数据库加载到目标数据库 每个表都与其他表不同 因此元数据不同 我想我可以使用父 pkg 来调用子 pkg 其中每个子 pkg 都有简单的结构 例如 DFT 来映射要导入的表 1 个子 pkg gt 1

  • 在数据库中有效存储商品位置（用于订购）

    设想 有一个用户拥有的电影数据库 电影显示在一个名为 我的电影 的页面上 电影可以按照用户想要的顺序显示 例如 位置 1 为 搏击俱乐部 位置 3 为 Drive 依此类推 显而易见的解决方案是存储每个项目的位置 例如 电影 ID 用户 I

  • Redis 与 SQL Server 性能对比

    应用程序性能是使用缓存而不是关系数据库的主要原因之一 因为它以键值对的形式将数据存储在内存中 所以我们可以将经常访问的不经常更改的数据存储在缓存中 从缓存中读取比从数据库中读取要快得多 Redis 是分布式缓存市场上最好的解决方案之一 我正

  • 查找缺失值

    我有一个表 有 2 个重要的列 DocEntry WebId 样本数据就像 DocEntry WebId 1 S001 2 S002 3 S003 4 S005 现在我们可以注意到 在 WebId 列中 S004 丢失了 我们如何通过查询找

  • 有向图 SQL

    我有以下数据集 它表示有向图中的节点 CREATE TABLE nodes NODE FROM VARCHAR2 10 NODE TO VARCHAR2 10 INSERT INTO nodes VALUES GT TG INSERT IN

  • SQL Server 2014执行计划创建需要很长时间（旧版本中很快）

    我在 SQL Server 2014 中遇到一个查询问题 第一次运行该查询时 需要很长时间才能生成执行计划 奇怪的是 它在 SQL Server 的所有早期版本 2012 2008 R2 2008 等 中都运行良好 它似乎与所涉及的表之一上

  • SQL Server 数据归档解决方案

    我正在寻找一种解决方案来存档数据库中存在的数据 我的数据库是 SQL Server 2008 大约有 250 个表 我搜索网络并找到以下链接 http www dbazine com sql sql articles charran13 h

  • SQL Server到Mysql迁移（使用Mysql Workbench）数据传输错误

    我正在使用 Mysql Work bench 6 3 将数据库从 MS Sql server 2008 迁移到 Mysql 在 批量数据传输 期间出错并出现以下警告 这种情况仅发生在像 varchar char 这样的列类型上 当我尝试使用

  • 如何使用 pgAdmin 恢复 postgreSQL 转储文件？

    我有一个 dmp 文件 想要从中恢复数据库 使用 pgAdmin 我该怎么做 在 PgAdmin3 内 在您正在使用的服务器中创建一个新数据库 右键单击该数据库并选择 恢复 使用 浏览器 按钮选择 dmp 文件 选择 恢复 开始恢复数据库

  • 在 JSP 中迭代列表对象

    我正在做一个项目来尝试自学 spring 和 struts 我目前卡在 JSP 页面上 我有一个 pojo 类 其中包含带有 getter setter 的变量 eid 和 ename 我还有一个 sql 中的表 其具有相同的值和六个填充行

  • 从 Visual Studio 调试 SQL Server 2005 中的存储过程？

    我在这里和其他地方看到很多令人沮丧的问题 但没有明确的答案 我试图让存储过程进行调试 但没有成功 客户端 VS2005或VS2008 两者都不起作用 当我从存储过程上下文菜单中选择 步入存储过程 时 我在调试窗口中看到 已被用户取消 这就是

  • 我应该如何优化 .net 代码中对一个简单存储过程的多次调用？

    我有一个非常简单的存储过程 create procedure spFoo v varchar 50 as insert into tbFoo select v 我有 50 个值要插入到 tbFoo 中 这意味着在我的 c 代码中我调用 sp

  • 为什么实体框架 6 在插入后不只选择scope_identity()？

    当您使用 EF 6 1 保存实体时 将生成并执行以下 SQL 代码 exec sp executesql N INSERT dbo Customers Name FirstName VALUES 0 1 SELECT CustomerId

  • 左连接，左表中没有重复行

    请看下面的查询 tbl 目录 Content Id Content Title Content Text 10002 New case Study New case Study 10003 New case Study New case S

  • 如何在 SQL 中编写 where 子句来按一天中的时间过滤 DATETIME 列？

    我有带有 DATETIME 列时间戳的数据 我想将其过滤到 DATETIME 介于上午 9 30 到下午 5 30 之间的任意一天的记录集 最好的方法是什么 更新 更改是因为我需要精确到分钟 而不仅仅是小时 对于那个很抱歉 您始终可以将其编

  • 获取SQL中前2个特殊字符之间的字符

    我有数据在sql 只是要注意 SQL STudio is the IDE like data a 10 b c a 1 b c 我想获取前两个符号之间的数据 Output 10 1 这就是我的方法 SELECT CAST

  • 计算行数并仅获取表中的最后一行

    我有一张桌子叫employeexam其结构和数据是这样的 id course id employee id degree date 1 1 3 8 2013 01 14 2 2 4 15 2013 01 14 3 2 4 17 2013 0

  • 批量更新 SQL Server C#

    我有一个 270k 行的数据库 带有主键mid和一个名为value 我有一个包含中值和值的文本文件 现在我想更新表格 以便将每个值分配给正确的中间值 我当前的方法是从 C 读取文本文件 并为我读取的每一行更新表中的一行 必须有更快的方法来做

  • SQL Server XQuery 返回错误

    我正在 SQL Server 2012 中对 XML 数据类型列执行查询 数据示例如下

随机推荐

  • Django：Haystack 或 ORM

    在一个项目中 我实现了 Haystack 但现在我想知道使用 Haystack 相对于 ORM 有何优缺点 对于我的项目 我需要按书名和 isbn 查找书籍 我想如果你必须进行全文搜索 Haystack 会更有用 或者我是否遗漏了一些东西

  • UseRowNumberForPaging 不是有效的

    我在用着 Microsoft EntityFrameworkCore SqlServer 1 0 0 rc2 final with SQL 2008根据谷歌上找到的一些结果 我只需添加选项 UseRowNmberForPaging 当创建一

  • 如何在 PHP 中通过 array_map(...) 使用数组的数组？ [复制]

    这个问题在这里已经有答案了 PHP 函数array map http php net manual en function array map php期望回调作为第一个参数 或null for 创建数组的数组 http php net ma

  • 如何在 Laravel 中验证文件类型

    我需要验证用户只上传 jpg 我有以下请求类 我制作了所需的图像 但不知道如何检查它是否只是 jpg public function rules return name gt required min 3 sku gt required m

  • 我可以为 Spring Boot 应用程序创建多个入口点吗？

    In 春季启动 需要指定一个主类 它是应用程序的入口点 通常 这是一个具有标准 main 方法的简单类 如下所示 SpringBootApplication public class MySpringApplication public s

  • 找不到 -lAFNetworking Xcode 问题的库

    ld warning directory not found for option L Users kh Library Developer Xcode DerivedData om euxqzibzllbcmndwqrgthdeotiro

  • 如何在ionic框架+ angularjs中调用函数？

    我正在使用 ionic 框架来制作 android 应用程序 我有 cordova5 1版本 first 我使用命令行创建一个项目ionic 启动 myApp 选项卡 我添加了android平台 现在我需要将java代码与javascrip

  • Google App Engine 的 JDO：转义引号

    如何转义 JDO Google App Engine 中的查询参数 例如 如果变量名称可能包含单引号 形式的不安全字符 如何使下一个片段安全 PersistenceManager pm String query select from Pe

  • 使用 jquery 淡入/淡出

    我正在研究我的学生项目 我是新的 jquery 对于该项目 我必须使用 jquery 来增强一些功能 并且我已经学到了很多来执行基本任务 但我陷入了一些非常令人困惑的事情 我的一个脚本实际上在鼠标悬停在功能上时更改了 div 容器的图像 功

  • 导入错误：Mac 上没有名为 bs4 的模块

    今晚我坐下来决定学习如何使用 python 受到这篇网页抓取文章的启发 cam ly danesblog 2011 01 craigslist arbitage 在完成教程后我 1 下载并安装python http www python o

  • 在 printf 参数中提升类型是否危险？

    我的问题源于尝试为多个位深度平台 例如 32 64 构建时尝试使用 printf 来记录内容 一个不断出现的问题是试图在多种架构上打印整数 在 32 位上它会是这样的 printf my int d n myInt 但在 64 位上 必须更

  • 在哪里可以找到所有 C 标准库的源代码？

    我正在寻找所有 C 标准库的完整源代码 也就是说 我正在寻找 stdio h stdlib h string h math h 等的源代码 我想看看它们是如何创建的 我认为这取决于不同的平台 但 Linux 或 Windows 都会受到欢迎

  • 将文件上传到 Google 云端硬盘时出错

    我已经在我的应用程序项目中实现了 Google Drive SDK 并尝试在 Google Drive 中上传图像 但出现错误 我的代码来自Here https developers google com drive quickstart

  • Powershell脚本将多个远程地址添加到防火墙规则

    我正在尝试编写一个脚本 该脚本将循环遍历本地防火墙规则并更新远程地址表 这是我到目前为止所拥有的 它不起作用 应该很简单 所以不确定发生了什么 该脚本运行时没有错误 但实际上并没有更新任何内容 name Get NetFirewallRul

  • 我的终端中的彩色、并排、内联 git diff 输出

    请注意 此问题不是该问题的重复问题 而是该问题的后续问题 当我执行 git diff 时 如何获得并排差异 https stackoverflow com questions 7669963 how can i get a side by

  • 在 Sql Server 中启用 DTD 支持

    我有各种 xml 文档需要存储在数据库列中 这些文档包含对 DTD 的引用 并且 SQL Server 不会导入 xml 因为它存在安全风险 如何在数据库上启用 DTD 支持 以便它可以让我插入 xml 内容 你必须CONVERT首先 MS

  • 通过 Flash 测试网络摄像头

    我正在寻找 测试你的网络摄像头 Flash 电影 例如this one http www sillywebcam com others demo test your webcam这是免费的 可以在封闭的环境中使用 我只需要相机测试 不需要视

  • NSImage 的 ressizedImageWithCapInsets ？

    In UIKit我们有 UIImage resizableImageWithCapInsets UIEdgeInsets capInsets 有类似的东西吗AppKit用于创建可平铺的NSImage NSImage 在 10 10 Yose

  • 自定义窗口上的 Unity 自定义检查器

    我有一个显示对象列表的自定义窗口 每个对象都有一个自定义检查器编辑器 是否可以在自定义窗口内显示自定义检查器 你不能强迫Unity3D将自定义检查器绘制在检查器窗口之外的其他位置 顺便说一句 您可以手动实例化Editor using 编辑器

  • 将 5 gig 文件导入表时出错

    我正在尝试批量插入表 use SalesDWH go BULK INSERT dbo npi FROM S tmp npi csv WITH FIELDTERMINATOR ROWTERMINATOR n lastrow 200 first

热门标签