Excel and Array Help

[Mercury049]

Hello All.

Here's is what I'm trying to do.

I currently have a script that reads a row from a worksheet in excel and then writes it to another worksheet with some minor changes X number of times. the X is the number of changes that need to be made. It's working fine. But what I would like to do is instead of having it write the data to each row one at a time as it does, I would rather have it build an array, just adding each record it modifies to the end of the array. There's usually like 500 records and about 100 changes. That ends up being like 50k rows. Each row has about 45 columns of data. I only mention this so you'll know it's a 2d row. Anyways, anyone have any idea of how I would go about building an array like that?

Thanks

[DaRam]

If you are comfortable with using COM - ADO Recordsets that might be the ideal solution.

1. Define an In memory (Disconnected) ADO Recordset

2. Scan Source Worksheet (First Row) Columns and add as Fields to Recordset

3. Open the Recordset, Scan Worksheet Rows and load as records to ADO recordset

4. Make changes to Rows or Append Rows with Modified Rows (in memory again)

5. Select Target Worksheet (Write out Column Names if desired)

6. Use Excel's Range.CopyFromRecordset method to write the entire RS data to the target sheet in one shot.

Hello All.

Here's is what I'm trying to do.

I currently have a script that reads a row from a worksheet in excel and then writes it to another worksheet with some minor changes X number of times. the X is the number of changes that need to be made. It's working fine. But what I would like to do is instead of having it write the data to each row one at a time as it does, I would rather have it build an array, just adding each record it modifies to the end of the array. There's usually like 500 records and about 100 changes. That ends up being like 50k rows. Each row has about 45 columns of data. I only mention this so you'll know it's a 2d row. Anyways, anyone have any idea of how I would go about building an array like that?

Thanks

[Mercury049]

If you are comfortable with using COM - ADO Recordsets that might be the ideal solution.

1. Define an In memory (Disconnected) ADO Recordset

2. Scan Source Worksheet (First Row) Columns and add as Fields to Recordset

3. Open the Recordset, Scan Worksheet Rows and load as records to ADO recordset

4. Make changes to Rows or Append Rows with Modified Rows (in memory again)

5. Select Target Worksheet (Write out Column Names if desired)

6. Use Excel's Range.CopyFromRecordset method to write the entire RS data to the target sheet in one shot.

Thanks for your suggestion. I'll look into that idea.

[PsaltyDS]

Thanks for your suggestion. I'll look into that idea.

To do this with AutoIt functions already built for the purpose, use _ExcelReadSheetToArray(), modify the array, then _ExcelWriteSheetFromArray(). These are in the Excel.au3 UDF included since Beta 3.2.13.4. They are also in ExcelCOM_UDF.au3 if you are using an earlier version.

[DjDeep00]

Hello All.

Here's is what I'm trying to do.

I currently have a script that reads a row from a worksheet in excel and then writes it to another worksheet with some minor changes X number of times. the X is the number of changes that need to be made. It's working fine. But what I would like to do is instead of having it write the data to each row one at a time as it does, I would rather have it build an array, just adding each record it modifies to the end of the array. There's usually like 500 records and about 100 changes. That ends up being like 50k rows. Each row has about 45 columns of data. I only mention this so you'll know it's a 2d row. Anyways, anyone have any idea of how I would go about building an array like that?

Thanks

50,000 rows?

FYI: That's cuttin it pretty close to Excel's 65,536 row limit.

[Mercury049]

Yeah the 50k rows is massive for sure.

I'm currently using the Excel.au3 for this and I have it working writing out each row, one at a time. But it's time consuming. I would like to write each row into an array that collects all 50k rows. I don't know if I'm explaining it well here, it's kind of confusing.

Ok, list A has 500 records, 43 columns wide.

LIST A

A B C D E F G H I J ...

1 x y z a b c d e f g

2 j k l m n o p q r s

3

4

Now, list B has 100 records but it's only a 1d array.

LIST B

A

1 ab

2 cd

3 ef

4 gh

And LIST C will be the finished product (i.e. 50k rows)

So what my script currently does is to Read in ROW 1 from LIST A, and write it to LIST C. then it edits the cell that needs to be changed in the row. Let's say the column it's editing is J from LIST 1, so it would look like this.

LIST C

A B C D E F G H I J ...

1 x y z a b c d e f ab

2 x y z a b c d e f cd

3 x y z a b c d e f ef

4 x y z a b c d e f gh

5 j k l m n o p q r ab

...

Ok, I hope this is making sense so far. Basically, it writes the same row as many times as there are edits from LIST B to make. So if there's 5 edits to make, it will write ROW 1 from LIST 1 5 times with column J changing each time. Then when it sees that there are no more changes to make, it goes back to LIST 1 and grabs ROW 2 and does the same thing by writing those next 5 rows/changes immediately after.

