Find and Replace Value Version 2.0

I had made a similiar post last year and received a great tool to accomplish the task at hand.  I have the same request except the CSV data i'm running the script against has changed slightly.  The original post was:

I have several csv files that sometimes contain a value of say 65000 in the very last column and the only acceptable value is less than that.

whether it be a solution or suggested source for custom scripting i am looking for guidance on how to accomplish the following:

scan all csv files for values greater than 65000 (in the last column) and replace it with the average of the previous 2 values.  sort of like an estimation find and replace where data is missing.

Attached is a sample CSV file.  The original post was answered by billprew...if you're out there bill please check out my posting.

Thanks,
Rich


1001101050-G1L1-1A.csv
strvenmrvnAsked:
Who is Participating?
 
Bill PrewCommented:
Hmm, we're disconnecting on something here.  I just ran a test on the original.txt sample, using the accepted solution to the prior question, and it worked fine.

The prior question was written to handle the last column in a file with this format:

"03/16/10" "02:45" 15 0 0 835

Open in new window

Notice that it delimits by space, and looks at and averages the 6th column when needed.

The new file format is

"09/15/10" "15:45 EDT" 15 0 65555555

Open in new window

While there is now a space in the time field, there seems to be a numeric column less in this format.  So since we are only parsing by spaces, the right most column is still the 6th column, so the origianl code still works.  And when I ran a test here it did work fine, changing these lines:

"09/15/10" "15:15 EDT" 15 0 6
"09/15/10" "15:30 EDT" 15 4 2
"09/15/10" "15:45 EDT" 15 0 65555555

Open in new window

into this:

"09/15/10" "15:15 EDT" 15 0 6
"09/15/10" "15:30 EDT" 15 4 2
"09/15/10" "15:45 EDT" 15 0 4

Open in new window

~bp
0
 
SiddharthRoutCommented:
strvenmrvn: The file that you have given has 123445556 in Cell E7 and E8. So if my understanding is correct

>>>replace it with the average of the previous 2 values.

So E7 will be 0 and
E8 will be 0 ?

Sid

0
 
strvenmrvnAuthor Commented:
Correct.  If you look in my old post titled "Find and replace value", there is original code.
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
Bill PrewCommented:
If I read this right, the difference between this and the prior solution is that there is now one less column.  If that's the case, then this adjustment to the prior solution should handle this file format:

(for reference, prior question was:  http://www.experts-exchange.com/Software/Office_Productivity/Word_Processors/Q_26111199.html )

' Define constants
Const ForReading = 1
Const ForWriting = 2
 
' Define literals
strFolder = "C:\Temp\EE26111199"
strFilter = ".csv"
 
' Set up objects needed
Set objFSO = CreateObject("Scripting.FileSystemObject")
 
' Work through all files in folder to be processed
Set objFolder = objFSO.GetFolder(strFolder)
For Each objFile In objFolder.Files
      
   ' Make sure it's a file type we want to process
   If Lcase(Right(objFile.Name, 4)) = strFilter Then
         
      ' Open file, read all contents into array
      Set objFileIn = objFSO.OpenTextFile(objFile.Path, ForReading)
      strAllData = objFileIn.ReadAll
      arrLines = Split(strAllData, vbNewLine)
      objFileIn.Close
   
      ' Open file for writing now
      Set objFileOut = objFSO.OpenTextFile(objFile.Path, ForWriting, True)
   
      ' Initialize last two good values
      intValue1 = 0
      intValue2 = 0
      
      ' Process all non blank lines
      For i=0 To UBound(arrLines)
         strLineIn = arrLines(i)
         If strLineIn <> "" Then
            ' Break apart values on line, space delimited
            arrLineIn = Split(strLineIn, " ")
            ' If last value is too big, set to average of prior 2 lines
            If arrLineIn(4) < 65000 Then
               intValue1 = Cint(intValue2)
               intValue2 = Cint(arrLineIn(4))
            Else
               arrLineIn(4) = Round((intValue1 + intValue2) / 2)
            End If
            strLineOut = arrLineIn(0) & " " & arrLineIn(1) & " " & arrLineIn(2) & " " & arrLineIn(3) & " " & arrLineIn(4)
            ' Write this line to output file
            objFileOut.WriteLine strLineOut
         End If
      Next
      
      ' Close output file
      objFileOut.Close
         
   End If
Next

Open in new window

~bp
0
 
SiddharthRoutCommented:
billprew (the original coder) is here so I will now retreat :)

Sid
0
 
strvenmrvnAuthor Commented:
bill,

Attached is my pretest file and posttest file.  somethings wrong.

the difference between this post and old post is there is a time zone value next to the time, in this example it is EDT.  


1001101050-G1L1-1A-posttest.csv
1001101050-G1L1-1A-pretest.csv
0
 
Bill PrewCommented:
Ah, okay, I get that now.  But based on that I would expect the old version of the code would actually still work, from the prior question, since the number of space delimiters is still the same.  Did it not do what you needed?

~bp
0
 
strvenmrvnAuthor Commented:
no, it deletes all the contents of the file making it a 0kb file.  the difference is the space after the time and the EDT or EST value after the time.

figured it had to do with delimiters in the original script.
0
 
strvenmrvnAuthor Commented:
here's the original sample, note the time is "hh:mm".  in the new csv file the time is "hh:mm EDT" or "hh:mm EST".
original.txt
0
 
strvenmrvnAuthor Commented:
i see what has happened.  you are right the old file has 6 columns and the new file still has 6 columns.  when i ran the new script it removed column 6 and i was still testing on that modified file.

i tested the original script on both old csv data and new csv data, still works as long as there are 6 columns of data.

thanks for the help.
0
 
Bill PrewCommented:
Welcome, glad it worked out, thanks.

~bp
0
 
strvenmrvnAuthor Commented:
Bill,

The newest file now has 7 columns and not 6.  What needs to be changed in the VBS file to account for this change?  or can you post a new script?

I can open a new question with points if needed.

Rich
0
 
Bill PrewCommented:
Can you provide a sample of the new input file?

~bp
0
 
strvenmrvnAuthor Commented:
see attached
0330110901-G1L2-1S.csv
0
 
Bill PrewCommented:
Try changing this:

            If arrLineIn(5) < 65000 Then
               intValue1 = Cint(intValue2)
               intValue2 = Cint(arrLineIn(5))
            Else
               arrLineIn(5) = Round((intValue1 + intValue2) / 2)
            End If
            strLineOut = arrLineIn(0) & " " & arrLineIn(1) & " " & arrLineIn(2) & " " & arrLineIn(3) & " " & arrLineIn(4) & " " & arrLineIn(5)

Open in new window

to this:

            If arrLineIn(6) < 65000 Then
               intValue1 = Cint(intValue2)
               intValue2 = Cint(arrLineIn(6))
            Else
               arrLineIn(6) = Round((intValue1 + intValue2) / 2)
            End If
            strLineOut = arrLineIn(0) & " " & arrLineIn(1) & " " & arrLineIn(2) & " " & arrLineIn(3) & " " & arrLineIn(4) & " " & arrLineIn(5) & " " & arrLineIn(6)

Open in new window


~bp
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.