0

I'm trying to use VBScript to select all csv's in a specific folder and then concatenate them into one. I am adding all of the csv's to a collection using ExecQuery on Win32_Directory, specifying the path and the extension properties. I have five csv's in the folder to test. But the collection returned is always null.

Here's my code:

strComputer = "."
Set wshShell = WScript.CreateObject( "WScript.Shell" )
Set objWMIService = GetObject("winmgmts:" _
   & "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")

'Options for CreateTextFile
boolOverwrite = True
boolUnicode = True

'Options for OpenTextFile
constForReading = 1
constForWriting = 2
constForAppending = 8
boolCreate = False
constTristate = -1

strPath = "C:\Users\adam\Documents\Test\Temp.csv"
strDrivePath = "C:\Users\adam\Documents\Test"

'Creates object reference to files in relevant folder.
Set objFSO = CreateObject ("Scripting.FileSystemObject")

'Creates new CSV to write others' contents to.
Set objNew = objFSO.CreateTextFile(strPath,boolOverwrite,boolUnicode)

Set colCSV = objWMIService.ExecQuery _
 ("SELECT * FROM Win32_Directory WHERE Path = strDrivePath AND Extension = 'csv'")

'Concatenates contents of CSVs
For Each objCSV in colCSV
 Set objTemp = objFSO.OpenTextFile(objCSV.Path & objCSV.FileName & objCSV.Extension,constForReading,boolCreate,constTristate)
 Set strLine = objTemp.ReadLine
 objNew.Write strLine 

Next

I am also unsure of whether the way I've specified the path for OpenTextFile is going to work or not. But my main concern right now is getting the query to return the files I want.

Thanks for any help!

Adam Harvey
  • 55
  • 1
  • 2
  • 10

1 Answers1

2

You've got a few issues here.

  1. If you want to select files, use the CIM_DataFile class. Win32_Directory is for, you guessed it, directories.

  2. Backslashes in the Path property must be escaped (\\).

  3. strDrivePath is a variable but you're using it literally in your WMI query.

  4. Is your intention to run this script on a remote machine? If not, why not use the FileSystemObject methods? You already have an FSO object created.

Here is a similar question I answered in the past showing how you can use a FileSystemObject or a WMI query to find files matching a specification.

In this situation, your query might look something like this:

strFileSpec = "C:\\Users\\Adam\\Documents\\Test\\%.csv"

Set colCSV = objWMIService.ExecQuery _
 ("select * from CIM_DataFile where Name like '" & strFileSpec & "'")

But your query will run faster (often noticeably) if you specify values for Drive and Path in your statement. See my linked post for an example.

Edit: I just realized that you were the OP of the question I linked to as well!

Community
  • 1
  • 1
Bond
  • 16,071
  • 6
  • 30
  • 53
  • Thanks for the help! Yeah, I thought it was weird for Win32_Directory to be used for files. But the MSDN entry made it sound like it would work. I don't know, I'm still not too great at reading the MSDN. I'm using WMI instead of FSO because I read a little on it, and it looks like it should be quicker for my purposes to use WMI. I'm querying in a folder that has more than just CSV's. And, from what I've read, FSO only works faster if you are selecting everything in a folder. Either way, my code still isn't working. It isn't entering the For Each statement. But at least this gave me a start. – Adam Harvey Aug 19 '14 at 15:55
  • Actually, if you've got any idea on why it won't enter the For Each statement, that would be really nice. I've been trying to figure it out. But I can't seem to get anywhere. I've put an echo statement on either side of the For Each to make sure the query is working. I've also tried using this For Each to make sure the collection isn't the issue: For Each objCSV in colCSV wscript "A" Next But it won't enter that statement either. – Adam Harvey Aug 19 '14 at 17:08
  • Try the FSO route. Really. It's _much_ easier and I can't imagine WMI would be any faster than using the FSO. `For Each objFile In objFSO.GetFolder("C:\Users\Adam\Documents\Test").Files`. That's all it takes to iterate every file in a folder. `If UCase(Right(objFile.Name, 4)) = ".CSV" Then`. One more line to test if it's a CSV. – Bond Aug 19 '14 at 18:15
  • Okay, so I changed all of the WMI stuff to this: For Each objFile In objFSO.GetFolder("C:\Users\Adam\Documents\Test").Files If UCase(Right(objFile.Name, 4)) = ".CSV" Then Set objTemp = objFSO.OpenTextFile(objFile.Name,constForReading,boolCreate,constTristate) Set strLine = objTemp.ReadLine objNew.Write strLine End If Next But CMD returns "Runtime Error: File Not Found" – Adam Harvey Aug 19 '14 at 19:08
  • Which line is causing the error? Try using `objFile.Path` instead of `objFile.Name` in `OpenTextFile`. – Bond Aug 20 '14 at 03:44
  • Also, `Set` is only used on objects. `ReadLine` returns a string, not an object, so don't "set" `strLine`, just assign it normally. – Bond Aug 20 '14 at 03:45
  • Okay, I've taken out Set where I should. It turns out my file not found error was due to me, once again, either misreading the MSDN documentation or the documentation being wrong (which it has been on several other occasions). I did need to use Path instead of Name. So my script is nearly working now. The only issue it is having is that it doesn't write the correct text to the new CSV. It might be an encoding issue. I'll have to check that. But, thanks for all of your help! I really appreciate it! – Adam Harvey Aug 20 '14 at 15:01