Jump to content
Sign in to follow this  
NickT

Array to Excel

Recommended Posts

NickT

I believe it's time to consult the experts on what I am trying to do...I am trying to take a large text file and write it to an Excel spreadsheet. After 2 days of trial and error I finally got it to work. What I would like help with is on making it faster. In my opinion it is taking WAY too long to write these values to the worksheet.

SCRIPT:

test.au3

TEXT FILE:

aptsource.txt

Share this post


Link to post
Share on other sites
water

Didn't have a look at your script in detail. You could try my UDF (Attention! Alpha version!) and use the _Excel_RangeWrite function to write an array to a worksheet. Or use the _Excel_BookOpenText function to directly read the file into a sheet.

That's much faster!


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites
NickT

Okay, I tried using the _Excel_BookOpenText function with no luck. I keep getting a failed object error on it.

Share this post


Link to post
Share on other sites
water

Can you post your code?


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites
NickT

#include <Excel.au3>

#include <File.au3>

#include <String.au3>

#include <Array.au3>

Global $ext,$slnum = "-1"

Func write($book,$val,$row,$x,$y,$z)

$zVAL = _ExcelReadCell($book,$row - 1,3)

If _ExcelReadCell($book,$row,3) = $zVAL Then

_ExcelWriteCell($book,$slnum,$row,10)

Else

$slnum += 1

_ExcelWriteCell($book,$slnum,$row,10)

EndIf

_ExcelWriteCell($book,$x,$row,7)

_ExcelWriteCell($book,$y,$row,8)

_ExcelWriteCell($book,$z,$row,9)

If Number(Round(($row - 2) / $val * 100)) > "0" Then

$per = Round(($row - 2) / $val * 100)

ProgressSet($per,$per & " Percent complete")

EndIf

EndFunc

Func parse($file,$tmpfile,$book,$lcount)

ProgressOn("AptSource Conversion","","",1,1)

Local $line

If Not StringInStr(FileRead($file),"GOTO",1) Then

MsgBox(0,"ERROR",'This is not an "aptsource" file.')

ex($book,$tmpfile)

EndIf

_ExcelWriteCell($book,"in machine datum : xm=-zb ; ym=-xb ; zm=yb with rotation (0.223deg)",1,1)

_ExcelWriteCell($book,"Xc",2,1)

_ExcelWriteCell($book,"Yc",2,2)

_ExcelWriteCell($book,"Zc",2,3)

_ExcelWriteCell($book,"i",2,4)

_ExcelWriteCell($book,"j",2,5)

_ExcelWriteCell($book,"k",2,6)

_ExcelWriteCell($book,"x+i",2,7)

_ExcelWriteCell($book,"y+j",2,8)

_ExcelWriteCell($book,"z+k",2,9)

_ExcelWriteCell($book,"scan line " & @CRLF & "number",2,10)

_ExcelHorizontalAlignSet($book,1,1,1,1,"center")

$book.Range($book.Cells(1,1),$book.Cells(1,10)).MergeCells=True

$book.Columns.NumberFormat="@"

$read = StringReplace(FileRead($file),"GOTO","",0,1);_StringStripChr(FileRead($file),"GOTO/ ",8)

$read = StringReplace($read," ","",0,1)

$read = FileWrite($tmpfile,StringReplace($read,"/","",0,1))

For $i = 1 To $lcount

$read = FileReadLine($tmpfile,$i)

$read = _StringExplode($read,",")

$x = StringReplace($read[0] + $read[3],".",",")

$y = StringReplace($read[1] + $read[4],".",",")

$z = StringReplace($read[2] + $read[5],".",",")

$read = StringReplace(_ArrayToString($read),".",",")

$read = _StringExplode($read,"|")

_ExcelWriteArray($book, $i + 2, 1, $read)

write($book,$lcount,$i + 2,$x,$y,$z)

Next

$book.columns.autofit

$folder = FileSaveDialog("Save",@ScriptDir,"Excel (*.xlsx)",18)

$book.ActiveWorkbook.SaveAs($folder);,"","",False,False,1,2,False)

MsgBox(0,"",@error)

ex($book,$tmpfile)

EndFunc

Func lcount($file)

If $file = "" Then Exit

Local $line

_FileReadToArray($file,$line)

parse($file,_TempFile(),_ExcelBookNew(1),$line[0])

EndFunc

Func ex($book,$tmpfile)

ProgressOff()

_ExcelBookClose($book,0)

