aleph01

_Excel_RangeWrite / RangeDelete problems

9 posts in this topic

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_

Share this post


Link to post
Share on other sites



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

 


When you're dead, you don't know you're dead - it's only difficult for those that know you. It's the same way when you're stupid...

My Scripts: SCCM UDFInclude Source with Compiled Script, Windows Firewall UDF

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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


When you're dead, you don't know you're dead - it's only difficult for those that know you. It's the same way when you're stupid...

My Scripts: SCCM UDFInclude Source with Compiled Script, Windows Firewall UDF

Share this post


Link to post
Share on other sites

#5 ·  Posted (edited)

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

prAPdownloadSample - Copy.csv

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

Edited by aleph01

Share this post


Link to post
Share on other sites

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.


When you're dead, you don't know you're dead - it's only difficult for those that know you. It's the same way when you're stupid...

My Scripts: SCCM UDFInclude Source with Compiled Script, Windows Firewall UDF

Share this post


Link to post
Share on other sites

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_

Share this post


Link to post
Share on other sites

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

 


When you're dead, you don't know you're dead - it's only difficult for those that know you. It's the same way when you're stupid...

My Scripts: SCCM UDFInclude Source with Compiled Script, Windows Firewall UDF

Share this post


Link to post
Share on other sites

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

_aleph_

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now