Jump to content

advice on processing excel


Recommended Posts

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

Link to comment
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.

Edited by exodius
Link to comment
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

Link to comment
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
Link to comment
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
 Share

  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...