VB Script to mimic this DOS Batch Script

I'm not sure but reading the files once instead of twice should make it a lot faster. I found an earlier version and changed that to read the files line by line. Disk caching should make that fast enough versus reading the file in memory. Splitting a very large string (when reading the whole file at once) into an array may also not be very fast.

Here's my version:

Option Explicit

' note the year is a string
Const C_YEAR = "2004" ' "2012"

Const C_DATA_DIR = ".\" ' "C:\Data\"

Const C_FILELIST = "filelist.txt"

Const C_SEP = "|"


Dim objFSO, objFilelist, arrFields, strFilename, intYearColumnNumber, objFile, blnFirst, strLine, objFileOut1, objFileOut2

Set objFSO = CreateObject("Scripting.FileSystemObject")

' get file name and year column number for each file to be processed
Set objFilelist = objFSO.OpenTextFile(C_FILELIST, 1, False)
While Not objFilelist.AtEndOfStream
	arrFields = Split(objFilelist.ReadLine, C_SEP)
	strFilename = arrFields(0)
	intYearColumnNumber = CInt(arrFields(1))
	Set objFile = objFSO.OpenTextFile(C_DATA_DIR & strFilename, 1, False)
	blnFirst = True
	While Not objFile.AtEndOfStream
		strLine = objFile.ReadLine
		If blnFirst Then
			Set objFileOut1 = objFSO.CreateTextFile(C_DATA_DIR & C_YEAR & "_" & strFilename, True)
			Set objFileOut2 = objFSO.CreateTextFile(C_DATA_DIR & "Non_" & C_YEAR & "_" & strFilename, True)
			objFileOut1.WriteLine strLine
			objFileOut2.WriteLine strLine
			blnFirst = False
		Else
			arrFields = Split(strLine, C_SEP)
			If UBound(arrFields) >= intYearColumnNumber-1 Then
				If arrFields(intYearColumnNumber-1) = C_YEAR Then
					objFileOut1.WriteLine strLine
				Else
					objFileOut2.WriteLine strLine
				End If
			End If
		End If
	Wend
	If Not blnFirst Then
		objFileOut1.Close
		Set objFileOut1 = Nothing
		objFileOut2.Close
		Set objFileOut2 = Nothing
	End If
	objFile.Close
	Set objFile = Nothing
Wend

objFilelist.Close
Set objFilelist = Nothing

Set objFSO = Nothing

Select all Open in new window

Note: I tested with files found also in an earlier question ("2012" files). Also I saw in another question that you wanted to use a separate data directory so prepared for that.

Yes I think the idea to create and append files as unique years are found is the best. I'll have a look at that. Because writing back to the file all the data from other years would take very long (with big files) until one or more years with a lot of data have been taken out.

Here's a new version:

Option Explicit

Const C_DATA_DIR = ".\" ' "C:\Data\"

Const C_FILELIST = "filelist.txt"

Const C_SEP = "|"
Const C_SEP_FILELIST = ","


Dim objFSO, objFilelist, arrFields, strFilename, intYearColumnNumber, objFile, blnFirst, strLine, strHeader, strYear, objFileOut1, objFileOut2, colFiles

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set colFiles = CreateObject("Scripting.Dictionary")

' get file name and year column number for each file to be processed
Set objFilelist = objFSO.OpenTextFile(C_FILELIST, 1, False)
While Not objFilelist.AtEndOfStream
	arrFields = Split(objFilelist.ReadLine, C_SEP_FILELIST)
	strFilename = arrFields(0)
	intYearColumnNumber = CInt(arrFields(1))
	Set objFile = objFSO.OpenTextFile(C_DATA_DIR & strFilename, 1, False)
	blnFirst = True
	While Not objFile.AtEndOfStream
		strLine = objFile.ReadLine
		If blnFirst Then
			strHeader = strLine
			blnFirst = False
		Else
			arrFields = Split(strLine, C_SEP)
			strYear = "Non"
			If UBound(arrFields) >= intYearColumnNumber-1 Then
				If IsNumeric(arrFields(intYearColumnNumber-1)) Then
					strYear = arrFields(intYearColumnNumber-1)
				End If
			End If
			If Not colFiles.Exists(strYear) Then
				colFiles.Add strYear, objFSO.CreateTextFile(C_DATA_DIR & strYear & "_" & strFilename, True)
				colFiles(strYear).WriteLine strHeader
			End If
			colFiles(strYear).WriteLine strLine
		End If
	Wend
	objFile.Close
	Set objFile = Nothing
	For Each strYear In colFiles
		colFiles(strYear).Close
		Set colFiles(strYear) = Nothing
	Next
	colFiles.RemoveAll
