locked
SQL Express x64 2017 + BULK INSERT = weird error RRS feed

  • Question

  • Hi,

    I tried to do BULK INSERT against CSV file but faced with the following error. Here my steps to reproduce the issue:

    1. Create a destination table:

    CREATE TABLE [dbo].[EvAlaska]
    (
        [EvID] BIGINT NOT NULL PRIMARY KEY IDENTITY, 
        [GeoID] VARCHAR(10) NOT NULL, 
        [Year] smallint NOT NULL, 
        [LocationName] VARCHAR(40) NULL, 
        [ParentLocation] VARCHAR(40) NULL, 
        [Population] int NULL, 
        [PovertyRate] decimal(7,2) NULL, 
        [RenterOccupiedHouseholds] int NULL, 
        [PctRenterOccupied] decimal(4,2) NULL, 
        [MedianGrossRent] int NULL, 
        [MedianHouseholdIncome] int NULL, 
        [MedianPropertyValue] int NULL, 
        [RentBurden] decimal(3,1) NULL, 
        [PctWhite] decimal(4,2) NULL, 
        [PctAfAm] decimal(4,2) NULL, 
        [PctHispanic] decimal(4,2) NULL, 
        PctAmInd decimal(3,1) NULL, 
        PctAsian decimal(3,1) NULL, 
        PctNhPi decimal(3,1) NULL, 
        PctMultiple decimal(3,1) NULL, 
        PctOther decimal(3,1) NULL, 
        [EvictionFilings] int NULL, 
        [Evictions] int NULL, 
        [EvictionRate] decimal(3,1) NULL, 
        [EvictionFilingRate] decimal(3,1) NULL, 
        [LowFlag] bit NULL, 
        [Imputed] bit NULL, 
        [Subbed] bit NULL
    )
    


    2. Download a CSV file to be inserted via the following command:

    curl https://eviction-lab-data-downloads.s3.amazonaws.com/AK/all.csv -o AK.csv

    The received file looks good and can be imported into an Excel table w/o issues. Now I am trying to  have it inserted into the table via T-SQL running on behalf of SA on the target server:

    BULK INSERT EvAlaska
    FROM 'C:\tmp\AK.csv'
    WITH
    (
        FIRSTROW = 2,
        FORMAT = 'CSV', 
        DATAFILETYPE = 'char',
        FIELDTERMINATOR = ',',
        ROWTERMINATOR = '0x0a',
        ERRORFILE = 'C:\tmp\AK-err.csv'
        ,TABLOCK
    )

    But instead of populated table I get the following error:

    Msg 7301, Level 16, State 2, Line 45
    Cannot obtain the required interface ("IID_IColumnsInfo") from OLE DB provider "BULK" for linked server "(null)".

    I have no clue about what to do with all that stuff. Any ideas how to make the script work?

    Saturday, May 9, 2020 1:43 PM

Answers

  • Hi,

    Quickest way is assign all data types as varchar so its easy to do buil import later you can assign them proper data type while moving into main tables with cast function

    ---- This should work please check

    --drop table EvAlaska;

    CREATE TABLE [dbo].[EvAlaska]
    (
        [GeoID] VARCHAR(20) NOT NULL, 
        [Year] varchar(10)NOT NULL, 
        [LocationName] VARCHAR(100) NULL, 
        [ParentLocation] VARCHAR(100) NULL, 
        [Population]  varchar(20) NULL, 
        [PovertyRate] decimal(7,2) NULL, 
        [RenterOccupiedHouseholds] varchar(10)NULL, 
        [PctRenterOccupied] decimal(6,2) NULL, 
        [MedianGrossRent] varchar(20) NULL, 
        [MedianHouseholdIncome] varchar(20) NULL, 
        [MedianPropertyValue] varchar(20) NULL, 
        [RentBurden] varchar(10) NULL, 
        [PctWhite] varchar(10) NULL, 
        [PctAfAm] varchar(10) NULL, 
        [PctHispanic] varchar(10) NULL, 
        PctAmInd varchar(10) NULL, 
        PctAsian varchar(10) NULL, 
        PctNhPi varchar(10) NULL, 
        PctMultiple varchar(10) NULL, 
        PctOther varchar(10) NULL, 
        [EvictionFilings] varchar(20) NULL, 
        [Evictions] varchar(20) NULL, 
        [EvictionRate] varchar(10) NULL, 
        [EvictionFilingRate] varchar(10) NULL, 
        [LowFlag] varchar(10) NULL, 
        [Imputed] varchar(10) NULL, 
        [Subbed] varchar(10) NULL
    )


    BULK INSERT EvAlaska
    FROM 'C:\tmp\AK.csv'
    WITH
    (
        FIRSTROW = 2,
        FORMAT = 'CSV', 
        DATAFILETYPE = 'char',
        FIELDTERMINATOR = ',',
        ROWTERMINATOR = '0x0a',
        ERRORFILE = 'C:\tmp\AK-err.csv'
        ,TABLOCK
    )


    http://uk.linkedin.com/in/ramjaddu

    • Marked as answer by Senglory Saturday, May 9, 2020 6:57 PM
    Saturday, May 9, 2020 4:50 PM

