Post reply

Bulk Insert (0 row(s) affected)

  • December 31, 2013 at 12:36 pm

    #280849

    I am trying to import data from an xls file. (It works if I only have one column in the xls file).

    I have a table in SQL Server with two columns

    PojectID (nchar(10, null)

    BAU(nchar(10), null)

    sample data from excel

    Textbox30

    Last Updated At 12/30/2013 10:30:12 PM

    ProjectID BAU

    100109 BAU

    100109 BAU

    100109 BAU

    100109 BAU

    100109 BAU

    100109 BAU

    100109 BAU

    100109 BAU

    using query in SSMS

    bulk

    insert [dbo].[Bulk]

    from 'C:\Users\JL04638\Desktop\Detailed_Report_by_Date_and_Dept_Teamaaa.xls'

    with

    (

    fieldterminator = '\t',

    ROWTERMINATOR = '\r',

    firstrow = 6);

    (0 row(s) affected)

    is there something I should change with the fieldterminator and roterminator?

    Thanks.

  • December 31, 2013 at 11:04 pm

    #1676704

    BULK INSERT won't import spreadsheet files. You need to export the data from the spreadsheet into a TAB or COMMA delimited text file and then BULK INSERT that. Either that or do a web search for the "ACE Drivers", which will allow you to import Excel Files directly into SQL Server using 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)
    Intro to Tally Tables and Functions

  • January 6, 2014 at 12:36 pm

    #1677858

    Thanks for your help.

Viewing 3 posts - 1 through 2 (of 2 total)

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