Results 1 to 7 of 7
  1. #1
    Join Date
    Jul 2011
    Posts
    78

    Unanswered: Error message on inserting from a path link

    This is the code I am using, why do I have an error message about the path? I tested out the link myself, I can open the file using that link when search for it. Is there a certain way to write the path link? Please let me know. Thanks.


    BULK INSERT [EDA].[DATES] FROM 'C:\Documents and Settings\bt\Desktop\Date-Time Tables.xlsx' WITH (FIELDTERMINATOR = ';', ROWTERMINATOR = '\n')

    GO

    INSERT INTO [EDA4010].[DATES]
    (
    [MSFTDATE],
    [REALDATE],
    [INTWEEK] ,
    [INTMONTH],
    [INTQUARTER] ,
    [INTYEAR] ,
    [INTNUMWEEKS] ,
    [CHRDAY]
    )
    SELECT
    [MSFTDATE],
    [REALDATE],
    [INTWEEK] ,
    [INTMONTH],
    [INTQUARTER],
    [INTYEAR] ,
    [INTNUMWEEKS] ,
    [CHRDAY]
    FROM [EDA].[DATES]
    GO



    Error:

    Msg 4861, Level 16, State 1, Line 1
    Cannot bulk load because the file "C:\Documents and Settings\bt\Desktop\Date-Time Tables.xlsx" could not be opened. Operating system error code 3(The system cannot find the path specified.).

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    I am going to guess that the file is on your laptop or desktop machine, and not on the server itself. BULKINSERT is a command that is sent to the server, and not the client. All paths in BULKINSERT are going to be relative to the server.
    Last edited by MCrowley; 07-19-11 at 11:42. Reason: Fixed tyops

  3. #3
    Join Date
    Mar 2007
    Location
    Holmestrand, Norway
    Posts
    332
    And, not do not forget that it is for importing csv files, not xslx, so the file needs to be saved as csv first.
    Ole Kristian Velstadbråten Bangås - Virinco - MSSQL.no - Facebook - Twitter

  4. #4
    Join Date
    Jul 2011
    Posts
    78
    sigh...when convert the excel file to csv, some data are missing. Guess I have to copy it manually. But its so inefficient, also have a file with 85000 plus rows.

  5. #5
    Join Date
    Jul 2011
    Posts
    78
    Quote Originally Posted by MCrowley View Post
    I am going to guess that the file is on your laptop or desktop machine, and not on the server itself. BULKINSERT is a command that is sent to the server, and not the client. All paths in BULKINSERT are going to be relative to the server.

    Yes, the file is on my desktop machine. Is there an alternative to the bulkinsert? I tried openrowset, but the database blocked it for security reason.

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  7. #7
    Join Date
    Jul 2011
    Posts
    78
    Thank you so much, Pat! It works great.

    Thanks for every one's replies =)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •