advice on processing excel

[joeloyzaga]

other than "Don't use it" would be appreciated

Can you read down an .xls and transpose to another file ???

This means with the attached files...... read reports.xls and you will see that for each object type – each new micclass :(Browser, Page, WebTable, WebList, WebElement,WebEdit,WebCheckBox, Link, Image) I have transposed the values into their properties (reports-transposed.xls) but only the first one for each micclass as I did that by hand

Don’t know how to process the whole reports.xls file – thought of using a macro but perhaps autoit would be better?

Can you advise?/help?

Thanks

Joe

reports.zip

[exodius]

Have you tried anything yet?

I guess I see this going one of two ways, although both ways basically end up the same...

You can either use the _Excel* functions that come with AutoIt and loop through every line and write out whatever you want to write out like this:

#include <Excel.au3>
#include <Array.au3>
$oExcel = _ExcelBookOpen(@ScriptDir & "\reports.xls")
$aData = _ExcelReadSheetToArray ($oExcel)

_ArrayDisplay($aData)

**Warning! This will probably take a long time to run because your .xls file is so big!!**

On my relatively beefy computer, it took 10 minutes to read in your Excel doc, and then about 20 seconds to do the _ArrayDisplay

Or you can save your .xls file as a .csv file, use _FileReadToArray, then split each line by a comma using StringSplit and loop through every line and write out whatever you want to write out like this:

#include <Array.au3>
#include <File.au3>
Dim $array
_FileReadToArray ("reports.csv", $array)
_ArrayDisplay ($array)
For $x = 1 to $array[0]
    $split = StringSplit ($array[$x], ",")
    _ArrayDisplay ($split)
Next

This is considerably quicker.

Edited October 21, 2009 by exodius

[joeloyzaga]

Have you tried anything yet?

I guess I see this going one of two ways, although both ways basically end up the same...

You can either use the _Excel* functions that come with AutoIt and loop through every line and write out whatever you want to write out like this:

#include <Excel.au3>
#include <Array.au3>
$oExcel = _ExcelBookOpen(@ScriptDir & "\reports.xls")
$aData = _ExcelReadSheetToArray ($oExcel)

_ArrayDisplay($aData)

**Warning! This will probably take a long time to run because your .xls file is so big!!**

On my relatively beefy computer, it took 10 minutes to read in your Excel doc, and then about 20 seconds to do the _ArrayDisplay

Or you can save your .xls file as a .csv file, use _FileReadToArray, then split each line by a comma using StringSplit and loop through every line and write out whatever you want to write out like this:

#include <Array.au3>
#include <File.au3>
Dim $array
_FileReadToArray ("reports.csv", $array)
_ArrayDisplay ($array)
For $x = 1 to $array[0]
    $split = StringSplit ($array[$x], ",")
    _ArrayDisplay ($split)
Next

This is considerably quicker.

using this i get

>"C:\Program Files\AutoIt3\SciTE\AutoIt3Wrapper\AutoIt3Wrapper.exe" /run /prod /ErrorStdOut /in "C:\Autoit-workarea\readcsv.au3" /autoit3dir "C:\Program Files\AutoIt3" /UserParams

+>07:13:38 Starting AutoIt3Wrapper v.2.0.0.1 Environment(Language:0409 Keyboard:00000409 OS:WIN_XP/Service Pack 3 CPU:X64 OS:X86)

>Running AU3Check (1.54.14.0) from:C:\Program Files\AutoIt3

+>07:13:38 AU3Check ended.rc:0

>Running:(3.3.0.0):C:\Program Files\AutoIt3\autoit3.exe "C:\Autoit-workarea\readcsv.au3"

C:\Autoit-workarea\readcsv.au3 (7) : ==> Subscript used with non-Array variable.:

For $x = 1 to $array[0]

For $x = 1 to $array^ ERROR

->07:13:38 AutoIT3.exe ended.rc:1

+>07:13:39 AutoIt3Wrapper Finished

>Exit code: 1 Time: 1.680

[exodius]

What code did you actually use?

[joeloyzaga]

What code did you actually use?

just copied yours to see it working

[exodius]

So.. the first example or the second example? (Hopefully not both)

Edited October 21, 2009 by exodius

[joeloyzaga]

So.. the first example or the second example? (Hopefully not both)

second (please remember its before I've had my coffee!!!!!!!!!!!!!!)

[joeloyzaga]

So.. the first example or the second example? (Hopefully not both)

second (please remember its before I've had my coffee!!!!!!!!!!!!!!)

[exodius]

Did you save the .xls file as a .csv file?

[joeloyzaga]

yup! should I be specific and include the drive?

[exodius]

If you don't have the .csv file in the same folder as the script, yes, give it the full path to it.

Here's a version with a little error-checking:

#include <Array.au3>
#include <File.au3>
Dim $array
_FileReadToArray ("reports.csv", $array)
If Not @error Then
    _ArrayDisplay ($array)
    For $x = 1 to $array[0]
        $split = StringSplit ($array[$x], ",")
        _ArrayDisplay ($split)
    Next
Else
    Switch @error
        Case 1
            MsgBox (0, "Can't Open Specified File", "_FileReadToArray couldn't open your file")
        Case 2
            MsgBox (0, "Can't Split File", "_FileReadToArray couldn't split your file")
    EndSwitch
EndIf

[joeloyzaga]

thanks - that did it - I'll take it from here (just finishing coffee!!)