FileDelete($tmpfile)

EndFunc

lcount(FileOpenDialog("Open","","All(*.*)",3))

Edited by NickT

Share this post


Link to post
Share on other sites
water

Sorry, my bad.

I didn't mean the original code which you already posted in #1 but the code where _Excel_BookOpenText didn't work.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites
NickT

Apologies for taking so long, been busy. Here is my code with _Excel_BookOpenText not working.

#include <Excel.au3>
#include <File.au3>
#include <String.au3>
#include <Array.au3>

Global $ext,$slnum = "-1"

Func write($book,$val,$row,$x,$y,$z,$time)
Local $per,$min[1]
_ExcelWriteCell($book,$x,$row,7)
_ExcelWriteCell($book,$y,$row,8)
_ExcelWriteCell($book,$z,$row,9)
$zVAL = _ExcelReadCell($book,$row - 1,3)
If _ExcelReadCell($book,$row,3) = $zVAL Then
_ExcelWriteCell($book,$slnum,$row,10)
Else
$slnum += 1
_ExcelWriteCell($book,$slnum,$row,10)
EndIf
If Number(Round(($row - 2) / $val * 100)) > "0" Then
$per = Round(($row - 2) / $val * 100)
; ProgressSet($per,$per & " Percent complete" & @CRLF & @CRLF & Round(TimerDiff($time) / 1000))
EndIf
$dif = Round(TimerDiff($time) / 1000)
If $dif >= "60" Then
$min[0] = $dif / 60
If StringInStr($min[0],".") Then $min[0] = _StringBetween($min,"",".")
Else
$min[0] = 0
EndIf
$sec = $dif / 60
$sec = _StringBetween($sec,".","")
$sec = _StringInsert(_ArrayToString($sec),".",0)
$sec = Round($sec * 60)
ProgressSet($per,$per & " Percent complete" & @CRLF & @CRLF & "Time elapsed: " & $min[0] & " minutes and " & $sec & " seconds")
EndFunc

Func parse($file,$tmpfile,$book,$lcount)
$time = TimerInit()
ProgressOn("AptSource Conversion","","",1,1,18)
Local $line
If Not StringInStr(FileRead($file),"GOTO",1) Then
MsgBox(0,"ERROR",'This is not an "aptsource" file.')
ex($book,$tmpfile)
EndIf
_ExcelWriteCell($book,"in machine datum : xm=-zb ; ym=-xb ; zm=yb with rotation (0.223deg)",1,1)
_ExcelWriteCell($book,"Xc",2,1)
_ExcelWriteCell($book,"Yc",2,2)
_ExcelWriteCell($book,"Zc",2,3)
_ExcelWriteCell($book,"i",2,4)
_ExcelWriteCell($book,"j",2,5)
_ExcelWriteCell($book,"k",2,6)
_ExcelWriteCell($book,"x+i",2,7)
_ExcelWriteCell($book,"y+j",2,8)
_ExcelWriteCell($book,"z+k",2,9)
_ExcelWriteCell($book,"scan line " & @CRLF & "number",2,10)
_ExcelHorizontalAlignSet($book,1,1,1,1,"center")
$book.Range($book.Cells(1,1),$book.Cells(1,10)).MergeCells=True
$book.Columns.NumberFormat="@"
$read = StringReplace(FileRead($file),"GOTO","",0,1)
$read = StringReplace($read," ","",0,1)
$read = FileWrite($tmpfile,StringReplace($read,"/","",0,1))
_Excel_BookOpenText($book,$tmpfile,3,1,Default,Default,",",2,Default,Default,Default)
; For $i = 1 To $lcount
; $read = FileReadLine($tmpfile,$i)
; If @error Then
; MsgBox(0,"",$read & @CRLF & $i & @CRLF & $lcount)
; ExitLoop
; EndIf
; $read = _StringExplode($read,",")
; $x = StringReplace($read[0] + $read[3],".",",")
; $y = StringReplace($read[1] + $read[4],".",",")
; $z = StringReplace($read[2] + $read[5],".",",")
; $read = StringReplace(_ArrayToString($read),".",",")
; $read = _StringExplode($read,"|")
; _ExcelWriteArray($book, $i + 2, 1, $read)
; write($book,$lcount,$i + 2,$x,$y,$z,$time)
; Next
$book.columns.autofit
$folder = FileSaveDialog("Save",@ScriptDir,"Excel (*.xlsx)",18)
$book.ActiveWorkbook.SaveAs($folder)
ex($book,$tmpfile)
EndFunc

