Login  | |
|
 |
Skipping 2 lines in txt file!! HELP :) - 6/13/2008 1:28:22 AM
|
|
 |
|
| |
rizakaya
Posts: 6
Score: 0
Joined: 6/13/2008
From: UK
Status: offline
|
Hi all,
I need help !!, below is the script to read from a text file and convert to a readable format. The txt file verifies converted actual number of records against verifying number on the actual txt file. This worked fine until the new text file verifying number moved 2 lines down the text file.
I need to point the code " intFileRecords = CLng(Mid(strRecord, 55, 6)) " to 2 lines below.
Please can someone help and make the new code " intFileRecords = CLng(Mid(strRecord, 15, 6)) " but reading from the 2 lines down or if it helps the verifying number is all always going to be at the end of the text file. So either will help. End of the text file or skipping 2 lines.
Thank you in Advance.
'
' Check that all records in the file were read by comparing the no of records read to the number
' of records the file says it contains.
'
intFileRecords = CLng(Mid(strRecord, 55, 6))
If(intNoOfRecords <> intFileRecords) Then
strErrNoOfRec = "Process Cancelled." & vbLF & vbLF & "The number of records copied from file '" & fsoRead.GetFileName(args(i)) & "' is " & intNoOfRecords & _
"," & vbLF & "but the number of records the file says it contains is " & intFileRecords & ". Please investigate."
shellError.Popup strErrNoOfRec, 0, "Error in quantity of records!", 0 & 48
Wscript.Quit()
End If
'
' Note the file successfully processed and increment the counters
'
strCopiedFiles = strCopiedFiles & strFileName & " - " & intNoOfRecords & vbLf
intTotNoOfRecords = intTotNoOfRecords + intNoOfRecords
intTotNoOfFiles = intTotNoOfFiles + 1
Next
'
' Copy the Temp file to the New files.
'
Set objTempFile = fsoWrite.GetFile(strTempFile)
objTempFile.Copy strNewFile, true
objTempFile.Copy "C:\FPAY Daily MailboxData.txt", true
'
' Inform the user of the files and records processed
'
strCopiedFilesMsg = "The following " & intTotNoOfFiles & " files were converted and copied to the files " & vbLf & _
"'FPAY Daily MailboxData.txt' and 'FPAY\FPAY CAF,KKL Removed\" & fsoWrite.GetFileName(strNewFile) & "'. " & vbLf & vbLf & _
"No of records in each file: " & vbLF & vbLF & strCopiedFiles & vbLF & _
"Total No of Records: " & intTotNoOfRecords
Msgbox strCopiedFilesMsg,,"Files Converted"
|
|
| |
|
|
|
|
RE: Skipping 2 lines in txt file!! HELP :) - 6/15/2008 8:52:30 PM
|
|
|
|
| |
rizakaya
Posts: 6
Score: 0
Joined: 6/13/2008
From: UK
Status: offline
|
Hi,
Below id the full code listing.
Thanks.
'************************************************************************************************************************************************************
'
' Date: 20 March 2008
'
'------------------------------------------------------------------------------------------------------------------------------------------------------------
Option Explicit
Const strWriteFilePath = "C:\FPAY\FPAY CAF,KKL Removed\"
const intFirstRecord = 5 ' Line Number of the first record
Dim args ' The collection of files dropped onto the program
Dim i ' Counter to loop through the files
Dim result ' Holds the users input from a Messagebox
Dim fsoRead ' Object for reading
Dim fsoWrite ' Object for writing
Dim readStrm ' Text stream for reading from the file Mailbox files
Dim writeStrm ' Text stream for writing to the temp file
Dim strFileName ' Name of the Mailbox File being read
Dim strTempFile ' Name of the Temp file being written to
Dim strNewFile ' Name that the Temp file is changed to
Dim objTempFile ' An instance of the Temporary file object
Dim strRecord ' The record as read from the original file
Dim intNoOfRecords ' The no of records read from the Mailbox file
Dim intFileRecords ' The no of records in the Mailbox file according to the files report
Dim intTotNoOfRecords ' The total no of records in all files combined
Dim intTotNoOfFiles ' The total no of files converted
Dim strAmendedRecord ' The converted record ready to be written to the new file
Dim shellError ' The shell used to display errors
Dim strErrNoArgs ' The No arguments error message
Dim strErrFileName ' The Incorrect file name message
Dim strErrNoOfRec ' Incorrect no of records message
Dim strErrDataType ' Incorrect data type for amount
Dim strCopiedFiles ' List of copied files
Dim strCopiedFilesMsg ' The message that is created to inform the user of the files copied
Dim straaa, strbbb, dblAmount, strName, strRef, strDate
Set shellError = Wscript.CreateObject("Wscript.Shell") ' Instantiate error object
Set args = fSortArray(Wscript.Arguments)
'
' Quit if user doesn't provide any files to process
'
If args.Count < 1 Then
strErrNoArgs = "Ctrl + click on the days files then drag and drop them onto this programs icon." & vbLF & _
"All of the files will be concatenated into a file and the data will be converted into comma separated values for CAF,KKL,etc..Extraction."
shellError.Popup strErrNoArgs, 0, "No files to process!", 0 & 48
Wscript.Quit()
End If
Set fsoRead = Wscript.CreateObject("Scripting.FileSystemObject")
Set fsoWrite = Wscript.CreateObject("Scripting.FileSystemObject")
strTempFile = strWriteFilePath & "TMPFile.txt"
'
' Check that the new file does not exist. If it does, ask the user if they want to overwrite it. Otherwise cancel the operation.
'
strNewFile = strWriteFilePath & Left(fsoRead.GetFileName(args(0)), 6) & " FPAY MailboxDailyData.txt"
If fsoWrite.FileExists(strNewFile) Then
result = Msgbox("File 'FPAY\FPAY CAF,KKL Removed\" & fsoWrite.GetFileName(strNewFile) & "' already exists. " & _
"Do you want " & vbLF & "to overwrite the existing file?", vbOK, "File already exists!")
If (result = vbOK) Then
Set writeStrm = fsoWrite.CreateTextFile(strTempFile, True)
Else
Wscript.Quit()
End If
Else
Set writeStrm = fsoWrite.CreateTextFile(strTempFile, True)
End If
intTotNoOfRecords = 0 ' Initialise the Total No of Records counter
intTotNoOfFiles = 0 ' Initialise the Total No of Files counter
writeStrm.WriteLine("Saa,Bbb,Amount,Name,Ref,Date") ' Write titles to first row in file
'
' For each file that is parsed to the script...
'
For i = 0 to args.Count - 1
'
' Open the file for reading
'
Set readStrm = fsoRead.openTextFile(args(i), 1, False)
strFileName = fsoRead.GetFileName(args(i))
If (Not IsNumeric(Left(strFileName, 6))) Then
strErrFileName = "Process cancelled." & vbLF & vbLF & "Could not create a date from the file name '" & strFileName & "'. Please make sure all the mailbox file names " & vbLF & _
"start with a reverse Gregorian date (eg. 070925 Mailbox.txt)."
shellError.Popup strErrFileName, 0, "Date not in title!", 0 & 48
Wscript.Quit()
Else
strDate = Mid(strFileName, 5, 2) & "/" & Mid(strFileName, 3, 2) & "/" & (2000 + Cint(Left(strFileName, 2)))
End If
'
' Skip through the irrelevant lines of metadata to get to the first record.
'
Do Until (readStrm.Line = intFirstRecord)
readStrm.SkipLine()
Loop
intNoOfRecords = 0 ' Initialise record counter.
strRecord = readStrm.ReadLine() ' Read the first record.
'
' Do the following to each record until the EOF trigger is reached.
'
Do Until (Left(strRecord, 3) = "EOF")
'
' Create concatenated string of data with commas then write it to the new file.
'
intNoOfRecords = intNoOfRecords + 1
straaa = Mid(strRecord, 18, 6)
strbbb = Mid(strRecord, 24, 8)
If(IsNumeric(Mid(strRecord, 36, 11))) Then
dblAmount = Mid(strRecord, 36, 11)/100
Else
strErrDataType = "Process cancelled. " & vbLF & vbLF & "The following file has data in the amount field which is not of a numeric type." & vbLF & vbLF & _
"File: " & strFileName & vbLF & _
"Record No: " & intNoOfRecords & vbLF & _
"Amount = '" & Mid(strRecord, 36, 11) & "'"
shellError.Popup strErrDataType, 0, "Incorrect Data Type!", 0 & 48
Wscript.Quit()
End If
strName = Trim(Mid(strRecord, 47, 18))
strRef = Trim(Mid(strRecord, 65, 18))
strAmendedRecord = straaa & "," & strbbb & "," & dblAmount & "," & _
strName & "," & strRef & "," & strDate
writeStrm.WriteLine(strAmendedRecord) ' Write the result to the temp file.
strRecord = readStrm.ReadLine() ' Read the next record.
Loop
readStrm.Close()
'
' Check that all records in the file were read by comparing the no of records read to the number
' of records the file says it contains.
'
Set oTS = oF.readStrm(strFileName)
strRecord = oTS.ReadLine
Set oTS = oF.readStrm(strFileName)
oTS.SkipLine
oTS.SkipLine
strRecord = oTS.ReadLine
intFileRecords = CLng(Mid(strRecord, 55, 6))
If(intNoOfRecords <> intFileRecords) Then
strErrNoOfRec = "Process Cancelled." & vbLF & vbLF & "The number of records copied from file '" & fsoRead.GetFileName(args(i)) & "' is " & intNoOfRecords & _
"," & vbLF & "but the number of records the file says it contains is " & intFileRecords & ". Please investigate."
shellError.Popup strErrNoOfRec, 0, "Error in quantity of records!", 0 & 48
Wscript.Quit()
End If
'
' Note the file successfully processed and increment the counters
'
strCopiedFiles = strCopiedFiles & strFileName & " - " & intNoOfRecords & vbLf
intTotNoOfRecords = intTotNoOfRecords + intNoOfRecords
intTotNoOfFiles = intTotNoOfFiles + 1
Next
'
' Copy the Temp file to the New files.
'
Set objTempFile = fsoWrite.GetFile(strTempFile)
objTempFile.Copy strNewFile, true
objTempFile.Copy "C:\FPAY Daily MailboxData.txt", true
'
' Inform the user of the files and records processed
'
strCopiedFilesMsg = "The following " & intTotNoOfFiles & " files were converted and copied to the files " & vbLf & _
"'FPAY Daily MailboxData.txt' and 'FPAY\FPAY CAF,KKL Removed\" & fsoWrite.GetFileName(strNewFile) & "'. " & vbLf & vbLf & _
"No of records in each file: " & vbLF & vbLF & strCopiedFiles & vbLF & _
"Total No of Records: " & intTotNoOfRecords
Msgbox strCopiedFilesMsg,,"Files Converted"
'**********************************************************************************************************************************************************************************************
' Function: fSortArray(aSortThisArray)
' Arguments: One array containing the files to be processed
' Return: One array containing the sorted files to be processed
'
' Desc: This function is parsed an array containing the files to be processed. It then sorts the array of files
' and returns the sorted array back to the calling program.
'---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Function fSortArray(aSortThisArray)
Dim oArrayList, iElement, oArrayDic
Set oArrayDic = CreateObject("Scripting.Dictionary")
Set oArrayList = CreateObject("System.Collections.ArrayList")
For iElement = 0 To aSortThisArray.Length - 1
oArrayList.Add aSortThisArray(iElement)
Next
oArrayList.Sort
Set fSortArray = oArrayList
End Function
'**********************************************************************************************************************************************************************************************
|
|
| |
|
|
|
|
RE: Skipping 2 lines in txt file!! HELP :) - 6/15/2008 9:41:46 PM
|
|
|
|
| |
rizakaya
Posts: 6
Score: 0
Joined: 6/13/2008
From: UK
Status: offline
|
Sorry I have posted the script with some tried solutions which did not work so below is the correct script pasted.
Hi,
The program needs to skip the first 5 lines and read the text after and conver to a comma delimited file. This works fine. The script also works fine when copying to a TEMP file but fails to leave a second copy in desired location because of the validation here:
intFileRecords = CLng(Mid(strRecord, 55, 6))
If(intNoOfRecords <> intFileRecords) Then
strErrNoOfRec = "Process Cancelled." & vbLF & vbLF & "The number of records copied from file '" & fsoRead.GetFileName(args(i)) & "' is " & intNoOfRecords & _
"," & vbLF & "but the number of records the file says it contains is " & intFileRecords & ". Please investigate."
shellError.Popup strErrNoOfRec, 0, "Error in quantity of records!", 0 & 48
Wscript.Quit()
End If
What i would like the script to do is same validation but on the text 2 below.
Option Explicit
Const strWriteFilePath = "C:\FPAY\FPAY CAF,KKL Removed\"
const intFirstRecord = 5 ' Line Number of the first record
Dim args ' The collection of files dropped onto the program
Dim i ' Counter to loop through the files
Dim result ' Holds the users input from a Messagebox
Dim fsoRead ' Object for reading
Dim fsoWrite ' Object for writing
Dim readStrm ' Text stream for reading from the file Mailbox files
Dim writeStrm ' Text stream for writing to the temp file
Dim strFileName ' Name of the Mailbox File being read
Dim strTempFile ' Name of the Temp file being written to
Dim strNewFile ' Name that the Temp file is changed to
Dim objTempFile ' An instance of the Temporary file object
Dim strRecord ' The record as read from the original file
Dim intNoOfRecords ' The no of records read from the Mailbox file
Dim intFileRecords ' The no of records in the Mailbox file according to the files report
Dim intTotNoOfRecords ' The total no of records in all files combined
Dim intTotNoOfFiles ' The total no of files converted
Dim strAmendedRecord ' The converted record ready to be written to the new file
Dim shellError ' The shell used to display errors
Dim strErrNoArgs ' The No arguments error message
Dim strErrFileName ' The Incorrect file name message
Dim strErrNoOfRec ' Incorrect no of records message
Dim strErrDataType ' Incorrect data type for amount
Dim strCopiedFiles ' List of copied files
Dim strCopiedFilesMsg ' The message that is created to inform the user of the files copied
Dim straaa, strbbb, dblAmount, strName, strRef, strDate
Set shellError = Wscript.CreateObject("Wscript.Shell") ' Instantiate error object
Set args = fSortArray(Wscript.Arguments)
'
' Quit if user doesn't provide any files to process
'
If args.Count < 1 Then
strErrNoArgs = "Ctrl + click on the days files then drag and drop them onto this programs icon." & vbLF & _
"All of the files will be concatenated into a file and the data will be converted into comma separated values for CAF,KKL,etc..Extraction."
shellError.Popup strErrNoArgs, 0, "No files to process!", 0 & 48
Wscript.Quit()
End If
Set fsoRead = Wscript.CreateObject("Scripting.FileSystemObject")
Set fsoWrite = Wscript.CreateObject("Scripting.FileSystemObject")
strTempFile = strWriteFilePath & "TMPFile.txt"
'
' Check that the new file does not exist. If it does, ask the user if they want to overwrite it. Otherwise cancel the operation.
'
strNewFile = strWriteFilePath & Left(fsoRead.GetFileName(args(0)), 6) & " FPAY MailboxDailyData.txt"
If fsoWrite.FileExists(strNewFile) Then
result = Msgbox("File 'FPAY\FPAY CAF,KKL Removed\" & fsoWrite.GetFileName(strNewFile) & "' already exists. " & _
"Do you want " & vbLF & "to overwrite the existing file?", vbOK, "File already exists!")
If (result = vbOK) Then
Set writeStrm = fsoWrite.CreateTextFile(strTempFile, True)
Else
Wscript.Quit()
End If
Else
Set writeStrm = fsoWrite.CreateTextFile(strTempFile, True)
End If
intTotNoOfRecords = 0 ' Initialise the Total No of Records counter
intTotNoOfFiles = 0 ' Initialise the Total No of Files counter
writeStrm.WriteLine("Saa,Bbb,Amount,Name,Ref,Date") ' Write titles to first row in file
'
' For each file that is parsed to the script...
'
For i = 0 to args.Count - 1
'
' Open the file for reading
'
Set readStrm = fsoRead.openTextFile(args(i), 1, False)
strFileName = fsoRead.GetFileName(args(i))
If (Not IsNumeric(Left(strFileName, 6))) Then
strErrFileName = "Process cancelled." & vbLF & vbLF & "Could not create a date from the file name '" & strFileName & "'. Please make sure all the mailbox file names " & vbLF & _
"start with a reverse Gregorian date (eg. 070925 Mailbox.txt)."
shellError.Popup strErrFileName, 0, "Date not in title!", 0 & 48
Wscript.Quit()
Else
strDate = Mid(strFileName, 5, 2) & "/" & Mid(strFileName, 3, 2) & "/" & (2000 + Cint(Left(strFileName, 2)))
End If
'
' Skip through the irrelevant lines of metadata to get to the first record.
'
Do Until (readStrm.Line = intFirstRecord)
readStrm.SkipLine()
Loop
intNoOfRecords = 0 ' Initialise record counter.
strRecord = readStrm.ReadLine() ' Read the first record.
'
' Do the following to each record until the EOF trigger is reached.
'
Do Until (Left(strRecord, 3) = "EOF")
'
' Create concatenated string of data with commas then write it to the new file.
'
intNoOfRecords = intNoOfRecords + 1
straaa = Mid(strRecord, 18, 6)
strbbb = Mid(strRecord, 24, 8)
If(IsNumeric(Mid(strRecord, 36, 11))) Then
dblAmount = Mid(strRecord, 36, 11)/100
Else
strErrDataType = "Process cancelled. " & vbLF & vbLF & "The following file has data in the amount field which is not of a numeric type." & vbLF & vbLF & _
"File: " & strFileName & vbLF & _
"Record No: " & intNoOfRecords & vbLF & _
"Amount = '" & Mid(strRecord, 36, 11) & "'"
shellError.Popup strErrDataType, 0, "Incorrect Data Type!", 0 & 48
Wscript.Quit()
End If
strName = Trim(Mid(strRecord, 47, 18))
strRef = Trim(Mid(strRecord, 65, 18))
strAmendedRecord = straaa & "," & strbbb & "," & dblAmount & "," & _
strName & "," & strRef & "," & strDate
writeStrm.WriteLine(strAmendedRecord) ' Write the result to the temp file.
strRecord = readStrm.ReadLine() ' Read the next record.
Loop
readStrm.Close()
'
' Check that all records in the file were read by comparing the no of records read to the number
' of records the file says it contains.
'
intFileRecords = CLng(Mid(strRecord, 55, 6))
If(intNoOfRecords <> intFileRecords) Then
strErrNoOfRec = "Process Cancelled." & vbLF & vbLF & "The number of records copied from file '" & fsoRead.GetFileName(args(i)) & "' is " & intNoOfRecords & _
"," & vbLF & "but the number of records the file says it contains is " & intFileRecords & ". Please investigate."
shellError.Popup strErrNoOfRec, 0, "Error in quantity of records!", 0 & 48
Wscript.Quit()
End If
'
' Note the file successfully processed and increment the counters
'
strCopiedFiles = strCopiedFiles & strFileName & " - " & intNoOfRecords & vbLf
intTotNoOfRecords = intTotNoOfRecords + intNoOfRecords
intTotNoOfFiles = intTotNoOfFiles + 1
Next
'
' Copy the Temp file to the New files.
'
Set objTempFile = fsoWrite.GetFile(strTempFile)
objTempFile.Copy strNewFile, true
objTempFile.Copy "C:\FPAY Daily MailboxData.txt", true
'
' Inform the user of the files and records processed
'
strCopiedFilesMsg = "The following " & intTotNoOfFiles & " files were converted and copied to the files " & vbLf & _
"'FPAY Daily MailboxData.txt' and 'FPAY\FPAY CAF,KKL Removed\" & fsoWrite.GetFileName(strNewFile) & "'. " & vbLf & vbLf & _
"No of records in each file: " & vbLF & vbLF & strCopiedFiles & vbLF & _
"Total No of Records: " & intTotNoOfRecords
Msgbox strCopiedFilesMsg,,"Files Converted"
'**********************************************************************************************************************************************************************************************
' Function: fSortArray(aSortThisArray)
' Arguments: One array containing the files to be processed
' Return: One array containing the sorted files to be processed
'
' Desc: This function is parsed an array containing the files to be processed. It then sorts the array of files
' and returns the sorted array back to the calling program.
'---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Function fSortArray(aSortThisArray)
Dim oArrayList, iElement, oArrayDic
Set oArrayDic = CreateObject("Scripting.Dictionary")
Set oArrayList = CreateObject("System.Collections.ArrayList")
For iElement = 0 To aSortThisArray.Length - 1
oArrayList.Add aSortThisArray(iElement)
Next
oArrayList.Sort
Set fSortArray = oArrayList
End Function
'**********************************************************************************************************************************************************************************************
|
|
| |
|
|
|
|
|
Printable Version
RE: Skipping 2 lines in txt file!! HELP :) - 
New Messages
No New Messages
Hot Topic w/ New Messages
Hot Topic w/o New Messages
Locked w/ New Messages
Locked w/o New Messages
Post New Thread