MrCheese

It was Avast doing false positives 😲

Hi All, I have Autoit/SciTe installed under: C:\Program Files (x86)\AutoIt3\ The last two nights, I've gone to execute a script via SciTE and I encounter this error: --- Autoit error Line 0 (File "C:\Program Files (x86)\AutoIt23\SciTE\AutoIt3Wrapper\AutoIt3Wrapper.au3"): Error: Error opening the file. --- I then check here: C:\Program Files (x86)\AutoIt23\SciTE\AutoIt3Wrapper\ and the file in question is missing. I then copy in a version from a portable vr of SciTe I have. This worked initially, but then after some time (hours), it also disappeared. edit: I can confirm that after I copy it into the dir, run a script, it is removed in the process. Has anyone seen this?

Hi @mLipok Really interesting work on this. I downloaded v2.1.15 beta, and attempting to use your MSSQL example - this is a server I am running locally Func _Example_MSSQL() Local $sDriver = 'SQL Server' Local $sDatabase = 'newserver' ; change this string to YourDatabaseName Local $sServer = 'localhost\SQLEXPRESS' ; change this string to YourServerLocation Local $sUser = 'XXXXX' ; change this string to YourUserName Local $sPassword = 'XXXXXX' ; change this string to YourPassword Local $sConnectionString = 'DRIVER={' & $sDriver & '};SERVER=' & $sServer & ';DATABASE=' & $sDatabase & ';UID=' & $sUser & ';PWD=' & $sPassword & ';' _Example_1_RecordsetToConsole($sConnectionString, "Select * from [RIM_Image_Info]") _Example_2_RecordsetDisplay($sConnectionString, "Select * from SOME_TABLE") _Example_3_ConnectionProperties($sConnectionString) _Example_4_MSSQL($sServer, $sDatabase, $sUser, $sPassword, "Select * from SOME_TABLE") EndFunc ;==>_Example_MSSQL However, when I run it, I get this output, referring to the ADO.au3 UDF file: "C:\Temp\CHW\ADO.au3"(381,89) : error: _ArrayDisplay() called with wrong number of args. _ArrayDisplay($aSelect, $sTitle, "", 0, '|', $sArrayHeader, Default, $iAlternateColors) ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^ "C:\JRStuff\MyDocs\JR Programs\Macros\autoit-v3-SciTE\Include\Array.au3"(480,176) : REF: definition of _ArrayDisplay(). Func _ArrayDisplay(Const ByRef $aArray, $sTitle = Default, $sArrayRange = Default, $iFlags = Default, $vUser_Separator = Default, $sHeader = Default, $iMax_ColWidth = Default) ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^ "C:\Temp\CHW\ADO.au3"(385,90) : error: _ArrayDisplay() called with wrong number of args. _ArrayDisplay($aRecordset, $sTitle, "", 0, Default, Default, Default, $iAlternateColors) ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^ "C:\JRStuff\MyDocs\JR Programs\Macros\autoit-v3-SciTE\Include\Array.au3"(480,176) : REF: definition of _ArrayDisplay(). Func _ArrayDisplay(Const ByRef $aArray, $sTitle = Default, $sArrayRange = Default, $iFlags = Default, $vUser_Separator = Default, $sHeader = Default, $iMax_ColWidth = Default) ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^ "C:\Temp\CHW\ADO_EXAMPLE.au3"(40,80) : warning: $sFileFullPath: possibly used before declaration. Local $sConnectionString = 'Driver={' & $sDriver & '};Dbq="' & $sFileFullPath & ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^ "C:\Temp\CHW\ADO_EXAMPLE.au3"(42,107) : warning: $sConnectionString already declared/assigned Local $sConnectionString = _ADO_ConnectionString_Access($sMDB_FileFullPath, $sUser, $sPassword, $sDriver) ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^ "C:\Temp\CHW\ADO_EXAMPLE.au3"(40,80) : error: $sFileFullPath: undeclared global variable. Local $sConnectionString = 'Driver={' & $sDriver & '};Dbq="' & $sFileFullPath & ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^ C:\Temp\CHW\ADO_EXAMPLE.au3 - 3 error(s), 2 warning(s) !>15:03:25 AU3Check ended. Press F4 to jump to next error.rc:2 Any thoughts? What am I doing wrong? ------------- I fixed it by adding: '_DebugArrayDisplay' instead of '_ArrayDisplay' needed the debug.au3 though.

