Post reply

Bulk insert with format file

  • March 8, 2006 at 2:51 pm

    #113614

    Hi,

    I am trying to insert data into a temp table from an excel file using bulk Insert.  Here is my code:

     

    exec master..xp_cmdshell ' (BCP ##Test in "E:\WUTemp\Spreadsheetname.xls" -fE:\WUTemp\Prospect.fmt -T)'

    Bulk Insert ##Test From 'E:\WUTemp\Spreadsheetname.xls' With (FormatFile = 'E:\WUTemp\Spreadsheetname.fmt')

     

    I get the following error:

     

    (3 row(s) affected)

    Server: Msg 4860, Level 16, State 1, Line 2

    Could not bulk insert. File 'E:\WUTemp\NewProspectJoJo.fmt' does not exist.

     

    Any help would be greatly appreciated

    KR

     

     

  • March 8, 2006 at 3:06 pm

    #625538

    Is 'NewProspectJoJo' your spreadsheet name?  Does 'E:\WUTemp\NewProspectJoJo.fmt'  exist?  Does the SQL Server service account have access to E:\WUTemp?  It sounds to me like the format file does not exist (just like the message is saying) or SQL Server cannot see it due to Windows permissions.

    John Rowan

    ======================================================
    ======================================================
    Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

  • March 8, 2006 at 3:14 pm

    #625541

    Yes,  that is the spreadsheet name. The sql server account does have access to E:\WUTemp.

  • March 8, 2006 at 3:17 pm

    #625542

    Have you created the format file NewProspectJoJo.fmt in E:\WUTemp?

    John Rowan

    ======================================================
    ======================================================
    Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

  • March 8, 2006 at 3:40 pm

    #625547

    I have.  However I am looking at another way to solve the problem of copying data into the temp file, so hopefully I will not need to resolve this particular issue at this point.

     

    Thanks for your input,

     

    KR

  • March 9, 2006 at 8:39 am

    #625716

    You could just use them SQL Bulk Insert via Query Analyzer:

    BULK INSERT tableName

       FROM '.....txt'

       WITH

         (

            formatfile = '........fmt'

                  )

    GO

     

     

  • March 9, 2006 at 8:44 am

    #625717

    You could also try running

    exec master..xp_cmdshell 'dir E:\WUTemp\NewProspectJoJo.fmt'

    ..and see what it says..

    SQL Server rarely lies - but if it says something doesn't exist, it just means that it can't find it where it's looking. It may exist, but the server may not look where we think it's looking

    /Kenneth

Viewing 7 posts - 1 through 6 (of 6 total)

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