Ideas on how to create arrays easily?

[Atrax27]

So I've got a relatively consistent workflow in order to create my arrays, and it involves

1) copying a list of data,

2) pasting into excel,

3) concatenate-ing each row 

=CONCATENATE(CHAR(34),A2,CHAR(34),",")

Then inserting into Notepad and deleting all the garbage space so that I can get everything in line so it works with autoit arrays (below)

$i = 0
$s = 3
Dim $array[3] = ["Apples","Bananas","Oranges"]

 

Is there a faster way to do this? I do it often enough that it's an annoyance (hundreds of items in each array), perhaps I can write a module in AutoIT with a GUI where I simply paste a list into a textbox and it spits out a pre-formatted array? Any recommendations? Thank you. 

Edited November 12, 2020 by Atrax27

[Subz]

Can you give an example of the list?  Normally you should just be able to use StringSplit to create an array from a string.

[Atrax27]

The list will be something not very difficult and be only letters or numbers

 

Liam
Olivia
Noah
Emma
Oliver
Ava
William
Sophia
Elijah
Isabella
James
Charlotte
Benjamin
Amelia
Lucas
Mia
Mason
Harper
Ethan
Evelyn

Just looking for a way to more quickly and efficiently turn the above list into a correctly formatted list like this:

Dim $array[20] = ["Liam","Olivia","Noah","Emma","Oliver","Ava","William","Sophia","Elijah","Isabella","James","Charlotte","Benjamin","Amelia","Lucas","Mia","Mason","Harper","Ethan","Evelyn"]

 

[Subz]

So you can use something like:

#include <Array.au3>
;~ Create dummy data
Local $sListData = "Liam" & @CRLF
    $sListData &= "Olivia" & @CRLF
    $sListData &= "Noah" & @CRLF
    $sListData &= "Emma" & @CRLF
    $sListData &= "Oliver" & @CRLF
    $sListData &= "Ava" & @CRLF
    $sListData &= "William" & @CRLF
    $sListData &= "Sophia" & @CRLF
    $sListData &= "Elijah" & @CRLF
    $sListData &= "Isabella" & @CRLF
    $sListData &= "James" & @CRLF
    $sListData &= "Charlotte" & @CRLF
    $sListData &= "Benjamin" & @CRLF
    $sListData &= "Amelia" & @CRLF
    $sListData &= "Lucas" & @CRLF
    $sListData &= "Mia" & @CRLF
    $sListData &= "Mason" & @CRLF
    $sListData &= "Harper" & @CRLF
    $sListData &= "Ethan" & @CRLF
    $sListData &= "Evelyn"

Local $aListData = StringSplit($sListData, @CRLF, 1)
_ArrayDisplay($aListData)

 

Edited November 12, 2020 by Subz
Complete example

[Atrax27]

Im not sure how to incorporate that code with a list of text. Below is the end result of what I desire it to do. Its getting to the below point that is my difficulty. 

 

Run("notepad.exe")
WinWaitActive("Untitled - Notepad")
$i = 0
$s = 20
Dim $array[20] = ["Liam","Olivia","Noah","Emma","Oliver","Ava","William","Sophia","Elijah","Isabella","James","Charlotte","Benjamin","Amelia","Lucas","Mia","Mason","Harper","Ethan","Evelyn"]
Do
send($array[$i])
send("{ENTER}")
$i = $i + 1
Until $i = $s

 

[Subz]

So you can just create the file using _FileWriteFromArray for example:

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

