Solved

Find and Replace Value Version 2.0

Posted on 2011-03-03
15
240 Views
Last Modified: 2012-08-14
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
Comment
Question by:strvenmrvn
  • 7
  • 6
  • 2
15 Comments
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35029735
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
 

Author Comment

by:strvenmrvn
ID: 35029828
Correct.  If you look in my old post titled "Find and replace value", there is original code.
0
 
LVL 53

Expert Comment

by:Bill Prew
ID: 35029858
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
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35030104
billprew (the original coder) is here so I will now retreat :)

Sid
0
 

Author Comment

by:strvenmrvn
ID: 35030283
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
 
LVL 53

Expert Comment

by:Bill Prew
ID: 35030672
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
 

Author Comment

by:strvenmrvn
ID: 35030760
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
 

Author Comment

by:strvenmrvn
ID: 35030784
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
 
LVL 53

Accepted Solution

by:
Bill Prew earned 500 total points
ID: 35032682
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
 

Author Comment

by:strvenmrvn
ID: 35036160
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
 
LVL 53

Expert Comment

by:Bill Prew
ID: 35036295
Welcome, glad it worked out, thanks.

~bp
0
 

Author Comment

by:strvenmrvn
ID: 35369717
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
 
LVL 53

Expert Comment

by:Bill Prew
ID: 35377713
Can you provide a sample of the new input file?

~bp
0
 

Author Comment

by:strvenmrvn
ID: 35378857
see attached
0330110901-G1L2-1S.csv
0
 
LVL 53

Expert Comment

by:Bill Prew
ID: 35380182
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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will show, step by step, how to integrate R code into a R Sweave document
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

773 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question