Jump to content
Sign in to follow this  
eternally1027

Problem with Script - Comparing Data from Excel File to Data from Database

Recommended Posts

eternally1027

To start off, I am very familiar with PHP. I spend 15+ hours a week working with PHP. I have recently decided to move some of my coding over to AutoIt to alleviate timeout errors, etc for Data Comparison, as my projects are getting more complex.

Now, I am not a trained programmer. I am self-taught and have issues with the logic of things sometimes.

With that said, I am comparing data from a MySQL Database and an Excel spreadsheet. I am able to read both data sets in without an issue. I now need to:

1) Compare one to another

2) When a match is found, I need to check if that item currently exists in my array ($outputData)

3) If it does, I want to increase the value of its [2] column by the value of $arr1[$i][2]

4) If not, I need to add it to my $outputData array (dept name and value)

All of my variables are declared. I do not get any errors, but the data is not counted correctly and I do not have an accurate listing of departments. I still have duplicates and some do not show up at all.

As you can see below, I have tried many ways to do this. I have taken a walk but I am still up against a wall.

Any help is much appreciated!

$mysqlcon = _MySQLConnect($dbun, $dbpw, $dbname, $dbserver)
$query = "SELECT * FROM `analysis1`"
;MsgBox(0,"Query",$query)
$result = _Query($mysqlcon, $query)

If @error Then
    MsgBox(0,"Error",@error)
EndIf
If @error=2 Then
    MsgBox(0,"Test","Fail")
EndIf
_ArrayDisplay($result)

$start1 = TimerInit()
$arr1 = _ExcelReadSheetToArray($oExcel1)
$elapsed1 = TimerDiff($start1)

_ExcelBookClose($oExcel1)
_MySQLEnd($query)

MsgBox(0,"Time Taken Reading File",$elapsed1/60000 & " minutes")

$p = 0

;With $result
;   While NOT .EOF
;       $p = $p + 1
;       $searchresult = _ArraySearch($deptcount,.Fields("dept").value)
;       If $searchresult = -1 Then
;           ReDim $deptcount[UBound($deptcount) + 1][4]
;           $deptcount[$p][1] = .Fields("dept").value
;       EndIf
;       .MoveNext
;   WEnd
;EndWith

;_ArrayDisplay($arr1)

$o = 0
$p = 0

With $result
    While NOT .EOF
        For $i=1 to $arr1[0][0]
            $searchresultd = _ArraySearch($deptcount,.Fields("dept").value)
            If $arr1[$i][1] = .Fields("dept").value Then
                $numcol = 5
                ;_ArrayDisplay($deptcount)
                $searchresultd = _ArraySearch($deptcount,.Fields("dept").value)
                ;MsgBox(0,"Search Res",$searchresult & "-" & @error)
                If $searchresultd = -1 Then
                    ;MsgBox(0,"Search $outputData for:", .Fields("dept").value)
                    $o = $o + 1
                    $p = $p + 1
                    ReDim $deptcount[UBound($deptcount) + 1][2]
                    $deptcount[$p][0] = $arr1[$i][1]
                    $deptcount[$p][1] = $arr1[$i][2]
                    ReDim $outputData[UBound($outputData) + 1][$numcol + 1]
                    $outputData[$o][1] = .Fields("dept").value
                    $outputData[$o][2] = $arr1[$i][2]
                    $outputData[$o][3] = $arr1[$i][3]
                    $outputData[$o][4] = .Fields("group").value
                    $outputData[$o][5] = .Fields("facility").value
                ElseIf $searchresultd <> -1 Then
                    $deptcount[$p][1] = $deptcount[$p][1] + $arr1[$i][2]
                    $outputData[$searchresultd][3] = $outputData[$searchresultd][3] + $arr1[$i][2]
                    ;MsgBox(0,"$searchresultd",$searchresultd)
                    MsgBox(0,$outputData[$searchresultd][1], $outputData[$searchresultd][1] + $arr1[$i][2])
                EndIf
                ;_ArrayDisplay($outputData)
                ;$searchresultg = _ArraySearch($grpcount,.Fields("group").value)
                ;MsgBox(0,"Search Res",$searchresult & "-" & @error)
                ;If $searchresultg = -1 Then
                ;   $p = $p + 1
                ;   ReDim $deptcount[UBound($grpcount) + 1][2]
                ;   $grpcount[$p][0] = $arr1[$i][1]
                ;   $grpcount[$p][1] = $arr1[$i][2]
                ;ElseIf $searchresultg <> -1 Then
                ;   $grpcount[$p][1] = $grpcount[$p][1] + $arr1[$i][2]
                ;EndIf
                ;$o = $o + 1
                ;Number of Columns on Created Spreadsheet

                ;$result.Fields("dept").value & "1" = $arr1[$i][2]
                ;$outputData[$o][6] = $arr2[$p][9]
            ElseIf $arr1[$i][1] = .Fields("dept").value AND $searchresultd = -1 Then
                MsgBox(0,"Secondary","Test")
            EndIf
        Next
        .MoveNext
    WEnd