;~ Create dummy data
Local $sListData = "Liam" & @CRLF
    $sListData &= "Olivia" & @CRLF
    $sListData &= "Noah" & @CRLF
    $sListData &= "Emma" & @CRLF
    $sListData &= "Oliver" & @CRLF
    $sListData &= "Ava" & @CRLF
    $sListData &= "William" & @CRLF
    $sListData &= "Sophia" & @CRLF
    $sListData &= "Elijah" & @CRLF
    $sListData &= "Isabella" & @CRLF
    $sListData &= "James" & @CRLF
    $sListData &= "Charlotte" & @CRLF
    $sListData &= "Benjamin" & @CRLF
    $sListData &= "Amelia" & @CRLF
    $sListData &= "Lucas" & @CRLF
    $sListData &= "Mia" & @CRLF
    $sListData &= "Mason" & @CRLF
    $sListData &= "Harper" & @CRLF
    $sListData &= "Ethan" & @CRLF
    $sListData &= "Evelyn"

;~ Create array
Local $aListData = StringSplit($sListData, @CRLF, 1)
;~ File to save the array
Local $sListFile = @ScriptDir & "\ListData.txt"
;~ Write the array to the file
_FileWriteFromArray($sListFile, $aListData, 1)

;~ Display the ListData.txt file
ShellExecute($sListFile)

 

[Atrax27]

Its not this list of 20 that I specifically care about, it's any list at all. If I have a list of 300 names I want it in the correct format. If I have a list of 5 names I also want it in that format. 

Step 1) Have a list of items

Apple
Orange
Salmon
Chainsaw

Step 2) ????

 

Step 3) Horray it's formatted in an array so it can be utilized in autoit

Dim $array[4] = ["Apple","Orange","Salmon","Chainsaw"]

Step 4) Run my script

Step 5) rinse and repeat at step 1 ad nauseum with a new list of items. Sometimes 10 items, sometimes 300 items. 

 

Step 2 is my heartache. 

[Atrax27]

4 minutes ago, Subz said:

So you can just create the file using _FileWriteFromArray for example:

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

;~ Create dummy data
Local $sListData = "Liam" & @CRLF
    $sListData &= "Olivia" & @CRLF
    $sListData &= "Noah" & @CRLF
    $sListData &= "Emma" & @CRLF
    $sListData &= "Oliver" & @CRLF
    $sListData &= "Ava" & @CRLF
    $sListData &= "William" & @CRLF
    $sListData &= "Sophia" & @CRLF
    $sListData &= "Elijah" & @CRLF
    $sListData &= "Isabella" & @CRLF
    $sListData &= "James" & @CRLF
    $sListData &= "Charlotte" & @CRLF
    $sListData &= "Benjamin" & @CRLF
    $sListData &= "Amelia" & @CRLF
    $sListData &= "Lucas" & @CRLF
    $sListData &= "Mia" & @CRLF
    $sListData &= "Mason" & @CRLF
    $sListData &= "Harper" & @CRLF
    $sListData &= "Ethan" & @CRLF
    $sListData &= "Evelyn"

;~ Create array
Local $aListData = StringSplit($sListData, @CRLF, 1)
;~ File to save the array
Local $sListFile = @ScriptDir & "\ListData.txt"
;~ Write the array to the file
_FileWriteFromArray($sListFile, $aListData, 1)

;~ Display the ListData.txt file
ShellExecute($sListFile)

 

This is very cool but it's the reverse of what I'm looking for I think? It creates a nice text file with the list of names and I like how it seems to immediately process everything. I'll definitely use this later for other functionality thank you. 

[Subz]

So where are the list items coming from?  If it was a file, you can just use FileReadToArray, if it's in clipboard, just capture the text using ClipGet, The example I posted above was just based on your text above, however it doesn't matter if you have 5 or 1000 names, it will still create the array.

[Atrax27]

Different sources and different data runs that I receive. The code is a little more complex than the notepad function I pasted above but it operates the same way. 

1) Get a list of items from another source, format is excel spreadsheet and length changes constantly. Just plain text data.

2) I take this plain text data and format it within excel (step 3 in the OP) and then also make it a single line

3) plug list into my au3 script

