VBS copy text to new file, but remove header

totalimpact
totalimpact used Ask the Experts™
on
I have a group of csv files that I am merging into a "master" file, but before merging them to the master file, I need to remove the header from the copied text.

My attempt is around lines 77-90, but it is not working.
 
' Compare temp files to archived files, merge new data to master file, then archive temp files

Option Explicit
'variables: -----------------------------------------------
Dim archived_Path, temp_path, sorted_logs_path, Master_file_path, Log_File, ftp_days, ftp_adr, ftp_user, ftp_pass, ftp_files
Dim intAnswer, datFetchDate, strFetchDate, strDestDir, intDays 
Dim oFTPScriptFSO, oFTPScriptShell, sFTPScript, sFTPTemp, sFTPTempFile, sFTPResults, sResults, fFTPScript, fFTPResults
Dim sOriginalWorkingDirectory, sRemotePath, sLocalPath 
Dim objFSO, objFSOText, objFile, objTextFile, objHash
Dim MyArray, IP, Value 
Dim i,strNextLine,strHeader
Dim bNeedToWriteHeader
Dim objFolder, Subfolder, objsubFolder, colFiles, objFiletemp, objMaster, strlinetemp, Lastline_temp 
Dim objFilear, Lastline_ar, subfolder_ar, strlinear, objfilear_path, file_ar_readall, AShell
Dim myIdx

archived_Path="c:\test\arc\" 'archived folder path
temp_path="c:\test\temp\" 'tempfolderpath for ftp downloads
sorted_logs_path="c:\test\sortedlogs\"
Master_file_path="c:\test\masterfile.txt" 'master file path - this is really a temp file.
'end variables: -------------------------------------------

intAnswer = _
    Msgbox("Do you want to process the logs?", _
        vbOkCancel, "Process New Logs")

If intAnswer = vbCancel Then
	WScript.Quit
Else
    End If
		
Set objFSO = CreateObject("Scripting.FileSystemObject")
'----------- Sort new files in temp folder, merge data into master file, and archive processed files --------------

'Set objFSO = CreateObject("Scripting.FileSystemObject")

If objfso.FileExists(Master_file_path) = False Then
    objfso.CreateTextFile(Master_file_path)
End If

Set objFolder = objFSO.GetFolder(temp_path)
'WScript.Echo objFolder.Path

For Each Subfolder In objFolder.SubFolders
'    WScript.Echo Subfolder.Path & " - " & temp_path & " - " & archived_path
    subfolder_ar=Replace(LCase(Subfolder.Path),LCase(temp_path),LCase(archived_Path))
    
'    WScript.Echo "Archive Subfolder =" & subfolder_ar
    If objfso.FolderExists(subfolder_ar) Then
        Set objsubFolder = objFSO.GetFolder(Subfolder.Path)
        
        Set colFiles = objsubFolder.Files
        For Each objFile In colFiles
'            WScript.Echo objfile.Path
            Set objFiletemp = objFSO.OpenTextFile(objfile.Path, 1)
            Do Until objFiletemp.AtEndOfStream
                strlinetemp=objFiletemp.ReadLine
                Lastline_temp = strlinetemp
            Loop
            objFiletemp.Close
            
            objfilear_path=Replace(LCase(objfile.Path),LCase(temp_path),LCase(archived_Path))
'            WScript.Echo objfilear_path
            If objfso.FileExists(objfilear_path) Then
                Set objFilear = objFSO.OpenTextFile(objfilear_path, 1)
                Do Until objFilear.AtEndOfStream
                    strlinear=objFilear.ReadLine
                    'WScript.Echo strlinear
                    Lastline_ar = strlinear
                Loop
                objFilear.Close
            End If
'            WScript.Echo objfso.FileExists(objfilear_path)
            If (StrComp(Lastline_temp,Lastline_ar)<>0) Or (objfso.FileExists(objfilear_path)=False)  Then
'                WScript.Echo objfile.Path & " - in if strcomp"
                Set objFiletemp = objFSO.OpenTextFile(objfile.Path, 1)
