{"id":67393,"date":"2006-05-04T10:02:00","date_gmt":"2006-05-04T10:02:00","guid":{"rendered":"https:\/\/blogs.technet.microsoft.com\/heyscriptingguy\/2006\/05\/04\/how-can-i-query-a-text-file-and-retrieve-records-that-occurred-on-a-specific-date\/"},"modified":"2006-05-04T10:02:00","modified_gmt":"2006-05-04T10:02:00","slug":"how-can-i-query-a-text-file-and-retrieve-records-that-occurred-on-a-specific-date","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/scripting\/how-can-i-query-a-text-file-and-retrieve-records-that-occurred-on-a-specific-date\/","title":{"rendered":"How Can I Query a Text File and Retrieve Records That Occurred on a Specific Date?"},"content":{"rendered":"

\"Hey, \n

Hey, Scripting Guy! We have tons of text files in which each line in the file represents a record or an event of some kind. How can I use a script to retrieve records or events that occurred on a specified date?

— TW<\/P>\"Spacer\"\"Hey,\"Script<\/A> \n

Hey, TW. You know, in your email you sort of apologized for using text files, noting that \u201cI know, I know, we should probably use a database rather than text files.\u201d Take it from us, TW: you have nothing to apologize for. If these text files do what you need them to do, then you should keep using them. Admittedly, there are times when a database might be the most appropriate tool; then again, based on the number of questions we get regarding text files it\u2019s obvious that lots of people still find the old-fashioned log file to be extremely useful. As the saying goes, if it ain\u2019t broke, don\u2019t fix it.<\/P>\n

The truth is, people often-times worry about things they shouldn\u2019t worry about. We get lots of letters from system administrators saying, \u201cI have a batch file that works great, but it is<\/I> just a batch file. Should I convert this to a script?\u201d Well, you can if you want to, but if it works (and works great) why bother? If you\u2019re like the Scripting Guys, you have enough to do fixing things that really do<\/I> need to be fixed; there\u2019s no sense going around fixing things that don\u2019t<\/I> need to be fixed.<\/P>\n

Good point: not only is that a long, philosophical discussion, but it doesn\u2019t mention baseball even once. Wait a minute: this is supposed to be the Hey, Scripting Guy!<\/I> column; somehow we\u2019ve wandered into Dr. Phil territory! <\/P>\n

On the other hand, Dr. Phil never (well, hardly ever) dispenses code like this:<\/P>

Const adOpenStatic = 3\nConst adLockOptimistic = 3\nConst adCmdText = &H0001<\/p>\n
Set objConnection = CreateObject(“ADODB.Connection”)\nSet objRecordSet = CreateObject(“ADODB.Recordset”)<\/p>\n
strPathtoTextFile = “C:\\Scripts”<\/p>\n
objConnection.Open “Provider=Microsoft.Jet.OLEDB.4.0;” & _\n    “Data Source=” & strPathtoTextFile & “;” & _\n        “Extended Properties=””text;HDR=YES;FMT=Delimited”””<\/p>\n
strHireDate = #3\/1\/2006#\nstrFile = “Employees.txt”<\/p>\n
objRecordset.Open “Select * FROM ” & strFile & ” where HireDate = #” & strHireDate & “#”, _\n    objConnection, adOpenStatic, adLockOptimistic, adCmdText<\/p>\n
Do Until objRecordset.EOF\n    Wscript.Echo objRecordset.Fields.Item(“LastName”)\n    Wscript.Echo objRecordset.Fields.Item(“FirstName”)   \n    Wscript.Echo objRecordset.Fields.Item(“Department”)\n    Wscript.Echo objRecordset.Fields.Item(“HireDate”)   \n    objRecordset.MoveNext\nLoop\n<\/PRE>\n
Before we start we should point out that TW has a series of comma-separated values (CSV) files, where the first line in each file is a header row indicating the fields in the file. For example, TW has a file similar to this (note that, in order to make the example a bit easier to follow, we\u2019ve removed several fields):<\/P>
LastName,FirstName,Department,HireDate\nMyer,Ken,Finance,3\/1\/2006\nAckerman,Pilar,Finance,3\/1\/2006\nSmith,Joe,Resarch,4\/1\/2006\n<\/PRE>\n
Is it important that your text files be formatted like this? It\u2019s not just important, it\u2019s crucial. We\u2019re going to use database techniques to retrieve information from the file, and to do that the file needs to be delimited in some way (in this case, using the comma as the delimiter). Ideally, your files should include a header row as well. As long as your text file looks like this you\u2019re in business.<\/P>\n\n<\/THEAD>\n\n\n
\n
Note<\/B>. What if your text file doesn\u2019t<\/I> look like this? Well, in some cases you might still be able to use database techniques to query the file. For more information, take a look at our MSDN column Much ADO About Text Files<\/B><\/A>.<\/P><\/TD><\/TR><\/TBODY><\/TABLE>\n
<\/DIV>\n
We should note two things here. First, we aren\u2019t going to explain each line of code in-depth; that\u2019s because we\u2019ve already done that in the MSDN column<\/B><\/A>. Second, don\u2019t be scared away by the cryptic nature of some of the code found in the script. As you\u2019re about to see, much of that code is boilerplate that you neither have to worry about nor modify. It\u2019s just code that needs to be there.<\/P>\n
The script itself starts out by defining three constants: adOpenStatic, adLockOptimistic, and adCmdText. These constants are used to configure our \u201cdatabase\u201d (text file) query. After defining the constants we create instances of the ADODB.Connection<\/B> and ADODB.Recordset<\/B> objects. That brings us to this block of code:<\/P>
strPathtoTextFile = “C:\\Scripts”<\/p>\n
objConnection.Open “Provider=Microsoft.Jet.OLEDB.4.0;” & _\n    “Data Source=” & strPathtoTextFile & “;” & _\n        “Extended Properties=””text;HDR=YES;FMT=Delimited”””\n<\/PRE>\n
As you can see, we\u2019re doing two things here. First, we assign a path to the variable strPathToTextFile. Note that this path represents the folder<\/I> in which the text file can be found. It\u2019s not the path to the actual text file itself; that would be akin to C:\\Scripts\\Employees.txt. It\u2019s just the path to the folder.<\/P>\n
Second, we call the Open<\/B> method to make an ADO (ActiveX Data Objects) connection to the folder C:\\Scripts. This is a perfect example of the boilerplate nature of this script: assuming your text file is properly formatted and assuming you\u2019ve assigned the folder path to strPathToTextFile, then you can leave this code exactly as-is; you don\u2019t need to change a thing.<\/P>\n
If only everything in life was that easy, huh?<\/P>\n
After we make a connection we then assign values to a pair of variables:<\/P>
strHireDate = #3\/1\/2006#\nstrFile = “Employees.txt”\n<\/PRE>\n
In this script we want to retrieve information about all the employees who were hired on March 1, 2006; consequently, we assign that date to the variable strHireDate. Why did we enclose the date in #\u2019s? That\u2019s easy: to ensure that VBScript treats the value as a date, and not as, say, an arithmetic problem (i.e., 3 divided by 1 divided by 2006). Meanwhile, we assign the name of the text file (Employees.txt) to a variable named strFile.<\/P>\n
What does all that mean? It means we\u2019re ready to issue our query:<\/P>
objRecordset.Open “Select * FROM ” & strFile & ” where HireDate = #” & strHireDate & “#”, _\n    objConnection, adOpenStatic, adLockOptimistic, adCmdText\n<\/PRE>\n
The query is actually pretty straightforward. One of the fields in our text file is named HireDate. How do we know that? Because that\u2019s one of the field names listed in the file\u2019s header row:<\/P>
LastName,FirstName,Department,HireDate\n<\/PRE>\n
When we issue our query we simply request all the records (that is, all the lines in the text file) where the HireDate field is equal to #3\/1\/2006#. Notice that we use the variable strFile (which contains the name of the text file) at the point in the query where we would typically insert a database table name:<\/P>
“Select * FROM ” & strFile & ”\n<\/PRE>\n
And that\u2019s basically that. Through the magic of ADO you\u2019ll get back a recordset consisting of all the records (all the lines in the text file) where the hire date is equal to 3\/1\/2006. At that point you simply walk through the recordset and echo back the values:<\/P>
Do Until objRecordset.EOF\n    Wscript.Echo objRecordset.Fields.Item(“LastName”)\n    Wscript.Echo objRecordset.Fields.Item(“FirstName”)   \n    Wscript.Echo objRecordset.Fields.Item(“Department”)\n    Wscript.Echo objRecordset.Fields.Item(“HireDate”)   \n    objRecordset.MoveNext\nLoop\n<\/PRE>\n
Not only do you get the same results as you would if your data was stored in a database, but you use pretty much the same scripting code as well. If this works for you, TW, then we recommend that you hang on to your text files. For obvious reasons, the Scripting Guys would never suggest that you should get rid of someone – er, something<\/I> just because it isn\u2019t cool. (Not that the Scripting Guys aren\u2019t cool, mind you, but, still \u2026.)<\/P><\/p>\n","protected":false},"excerpt":{"rendered":"
Hey, Scripting Guy! We have tons of text files in which each line in the file represents a record or an event of some kind. How can I use a script to retrieve records or events that occurred on a specified date?— TW Hey, TW. You know, in your email you sort of apologized for […]<\/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,4,14,5],"class_list":["post-67393","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-scripting","tag-activex-data-objects-ado","tag-databases","tag-scripting-guy","tag-scripting-techniques","tag-text-files","tag-vbscript"],"acf":[],"blog_post_summary":"
Hey, Scripting Guy! We have tons of text files in which each line in the file represents a record or an event of some kind. How can I use a script to retrieve records or events that occurred on a specified date?— TW Hey, TW. You know, in your email you sort of apologized for […]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/67393","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=67393"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/67393\/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=67393"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/categories?post=67393"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/tags?post=67393"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}