Sign in to follow this  
Followers 0
joeloyzaga

advice on processing excel

12 posts in this topic

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

Share this post


Link to post
Share on other sites



#2 ·  Posted (edited)

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 by exodius

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

What code did you actually use?

Share this post


Link to post
Share on other sites

What code did you actually use?

just copied yours to see it working

Share this post


Link to post
Share on other sites

#6 ·  Posted (edited)

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

Edited by exodius

Share this post


Link to post
Share on other sites

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

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

Share this post


Link to post
Share on other sites

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

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

yup! should I be specific and include the drive?

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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

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  
Followers 0