miniGeek.org » SQL activeX file concatenation

SQL activeX file concatenation

Believe it or not, vbscript doesn't seem to have a filesystem object that allows file concatenation.

This here is an article on creating a file concatenation script in ActiveX (original source: http://www.codecomments.com/archive300-2004-3-158880.html). I have written this to call the copy command in DOS, I originally wrote this by opening the file, using the ReadAll (ReadLine) method and writing to a new file, but I found this to be a slow method given the size of the files.

Onto the script:

'**********************************************************************
' Visual Basic ActiveX Script
'************************************************************************
' This script was written by Andrew Bowden on 11th of May 2006
' for Clinical Information Management. This script is designed to
' loop through a folder and search for all the files in the folder that
' begin with a 0 (zero), then concatenate these files together into
' a single file. The script then goes on to delete these files. In addition,
' it also moves any files that are in that folder that have the string:
' "Extract_PA_UND.txt" in it to another location. I have used a system
' call to copy the files as there is no built in method in vbscript to do this.
' The alternative was to open each file, read each line and write each
' line to a new file but this was too time consumptive given that the
' files are large.
'
' Created by: Andrew Bowden
' Created on: 11th of May 2006
' Modified by: Andrew Bowden
' Modified on: 11th of May 2006
Function Main()
Set fs = CreateObject("Scripting.FileSystemObject")
Set oShell = CreateObject("WScript.Shell")
sSourceFolder = "cisbar2000cisnewExtracts" 'The folder to scan through
sTargetFile = "cisbar2000cisAttached_SQL_DBsExtractsExtract_PA.txt" 'The file to concatenate everything to
sTarget_UND = "cisbar2000cisAttached_SQL_DBsExtractsExtract_PA_UND.txt" 'The file to move the UND to
sTmpString = ""
Set oFolder = fs.GetFolder(sSourceFolder)
' Loop through all of the files in the folder selected and create a string
For Each oFile In oFolder.Files
' I have assumed that every file in the folder that begins with a zero (0) is one that I want - ignore all others
If Left(fs.GetFileName(oFile),1) = "0" Then
sTmpString = sTmpString & oFile.shortPath & "+"
End If
' Now, handle the Undischarged file and simply move it to where you want it!
' Move the undischarged files (for some reason this didn't work in the delete loop below)
If Instr(fs.GetFileName(oFile), "Extract_PA_UND.txt")>0 Then
fs.MoveFile oFile,sTarget_UND
End If
Next

' If there are files to copy, then do it and return true. Else, return false If Len(sTmpString)>1 Then sTmpString = Left(sTmpString, Len(sTmpString)-1) sCmd = "%comspec% /c copy /b /y " & sTmpString & " " & sTargetFile ' msgbox (sCmd) ' run the command. oShell.Run sCmd, 0, True ' Now, Delete all of the source files For Each oFile In oFolder.Files ' Use the same criteria for deletion as above If Left(fs.GetFileName(oFile),1) = "0" Then fs.DeleteFile oFile End If Next Main = DTSTaskExecResult_Success Else Main = DTSTaskExecResult_Failure End If End Function