Solved

Find and Replace Value Version 2.0

Posted on 2011-03-03
15
235 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 51

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

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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

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 51

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 51

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 51

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 51

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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …

707 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now