EndWith

Share this post


Link to post
Share on other sites
enaiman

Wow - registered in 2006 and first post almost 4 years later :D

Unfortunately, your code doesn't give me enough information to try to debug what are you doing. You also have some "unusual" coding practice (using With/EndWith); it wouldn't be easier to use a "normal" for/next approach?

There is absolutely no info about the arrays you get from the database and Excel spreadsheet (are they 1-dimensional or 2-dimensional?), also, I have noticed that you use _ArraySearch without any other additional parameters (are you searching only the first column of your arrays?)

Give me a short example of how your tables look (2-3 lines of each) and I might spend some time trying to figure a way to accomplish what you need. Until then, I'm afraid I can't do much more.

Edited by enaiman

SNMP_UDF ... for SNMPv1 and v2c so far, GetBulk and a new example script

wannabe "Unbeatable" Tic-Tac-Toe

Paper-Scissor-Rock ... try to beat it anyway :)

Share this post


Link to post
Share on other sites
eternally1027

Normally, I can find the answer somewhere in the forums and I have no need to actually post.

Database: 3 Fields - dept, group, facility. This is read into an object, of course.

$arr1 (Excel Spreadsheet): 3 Columns - multidimensional array. First dimension counts rows, second is columns.

$outputData: Multidimensional array that is later written to an Excel Spreadsheet. I find that it is much quicker to write the whole sheet in one fell swoop.

From what I have read (as far as I understand), _ArraySearch should search both dimensions by default. Is this not correct?

It has been doing some very funny things. For example, it will say there is no match when there is obviously a match.

Thank you so much!

Share this post


Link to post
Share on other sites
jchd

As enaiman says, we only have scarce information about your actual problem on the one hand and how you try to solve it on the other hand.

First, you say you have no problem accessing both datasets, but just after, you say the recordsets you get are inaccurate.

You should make it clear which side is failing: MySQL and/or Excel. I just read that mySQL ODBC has problems with recordcount and has other issues with cursors, not unlike connectors/ODBC.

Also use a COM error handler, don't test @error after ADO/COM since @error is not set outside AutoIt, AFAIK.

Try perfoming your queries using ADO but put the rowsets in 2-D arrays and make sure both are correct. There can be several issues even at this stage.

Then write and debug your sync code from there.

It's unclear where the output goes or if it serves to update one of the bases (or is it a two-way sync ?).

Finally, I wonder that you time the loading of the Excel table in _minutes_. Which size if it (mere curiosity)?

Some directions for improvement:

Order your mySQL select * to get an already ordered rowset. Do the same for Excel. This will simplify matching a lot, from a O(n^2) down to O(n log(n)).

If your tables are that big (minutes), I personally would try yet another option: load both tables in a SQLite memory base and work from there in SQL. It could prove a superior approach for medium-size datasets.

Edited by jchd

This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites
eternally1027

Sorry I am not giving enough detail.

I am able to get the information out of my MySQL Database and the Excel Spreadsheet without issue.

The problem occurs in the actual comparison of the data.

I need to compare the data, row by row, and form a report.

That report is then written into $outputData, which is saved into an Excel Spreadsheet at the end of the script.

As far as the timer goes, it is from a previous script I had written. I merely copied and pasted it, just to verify that the loading time was not excessive. It usually takes about 4-6 seconds to load the Excel data.

This script is solely for my use, so it does not have to be completely polished. I do, however, need my output data ($outputData) to be correct.

Edited by eternally1027

Share this post


Link to post
Share on other sites
jchd

Database: 3 Fields - dept, group, facility. This is read into an object, of course.

$arr1 (Excel Spreadsheet): 3 Columns - multidimensional array. First dimension counts rows, second is columns.

$outputData: Multidimensional array that is later written to an Excel Spreadsheet. I find that it is much quicker to write the whole sheet in one fell swoop.

In this case, what is the point in comparing? difference with copying the mySQL table directly into an Excel one?

BTW, I notice you finalize your query before using it. I've no direct experience with the mySQL ADO layer, but in all others, this boils down to accessing a destroyed object.

Can you please try placing _MySQLEnd($query) after the processing loop.

Grrr, I dunno why I can't paste your code: it puts everything on a single line, but also skip lines.

Edited by jchd

This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites
eternally1027

The comparison generates a fairly complex report which will be ran once a month. This will automate that report.

I have moved my closing statement for the MySQL connection. No effect.

I am working on debugging this code but am getting nowhere.

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
Sign in to follow this  

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.