Name,Department,Title\nKen Myer,Finance,Fiscal Specialist\nPilar Ackerman,Research,Manager\nJonathan Haas,Headquarters,Fiscal Specialist\n<\/PRE>\nIf you\u2019d 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:<\/P>
Const adOpenStatic = 3\nConst adLockOptimistic = 3\nConst adCmdText = &H0001<\/p>\nSet objConnection = CreateObject(“ADODB.Connection”)\nSet objRecordSet = CreateObject(“ADODB.Recordset”)<\/p>\n
strPathtoTextFile = “C:\\Scripts\\Text files”<\/p>\n
objConnection.Open “Provider=Microsoft.Jet.OLEDB.4.0;” & _\n “Data Source=” & strPathtoTextFile & “;” & _\n “Extended Properties=””text;HDR=YES;FMT=Delimited”””<\/p>\n
strFile = “Employees.txt”<\/p>\n
objRecordset.Open “SELECT * FROM ” & strFile & ” WHERE Title = ‘Fiscal Specialist'”, _\n objConnection, adOpenStatic, adLockOptimistic, adCmdText<\/p>\n
Do Until objRecordset.EOF\n Wscript.Echo objRecordset.Fields.Item(“Name”)\n Wscript.Echo objRecordset.Fields.Item(“Department”)\n Wscript.Echo objRecordset.Fields.Item(“Title”) \n objRecordset.MoveNext\nLoop\n<\/PRE>\n
What we\u2019re doing here is connecting to the folder C:\\Scripts\\Text files<\/B> and then querying the file Employees.txt<\/B>. Here\u2019s the line of code that specifies the name of the file we want to work with:<\/P>strFile = “Employees.txt”\n<\/PRE>\nThis works great: the script will run, and it will report back the name, department, and title of all the Fiscal Specialists.<\/P>\n
So what\u2019s the problem? Well, suppose our text file is named Fabrikam Employees.txt<\/B>. No big deal, right? After all, you just change the value of the variable strFile to this:<\/P>strFile = “Fabrikam Employees.txt”\n<\/PRE>\nWell, that\u2019s what we thought, too. But here\u2019s what we got when we ran the script:<\/P>
Microsoft JET Database Engine: Syntax error in FROM clause.\n<\/PRE>\nYikes. Obviously the space in the file name Fabrikam Employees.txt<\/B> is causing problems; that we could figure out. What we couldn\u2019t 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.<\/P>\n
Yep, square brackets. If we set the value of strFile to this<\/I> the script will work just fine:<\/P>strFile = “[Fabrikam Employees.txt]”\n<\/PRE>\nTo 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, \u201cWell, it took us a long time, but that\u2019s only because this is such a complex problem that it required incredible feats of intellectual reasoning and ability before we could determine the answer.\u201d Instead all we can say is, \u201cJust enclose your file name in square brackets.\u201d But, hey, at least it works, right? And, like we said, we couldn\u2019t find anything in their writings to indicate that either Albert Einstein or Isaac Newton ever<\/I> figured this out. So there.<\/P>\n
By the way, here\u2019s what the complete script looks like:<\/P>
Const adOpenStatic = 3\nConst adLockOptimistic = 3\nConst adCmdText = &H0001<\/p>\nSet objConnection = CreateObject(“ADODB.Connection”)\nSet objRecordSet = CreateObject(“ADODB.Recordset”)<\/p>\n
strPathtoTextFile = “C:\\Scripts\\Text files”<\/p>\n
objConnection.Open “Provider=Microsoft.Jet.OLEDB.4.0;” & _\n “Data Source=” & strPathtoTextFile & “;” & _\n “Extended Properties=””text;HDR=YES;FMT=Delimited”””<\/p>\n
strFile = “[Fabrikam Employees.txt]”<\/p>\n
objRecordset.Open “SELECT * FROM ” & strFile & ” WHERE Title = ‘Fiscal Specialist'”, _\n objConnection, adOpenStatic, adLockOptimistic, adCmdText<\/p>\n
Do Until objRecordset.EOF\n Wscript.Echo objRecordset.Fields.Item(“Name”)\n Wscript.Echo objRecordset.Fields.Item(“Department”)\n Wscript.Echo objRecordset.Fields.Item(“Title”) \n objRecordset.MoveNext\nLoop\n<\/PRE>\n
Added bonus<\/B>. Ironically, after trying so hard to find one<\/I> way to access text files that have spaces in their file names we ended up finding two<\/I> ways. You can also enclose the file name using the ` character. This isn\u2019t the single quote mark; instead it\u2019s 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:<\/P>strFile = “`Fabrikam Employees.txt`”\n<\/PRE><\/p>\n","protected":false},"excerpt":{"rendered":"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\u2019s been awhile would be an understatement. (You should have seen all the dust and cobwebs we had to brush off […]<\/p>\n","protected":false},"author":595,"featured_media":87096,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[1],"tags":[19,146,3,5],"class_list":["post-69743","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-scripting","tag-activex-data-objects-ado","tag-databases","tag-scripting-guy","tag-vbscript"],"acf":[],"blog_post_summary":"
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\u2019s been awhile would be an understatement. (You should have seen all the dust and cobwebs we had to brush off […]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/69743","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/users\/595"}],"replies":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/comments?post=69743"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/69743\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/media\/87096"}],"wp:attachment":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/media?parent=69743"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/categories?post=69743"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/tags?post=69743"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
![\"Hey,](\"https:\/\/devblogs.microsoft.com\/wp-content\/uploads\/sites\/29\/2019\/02\/q-for-powertip.jpg\" "\\"Hey,")
<\/P>\n![\"Spacer\"](\"https:\/\/devblogs.microsoft.com\/scripting\/wp-content\/uploads\/sites\/29\/2019\/05\/spacer.gif\")
![\"Hey,](\"https:\/\/devblogs.microsoft.com\/wp-content\/uploads\/sites\/29\/2019\/02\/a-for-powertip.jpg\" "\\"Hey,")
![\"Script](\"http:\/\/img.microsoft.com\/library\/media\/1033\/technet\/images\/scriptcenter\/ad.jpg\" "\\"Script")
<\/A> \n