Func lcount($file)
If $file = "" Then Exit
Local $line
_FileReadToArray($file,$line)
parse($file,_TempFile(),_ExcelBookNew(0),$line[0])
EndFunc

Func ex($book,$tmpfile)
ProgressOff()
_ExcelBookClose($book,0)
FileDelete($tmpfile)
EndFunc

lcount(FileOpenDialog("Open","","All(*.*)",3))
Apologies for taking so long, been busy. Here is my code with _Excel_BookOpenText not working.

Share this post


Link to post
Share on other sites
water

You can't combine the Excel UDF that comes with AutoIt and my rewrite of the Excel UDF.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites
NickT

I've tried it both ways, using your rewrite and $book.OpenText method.

-- Note that I have not only tried $book.OpenText but also $book.ActiveWorkbook.OpenText and $book.Workbooks.OpenText.

Share this post


Link to post
Share on other sites
water

If you run the _Excel_BookOpenText.au3 example of my rewritten Excel UDF. Does it work?


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites
NickT

Sweet! That works like I wanted it too! Is there any way to attach the "OpenText" book that is created to the previously created workbook instead of having two seperate books? I've looked around _ExcelBookAttach but it doesn't seem to be what I'm looking for. Of course I'm probably wrong.

Share this post


Link to post
Share on other sites
water

Not at the moment. The Excel method OpenText is only available for workbooks.

What should be possible is to copy the worksheet to another book.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites
NickT

I suppose what I could do is close the unused book and set my variable to the new book. Thank you for all of your help. It is greatly appreciated!

Share this post


Link to post
Share on other sites
water

I will test how to copy a worksheet from one workbook to another tomorrow.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites
water

How to copy a worksheet from one workbook to another:

#include <Excel Rewrite.au3>
$oExcel = _Excel_Open()
$oBook1 = _Excel_BookOpen($oExcel, @ScriptDir & "\_Excel1.xls")
$oSheet1 = $oBook1.Sheets(1)
$oBook2 = _Excel_BookOpen($oExcel, @ScriptDir & "\_Excel2.xls")
MsgBox(0, "", "Copying sheet1 of workbook1 before sheet1 of workbook2")
$oSheet1.Copy($oBook2.Sheets(1))
Edited by water

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites
NickT

I finally got the script to work. Thanks for your help. I have another question that pertains to the same script but this is more of a "want to know how" sort of deal. I'm wondering what makes this work:

dim $Array1[2]=[0,2]

dim $Array2[2]=[7,2]

dim $Array3[2]=[41,2]

dim $Array4[2]=[47,2]

dim $Array5[2]=[61,1]

dim $Array6[2]=[98,1]

dim $Array7[2]=[99,1]

dim $ArrayComplet[7]=[$Array1,$Array2,$Array3,$Array4,$Array5,$Array6,$Array7]

When I run _ArrayDisplay($ArrayComplet) I get 7 rows, 1 column, and no values. I use this in my script but I just want to know how this is working. This came from

#Region ;**** Directives created by AutoIt3Wrapper_GUI ****
#AutoIt3Wrapper_Icon=..\..\..\..\Program Files (x86)\AutoIt3\Icons\au3.ico
#AutoIt3Wrapper_Outfile=AptSource_Converter.exe
#EndRegion ;**** Directives created by AutoIt3Wrapper_GUI ****
#include
#include
#include