Hi All, as an updated, I actually ended up needing to filter on two columns, which makes it more complex. Keeping this in excel made it complex. and that next time I should look into using sql. to compute 602,335 rows with ~21 possible filtered outcomes (3 in column X:X and 7 in column A:A) took 27mins to filter and extract and save each workbook. I'm sure this can be optimised, but it works. #include <Excel.au3> #include <MsgBoxConstants.au3> #include <Array.au3> Local $oExcel = _Excel_Open() ;true,false,false) If @error Then Exit MsgBox($MB_SYSTEMMODAL, "", "Error creating the Excel application object.") $dir = @ScriptDir & "\" $book = "master_baseline.xlsx" Local $oWorkbook = _Excel_BookOpen($oExcel, $dir & $book) If @error Then Exit MsgBox($MB_SYSTEMMODAL, "", "Error opening Workbook") $Fcol1 = "X" $Fcol2 = "A" $baserange = "A:X" Global $aRowSort[0] Global $aColUnique[0] Global $aRowSort[] = [$Fcol1, $Fcol2] $iRow = UBound($aRowSort) $msg = "$iRow |" & $iRow dbb() ;for each column that we want to sort on, lets extract all the unique rows into an array, and create a larger array containing those suparrays. For $i = 0 To UBound($aRowSort) - 1 $rowval = $aRowSort[$i] $rowsort = $rowval & ":" & $rowval $msg = "reading range" & $rowsort dbb() $aColCollect = _Excel_RangeRead($oWorkbook, Default, $oWorkbook.ActiveSheet.Usedrange.Columns($rowsort)) $msg = "adding array" dbb() _ArrayAdd($aColUnique, _ArrayUnique($aColCollect, Default, Default, Default, $ARRAYUNIQUE_NOCOUNT), 0, "|", @CRLF, 1) Next $iRowT = UBound($aColCollect) $msg = $iRowT & " total size" dbb() $aTmpArray0 = $aColUnique[0] ; setting temp0 arrays as the sub array0 $aTmpArray1 = $aColUnique[1] ; setting temp1 array as the sub array1 ;lets apply a filter once based on the first column to sort on (X:X) For $u = 0 To UBound($aTmpArray0) - 1 If $aTmpArray0[$u] = "" Then ContinueLoop; if filter value is blank then skip $i = 0 $rowval = $aRowSort[$i] $rowsort = $rowval & ":" & $rowval $nCol1 = _Excel_ColumnToNumber($rowval) _Excel_FilterSet($oWorkbook, Default, Default, $nCol1, $aTmpArray0[$u]) $msg = "first filter: " & $aTmpArray0[$u] dbb() ; lets apply the second filter to apply based on unique values contained in A:A For $j = 0 To UBound($aTmpArray1) - 1 If $aTmpArray1[$j] = "" Then ContinueLoop ; if filter value is blank then skip $i = 1 $rowval = $aRowSort[$i] $rowsort = $rowval & ":" & $rowval $nCol1 = _Excel_ColumnToNumber($rowval) _Excel_FilterSet($oWorkbook, Default, Default, $nCol1, $aTmpArray1[$j]) $msg = "first filter: " & $aTmpArray1[$j] dbb() Local $afilter = _Excel_FilterGet($oWorkbook) If $afilter[0][6] <= 1 Then ; location of filtered row count, If the filter obtained no data, then skip $msg = "skipping loop" dbb() ContinueLoop EndIf $sName = @ScriptDir & "\R1_" & $aTmpArray0[$u] & "_score_" & $aTmpArray1[$j] & ".xls" $msg = "saving" dbb() MakeCopy(1, $iRowT, $sName) ; lets save the output as a xls Next _Excel_FilterSet($oWorkbook, Default, Default, 0) Next MsgBox(0, "completed", "Completed") Exit Func MakeCopy($iStart, $iEnd, $sName) Local $oRange = $oWorkbook.ActiveSheet.Range("A" & $iStart & ":X" & $iEnd) ; Local $oRange = $oWorkbook.ActiveSheet.Range("A1:X"$iRowT&") Local $oWB = _Excel_BookNew($oExcel, 1) _Excel_RangeCopyPaste($oWB.ActiveSheet, $oRange, "A1") If @error Then Exit MsgBox($MB_SYSTEMMODAL, "", "Error copying cells." & "/" & @error) _Excel_BookSaveAs($oWB, $sName, $xlWorkbookNormal, True) If @error Then Exit MsgBox($MB_SYSTEMMODAL, "", "Error saving as" & "/" & @error) _Excel_BookClose($oWB, False) EndFunc ;==>MakeCopy Func dbb() ; MsgBox(0,$msg,$msg) ConsoleWrite($msg & @CRLF) EndFunc ;==>dbb I did ask in this post how to deal best with the incremental variables and thus @subz suggested the subarrays; and therefore because of lack of foundational knowledge, I had to make the variables static for use in the nested loop. Working on this a little more.... HOWEVER, removing the _excel_filterget and the subsequent if statement reduced this to only 1.8mins, but of course I had a few dummy sheets I had to delete. So changing the last function to this, stopped the excel sheets from being created with empty filter results, this took 1.9mins but avoided having blank exported files. ...... _Excel_RangeCopyPaste($oWB.ActiveSheet, $oRange, "A1") $oRangeRead = _Excel_RangeRead($oWB, Default, "A1:B3") $msg = "checking cell" dbb() ;_ArrayDisplay($oRangeRead, "$oRangeRead") If $oRangeRead[1][0] = "" Then $msg = "skipping sheet" dbb() Return EndIf $msg = "new book saving" dbb() If @error Then Exit MsgBox($MB_SYSTEMMODAL, "", "Error copying cells." & "/" & @error) ..... Thanks for your help, see what you think?

