Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 257
  • Last Modified:

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
0
strvenmrvn
Asked:
strvenmrvn
  • 7
  • 6
  • 2
1 Solution
 
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
 
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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
 
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
 
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

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 7
  • 6
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now