Func parse($file,$tmpfile,$book,$lcount)
Dim $arr0[2] = [0,2]
Dim $arr1[2] = [1,2]
Dim $arr2[2] = [2,2]
Dim $arr3[2] = [3,2]
Dim $arr4[2] = [4,2]
Dim $arr5[2] = [5,2]
Dim $arr6[2] = [6,2]
Dim $arr7[2] = [7,2]
Dim $arr8[2] = [8,2]
Dim $arr9[2] = [9,2]
Dim $array[10] = [$arr0,$arr1,$arr2,$arr3,$arr4,$arr5,$arr6,$arr7,$arr8,$arr9]
$tmpfile2 = _TempFile()
If Not StringInStr(FileRead($file),"GOTO",1) Then
MsgBox(0,"ERROR",'This is not an "aptsource" file.')
ex($book,$tmpfile,$tmpfile2,"")
EndIf
ProgressOn("Converting","","",1,1,18)
$read = StringReplace(FileRead($file),"GOTO","",0,1)
$read = StringReplace($read," ","",0,1)
$read = FileWrite($tmpfile,StringReplace($read,"/","",0,1))
$book.Application.DisplayAlerts=0
$book.Workbooks.OpenText($tmpfile,Default,1,Default,1,False,False,False,True,False,False,"",$array, Default,Default,Default,True)
_ExcelWriteCell($book,0,1,10)
$book.Range($book.Cells(1,7),$book.Cells($lcount,9)).Formula="=A1:A" & $lcount & "+D1:D" & $lcount
$book.Range($book.Cells(2,10),$book.Cells($lcount,10)).Formula="=IF(C2=C1,J1,J1+1)"
MsgBox(0,"","")
$book.ActiveWorkbook.SaveAs($tmpfile,20,Default,Default,False,False,1,2)
_ExcelBookClose($book,0)
ProgressSet(25,"25 percent complete" & @CRLF & @CRLF & $file)
$read = StringReplace(FileRead($tmpfile),".",",")
FileWrite($tmpfile2,$read)
ProgressSet(50,"50 percent complete" & @CRLF & @CRLF & $file)
$book = _ExcelBookNew(0)
$book.Application.DisplayAlerts=0
$book.Application.ErrorCheckingOptions.NumberAsText=False
$book.Workbooks.OpenText($tmpfile2,Default,1,Default,1,False,True,False,False,False,False,"",$array, Default,Default,Default,True)
$book.ActiveSheet.Rows(1).Insert()
$book.ActiveSheet.Rows(1).Insert()
ProgressSet(75,"75 percent complete" & @CRLF & @CRLF & $file)
_ExcelWriteCell($book,"in machine datum : xm=-zb ; ym=-xb ; zm=yb with rotation (0.223deg)",1,1)
_ExcelWriteCell($book,"Xc",2,1)
_ExcelWriteCell($book,"Yc",2,2)
_ExcelWriteCell($book,"Zc",2,3)
_ExcelWriteCell($book,"i",2,4)
_ExcelWriteCell($book,"j",2,5)
_ExcelWriteCell($book,"k",2,6)
_ExcelWriteCell($book,"x+i",2,7)
_ExcelWriteCell($book,"y+j",2,8)
_ExcelWriteCell($book,"z+k",2,9)
_ExcelWriteCell($book,"scan line " & @CRLF & "number",2,10)
_ExcelHorizontalAlignSet($book,1,1,1,1,"center")
_ExcelHorizontalAlignSet($book,3,1,$lcount + 2,10,"right")
$book.Range($book.Cells(1,1),$book.Cells(1,10)).MergeCells=True
$book.Columns.AutoFit
ProgressSet(100,"100 percent complete" & @CRLF & @CRLF & $file)
$folder = FileSaveDialog("Save",@ScriptDir,"Excel (*.xlsx)",18)
ProgressOff()
If Not $folder Then ex($book,$tmpfile,$tmpfile2,"")
$book.ActiveWorkbook.SaveAs($folder,51,Default,Default,False,False,1,2)
ex($book,$tmpfile,$tmpfile2,$folder)
EndFunc

Func lcount($file)
If $file = "" Then Exit
Local $line
_FileReadToArray($file,$line)
For $i = UBound($line) - 1 To 0 Step -1
If $line[$i] = "" Then _ArrayDelete($line,$i)
Next
$line = UBound($line) - 1
parse($file,_TempFile(),_ExcelBookNew(1),$line)
EndFunc

Func ex($book,$tmpfile,$tmpfile2,$folder)
_ExcelBookClose($book,0)
Do
FileDelete($tmpfile)
FileDelete($tmpfile2)
Until Not FileExists($tmpfile) And Not FileExists($tmpfile2)
If Not $folder = "" Then MsgBox(0,"Conversion",'Apt Source conversion complete!!' & @CRLF & @CRLF & 'New file is located at: ' & $folder & ".xlsx")
Exit
EndFunc

lcount(FileOpenDialog("Open","","All(*.*)",3))
Edited by NickT

Share this post


Link to post
Share on other sites
water

Array in Array is not recommended. See the end of this page.

Function _ArrayDisplay doesn't know how to handle arrays in arrays.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites
NickT

Okay, that's understandable. Is there a way to do this without an array in an array?

Share this post


Link to post
Share on other sites
water

Use a two-dimensional array. Something like this:

