Post reply

BCP not copying

  • February 4, 2015 at 2:00 am

    #294494

    Hi Guys,

    I urgently need some help using BCP.

    When I try to copy data from an Excel spreadsheet file it gives me the following error: SQLState = S1000, Native Error = 0 and then it says Sql Server Native Client 10.0 Unable to open BCP host data file

    My command is bcp DBName.dbo.TableName in C:\testing.xls -c -T -t.

    Why is this not working?

    Any help will be greatly appreciated.

    Thanks Guys

  • February 4, 2015 at 5:24 am

    #1775253

    I have now changed the file name to Capitals, is the bcp case sensitive?

    Now it says starting copy, then gives me SQLState = 220011, Native Error = 0.

    String data right truncation

  • February 4, 2015 at 5:33 am

    #1775257

    Sounds like the data in your spreadsheet is too wide to insert into at least one of the columns in your table.

    John

  • February 4, 2015 at 5:45 am

    #1775262

    What you says makes sense since this is what I have found when searching the error, but when I use the Import wizard to do it, it creates a new table for the data. Then I just insert all off that data into my table and I dont get any errors? One thing I have noticed is that the new table that gets created sometimes get an extra column that does not appear in the excel spreadsheet. How is this possible?

  • February 4, 2015 at 5:54 am

    #1775267

    If I remember correctly, when data is imported from Excel, it only looks at the first half dozen or so rows in order to determine the width of the columns. You probably ought to create the table explicitly based on your familiarity with the data.

    John

  • February 4, 2015 at 5:58 am

    #1775269

    I created the table exactly like the spreadsheet. The format of the spreadsheet will never change and I made the data types more than big enough to handle the data coming in

  • February 4, 2015 at 7:37 am

    #1775312

    My final command is now:

    bcp DBName.dbo.TableName IN FilePath -T -t -f FormatFilePath

    This gives no error, it only says

    Starting Copy....

    0 Rows Copied

    Network Packet Size (bytes): 4096

    Clock Time (ms.) Total : 1

  • February 4, 2015 at 8:30 pm

    #1775548

    BCP cannot import .XLS (Excel spreadsheet) files... period. If you're trying to import a spreadsheet to SQL Server via T-SQL, you need to use the "ACE" drivers and either OPENQUERY and OPENROWSET.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • February 4, 2015 at 11:44 pm

    #1775576

    I wanted to use the openrowset statement in sql but it complains about my drivers.

  • February 5, 2015 at 2:18 am

    #1775601

    Please don't cross post. Answered here and you'll need to install the drivers mentioned.

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply