How Can I Use ADO to Open a Text File That has Spaces in the File Name?

ScriptingGuy1

Hey, Scripting Guy! How can I use ADO to open a text file that has spaces in the file name?

— TL

Hey, TL. You might not even remember sending us this question; to say it’s been awhile would be an understatement. (You should have seen all the dust and cobwebs we had to brush off this question before we could post it in the Script Center.) A long time ago you wrote to us asking a simple question: how can you use ADO to open a text file if that file happens to have spaces in the file name? That seemed easy enough, but – to tell you the truth – we couldn’t figure it out. We tried inserting double quotes and single quotes, we tried using short file names, and we scoured the Web looking for the answer. In baseball parlance, we struck out.

So did we give up? As a matter of fact, we did. Nevertheless, we always had this in the back of our minds, and every now and then we’d try something different, just in case. Each time we tried something new we failed.

And then the other day, while looking up some information on Microsoft Excel, we stumbled upon the answer. (Actually we stumbled upon the answer to a different question, but as it turned out the same technique that worked for Excel also works for text files.) So at long last, TL, we can give you (or at least your great-great-grandchildren) an answer to the question, “How can I use ADO to open a text file that has spaces in the file name?”

Before we do that we should probably explain the situation to the rest of the world. ADO (ActiveX Data Objects) enables you to use database techniques to read data from a text file. We won’t go into all the details today; for more information, take a look at this Scripting Clinic column on MSDN. But suppose you have a simple text file that looks like this (just pretend there are a thousand or so lines in the file):

Name,Department,Title
Ken Myer,Finance,Fiscal Specialist
Pilar Ackerman,Research,Manager
Jonathan Haas,Headquarters,Fiscal Specialist

If you’d like a list of all the Fiscal Specialists, you can easily retrieve that information using an ADO script similar to this, and without having to read through the text file line-by-line:

Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adCmdText = &H0001

Set objConnection = CreateObject(“ADODB.Connection”)
Set objRecordSet = CreateObject(“ADODB.Recordset”)
strPathtoTextFile = “C:\Scripts\Text files”
objConnection.Open “Provider=Microsoft.Jet.OLEDB.4.0;” & _
    “Data Source=” & strPathtoTextFile & “;” & _
        “Extended Properties=””text;HDR=YES;FMT=Delimited”””
strFile = “Employees.txt”
objRecordset.Open “SELECT * FROM ” & strFile & ” WHERE Title = ‘Fiscal Specialist'”, _
    objConnection, adOpenStatic, adLockOptimistic, adCmdText
Do Until objRecordset.EOF
    Wscript.Echo objRecordset.Fields.Item(“Name”)
    Wscript.Echo objRecordset.Fields.Item(“Department”)
    Wscript.Echo objRecordset.Fields.Item(“Title”)   
    objRecordset.MoveNext
Loop

What we’re doing here is connecting to the folder C:\Scripts\Text files and then querying the file Employees.txt. Here’s the line of code that specifies the name of the file we want to work with:

strFile = “Employees.txt”

This works great: the script will run, and it will report back the name, department, and title of all the Fiscal Specialists.

So what’s the problem? Well, suppose our text file is named Fabrikam Employees.txt. No big deal, right? After all, you just change the value of the variable strFile to this:

strFile = “Fabrikam Employees.txt”

Well, that’s what we thought, too. But here’s what we got when we ran the script:

Microsoft JET Database Engine: Syntax error in FROM clause.

Yikes. Obviously the space in the file name Fabrikam Employees.txt is causing problems; that we could figure out. What we couldn’t figure out was how to work around that problem. We tried all sorts of different permutations and combinations of single quotes and double quotes, only to discover that the answer is actually much easier: all we have to do is enclose the file name in square brackets.

Yep, square brackets. If we set the value of strFile to this the script will work just fine:

strFile = “[Fabrikam Employees.txt]”

To be honest, this was a little disappointing. After spending all that time and effort we were hoping the answer would be really complicated; that way we could say, “Well, it took us a long time, but that’s only because this is such a complex problem that it required incredible feats of intellectual reasoning and ability before we could determine the answer.” Instead all we can say is, “Just enclose your file name in square brackets.” But, hey, at least it works, right? And, like we said, we couldn’t find anything in their writings to indicate that either Albert Einstein or Isaac Newton ever figured this out. So there.

By the way, here’s what the complete script looks like:

Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adCmdText = &H0001

Set objConnection = CreateObject(“ADODB.Connection”)
Set objRecordSet = CreateObject(“ADODB.Recordset”)
strPathtoTextFile = “C:\Scripts\Text files”
objConnection.Open “Provider=Microsoft.Jet.OLEDB.4.0;” & _
    “Data Source=” & strPathtoTextFile & “;” & _
        “Extended Properties=””text;HDR=YES;FMT=Delimited”””
strFile = “[Fabrikam Employees.txt]”
objRecordset.Open “SELECT * FROM ” & strFile & ” WHERE Title = ‘Fiscal Specialist'”, _
    objConnection, adOpenStatic, adLockOptimistic, adCmdText
Do Until objRecordset.EOF
    Wscript.Echo objRecordset.Fields.Item(“Name”)
    Wscript.Echo objRecordset.Fields.Item(“Department”)
    Wscript.Echo objRecordset.Fields.Item(“Title”)   
    objRecordset.MoveNext
Loop

Added bonus. Ironically, after trying so hard to find one way to access text files that have spaces in their file names we ended up finding two ways. You can also enclose the file name using the ` character. This isn’t the single quote mark; instead it’s the accent grave character found on the same key as the ~ character (on U.S. English keyboards anyway). In other words, you can also set the value of strFile to this and your script will work just fine:

strFile = “`Fabrikam Employees.txt`”

ScriptingGuy1

Follow