Wend

objFilelist.Close
Set objFilelist = Nothing

Set objFSO = Nothing
Set colFiles = Nothing

Select all Open in new window

How's the speed? Any use testing with the original code, reading the file into memory at once and then splitting it? Probably mainly depends on the size of your biggest file.

Yes, you could add a check on line 34:

If CheckYear(CInt(arrFields(intYearColumnNumber-1))) Then strYear = arrFields(intYearColumnNumber-1)

Select all Open in new window

and for example at the bottom of the script add:

Function CheckYear(intYear)
	Select Case intYear
		Case 2011, 2012, 2013:
			CheckYear = True
		Case Else
			CheckYear = False
	End Select
End Function

Select all Open in new window

That last function is easy to extend when needed or even based on the current year (if that's the logic you're after).

If you call it like that it should be possible to just use a line like this a number of times, obviously at least at the start and at the end:

WScript.Echo Now()

Select all Open in new window

You could test one specific year easily by adjusting the CheckYear function now. If you want to take out the 'non' year processing that would be a bit more work...

Earlier it ran fine, so is this after adding the CheckYear function? If so, what does it look like now? The version I posted was for 2011/12/13 so maybe a typo there? If not using this new functionality, can you post the file because like I said, it was running fine before?

No I meant the code file. Order doesn't matter. New files are created as needed and kept open until the end of the input file.

Ah, I see. Yeah, this behaviour changed when the split changed (at the time I thought for the better) to make files for each year as necessary. So then I guess it would be needed to define a range of years you want to have and create those files (with header) up front even when no data is found, that part of the code should be easy to adjust. We could use an array so the later addition of years is still easy. I'll be back...

I was out for a bit, reading your comments just now.

I see you found the commented out bit. And assuming you took out that first entry from the Array you posted earlier, as those should all be numbers.

I'm not sure what that last post means. You don't need another .vbs file, or are you running all scripts from one other script for testing purposes? In that case post the whole code because I don't know what objShell is (I can guess but what if that is actually the problem itself)...

Oh wow, that was a bit more than I expected... ;-)

Well at least it's clear now that the Print function is defined, because when you posted the bit of code earlier I couldn't tell what that did.

It's a common problem that when you call a Sub (or a function without using the return value) you have to leave out the parentheses. So either

objShell.Run """C:\Windows\System32\cscript.exe"" ""D:\AppFiles\SDS\SDS_Retail_R3\Scripts\splice_years.vbs"""

Select all Open in new window

or

intErr = objShell.Run("""C:\Windows\System32\cscript.exe"" ""D:\AppFiles\SDS\SDS_Retail_R3\Scripts\splice_years.vbs""")

Select all Open in new window

It will usually work when there is only 1 parameter because then it's just as if you're surrounding that value with parentheses instead of using them to 'bind' the parameter(s) to the function call.

The reason I go into this is that you probably need to add the parameters

, 0, True

Select all Open in new window

back in unless the process can run in the background (same applies to line 259). The 'True' means the calling process waits for the called process to end. This is obviously necessary if another process (or the calling process) needs to work with the output generated by the previous call. Have a look here for more info: http://technet.microsoft.com/en-us/library/ee156605.aspx 

The data directory can be configured in "splice_years.vbs":

Const C_DATA_DIR = "D:\AppFiles\SDS\SDS_Retail_R3\Data\"

Select all Open in new window

In that case don't use parentheses.