All replies

  • Hi Senglory,

    Please remove identity column from table and try - usually we should import into interim table first with out identity column then push into main table which with identity column.

    Regards

    Ram


    http://uk.linkedin.com/in/ramjaddu

    Saturday, May 9, 2020 4:00 PM
  • Hi Ram,

    Is there any way to specify tbl format for the BULK INSERT command in T-SQL?

    Saturday, May 9, 2020 4:06 PM
  • Hi,

    Quickest way is assign all data types as varchar so its easy to do buil import later you can assign them proper data type while moving into main tables with cast function

    ---- This should work please check

    --drop table EvAlaska;

    CREATE TABLE [dbo].[EvAlaska]
    (
        [GeoID] VARCHAR(20) NOT NULL, 
        [Year] varchar(10)NOT NULL, 
        [LocationName] VARCHAR(100) NULL, 
        [ParentLocation] VARCHAR(100) NULL, 
        [Population]  varchar(20) NULL, 
        [PovertyRate] decimal(7,2) NULL, 
        [RenterOccupiedHouseholds] varchar(10)NULL, 
        [PctRenterOccupied] decimal(6,2) NULL, 
        [MedianGrossRent] varchar(20) NULL, 
        [MedianHouseholdIncome] varchar(20) NULL, 
        [MedianPropertyValue] varchar(20) NULL, 
        [RentBurden] varchar(10) NULL, 
        [PctWhite] varchar(10) NULL, 
        [PctAfAm] varchar(10) NULL, 
        [PctHispanic] varchar(10) NULL, 
        PctAmInd varchar(10) NULL, 
        PctAsian varchar(10) NULL, 
        PctNhPi varchar(10) NULL, 
        PctMultiple varchar(10) NULL, 
        PctOther varchar(10) NULL, 
        [EvictionFilings] varchar(20) NULL, 
        [Evictions] varchar(20) NULL, 
        [EvictionRate] varchar(10) NULL, 
        [EvictionFilingRate] varchar(10) NULL, 
        [LowFlag] varchar(10) NULL, 
        [Imputed] varchar(10) NULL, 
        [Subbed] varchar(10) NULL
    )


    BULK INSERT EvAlaska
    FROM 'C:\tmp\AK.csv'
    WITH
    (
        FIRSTROW = 2,
        FORMAT = 'CSV', 
        DATAFILETYPE = 'char',
        FIELDTERMINATOR = ',',
        ROWTERMINATOR = '0x0a',
        ERRORFILE = 'C:\tmp\AK-err.csv'
        ,TABLOCK
    )


    http://uk.linkedin.com/in/ramjaddu

    • Marked as answer by Senglory Saturday, May 9, 2020 6:57 PM
    Saturday, May 9, 2020 4:50 PM
  • Is there any way to specify tbl format for the BULK INSERT command in T-SQL?

    You can use a format file to specify a mapping between fields in the file to columns in the table. No need to use a staging table.

    I have an article on bulk load on my web site, from which you cn learn how to craft a format file. http://www.sommarskog.se/bulkload.html


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    • Proposed as answer by Uwe RickenMVP Sunday, May 10, 2020 5:43 AM
    Saturday, May 9, 2020 7:42 PM