{"id":55433,"date":"2008-06-03T06:10:00","date_gmt":"2008-06-03T06:10:00","guid":{"rendered":"https:\/\/blogs.technet.microsoft.com\/heyscriptingguy\/2008\/06\/03\/hey-scripting-guy-how-can-i-move-files-based-on-their-three-part-file-name\/"},"modified":"2008-06-03T06:10:00","modified_gmt":"2008-06-03T06:10:00","slug":"hey-scripting-guy-how-can-i-move-files-based-on-their-three-part-file-name","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/scripting\/hey-scripting-guy-how-can-i-move-files-based-on-their-three-part-file-name\/","title":{"rendered":"Hey, Scripting Guy! How Can I Move Files Based on Their Three-Part File Name?"},"content":{"rendered":"

\"Hey, <\/h2>\n

Hey, Scripting Guy! I have a folder filled with invoice files. Each of these files has been given a file name based on a specific three-part naming convention: a three-letter abbreviation of the vendor name; the invoice date (e.g., 06032008) ; and the invoice number (which can be of varying length). In other words, all the files have names like this: ABC06032008I5963.pdf. I also have a series of folders for each vendor, a series of subfolders for each year, and a folder for each month of each year for each vendor. I need a script that can go through the invoice folder and move each file to the appropriate folder. Can you help me with this?
— JC<\/p>\n

\"Spacer\"\"Hey,\"Script<\/a> <\/p>\n

Hey, JC. Great question! Here’s the script:<\/p>\n

Set objFSO = CreateObject(\"Scripting.FileSystemObject\")\nSet objFolder = objFSO.GetFolder(\"C:\\Invoices\")\n\nFor Each objFile in objFolder.Files\n    strVendorName = Left(objFile.Name, 3)\n    strMonth = MonthName(Mid(objFile.Name, 4, 2))\n    intYear = Mid(objFile.Name, 8, 4)\n\n    strFolderPath = \"C:\\Archive\\\" & strVendorName & \"\\\" & intYear & \"\\\" & strMonth & \"\\\"\n\n    If Not objFSO.FolderExists(strFolderPath) Then\n        Set objFolder2 = objFSO.CreateFolder(strFolderPath)\n    End If\n  \n    objFSO.MoveFile objFile.Path, strFolderPath\nNext\n<\/pre>\n
Before we explain how this script works let\u2019s review the scenario. JC has a folder named Invoices which, not too surprisingly, is filled to the brim with invoice files. Each of these files has a name similar to this one:<\/p>\n
ABC06032008I5963.pdf\n<\/pre>\n
As it turns out, ABC06032008I5963.pdf happens to be the Scripting Editor\u2019s real first name. (She goes by Jean, but, then again, if your first name was ABC06032008I5963.pdf you\u2019d probably go by Jean, too.) However, this isn\u2019t why JC chose the name; that\u2019s just a coincidence. Instead, there\u2019s method to JC\u2019s seeming-madness:<\/p>\n\n\n\n\n\n
\u2022<\/td>\n\n
ABC (the first three letters) is an abbreviation for a specific vendor.<\/p>\n<\/td>\n<\/tr>\n
\u2022<\/td>\n\n
06032008 is the invoice date (in this case, June 3, 2008).<\/p>\n<\/td>\n<\/tr>\n
\u2022<\/td>\n\n
I5963 is the invoice number.<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
In addition to the folder C:\\Invoices, JC also has a second folder: C:\\Archive. Inside this folder are subfolders for each of his vendors; for example, there\u2019s a folder named C:\\Archive\\ABC and another named C:\\Archive\\WSH. In turn, each of those subfolders has its own set of subfolders, one for each year. For example, C:\\Archive\\ABC might have the following subfolders:<\/p>\n\n\n\n\n\n
\u2022<\/td>\n\n
C:\\Archive\\ABC\\2006<\/p>\n<\/td>\n<\/tr>\n
\u2022<\/td>\n\n
C:\\Archive\\ABC\\2007<\/p>\n<\/td>\n<\/tr>\n
\u2022<\/td>\n\n
C:\\Archive\\ABC\\2008<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
Finally, each of these sub-subfolders has as many as 12 sub-sub-subfolders, one for each month of the year. For example, there\u2019s a subfolder named C:\\Archive\\ABC\\2008\\June. Is that important to us? You bet it is. What JC needs is a script that can parse the file name ABC06032008I5963.pdf and then, based on the information contained in that file name, move this invoice to the folder C:\\Archive\\ABC\\2008\\June.<\/p>\n
Got all that? Good. At least someone<\/i> is having some success today.<\/p>\n
So how does<\/i> the script work? Well, because the script only has to work locally, we start out by creating an instance of the Scripting.FileSystemObject<\/b>.<\/p>\n\n<\/thead>\n\n\n
\n
Note<\/b>. Could we get this script to work against a remote<\/i> machine? Well, yes \u2026 sort of. The problem is that JC needs the script to create any subfolders that don\u2019t currently exist; for example, if C:\\ Archive\\ABC\\2008\\June doesn\u2019t exist the script will have to create it. As we\u2019ve discussed before<\/b>, creating folders on remote computers can be a little tricky. If you can get past that hurdle, however, then you could use WMI to parse the file names and move the files. Which, interestingly enough, is something else<\/i> we\u2019ve discussed before<\/b>.<\/p>\n
Good point: by now what haven\u2019t<\/i> we discussed before? <\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
<\/div>\n
After creating an instance of the FileSytsemObject we then use the GetFolder<\/b> method to bind to the folder C:\\Invoices:<\/p>\n
Set objFolder = objFSO.GetFolder(\"C:\\Invoices\")\n<\/pre>\n
And once we\u2019ve done that<\/i>, well, all we can say is, \u201cHold on to your hats; things are about to get a little bit exciting.\u201d<\/p>\n
Well, OK. But we did say a little<\/i> bit exciting.<\/p>\n
Our first step on the road to our excitement \u2013 heck, the first step on any<\/i> road to excitement \u2013 is to set up a For Each loop to loop us through all the items in the folder\u2019s Files<\/b> collection. Inside that loop, we immediately execute these three lines of code:<\/p>\n
strVendorName = Left(objFile.Name, 3)\nstrMonth = MonthName(Mid(objFile.Name, 4, 2))\nintYear = Mid(objFile.Name, 8, 4)\n<\/pre>\n
This, as you might have guessed, is where we tease apart the file name. In line 1, we use the Left<\/b> function to grab the first three characters from the file name (the Name<\/b> property) and store those characters in the variable strVendorName. Why? You got it: because the first three characters in the file name represent the vendor abbreviation.<\/p>\n
In line 2, we actually do a couple things. To begin with, we use the Mid<\/b> function to grab two characters from the file name. Which two characters? Characters 4 and 5. (The parameter 4<\/b> means we should start with the fourth character in the string; the 2<\/b> means that we want to grab two characters.) As soon as we have those two characters we then use the MonthName<\/b> function to return the name of the month that corresponds to the numeric value; for example, if characters 4 and 5 are 06 (which they are) then MonthName returns June. Why? Because June is the sixth month of the year.<\/p>\n
No, really; you can look it up.<\/p>\n
Finally, we use the Mid function again, this time starting with the 8th<\/sup> character and taking a total of 4 characters. And because characters 8,9, 10, and 11 correspond quite nicely with the year, we stash this information in a variable named intYear.<\/p>\n
That brings us to this line of code:<\/p>\n
strFolderPath = \"C:\\Archive\\\" & strVendorName & \"\\\" & intYear & \"\\\" & strMonth & \"\\\"\n<\/pre>\n
What we\u2019re doing here is constructing the path to the folder where the first file in the collection needs to be moved. To construct that path we simply combine the following items:<\/p>\n\n\n\n\n\n\n\n\n\n
\u2022<\/td>\n\n
C:\\Archive\\<\/b>.<\/p>\n<\/td>\n<\/tr>\n
\u2022<\/td>\n\n
strVendorName<\/b>, the variable containing the vendor abbreviation.<\/p>\n<\/td>\n<\/tr>\n
\u2022<\/td>\n\n
A \\<\/b>.<\/p>\n<\/td>\n<\/tr>\n
\u2022<\/td>\n\n
intYear<\/b>, the variable containing the year the invoice was issued.<\/p>\n<\/td>\n<\/tr>\n
\u2022<\/td>\n\n
Another \\<\/b>.<\/p>\n<\/td>\n<\/tr>\n
\u2022<\/td>\n\n
strMonth<\/b>, the variable containing the month the invoice was issued.<\/p>\n<\/td>\n<\/tr>\n
\u2022<\/td>\n\n
A final \\<\/b>.<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
By the way, don\u2019t leave that final \\ off. If you do, the FileSystemObject will try to replace the folder C:\\Archive\\ABC\\2008\\June with the file itself. Needless to say, that\u2019s not going to work.<\/p>\n\n<\/thead>\n\n\n
\n
Note<\/b>. How do we know that doing a dumb thing like leaving off the trailing \\ will cause the script to fail? Well, um, when you\u2019re a Scripting Guy, well, you just know these things. OK?<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
<\/div>\n
After we construct the path we use this line of code to determine whether or not the folder C:\\Archive\\ABC\\2008\\June already exists (note the somewhat-clumsy syntax If Not Exists<\/b>, a goofy way of saying \u201cIf this doesn\u2019t exist \u2026\u201d):<\/p>\n
If Not objFSO.FolderExists(strFolderPath) Then\n<\/pre>\n
And what if the folder doesn\u2019t<\/i> exist? Why, then we create it, of course:<\/p>\n
Set objFolder2 = objFSO.CreateFolder(strFolderPath)\n<\/pre>\n
Once that\u2019s done we can then use the MoveFile<\/b> method to move the file to its new home:<\/p>\n
objFSO.MoveFile objFile.Path, strFolderPath\n<\/pre>\n
As you can see, we simply pass MoveFile two parameters: objFile.Path<\/b>, the complete path to the file; and strFolderPath<\/b>, the variable containing the path to the appropriate Archive folder.<\/p>\n
And then from there it\u2019s back to the top of the loop, where we repeat the process with the next file in the collection. Etc., etc.<\/p>\n
Good point: guess we do<\/i> owe you an apology, don\u2019t we? After all, this turned out to be more than just a little<\/i> bit exciting. A lot<\/i> more.<\/p>\n","protected":false},"excerpt":{"rendered":"
Hey, Scripting Guy! I have a folder filled with invoice files. Each of these files has been given a file name based on a specific three-part naming convention: a three-letter abbreviation of the vendor name; the invoice date (e.g., 06032008) ; and the invoice number (which can be of varying length). In other words, all […]<\/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":[38,3,12,5],"class_list":["post-55433","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-scripting","tag-files","tag-scripting-guy","tag-storage","tag-vbscript"],"acf":[],"blog_post_summary":"
Hey, Scripting Guy! I have a folder filled with invoice files. Each of these files has been given a file name based on a specific three-part naming convention: a three-letter abbreviation of the vendor name; the invoice date (e.g., 06032008) ; and the invoice number (which can be of varying length). In other words, all […]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/55433","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=55433"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/55433\/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=55433"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/categories?post=55433"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/tags?post=55433"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}