#include <Array.au3>
Global $Array[7][2]=[[0,2],[7,2],[41,2],[47,2],[61,1],[98,1],[99,1]]
_ArrayDisplay($Array)

BTW: Don't use "DIM", use "Global"


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites
NickT

I tried that in my script but it doesn't work. I've also tried a 3-dimensional array as well.

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  

  • Similar Content

    • nooneclose
      By nooneclose
      I want to check some Excel data against data on a website in Chrome. I use Chrome because the site I use does not function properly in Internet Explorer or Firefox. I know how to do the Excel stuff I just can not figure out how to send to Chrome, let alone check to see if the data matches or not. I am also having trouble finding any help online while searching for Chrome functions for Autoit. I have a Chrome UDF installed but I still can not figure out how to get my code to properly function. (I am not posting code because I am  sure my code isn't right, to begin with)
      As usual, any and all help would be greatly appreciated. 
    • rm4453
      By rm4453
      I have a table I am parsing, to find specific vehicle information. I am unable to get _ArrayFindAll to return the only valid result with my test data.
      Below is a sample of the table's HTML:
      <td class="textCenter">2010</td> <td>TOYOTA</td> <td>TACOMA 4X4 DB</td> <td></td> <td>BLACK</td> <td class="textCenter">C</td> <td class="textCenter">6</td> <td>GAS</td> <td class="textCenter">A</td> <td class="textCenter">4X4</td> <td class="textCenter">Y</td> <td>CD</td> <td class="textCenter">HT</td> <td class="textCenter">C</td> <td class="textRight" nowrap="nowrap">80,975</td> <td class="textRight" nowrap="nowrap">$16,800</td> </tr> <tr><!-- 308 --> <td class="textCenter">2010</td> <td>TOYOTA</td> <td>TACOMA 4X4 RG</td> <td></td> <td>BLACK</td> <td class="textCenter">R</td> <td class="textCenter">4</td> <td>GAS</td> <td class="textCenter">5</td> <td class="textCenter">4X4</td> <td class="textCenter">A</td> <td>CD</td> <td class="textCenter">HT</td> <td class="textCenter">C</td> <td class="textRight" nowrap="nowrap">95,224</td> <td class="textRight" nowrap="nowrap">$9,500</td> </tr> <tr><!-- 309 --> <td class="textCenter">2011</td> <td>BUICK</td> <td>REGAL</td> <td>CXL RL4</td> <td>BLACK</td> <td class="textCenter">4</td> <td class="textCenter">4</td> <td>GAS</td> <td class="textCenter">A</td> <td class="textCenter"></td> <td class="textCenter">A</td> <td>CD</td> <td class="textCenter">SR</td> <td class="textCenter">L</td> <td class="textRight" nowrap="nowrap">102,694</td> <td class="textRight" nowrap="nowrap">$5,000</td> </tr> <tr><!-- 310 --> <td class="textCenter">2011</td> <td>CHEVROLET</td> <td>AVALANCH 4X4 CR</td> <td>LS</td> <td>GRAY</td> <td class="textCenter">C</td> <td class="textCenter">8</td> <td>E</td> <td class="textCenter">A</td> <td class="textCenter">4X4</td> <td class="textCenter">A</td> <td>CD</td> <td class="textCenter">HT</td> <td class="textCenter">C</td> <td class="textRight" nowrap="nowrap">64,759</td> <td class="textRight" nowrap="nowrap">$16,300</td> </tr> <tr><!-- 311 --> <td class="textCenter">2011</td> <td>CHEVROLET</td> <td>EQUINOX AWD 4C</td> <td>LT W/2LT</td> <td>BLACK</td> <td class="textCenter">S</td> <td class="textCenter">4</td> <td>GAS</td> <td class="textCenter">A</td> <td class="textCenter">AWD</td> <td class="textCenter">Y</td> <td>CD</td> <td class="textCenter">SR</td> <td class="textCenter">C</td> <td class="textRight" nowrap="nowrap">91,896</td> <td class="textRight" nowrap="nowrap">$4,400</td> </tr> <tr><!-- 312 --> <td class="textCenter">2011</td> <td>CHEVROLET</td> <td>TAHOE 4X4 V8</td> <td>LTZ</td> <td>WHITE</td> <td class="textCenter">S</td> <td class="textCenter">8</td> <td>C</td> <td class="textCenter">A</td> <td class="textCenter">4X4</td> <td class="textCenter">A</td> <td>N</td> <td class="textCenter">MR</td> <td class="textCenter">L</td> <td class="textRight" nowrap="nowrap">126,982</td> <td class="textRight" nowrap="nowrap">$17,800</td> </tr> <tr><!-- 313 --> <td class="textCenter">2011</td> <td>CHEVROLET</td> <td>1500 SLV 4X4 EX</td> <td>LT</td> <td>GRAY</td> <td class="textCenter">X</td> <td class="textCenter">8</td> <td>GAS</td> <td class="textCenter">O</td> <td class="textCenter">4X4</td> <td class="textCenter">A</td> <td>CD</td> <td class="textCenter">HT</td> <td class="textCenter">C</td> <td class="textRight" nowrap="nowrap">60,303</td> <td class="textRight" nowrap="nowrap">$18,100</td> </tr> <tr><!-- 314 --> <td class="textCenter">2011</td> <td>CHEVROLET</td> <td>1500 SLV 4X4 EX</td> <td>LT</td> <td>SILVER</td> <td class="textCenter">X</td> <td class="textCenter">8</td> <td>E</td> <td class="textCenter">O</td> <td class="textCenter">4X4</td> <td class="textCenter">A</td> <td>CD</td> <td class="textCenter">HT</td> <td class="textCenter">C</td> <td class="textRight" nowrap="nowrap">89,403</td> <td class="textRight" nowrap="nowrap">$15,900</td> </tr> <tr><!-- 315 --> <td class="textCenter">2011</td> <td>CHEVROLET</td> <td>1500 SLV 4X4 EX</td> <td>LTZ</td> <td>BLUE</td> <td class="textCenter">X</td> <td class="textCenter">8</td> <td>E</td> <td class="textCenter">A</td> <td class="textCenter">4X4</td> <td class="textCenter">A</td> <td>CD</td> <td class="textCenter">HT</td> <td class="textCenter">L</td> <td class="textRight" nowrap="nowrap">53,087</td> <td class="textRight" nowrap="nowrap">$17,700</td> </tr> <tr><!-- 316 --> <td class="textCenter">2011</td> <td>CHEVROLET</td> <td>3500 CUTAWAY</td> <td>WORK VAN</td> <td>WHITE</td> <td class="textCenter">S</td> <td class="textCenter"></td> <td></td> <td class="textCenter">A</td> <td class="textCenter">4X2</td> <td class="textCenter"></td> <td>N</td> <td class="textCenter">HT</td> <td class="textCenter">C</td> <td class="textRight" nowrap="nowrap">202,477</td> <td class="textRight" nowrap="nowrap">$2,700</td> </tr> <tr><!-- 317 --> <td class="textCenter">2011</td> <td>CHRYSLER</td> <td>TOWN &amp; COUNTRY</td> <td>TOURING</td> <td>BLACK</td> <td class="textCenter">4</td> <td class="textCenter">6</td> <td>E</td> <td class="textCenter">A</td> <td class="textCenter">4X2</td> <td class="textCenter">A</td> <td>CD</td> <td class="textCenter">HT</td> <td class="textCenter"></td> <td class="textRight" nowrap="nowrap">198,541</td> <td class="textRight" nowrap="nowrap">$1,900</td> </tr> <tr><!-- 318 --> <td class="textCenter">2011</td> <td>DODGE</td> <td>DURANGO AWD V6</td> <td>CREW</td> <td>BLUE</td> <td class="textCenter">S</td> <td class="textCenter">6</td> <td>GAS</td> <td class="textCenter">A</td> <td class="textCenter">AWD</td> <td class="textCenter">A</td> <td>CD</td> <td class="textCenter">SR</td> <td class="textCenter">C</td> <td class="textRight" nowrap="nowrap">176,036</td> <td class="textRight" nowrap="nowrap">$2,800</td> </tr> <tr><!-- 319 --> <td class="textCenter">2011</td> <td>FORD</td> <td>FOCUS</td> <td>SE</td> <td>SILVER</td> <td class="textCenter">4</td> <td class="textCenter">4</td> <td>GAS</td> <td class="textCenter">A</td> <td class="textCenter"></td> <td class="textCenter">Y</td> <td>CD</td> <td class="textCenter">HT</td> <td class="textCenter">C</td> <td class="textRight" nowrap="nowrap">101,929</td> <td class="textRight" nowrap="nowrap">$3,100</td> </tr> <tr><!-- 320 --> <td class="textCenter">2011</td> <td>FORD</td> <td>FUSION FWD 4C</td> <td>SEL</td> <td>WHITE</td> <td class="textCenter">4</td> <td class="textCenter">4</td> <td>GAS</td> <td class="textCenter">A</td> <td class="textCenter"></td> <td class="textCenter">Y</td> <td>CD</td> <td class="textCenter">SR</td> <td class="textCenter">L</td> <td class="textRight" nowrap="nowrap">78,290</td> <td class="textRight" nowrap="nowrap">$5,500</td> </tr> <tr><!-- 321 --> <td class="textCenter">2011</td> <td>FORD</td> <td>F150 4X4 CR</td> <td>XLT</td> <td>BLACK</td> <td class="textCenter">C</td> <td class="textCenter">8</td> <td>GAS</td> <td class="textCenter">A</td> <td class="textCenter">4X4</td> <td class="textCenter">Y</td> <td>CD</td> <td class="textCenter">HT</td> <td class="textCenter">C</td> <td class="textRight" nowrap="nowrap">70,909</td> <td class="textRight" nowrap="nowrap">$16,000</td> </tr> <tr><!-- 322 --> <td class="textCenter">2011</td> <td>FORD</td> <td>MUSTANG V6 CPE</td> <td>V6 PREMIUM</td> <td>BLACK</td> <td class="textCenter">2</td> <td class="textCenter">6</td> <td>GAS</td> <td class="textCenter">A</td> <td class="textCenter">4X2</td> <td class="textCenter">A</td> <td>CD</td> <td class="textCenter">HT</td> <td class="textCenter">L</td> <td class="textRight" nowrap="nowrap">92,531</td> <td class="textRight" nowrap="nowrap">$2,700</td> </tr> <tr><!-- 323 --> <td class="textCenter">2011</td> <td>GMC</td> <td>ACADIA FWD</td> <td>SLE</td> <td>RED</td> <td class="textCenter">4</td> <td class="textCenter">6</td> <td>GAS</td> <td class="textCenter">A</td> <td class="textCenter"></td> <td class="textCenter">A</td> <td>CD</td> <td class="textCenter">HT</td> <td class="textCenter">C</td> <td class="textRight" nowrap="nowrap">79,199</td> <td class="textRight" nowrap="nowrap">$10,700</td> A picture of the table test data is attached here:

      Here is the _query function, and all other relevant code that I can share.
       
      Func _query($aSel, $aUrls) $oIE = _login() If $oIE = "Return" Then Return EndIf $j = 0 While $j < UBound($aSel) - 1 $i = 1 $aucID ;Unable To Share What This Is Other Than Var Name. _IENavigate($oIE, "Something" & $aucID[0] & "Something") _IELoadWait($oIE, 100, 2000) $oObj = _IETableGetCollection($oIE, 3) $cars = _IETableWriteToArray_ProgressBar($oObj, True, "Processing Requested Information!") ;<---- Modified Version See Post For It: https://www.autoitscript.com/forum/topic/195335-solved-how-to-add-a-progress-bar-to-_ietablewritetoarray/?tab=comments#comment-1400699 $carsYear = _filter($cars, 0, 0, GUICtrlRead($year)) $carsMake = _filter($carsYear, 0, 1, GUICtrlRead($make)) Global $carsModel = _filter($carsMake, 0, 2, GUICtrlRead($model)) $i = 0 $engine = GUICtrlRead($engine) If $engine <> "" Then While $i < StringLen($engine) $carsEngine = _filter($carsModel, 0, 6 + $i, StringLeft($engine, 1)) ;_ArrayDisplay($carsModel, "Cars Model Before Array Delete") $x = 1 While $x <= UBound($carsModel) _ArrayDelete($carsModel, $x) $x += 1 WEnd ;_ArrayDisplay($carsModel, "Cars Model After Array Delete") ;_ArrayConcatenate($carsModel, $carsEngine) ;_ArrayDisplay($carsModel, "Cars Model After Concatenate") $engine = StringTrimLeft($engine, 1) $i += 1 WEnd Else Dim $carsEngine[1][16] EndIf _ArrayConcatenate($carsEngine, $carsModel) _ArrayDisplay($carsEngine, "Cars Engine") Dim $carsDriveTrain[1][16] Dim $carsDriveTrain2[1][16] $driveTrainVal = GUICtrlRead($driveTrain) If $driveTrainVal = "4x4" Or $driveTrainVal = "awd" Then $carsDriveTrain = _filter($carsEngine, 0, 9, "4") $carsDriveTrain2 = _filter($carsEngine, 0, 9, "a") ;~ _ArrayDisplay($carsDriveTrain, "Drive Train Before") ;~ If @error Then ;~ MsgBox("", "", "Cars Drive Train Error: " & @error) ;~ EndIf ;~ _ArrayDisplay($carsDriveTrain2, "Drive Train2 Before") ;~ If @error Then ;~ MsgBox("", "", "Cars Drive Train 2 Error: " & @error) ;~ EndIf _ArrayConcatenate($carsDriveTrain, $carsDriveTrain2) _ArrayDisplay($carsDriveTrain, "Drive Train After Concat") ElseIf $driveTrainVal = "" Then _ArrayConcatenate($carsDriveTrain, $carsEngine) Else $carsDriveTrain = _filter($carsEngine, 0, 9, $driveTrain) EndIf Dim $carsOdom[1][16] $min = GUICtrlRead($odomMin) $max = GUICtrlRead($odomMax) For $i = 0 To UBound($carsDriveTrain) - 1 If $carsDriveTrain[$i][14] > $min And $carsDriveTrain[$i][14] < $max Then _ArrayAdd($carsOdom, $carsDriveTrain[$i]) MsgBox("", "", "ADDED!") EndIf Next _ArrayDisplay($carsOdom, "Cars Odom") $j += 1 WEnd _IEQuit($oIE) EndFunc ;==>_query Func _filter($tofilter, $xpos1, $ypos1, $str) ;~ If UBound($tofilter, 1) <= 1 Then ;~ $endx = 0 ;~ Else ;~ $endx = UBound($tofilter, 1) - 1 ;~ EndIf ;~ $cars = _ArrayFindAll($tofilter, $str, $tofilter[$xpos1][$ypos1], $tofilter[$endx][$ypos1], 0, 1, $ypos1, False) $cars = _ArrayFindAll($tofilter, $str, Default, Default, 0, 1, $ypos1) Dim $carsFiltered[1][16] = [["a", "b", "c", "d", "e", "f", "g", "h", "i", "j", "k", "l", "m", "n", "o", "p"]] ;_ArrayDisplay($carsFiltered) $i = 0 $uBound = UBound($cars) - 1 While $i < $uBound $filtered = _ArrayExtract($tofilter, $cars[$i], $cars[$i], 0, 15) ;_ArrayDisplay($filtered, "_filter Array of Filtered") _ArrayConcatenate($carsFiltered, $filtered) $i += 1 WEnd _ArrayDelete($carsFiltered, 0) Return $carsFiltered EndFunc ;==>_filter  
      If you know of a more efficient way of doing this please let me know would be more than happy to chew down my inefficiency while learning! (It's like my grandpa used to say, "The only criticism I can't use is that which is not given to me.")
       
      The Item an I am using to test the filter is:

       
    • rm4453
      By rm4453
      Hello,
       
      I am currently writing a program that parses a massive table from a website, and need a way to add a progress bar while parsing.
      I am currently using the function _IETableWriteToArray($oObj, True) to parse the array. I need the progress bar to update as the table is parsed, not just at the end of the parsing.
      Any help at all would be very much appreciated!
       
      *EDIT --> The array I am left with after parsing is $array[0-50000][16]
    • nooneclose
      By nooneclose
      My program has to first search for names in Column D that do not match up with column C. I got that search to work using arrays. It was slow and I could not figure out how to delete them so I just manually put coded the names that do not belong. I found their cell location but I do not know how to store that location and delete it.
      This is what I have so far.
      Local $NameToDelete1[6]  = _Excel_RangeFind($OpenWorkbook, "Smith, Bill") _ArrayDisplay($NameToDelete1, "Excel UDF: _Excel_RangeFind Example 1", "", 0, "|", "Sheet|Name|Cell|Value|Formula|Comment") _Excel_RangeDelete($OpenWorkbook.ActiveSheet, $NameToDelete1[2], $xlShiftUp)  
      Please help, I wanted to have this program done yesterday but I did not see this problem until yesterday. 
    • TrashBoat
      By TrashBoat
      So Im trying to make a simple 2d game and make some sort of collision detection so why not to make a 2 dimensional array but i have no clue how  to write it in multiple lines
      Global $map[5,5] = [0,0,0,0,0 _ [0,0,0,0,0 _ [0,0,0,0,0 _ [0,0,0,0,0 _ [0,0,0,0,0] something like this but it doesn't work
×