none
MS Access FROM clause with IN clause to external file when the path includes brackets? RRS feed

  • Question

  • Given a valid path and file, the following works.

        SELECT * FROM [Excel 12.0;HDR=YES;IMEX=1;DATABASE=C:\ExtFile.xlsx].[Sheet1$]

    My question is how to handle a path that includes brackets ([test] folder)? I understand the extra brackets cause an issue since the entire connection definition is enclosed/qualified with brackets. Is there an escape character to process the brackets as part of the path?

        SELECT * FROM [Excel 12.0;HDR=YES;IMEX=1;DATABASE=C:\[test]\ExtFile.xlsx].[Sheet1$]

    Thanks,
    Erich
    Wednesday, May 24, 2017 9:39 PM

Answers

  • Thanks for the suggestions, but they didn't work for me.

    However, I continued troubleshooting and believe I found a solution. I knew IN would work with an ACCDB file w/o a PWD, but I had abandoned IN in an effort to support an ACCDB w/ a PWD or Excel/Text files. The solution is a hybrid. Use IN for the path, followed by brackets with the remaining connection parameters.

        SELECT *
        FROM [Sheet1$] IN 'C:\[test]\ExtFile.xlsx' [Excel 12.0;HDR=YES;IMEX=1]

    Thanks again for your time.
    Thursday, May 25, 2017 12:20 PM

All replies

  • I guess you could try

     SELECT * FROM 'Excel 12.0;HDR=YES;IMEX=1;DATABASE=C:\[test]\ExtFile.xlsx.Sheet1$'

    but I have not tested it.

    Matthias Kläy, Kläy Computing AG

    Wednesday, May 24, 2017 10:19 PM
  • Hi Erich,

    I am also guessing (since I can't do any testing right now), but try:

    SELECT * FROM [Excel 12.0;HDR=YES;IMEX=1;DATABASE=C:\[[]test[]]\ExtFile.xlsx].[Sheet1$]

    Basically, try enclosing the bracket with brackets to escape it. I don't remember where I got this idea.

    Hope it helps...

    

    Wednesday, May 24, 2017 10:41 PM
  • Hello,

    Tested the suggestions, unfortunately, they dont work.

    I would suggest you use ADO connection to query this kind of Excel files.

    Set objConnection = CreateObject("ADODB.Connection")
    Set objRecordset = CreateObject("ADODB.Recordset")
    Dim qry As String
    qry = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\[test]\test.xlsx;Extended Properties=""Excel 12.0 Xml;HDR=YES;IMEX=1"";"
    objConnection.Open qry
    objRecordset.Open "Select * FROM [Sheet1$]", _
        objConnection, 3, 3, &H1

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, May 25, 2017 7:29 AM
    Moderator
  • Thanks for the suggestions, but they didn't work for me.

    However, I continued troubleshooting and believe I found a solution. I knew IN would work with an ACCDB file w/o a PWD, but I had abandoned IN in an effort to support an ACCDB w/ a PWD or Excel/Text files. The solution is a hybrid. Use IN for the path, followed by brackets with the remaining connection parameters.

        SELECT *
        FROM [Sheet1$] IN 'C:\[test]\ExtFile.xlsx' [Excel 12.0;HDR=YES;IMEX=1]

    Thanks again for your time.
    Thursday, May 25, 2017 12:20 PM
  • Thanks for the suggestions, but they didn't work for me.

    However, I continued troubleshooting and believe I found a solution. I knew IN would work with an ACCDB file w/o a PWD, but I had abandoned IN in an effort to support an ACCDB w/ a PWD or Excel/Text files. The solution is a hybrid. Use IN for the path, followed by brackets with the remaining connection parameters.

        SELECT *
        FROM [Sheet1$] IN 'C:\[test]\ExtFile.xlsx' [Excel 12.0;HDR=YES;IMEX=1]

    Thanks again for your time.

    Hi,

    Congratulations! Glad to hear you found a working solution. Good luck with your project.

    Thursday, May 25, 2017 1:29 PM
  • Hello Erich,

    Brilliant and thanks for sharing the solution here. I suggest you mark it as answer to close this thread. I think it would be of great help for others with similar issue.

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, May 26, 2017 1:33 AM
    Moderator
  • Upon further review, there is an exception to my suggested solution. It does not work for MS Access files that require a DB Password. In which case, a work-around could be mapping a drive so the folder with brackets does not appear in the path (if the  folder isn't the last one).

    Friday, May 26, 2017 3:32 PM