'-------------	Code to remove firstline before write to Master
	  Set objMaster=objFSO.OpenTextFile(Master_file_path, 8)
	  myIdx = 0
                Do Until objFiletemp.AtEndOfStream
	      myIdx = myIdx + 1
                    strlinetemp=objFiletemp.ReadLine
	      If myIdx > 1 Then
		objMaster.WriteLine strlinetemp
	      End If
                Loop
                objFiletemp.Close
                Err.Clear
                objMaster.Close
'-------------	end new code to remove first line
                If Err.Number=0 Then
'                    WScript.Echo "in move file 1 - " & Replace(LCase(objfilear_path),LCase(objfile.Name),"")
                    Err.Clear
                    objfso.copyFile objfile.Path,Replace(LCase(objfilear_path),LCase(objfile.Name),""),True
                    objfso.DeleteFile objfile.Path
                    If Err.Number<>0 Then
'                        WScript.Echo Err.Number & Err.Description
                    End If
                End If
            Else
                objfso.DeleteFile(objfile.Path)
'                WScript.Echo "in delete file 2"
            End If    
        Next
    Else
        Set objsubFolder = objFSO.GetFolder(Subfolder.Path)
        Set colFiles = objsubFolder.Files
        For Each objFile In colFiles
'            WScript.Echo objfile.Path & " - in move folder copy file content"
            Set objFiletemp = objFSO.OpenTextFile(objfile.Path, 1)
            file_ar_readall=objfiletemp.ReadAll
            objFiletemp.Close
            Set objMaster=objFSO.OpenTextFile(Master_file_path, 8)
            objMaster.Write file_ar_readall
            objMaster.Close
        Next    
        objfso.MoveFolder Subfolder.Path,subfolder_ar
'        WScript.Echo "in move folder 3"
    End If
Next

Set objFolder = objFSO.GetFolder(temp_path)

For Each Subfolder In objFolder.SubFolders
		If subfolder.Files.Count = 0 And subfolder.SubFolders.Count = 0 Then
'    If subfolder.Files.Count = 0 Then
'        WScript.Echo "Delete folder - " & subfolder.Path
        subfolder.Delete
    End If
Next
WScript.Quit

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Bill PrewTest your restores, not your backups...
Top Expert 2016

Commented:
I didn't read the entire script, but the code you have there seems like it should skip the first line.  Are you sure there aren't more than one line at the top?

~bp
KarenAnalyst programmer

Commented:
What do you mean by "it is not working"? Is there an error?
You do not seem to be doing anything with this new master file you write, ie. line 94
                    objfso.copyFile objfile.Path,Replace(LCase(objfilear_path),LCase(objfile.Name),""),True
 
Shouldnt this be
                  objfso.copyFile Master_file_path,Replace(LCase(objfilear_path),LCase(objfile.Name),""),True
 

Author

Commented:
hmm, ee mail is hitting my spam folder...

Yes - the header is still being copied to the combined master file, so if there are several files, being parsed theres headers all through the middle of my combined master file.

I will just tried that tip snowberry, the header is still being copied over.
OWASP Proactive Controls

Learn the most important control and control categories that every architect and developer should include in their projects.

Most Valuable Expert 2012
Top Expert 2014

Commented:
While I haven't read the script either (because I find it hard to follow), I think your best bet would be set a boolean for whether the header was written, then use SkipLine when that boolean value is True.

This assumes that the header is always on the very first line of each file.  Something like this.

Regards,

Rob.
blnHeaderWritten = False
For Each objFile In colFiles
	Set objInput = objFSO.OpenTextFile(objFile.Path, 1, False)
	If blnHeaderWritten = False Then
		objOutput.WriteLine objInput.ReadAll
	Else
		objInput.SkipLine
		objOutput.WriteLine objInput.ReadAll
	End If
Next

Open in new window

Bill PrewTest your restores, not your backups...
Top Expert 2016

Commented:
Could you be falling into this code block, which doesn't seem to skip the headers?

    Else
        Set objsubFolder = objFSO.GetFolder(Subfolder.Path)
        Set colFiles = objsubFolder.Files
        For Each objFile In colFiles
