totalimpact
asked on
VBS copy text to new file, but remove header
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.
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
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),LCa se(objfile .Name),"") ,True
Shouldnt this be
objfso.copyFile Master_file_path,Replace(L Case(objfi lear_path) ,LCase(obj file.Name) ,""),True
You do not seem to be doing anything with this new master file you write, ie. line 94
objfso.copyFile objfile.Path,Replace(LCase
Shouldnt this be
objfso.copyFile Master_file_path,Replace(L
ASKER
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.
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.
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.
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
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(objfil e.Path, 1)
file_ar_readall=objfiletem p.ReadAll
objFiletemp.Close
Set objMaster=objFSO.OpenTextF ile(Master _file_path , 8)
objMaster.Write file_ar_readall
objMaster.Close
Next
objfso.MoveFolder Subfolder.Path,subfolder_a r
' WScript.Echo "in move folder 3"
End If
~bp
Else
Set objsubFolder = objFSO.GetFolder(Subfolder
Set colFiles = objsubFolder.Files
For Each objFile In colFiles
' WScript.Echo objfile.Path & " - in move folder copy file content"
Set objFiletemp = objFSO.OpenTextFile(objfil
file_ar_readall=objfiletem
objFiletemp.Close
Set objMaster=objFSO.OpenTextF
objMaster.Write file_ar_readall
objMaster.Close
Next
objfso.MoveFolder Subfolder.Path,subfolder_a
' WScript.Echo "in move folder 3"
End If
~bp
Oh, I just released I forgot to set blnHeaderWritten to True....if you decide to implement that approach...
Rob.
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
I think I see what''s going wrong:
First off; whatch your if and elses
On line 27, remove the extra Else
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
First off; whatch your if and elses
On line 27, remove the extra Else
If intAnswer = vbCancel Then
WScript.Quit
Else
End If
On line 96, comment out the if and End If if it doesn't do anythingIf Err.Number<>0 Then
' WScript.Echo Err.Number & Err.Description
End If
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
~bp