Well as opposed to this being written as it goes into Excel. I would prefer to have everything that WOULD be written to LIST C to instead be written into an array that I could dump into excel later.

I think my explanation above should make things clearer or muddier. =)

Thanks again for everyone's time in viewing all of this and any ideas you have.

[Mercury049]

I have been looking further over it and I realize I just really need to know how to manage the array that I would write everything into.

For example, I run a _ExcelReadSheetToArray. I take the array that I'm passed back and try to seperate it by row and I can't seem to get it to work.

Any ideas?

[PsaltyDS]

I have been looking further over it and I realize I just really need to know how to manage the array that I would write everything into.

For example, I run a _ExcelReadSheetToArray. I take the array that I'm passed back and try to seperate it by row and I can't seem to get it to work.

Any ideas?

You have to note the special formatting of the array you get back. It's a 2D array, [0][0] = row count, and [0][1] = column count. Although an AutoIt array always uses 0-based indexes, the data is actually at [1][1] thru [$row_n][$col_n] so the indexes can match the 1-based row/col numbering in Excel.

Study this a little:

#include <Excel.au3>

Global $sExcel_1 = "C:\Temp\Test_1.xls", $oExcel_1, $avExcel_1
Global $sExcel_2 = "C:\Temp\Test_2.xls", $oExcel_2, $avExcel_2
Global $sExcel_3 = "C:\Temp\Test_3.xls", $oExcel_3, $avExcel_3
Global $NewRow

; Get "500 rows of stuff"
$oExcel_1 = _ExcelBookOpen($sExcel_1)
$avExcel_1 = _ExcelReadSheetToArray($oExcel_1)
_ExcelBookClose($oExcel_1)

; Get single column of stuff
$oExcel_2 = _ExcelBookOpen($sExcel_2)
$avExcel_2 = _ExcelReadSheetToArray($oExcel_2, 1, 1, 0, 1)
_ExcelBookClose($oExcel_2)

; Create new data array
ReDim $avExcel_3[$avExcel_1[0][0] * $avExcel_2[0][0]][$avExcel_1[0][1]]

; Write data to array
$NewRow = 1
For $r = 1 To $avExcel_1[0][0]
    For $n = 1 To $avExcel_2[0][0]
        For $c = 1 To $avExcel_1[0][1] - 1
            $avExcel_3[$NewRow][$c] = $avExcel_1[$r][$c]
        Next
        $avExcel_3[$NewRow][$avExcel_1[0][1]] = $avExcel_2[$n][1]
    Next
Next

; Write new array to file
$oExcel_3 = _ExcelBookOpen($sExcel_3)
_ExcelWriteSheetFromArray($oExcel_3, $avExcel_3)
_ExcelBookClose($oExcel_3)

[Mercury049]

You have to note the special formatting of the array you get back. It's a 2D array, [0][0] = row count, and [0][1] = column count. Although an AutoIt array always uses 0-based indexes, the data is actually at [1][1] thru [$row_n][$col_n] so the indexes can match the 1-based row/col numbering in Excel.

Study this a little:

#include <Excel.au3>

Global $sExcel_1 = "C:\Temp\Test_1.xls", $oExcel_1, $avExcel_1
Global $sExcel_2 = "C:\Temp\Test_2.xls", $oExcel_2, $avExcel_2
Global $sExcel_3 = "C:\Temp\Test_3.xls", $oExcel_3, $avExcel_3
Global $NewRow

; Get "500 rows of stuff"
$oExcel_1 = _ExcelBookOpen($sExcel_1)
$avExcel_1 = _ExcelReadSheetToArray($oExcel_1)
_ExcelBookClose($oExcel_1)

; Get single column of stuff
$oExcel_2 = _ExcelBookOpen($sExcel_2)
$avExcel_2 = _ExcelReadSheetToArray($oExcel_2, 1, 1, 0, 1)
_ExcelBookClose($oExcel_2)

; Create new data array
ReDim $avExcel_3[$avExcel_1[0][0] * $avExcel_2[0][0]][$avExcel_1[0][1]]

; Write data to array
$NewRow = 1
For $r = 1 To $avExcel_1[0][0]
    For $n = 1 To $avExcel_2[0][0]
        For $c = 1 To $avExcel_1[0][1] - 1
            $avExcel_3[$NewRow][$c] = $avExcel_1[$r][$c]
        Next
        $avExcel_3[$NewRow][$avExcel_1[0][1]] = $avExcel_2[$n][1]
    Next
Next

; Write new array to file
$oExcel_3 = _ExcelBookOpen($sExcel_3)
_ExcelWriteSheetFromArray($oExcel_3, $avExcel_3)
_ExcelBookClose($oExcel_3)

Thank you VERY much!

[jimmyjmmy]

Hi,

I want to know more about how to use excel with autoit. Where can I read more about using excel with autoit.

When I download the latest version of autoit, does it also include excel.au3.

Thanks