i realised that I had to call it as a set array not attached to a looping variable as it the two $i values were referenced in different levels of a nested loop. I'm sure there is a better way $aTmpArray0 = $aColUnique[0] ; setting temp0 arrays as the sub array0 $aTmpArray1 = $aColUnique[1] ; setting temp1 array as the sub array1 For $u = 0 To UBound($aTmpArray0) - 1 If $aTmpArray0[$u] = "" Then ContinueLoop $i = 0 $rowval = $aRowSort[$i] $rowsort = $rowval & ":" & $rowval $nCol1 = _Excel_ColumnToNumber($rowval) _Excel_FilterSet($oWorkbook, Default, Default, $nCol1, $aTmpArray0[$u]) $msg = "first filter: " & $aTmpArray0[$u] dbb() For $j = 0 To UBound($aTmpArray1) - 1 If $aTmpArray1[$j] = "" Then ContinueLoop $i = 1 $rowval = $aRowSort[$i] $rowsort = $rowval & ":" & $rowval $nCol1 = _Excel_ColumnToNumber($rowval) _Excel_FilterSet($oWorkbook, Default, Default, $nCol1, $aTmpArray1[$j]) $msg = "first filter: " & $aTmpArray1[$j] dbb() Local $afilter = _Excel_FilterGet($oWorkbook) If $afilter[0][6] <= 1 Then ; location of filtered row count $msg = "skipping loop" dbb() ContinueLoop EndIf $sName = @ScriptDir & "\R1_" & $aTmpArray0[$u] & "_score_" & $aTmpArray1[$j] & ".xls" $msg = "saving" dbb() MakeCopy(1, $iRowT, $sName) Next _Excel_FilterSet($oWorkbook, Default, Default, 0) Next

I think I understand! I'll work on it.

thanks Subz, when I do this, it adds the second / nth loop onto the bottom of the array and not into an adjacent column. How do I add to the adjacent column instead of down the row?

HI All, Thanks in advance for your help! If I'm reading and collecting unique values out of x arrays, how do I sequentially name "$aColUnique&$i" in the following example: $rSort1 = "X:X" ; first sort $rSort2 = "A:A" ; second sort Local $aRowSort [] = [$rSort1,$rSort2] $iRow =UBound($aRowSort) For $i = 0 To $iRow - 1 $aColCollect = _Excel_RangeRead($oWorkbook,Default,$aRowSort[$i]) $aColUnique&$i = _ArrayUnique($aColCollect, Default, Default, Default, $ARRAYUNIQUE_NOCOUNT) _ArrayDisplay($aColUnique&$i,"$aRowSort") Next An alternative would be to add the ColUnique data onto the $aRowSort as another column. I attempted to do this, but it wouldn't add to the end of the column. I can revisit it if this new idea is dumb.

Once its accessed via ADO, and extract it into a SQLite DB (as jchd said), would you then use Autoit's sql commands on top, or would you just access it via a SQLite and use sql commands?

Thanks Water. in light of the UDFs and excels limitations and intricacies, would you have a suggested a different way to do this? I can convert xlsx to csv, and use the csv phase / split functions.

thanks Nine and Subz! I'll assess and see how I go.

Hi All, Thanks so much in advance for your help! I have a data set with 300,000 rows (in excel), with 14 columns. In the first column, I have a value, and there are only 10 unique values in this column across all whole data set. I wish to extract these 10 separate data sets based on the common value in the first column. I know there are multiple ways to do this, but in your experience, which is the best way? I have come across/ thought of these ideas: * focused in the excel udf using filter get, filter set, read to array, save to file * using this process, but I'm not sure if it does what I want: https://www.autoitscript.com/forum/topic/178998-solved-create-an-array-from-filtered-excel-sheet/ * read excel to array, copy row (column by column) to new array based on column value *read excel to array, copy to temp array, delete row where its not the set value for this iteration, move to next iteration. I've performed the last two when extracting full columns where there is a desired header to be extracted, whilst keeping a key_id field common across the extracts. I don't need code written by anyone at this point, just a nudge in the right direction

@mikell thanks for your help! Solution worked well.

argh, pulling my hair out. considering this post: say for a string = "03a", how can I strip out the leading 0 and the a. I have tried: $new = StringRegExpReplace($string, '[^1-9][^0-9]', '') and various combinations: ^0+[^0-9] [^[:digit:]] "[^0].*" "^0*(d+)" I'm going loopy!

Thanks @FrancescoDiMuro and thanks @water so i see that my problem that I need to attend to first is initially loading it. I need to load it correctly as a CSV, so I can export it accordingly. The CSV.au3 that you've linked to does everything perfectly; I've kept my current import process (as its scripted out of sql already), and just utilised this as an export. Thanks