{"id":64823,"date":"2007-05-21T23:08:00","date_gmt":"2007-05-21T23:08:00","guid":{"rendered":"https:\/\/blogs.technet.microsoft.com\/heyscriptingguy\/2007\/05\/21\/how-can-i-add-the-contents-of-a-group-of-text-files-to-an-access-database\/"},"modified":"2007-05-21T23:08:00","modified_gmt":"2007-05-21T23:08:00","slug":"how-can-i-add-the-contents-of-a-group-of-text-files-to-an-access-database","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/scripting\/how-can-i-add-the-contents-of-a-group-of-text-files-to-an-access-database\/","title":{"rendered":"How Can I Add the Contents of a Group of Text Files to an Access Database?"},"content":{"rendered":"

\"Hey, \n

Hey, Scripting Guy! I have a folder that has hundreds of text files in it. I\u2019d like to take each of those files and add the contents (as a memo field) to an Access database. How can I do that?

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

Hey, KB. You know, a few years ago the Scripting Guy who writes this column gave a talk at a conference in Orlando. (And yes, he\u2019ll be back in Orlando June 4-8 for TechEd 2007<\/B><\/A> \u2026 assuming, of course, that the city\u2019s restraining order really has<\/I> expired.) Because it\u2019s no fun to go to Orlando by yourself, he did some negotiating, and ended up taking along his then 10-year-old son and one of his son\u2019s friends. (Yes, he did<\/I> take the two boys out of school for a week. But, in his view, at least, education is highly-overrated: after all, the Scripting Guy who writes this column has a master\u2019s degree, and look where that got him<\/I>.)<\/P>\n

As it turned out, the trio happened to arrive in Florida during the closest thing Orlando has to an off-season. One evening they were at Disney World and, about an hour or so before the park was due to close, rode Splash Mountain<\/B><\/A>. When the ride was over they realized that there was no one waiting in line; naturally, they walked around, got back on the ride, and rode again. When they got off, they noticed the same thing: pretty much everyone had already gone home for the day. And so they continued riding Splash Mountain, over and over again, until the folks at Disney finally told them it was time to go.<\/P>\n

The moral of the story? There are some things that you just never get tired of; you\u2019re willing to do them over and over and over again. For some people, that means riding rides at an amusement park; for most of us, though (including those who breathlessly followed the Hey, Scripting Guy!<\/I> columns of May 17<\/B><\/A> and May 18<\/B><\/A>) that means writing scripts that work with text files. In particular, that means writing scripts that work with all the text files in a folder:<\/P>

Const ForReading = 1\nConst adLockOptimistic = 3<\/p>\n
Set objConnection = CreateObject(“ADODB.Connection”)\nSet objRecordSet = CreateObject(“ADODB.Recordset”)<\/p>\n
Set objFSO = CreateObject(“Scripting.FileSystemObject”)<\/p>\n
objConnection.Open _\n    “Provider = Microsoft.Jet.OLEDB.4.0; ” & _\n        “Data Source = C:\\Scripts\\Test.mdb” <\/p>\n
objRecordSet.Open “SELECT * FROM TextFiles” , _\n    objConnection, adOpenStatic, adLockOptimistic<\/p>\n
strComputer = “.”<\/p>\n
Set objWMIService = GetObject(“winmgmts:\\\\” & strComputer & “\\root\\cimv2”)<\/p>\n
Set colFileList = objWMIService.ExecQuery _\n    (“ASSOCIATORS OF {Win32_Directory.Name=’C:\\Archive’} Where ” _\n        & “ResultClass = CIM_DataFile”)<\/p>\n
For Each objFile In colFileList\n    Set objTextFile = objFSO.OpenTextFile(objFile.Name, ForReading)\n    strContents = objTextFile.ReadAll\n    objTextFile.Close<\/p>\n
    objRecordSet.AddNew\n    objRecordSet(“FileName”) = objFile.Name\n    objRecordSet(“FileContents”) = strContents\n    objRecordSet.Update\nNext<\/p>\n
objRecordSet.Close\nobjConnection.Close\n<\/PRE>\n
We\u2019ll give everyone a moment to catch their breath and then we\u2019ll see if we can figure out how this script works. We start out by defining a pair of constants, ForReading and adLockOptimistic. ForReading is the constant we\u2019ll use to specify the file mode (for reading) when we open all the text files; adLockOptimistic is the constant we\u2019ll use to indicate how we want to lock our recordset when working with the database. (Optimistic locking simply means that we\u2019ll only lock the recordset when calling the Update method.) After defining the two constants we then create a pair of database objects: ADODB.Connection<\/B> and ADODB.Recordset<\/B>. From there we go ahead and create an instance of the Scripting.FileSystemObject<\/B>, the object used to read text files. We won\u2019t need this object right away, but this seemed as good a time as any to create it. (Fortunately it won\u2019t go stale if we don\u2019t use it immediately.)<\/P>\n\n<\/THEAD>\n\n\n
\n
Note<\/B>. Before you ask, no, we\u2019re not going to explain much about databases and database objects in today\u2019s column. If you could use a refresher course on working with databases you might take a look at our Scripting Guys webcast Database Scripting for System Administrators<\/B><\/A>.<\/P><\/TD><\/TR><\/TBODY><\/TABLE>\n
<\/DIV>\n
Unlike the FileSystemObject, we\u2019re going to immediately put our two database objects to use. First, we use the Connection object to connect us to the database C:\\Scripts\\Test.mdb:<\/P>
objConnection.Open _\n    “Provider = Microsoft.Jet.OLEDB.4.0; ” & _\n        “Data Source = C:\\Scripts\\Test.mdb”\n<\/PRE>\n
Once we\u2019ve made that connection we use the Recordset object to retrieve a collection of all the records found in the TextFiles<\/I> table:<\/P>
objRecordSet.Open “SELECT * FROM TextFiles” , _\n    objConnection, adOpenStatic, adLockOptimistic\n<\/PRE>\n
As you\u2019ve probably already figured out, TextFiles is the name of the table where we plan to add the new records. For today\u2019s script we\u2019re assuming that the TextFiles table already exists. However, you could modify this script so that it first creates the new table and then adds records to this brand-new table. For more information on programmatically creating database tables, see the webcast<\/B><\/A> we mentioned a few minutes ago.<\/P>\n
Now that we have a database connection our next task is to grab hold of all the text files. To do that we first use this block of code to connect to the WMI service on the local computer:<\/P>
strComputer = “.”<\/p>\n
Set objWMIService = GetObject(“winmgmts:\\\\” & strComputer & “\\root\\cimv2”)\n<\/PRE>\n
And yes, due to limitations with the FileSystemObject, this script is designed to work only on the local computer. That doesn\u2019t mean that you can\u2019t ever, under any circumstances, read text files stored on a remote machine; it just means you\u2019ll have to make some modifications to the script before you can do so. What kind<\/I> of modifications? Why, the ones we discuss in this other column<\/B><\/A>, of course.<\/P>\n
You know, now that you mention it, there are<\/I> a lot of links and see-alsos in today\u2019s column, aren\u2019t there? That couldn\u2019t possibly mean that the Scripting Guy who writes this column is getting lazy?<\/P>\n
Nah.<\/P>\n
OK; what\u2019s next? Well, next we use this bit of code to retrieve a collection of all the files found in the folder C:\\Archive:<\/P>
Set colFileList = objWMIService.ExecQuery _\n    (“ASSOCIATORS OF {Win32_Directory.Name=’C:\\Archive’} Where ” _\n        & “ResultClass = CIM_DataFile”)\n<\/PRE>\n
At this point, and at long last, we\u2019re finally ready to roll: we\u2019re ready to start reading text files and to start adding the contents of those files to our database.<\/P>\n
Speaking of which, we should note that we\u2019re using a very simple database table, one that includes just two fields:<\/P>\n\n\n\n\n
\u2022<\/TD>\n\n
FileName<\/B>, a text field where we\u2019ll store the path of the file we opened.<\/P><\/TD><\/TR>\n
\u2022<\/TD>\n\n
FileContents<\/B>, a memo field where we\u2019ll store the contents of the file we opened.<\/P><\/TD><\/TR><\/TBODY><\/TABLE>\n
Just something we thought you should know.<\/P>\n
OK, now<\/I> it\u2019s time to start adding records to the database. Thanks to our WMI query we have a collection of all the files in the folder C:\\Archive, a collection we stashed away in a variable named colFileList. With that in mind we next set up a For Each loop to loop through each and every one of these files:<\/P>
For Each objFile In colFileList\n<\/PRE>\n
The first thing we do inside this loop is use the OpenTextFile<\/B> method to open file No. 1 in the collection:<\/P>
Set objTextFile = objFSO.OpenTextFile(objFile.Name, ForReading)\n<\/PRE>\n
Notice that we pass OpenTextFile two parameters: objFile.Name<\/B>, a WMI property that returns the complete path to the file; and the constant ForReading (because we want to read from this file). We then use the ReadAll<\/B> method to read the contents of the file into a variable named strContents:<\/P>
strContents = objTextFile.ReadAll\n<\/PRE>\n
And once we have the contents stashed safely away in memory we call the Close<\/B> method and close the file.<\/P>\n
That brings us to the following block of code:<\/P>
objRecordSet.AddNew\nobjRecordSet(“FileName”) = objFile.Name\nobjRecordSet(“FileContents”) = strContents\nobjRecordSet.Update\n<\/PRE>\n
This \u2013 in case you were wondering \u2013 is the exciting, thrill-ride portion of today\u2019s column. (Yes, we know: today\u2019s entire column does<\/I> seem like a thrill ride, doesn\u2019t? But this is the really<\/I> good part.) As you can see, the first thing we do here is call the Recordset object\u2019s AddNew<\/B> method; that\u2019s going to add a new, blank record to our database table. We then use these two lines of code to assign the file path (Name) to the FileName field, and the contents of the file (strContents) to the FileContents field:<\/P>
objRecordSet(“FileName”) = objFile.Name\nobjRecordSet(“FileContents”) = strContents\n<\/PRE>\n
And then we call the Update<\/B> method to actually write this new record to the database. Don\u2019t forget to call this method; if you do forget, none of your changes (that is, none of your new records) will be added to the database.<\/P>\n
Which, of course, would sort of defeat the purpose of the script.<\/P>\n
After calling the Update method for file No. 1 we loop around and repeat the process with the next file in the collection (that is, the next file in the folder). Sooner or later we\u2019ll have opened and read all the files in the collection; when that happens we automatically exit the For Each loop, then use these two lines of code to sever our connection to the database:<\/P>
objRecordSet.Close\nobjConnection.Close\n<\/PRE>\n
At that point our script, like all good things, comes to an end.<\/P>\n
We hope that helps, KB. We also hope that if you (or anyone else) happens to be in Orlando June 4-8<\/B><\/A> that you\u2019ll stop by the Scripting Guys booth and say hi. And don\u2019t worry: you won\u2019t be intruding on any family time. This time around the Scripting Son and his friend won\u2019t be going; instead, the Scripting Guy who writes this column has the \u2026 privilege \u2026 of attending the conference alongside Scripting Guy Jean Ross. They say that it\u2019s impossible to go to Orlando and not<\/I> have a good time. But, then again, they never met Scripting Guy Jean Ross, did they?<\/P>\n\n<\/THEAD>\n\n\n
\n
Note<\/B>. No, hey, we\u2019re just kidding; Jean\u2019s is the perfect<\/I> person to go to Orlando with. Well, other than the fact that she won\u2019t go on scary amusement park rides like Dr. Doom\u2019s Fear Fall<\/B><\/A>. And the fact that she won\u2019t go to a water park like Wet \u2019n Wild<\/B><\/A>. And the fact that she won\u2019t \u2013 well you get the idea. If you do<\/I> attend TechEd and you do<\/I> swing by the Scripting Guys booth, could you wake Scripting Guy Greg Stemp up for us? Thanks.<\/P>\n
Ed<\/B>itor\u2019s Note:<\/B> In other words, Scripting Guy Jean Ross won\u2019t go on any rides that might make her throw up, or go anywhere where she\u2019d have to see her coworkers in bathing suits (which might also fall into that first category). And Scripting Guy Greg Stemp will be riding one ride over and over again, then sleeping through TechEd. You probably can\u2019t wait to meet these people, can you?<\/P><\/TD><\/TR><\/TBODY><\/TABLE><\/p>\n","protected":false},"excerpt":{"rendered":"
Hey, Scripting Guy! I have a folder that has hundreds of text files in it. I\u2019d like to take each of those files and add the contents (as a memo field) to an Access database. How can I do that?— KB Hey, KB. You know, a few years ago the Scripting Guy who writes this […]<\/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":[54,49,3,4,14,5],"class_list":["post-64823","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-scripting","tag-microsoft-access","tag-office","tag-scripting-guy","tag-scripting-techniques","tag-text-files","tag-vbscript"],"acf":[],"blog_post_summary":"
Hey, Scripting Guy! I have a folder that has hundreds of text files in it. I\u2019d like to take each of those files and add the contents (as a memo field) to an Access database. How can I do that?— KB Hey, KB. You know, a few years ago the Scripting Guy who writes this […]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/64823","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=64823"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/64823\/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=64823"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/categories?post=64823"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/tags?post=64823"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}