_Excel_RangeWrite / RangeDelete problems

[aleph01]

Good day,

I'm trying to automate some manipulations of our wireless AP logfiles.  I'm only interested in the login rows, and login (or logout, or other) is listed in column C of a .cvs file.  My script opens the file and takes the time that would be needed for a For...Next loop, but the file doesn't change.

#include <File.au3>
#include <Excel.au3>
$CountLine = 0

$oExcel = _Excel_Open ()
$sFilePath = "C:\Users\adminkeitht\Desktop\prAPdownloadSample - Copy.csv"
$oWorkbook = _Excel_BookOpen ( $oExcel, $sFilePath)
$LineCount = _FileCountLines ($sFilePath)
;MsgBox (1, "", $LineCount)

For $i = 1 to $LineCount
    If NOT _Excel_RangeRead ($oWorkbook, "Default", "C" & $i, 1) = "login" Then
        _Excel_RangeDelete ($oWorkbook, $i, "", 1)
    EndIf
Next

Up to the remmed out MsgBox, everything seems find.  Then, about 20 seconds later, the script ends without making any changes in the .csv.  I am trying to delete all rows that aren't login rows.

Anybody see what I'm doing wrong?

Thanks, in advance.

_aleph_

[JLogan3o13]

At first glance, on your IF line, try one of these:

If NOT (_Excel_RangeRead ($oWorkbook, "Default", "C" & $i, 1) = "login") Then
 
 ;or
 
  If _Excel_RangeRead ($oWorkbook, "Default", "C" & $i, 1) <> "login" Then

 

[aleph01]

Thanks, JLogan3o13, I can see how that could be a problem, and I have corrected it, but I still get no manipulation of the .csv.  Is my use of $oWorkbook correct?  I have it defined as $oWorkbook = _Excel_BookOpen ( $oExcel, $sFilePath).

[JLogan3o13]

It looks correct to me. Can you post an example csv I can play with so I don't have to mock one up?

[aleph01]

Here's the one I'm using...

prAPdownloadSample - Copy.csv

It's been pruned down quite a bit from the original.

Edited September 7, 2016 by aleph01

[JLogan3o13]

This works for me:

#include <File.au3>
#include <Excel.au3>

$CountLine = 0

$oExcel = _Excel_Open()
$sFilePath = "C:\Users\JLogan3o13\Downloads\prAPdownloadSample - Copy.csv"
$oWorkbook = _Excel_BookOpen($oExcel, $sFilePath)
$LineCount = _FileCountLines ($sFilePath)

For $i = $LineCount To 2 Step -1
   If _Excel_RangeRead($oWorkbook, Default, "C" & $i) <> "login" Then
      _Excel_RangeDelete($oWorkbook.ActiveSheet, $i & ":" & $i)
    EndIf
Next

A couple of things I noticed. You have the default keyword in quotes in your call to _Excel_RangeRead, which may have been boogering it up. Also, you were deleting your header line. I would suggest starting at the bottom and moving upwards as I have done; otherwise you'll see rows that are missed.

[aleph01]

Thanks, JLogan3o13, that works like a charm.  I actually intended to get rid of the header line.  I only need to know how many entry lines there are and what percentage of them come from which library branch.  Thanks for getting me past my first sticking point.

I don't know why parsing through the file backward works better, but I'm not one to argue with success.

My next step is to read column E - the last thing in there is the AP MAC addy, which I will use to determine at which branch the AP is located.  Do you have any suggestions which commands I should look at to find a string that always begins with AP: (space) and a mac addy but is in a cell with a bunch of irrelevant junk?  The MAC addy is always last.

Thanks, again.

_aleph_

[JLogan3o13]

As for why it is better to go backwards, think of the progress. You find a line you want to get rid of on row 2 and delete it. The script then moves on to row 3, but what was row 3 (also one you want to get rid of) is now row 2. You would be forced to make multiple passes to ensure you got them all.

For searching the MAC addresses, one of our Regex masters could probably come up with something much better, but as Regex makes my eye bleed I would do something like this:

#include <Array.au3>
#include <File.au3>
#include <Excel.au3>

$CountLine = 0

$oExcel = _Excel_Open()
$sFilePath = "C:\Users\JLogan3o13\Downloads\prAPdownloadSample - Copy.csv"
$oWorkbook = _Excel_BookOpen($oExcel, $sFilePath)
$LineCount = _FileCountLines ($sFilePath)

For $i = 2 To $LineCount
    $aTemp = StringSplit(_Excel_RangeRead($oWorkbook, Default, "E" & $i), ",")
        For $a = 1 To $aTemp[0]
            If StringInStr($aTemp[$a], "mac:") Then MsgBox(0, "", $aTemp[$a] & " found on line " & $i)
        Next
Next

 

[aleph01]

Thanks, JLogan3o13, I'll put it to the test.

_aleph_