4) run the script (open website, login, go to form field, type first item in array, hit enter, type second item in array, hit enter, type third item in array, hit enter, etc...

 

So it's a file, but there is a lot of additional info I don't need, and what I do need is often in different locations throughout the excel spreadsheet, or I need to filter data down to get the list I want. In my mind the perfect solution would be an autoit script that prompted me to paste a list of items into a GUI box (unlimited length), I would click "process", and it would spit out my data perfectly formatted in the way I need to run my script. My current solution (step 2) works, but not very efficiently. 

[Nine]

1 hour ago, Atrax27 said:

In my mind the perfect solution would be an autoit script that prompted me to paste a list of items into a GUI box (unlimited length), I would click "process", and it would spit out my data perfectly formatted in the way I need to run my script.

Why don't you start with that ?  Just create a simple GUI with a large Edit box and a button "Process".  You can easily paste your data into the edit box.  When you hit  button process, it will split you data from the edit box and reformat it the way you need.  All that in a single script.

[GokAy]

Hey Atrax,

You can do all of that with VBA.

I created an xlsm file for you. How to use:

1. Open both excel files (The one I am attaching (code at the bottom of post) and your data xlsx)

2. I added shortcuts to the couple of macros, you can change them with ALT+F8 > Select the macro > Options.

- Copy with CurrentRegion method: CTRL + SHIFT + C

- Copy with Range method: CTRL + SHIFT + R

- Toggle "Got Headers" value: CTRL + SHIFT + T

- Clean Data Sheet: CTRL + SHIFT + L

 

CurrentRegion method:

Say you have data like this, filtered and you want all of the rows visible. Row1 is header. So you toggle "Got Headers" to "Yes".

- If you want only 1 column, select the header of that column and use ShortCut key combo (see above) to add the visible rows to xlsm file Data sheet.

- For consecutive columns: If you want to concatenate B and C columns with a space in between, you select both headers (click hold and drag) 

- If you want B and C seperately, select B first and select C afterwards by holding CTRL.

I guess you get the idea, for example this would work too: Select A, Select B & C, Select D. This would first copy rows of A, then copy B & C rows, and finally copy D rows

Range method:

If you just want a part of the list, use this method. The following selection would, for example, add to Data sheet the following.

A2

A4 & B4

A10 & B10 & C10 & D10

A13 & B13 & C13 & D13

A17 & B17 & C17 & D17

B24

C30 & D30

B32 & C32 & D32

C34

C35

C36

 

When you are done, there is another macro that creates the file to be used by your AutoIt script. I tested with a3x compiled and it works. Also, starts that script. I haven't assigned any shortcuts to this. But as you already know, it is easy to assign/change a shortcut. There are a couple of options:

All you need to do within your AutoIt script is add this line:

#include <Array.au3>

Local $aArray = FileReadToArray(@DesktopDir & "\array_list.txt")

 

The code involved is as follows ( I used Sheet.CodeName's instead of Name, however they are the same)

Option Explicit
Option Compare Text

Public Sub Copy_CurrentRegion()
    
    Dim rngData As Range
    Dim cCell As Range
    Dim ColumnCount As Integer
    Dim RegionCount As Integer
    Dim HeaderOffset As Integer
    Dim rngRow As Range
    Dim rowData As Long
    
    ' Find out first available line in Data sheet
    If Data.Range("A1").Value = "" Then
        rowData = 1
    Else
        rowData = Data.Range("A1").CurrentRegion.Rows.Count + 1
    End If
    
    ' Set HeaderOffset
    HeaderOffset = 0
    If Settings.Range("Headers").Value = "yes" Then HeaderOffset = 1
    
    ' Find out how many regions are selected
    RegionCount = Len(Selection.Address) - Len(Replace(Selection.Address, ",", "")) + 1
    
    Dim i As Integer
    Dim j As Long
    
    Dim strData As String
    
    ' Cycle through all regions
    For i = 1 To RegionCount
        Set rngData = ActiveSheet.Range(Split(Selection.Address, ",")(i - 1))
        
        ' Find how many columns are in the region
        ColumnCount = Len(rngData.Address) - Len(Replace(rngData.Address, ":", "")) + 1
        
        If ColumnCount = 2 Then
            ColumnCount = Range(Split(rngData.Address, ":")(1)).Column - Range(Split(rngData.Address, ":")(0)).Column + 1
        End If
        
        ' Set rngData to the current region's first column for un-filtered rows
        Set rngData = rngData.Offset(HeaderOffset, 0).Resize(rngData.CurrentRegion.Rows.Count - 1, 1).SpecialCells(xlCellTypeVisible)
        
        ' Cycle thorough all cells in rngData
        For Each cCell In rngData
            strData = ""
            
            For j = 1 To ColumnCount
                 strData = strData & " " & Trim(cCell.Offset(0, j - 1).Value)
            Next j
            
            ' Write concatenated string to Data sheet
            If Settings.Range("DoubleQuotes").Value = "no" Then
                Data.Range("A" & rowData).Value = Trim(strData)
            Else
                Data.Range("A" & rowData).Value = Chr(34) & Trim(strData) & Chr(34)
            End If
            
            rowData = rowData + 1
        Next cCell
    Next i
    
    ' Clean up
    Set rngRow = Nothing
    Set rngData = Nothing

End Sub

Public Sub Copy_Range()
    
    Dim rngData As Range
    Dim cCell As Range
    Dim ColumnCount As Integer
    Dim RegionCount As Integer
    Dim HeaderOffset As Integer
    Dim rngRow As Range
    Dim rowData As Long
    
    ' Find out first available line in Data sheet
    If Data.Range("A1").Value = "" Then
        rowData = 1
    Else
        rowData = Data.Range("A1").CurrentRegion.Rows.Count + 1
    End If
    
    ' Set Header Offset to 0 for Range Copy
    HeaderOffset = 0
    
    ' Find out how many regions are selected
    RegionCount = Len(Selection.Address) - Len(Replace(Selection.Address, ",", "")) + 1
    
    Dim i As Integer
    Dim j As Long
    
    Dim strData As String
    
    ' Cycle through all regions
    For i = 1 To RegionCount
        Set rngData = ActiveSheet.Range(Split(Selection.Address, ",")(i - 1))
        
        ' Find how many columns are in the region
        ColumnCount = Len(rngData.Address) - Len(Replace(rngData.Address, ":", "")) + 1
        
        If ColumnCount = 2 Then
            ColumnCount = Range(Split(rngData.Address, ":")(1)).Column - Range(Split(rngData.Address, ":")(0)).Column + 1
        End If
        
        ' Set rngData to the first column in the current region
        Set rngData = rngData.Resize(rngData.Rows.Count, 1)
        
        ' Cycle through all cells in rngData
        For Each cCell In rngData
            ' Check if cell is filtered (RowHeight = 0)
            If Not ActiveSheet.Rows(cCell.Row).RowHeight = 0 Then
                strData = ""
                
                For j = 1 To ColumnCount
                     strData = strData & " " & Trim(cCell.Offset(0, j - 1).Value)
                Next j
                
                ' Write concatenated string to Data sheet
                If Settings.Range("DoubleQuotes").Value = "no" Then
                    Data.Range("A" & rowData).Value = Trim(strData)
                Else
                    Data.Range("A" & rowData).Value = Chr(34) & Trim(strData) & Chr(34)
                End If
                
                rowData = rowData + 1
            End If
        Next cCell
    Next i
    
    ' Clean up
    Set rngRow = Nothing
    Set rngData = Nothing

End Sub

Public Sub Toggle_Headers()

    ' Toggle Got Headers value
    If Settings.Range("Headers").Value = "Yes" Then
        Settings.Range("Headers").Value = "No"
    Else
        Settings.Range("Headers").Value = "Yes"
    End If

End Sub

Public Sub Clean_ArrayList_Data()

    ' Clean existing data on the Data sheet
    Data.Range("A1").CurrentRegion.ClearContents

End Sub

Public Sub Create_ArrayListFile()

    Dim FolderName As String
    Dim FileName As String

    ' Get Folder to save txt file
    If Settings.Range("ArrayList_Folder").Value = "Desktop" Then
    	FolderName = Environ$("Userprofile") & "\Desktop"
    else
    	FolderName = Settings.Range("ArrayList_Folder").Value
    endif
    
    FileName = Settings.Range("ArrayList_Filename").Value
    
    Dim fso As FileSystemObject
    Dim oFile As TextStream

    Set fso = New FileSystemObject
    
    ' Delete existing txt file if it exists
    If fso.FileExists(FolderName & "\" & FileName) Then fso.DeleteFile (FolderName & "\" & FileName)
    
    ' Create new txt file
    Set oFile = fso.CreateTextFile(FolderName & "\" & FileName, True)
    
    ' Write all lines in Data sheet to txt file
    Dim i As Long
    
    For i = 1 To Data.Range("A1").CurrentRegion.Rows.Count
        oFile.WriteLine (Data.Cells(i, 1).Value)
    Next i
    
    ' Close txt file
    oFile.Close
    
    ' Clean up
    Set oFile = Nothing
    Set fso = Nothing

    ' Call Sub to run the script
    Call Run_Script

End Sub

Private Sub Run_Script()

    ' Run script file
    Shell ("cmd.exe /c start """ & Settings.Range("AutoIt").Value & """ """ & Settings.Range("Script").Value & """")

End Sub

 

 

 

 

test.xlsm

Edited November 12, 2020 by GokAy
Added if-then-else for foldername in xlsm file, re-uploaded

[rudi]

I'd like to suggest to have a look at the functions StringSplit() and _ArrrayAdd(), might be helpful for your task:

 

expandcollapse popup

#include <Array.au3>
#include <Debug.au3>


; 1D, just one collumn data

$s1D="Liam" & @crlf & _
"Olivia" & @crlf & _
"Noah" & @crlf & _
"Emma" & @crlf & _
"Oliver" & @crlf & _
"Ava" & @crlf & _
"William" & @crlf & _
"Sophia" & @crlf & _
"Elijah" & @crlf & _
"Isabella" & @crlf & _
"James" & @crlf & _
"Charlotte" & @crlf & _
"Benjamin" & @crlf & _
"Amelia" & @crlf & _
"Lucas" & @crlf & _
"Mia" & @crlf & _
"Mason" & @crlf & _
"Harper" & @crlf & _
"Ethan" & @crlf & _
"Evelyn"

$aWork=StringSplit($s1D,@CRLF,1)
_DebugArrayDisplay($aWork)




; 2D sample data from Excel
$s2D="XL Line1 Col1" & @TAB & "XL Line1 Col2" & @TAB & "XL Line1 Col3" & @CRLF & _
"XL Line2 Col1" & @TAB & "XL Line2 Col2" & @TAB & "XL Line2 Col3" & @CRLF & _
"XL Line3 Col1" & @TAB & "XL Line3 Col2" & @TAB & "XL Line3 Col3" & @CRLF & _
"XL Line4 Col1" & @TAB & "XL Line4 Col2" & @TAB & "XL Line4 Col3"



Dim $a2Work [1][3] ; Initial Array, correct number of col, "empty-line-0"
_ArrayAdd($a2Work,$s2D,0,@TAB,@CRLF)
$a2Work[0][0]=UBound($a2Work)-1

_DebugArrayDisplay($a2Work)

 

[water]

.. and function _Excel_RangeRead (Excel UDF) returns an array.

[markyrocks]

check out the library i'm writing its still in its prepubescent form but its covering a wide variety of ways to manipulate arrays.  I'm getting ready to dump a huge update but it covers about everything you can imagine.  that and it ridiculously simplifies creating and expanding arrays.  It will take a few to get used to what it does.  As it stands a few functions aren't working optimally or even as intended but they are very complex.  my head is about to explode but i think its a real game changer.  The mass majority are working will you just have to get used to the system.  It is overwhelmingly huge but i have a bunch of test cases to play with.  So playing around with those should get anyone started somewhat quickly.  O and the Sort function in the currently posted version is horribly unoptimized so i'd suggest using the regular _array_sort() at least for the time being.

 

Edited November 12, 2020 by markyrocks

[Atrax27]

Guys I'm so close..... 

 

#include <MsgBoxConstants.au3>
#include <StringConstants.au3>
#include <String.au3>
#include <File.au3>


$_FilePath = 'D:\text.txt'
$_FileRead = FileRead ( $_FilePath )
Local $iCountLines = _FileCountLines($_FilePath) ; Retrieve the number of lines in the current script.
Local $sString = StringStripWS($_FileRead, $STR_STRIPALL); Strip all chars in between the words.
Local $Txt = "Dim $array[" & $iCountLines & "] = [" & $sString & "]"
ClipPut($Txt)
MsgBox($MB_SYSTEMMODAL, "", $Txt); may not need this but here just so I can quickly see what it's showing me

Now I just need it to replace all "Line Feeds" with a 

","

and then just put a single quotation at the beginning and end of the array. 

 

EDIT:

for the text.txt file (if testing), just throw a bunch of garbage in there to see what it does

grtey4yy6
yu
y5
y65
564u78365
7585853
735
65trehrtehrte
rtj67777
00000000000
--------
78oi78o

returns

Dim $array[12] = [grtey4yy6yuy5y65564u78365758585373565trehrtehrtertj6777700000000000--------78oi78o]

but I'm looking for it to return instead,

Dim $array[12] = ["grtey4yy6","yu","y5","y65","564u78365","7585853","735","65trehrtehrte","rtj67777","00000000000","--------","78oi78o]

 

Edited November 12, 2020 by Atrax27

[rudi]

Hi.

 

Simply read the file to the array directly?

Pls. lookup in the help file:

 

filereadtoarray() and  _filereadtoarray()

[Atrax27]

4 minutes ago, rudi said:

Hi.

 

Simply read the file to the array directly?

Pls. lookup in the help file:

 

filereadtoarray() and  _filereadtoarray()

I'll be honest I don't yet have a need for the type of array this produces. 2D arrays? Splits on full  delimiter? I'm 100% sure I may use them one day but it's beyond my skill at the moment hence why I'm just using a list type array (would that be a 1D array? 😆 ) cause it's simple and I'm not yet smart enough to see a use case for anything else. 

 

[Nine]

Try this :

#include <Constants.au3>
#include <Array.au3>

$_FilePath = '1.txt'
$_FileRead = FileReadToArray ($_FilePath)
;_ArrayDisplay($_FileRead)
$Txt = '"' & _ArrayToString($_FileRead,",") & '"'
ClipPut($Txt)
MsgBox($MB_SYSTEMMODAL, "", $Txt); may not need this but here just so I can quickly see what it's showing me

 

[Atrax27]

Nine, can I be your apprentice? 

EDIT: thank you everyone for your pointers and help here, I really appreciate this forum and everyone here who contributes  

#include <Constants.au3>
#include <Array.au3>
#include <File.au3>

$_FilePath = 'D:\text.txt'
Local $iCountLines = _FileCountLines($_FilePath)
$_FileRead = FileReadToArray ($_FilePath)
;_ArrayDisplay($_FileRead)
$Txt = "Dim $array[" & $iCountLines & "] = [" & '"' & _ArrayToString($_FileRead,CHR(34)&","&CHR(34)) & '"]'
ClipPut($Txt)
MsgBox($MB_SYSTEMMODAL, "", $Txt)

I think theres probably a way to count the number (12) without using the _FileCountLines function, but I can't easily see it. 

Edited November 12, 2020 by Atrax27