Jump to content

This site uses cookies. By continuing to browse the site you are agreeing to our use of cookies. Find out more here. X
X


Photo

Excel and Array Help


  • Please log in to reply
9 replies to this topic

#1 Mercury049

Mercury049

    Seeker

  • Active Members
  • 48 posts

Posted 24 September 2008 - 03:37 PM

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







#2 DaRam

DaRam

    Universalist

  • Active Members
  • PipPipPipPipPipPip
  • 399 posts

Posted 24 September 2008 - 04:45 PM

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



#3 Mercury049

Mercury049

    Seeker

  • Active Members
  • 48 posts

Posted 24 September 2008 - 05:00 PM

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.

#4 PsaltyDS

PsaltyDS

    Most Venerable Penguin

  • MVPs
  • 13,279 posts

Posted 24 September 2008 - 05:29 PM

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.

:)
Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law

#5 DjDeep00

DjDeep00

    Universalist

  • Active Members
  • PipPipPipPipPipPip
  • 654 posts

Posted 24 September 2008 - 05:33 PM

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. :)

#6 Mercury049

Mercury049

    Seeker

  • Active Members
  • 48 posts

Posted 24 September 2008 - 05:58 PM

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.

#7 Mercury049

Mercury049

    Seeker

  • Active Members
  • 48 posts

Posted 24 September 2008 - 07:45 PM

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?

#8 PsaltyDS

PsaltyDS

    Most Venerable Penguin

  • MVPs
  • 13,279 posts

Posted 24 September 2008 - 09:59 PM

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:
Plain Text         
#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)


:)
Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law

#9 Mercury049

Mercury049

    Seeker

  • Active Members
  • 48 posts

Posted 25 September 2008 - 06:31 PM

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:

Plain Text         
#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!

#10 jimmyjmmy

jimmyjmmy

    Adventurer

  • Active Members
  • PipPip
  • 100 posts

Posted 28 September 2008 - 10:08 PM

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




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users