Solved

Find and Replace Value Version 2.0

Posted on 2011-03-03
15
243 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 54

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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
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 54

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 54

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 54

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 54

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 54

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

Guide to Performance: Optimization & Monitoring

Nowadays, monitoring is a mixture of tools, systems, and codes—making it a very complex process. And with this complexity, comes variables for failure. Get DZone’s new Guide to Performance to learn how to proactively find these variables and solve them before a disruption occurs.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
split data by day 21 35
Tricky Shapes formula 3 18
Populating a Cell with specific Characters 13 16
I need macro to insert an character 8 22
Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

730 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