'            WScript.Echo objfile.Path & " - in move folder copy file content"
            Set objFiletemp = objFSO.OpenTextFile(objfile.Path, 1)
            file_ar_readall=objfiletemp.ReadAll
            objFiletemp.Close
            Set objMaster=objFSO.OpenTextFile(Master_file_path, 8)
            objMaster.Write file_ar_readall
            objMaster.Close
        Next    
        objfso.MoveFolder Subfolder.Path,subfolder_ar
'        WScript.Echo "in move folder 3"
    End If

~bp
Most Valuable Expert 2012
Top Expert 2014

Commented:
Oh, I just released I forgot to set blnHeaderWritten to True....if you decide to implement that approach...

Rob.
blnHeaderWritten = False
For Each objFile In colFiles
	Set objInput = objFSO.OpenTextFile(objFile.Path, 1, False)
	If blnHeaderWritten = False Then
		objOutput.WriteLine objInput.ReadAll
		blnHeaderWritten = True
	Else
		objInput.SkipLine
		objOutput.WriteLine objInput.ReadAll
	End If
Next

Open in new window

Commented:
I think I see what''s going wrong:
First off; whatch your if and elses
On line 27, remove the extra Else
 
If intAnswer = vbCancel Then  
        WScript.Quit  
Else  
	End If

Open in new window

On line 96, comment out the if and End If if it doesn't do anything
 
If Err.Number<>0 Then  
'                        WScript.Echo Err.Number & Err.Description  
                    End If

Open in new window


I can't really explain what I think is going wrong here (aint I usefull..) but to get it fixed I think you should begin with simplifying it. Remove some of the file exists checks and such. Focus on getting your script to do what you want it to do when you create the perfect environment, after that you can make it better to ensure it always works. I'll rewrite your script a bit then post it later to show you what I mean.

Cheers!
Tischa
Commented:
Okay, so I tried to rewrite your script...that took to long so I made a small new one :)
In writing this new one, I found out that the problem lies in the way VBScript handles the data it gets from reading a line using: objfile.readline. It keeps the data until you do something with it..see attached script for a working version of what you wanted, and illustration of what I mean by the above.

Note: This script doesn't check for anything nor copies or deletes anything, I thought that (judging from the complexity of your own script) you could ad that yourself...should you require help with those parts: I'm here.

PS: What I meant with my post before you'll see here. This script is as short and simple as I could make it. Thus I was easily able to debug/troubleshoot it, thus finding out about the weird side effect of readline.  

Hope it helps you along!
Tischa
Option Explicit
'Declare variables
Dim BooProcess, strSubfolder, objSubFolder, strFile, objFileTemp, strTextPart, strTextFull, objMasterFile, strTempText
Dim strPath1		:	strPath1		=	"C:\test\temp\"
Dim objFso			:	Set objFso		=	WScript.CreateObject("Scripting.FileSystemObject")
Dim objFolder		:	Set objFolder	=	objFso.Getfolder(strPath1)
Dim i				:	i				=	0
Dim strMasterFile	:	strMasterFile	=	"C:\test\masterfile.txt"

BooProcess	=	MsgBox("Do you want to process the logs?", vbOkCancel, "Process New Logs")

If(BooProcess = vbCancel) Then
	WScript.Quit()
End If

For Each strSubFolder In objFolder.SubFolders
	Set objSubFolder	=	objFso.GetFolder(strSubFolder)
	For Each strFile In objSubFolder.Files
		Set objFileTemp	=	objFso.OpenTextFile(strFile.Path, 1)
		Do Until objFileTemp.AtEndOfStream
			i	=	i + 1
			'Dump the first line in a temp variable so that we can exclude it from the full text
			If(i = 1) Then
				strTempText	=	objFileTemp.ReadLine
			Else
				strTextPart	=	strTextPart & vbCrLf & objFileTemp.ReadLine
			End If
		Loop
		i	=	0
		strTextFull		=	strTextFull & VbCrLf & strTextPart
		objFileTemp.Close
		strTextPart		=	""
	Next
Next 

Set objMasterFile	=	objFso.OpenTextFile(strMasterFile, 8)
objMasterFile.WriteLine	strTextFull
objMasterFile.Close

WScript.Echo "Done Processing!"
WScript.Quit()

Open in new window

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial