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

[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!

expandcollapse popup

$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

[enaiman]

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

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 January 27, 2010 by enaiman

[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!

[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 January 27, 2010 by jchd

[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 January 27, 2010 by eternally1027

[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 January 27, 2010 by jchd

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