Jump to content
AnonymousX

[Solved] Challenge - Concatenating multiple files, summing values with same dates

Recommended Posts

Hello,

So this may be more of a challenge of effective programming then specific to AutoIT but I want to solve this problem with AutoIT  so i'm putting it here. (If someone has a better language to solve with I'm all ears)

 

So the task I'm trying to achieve is that I have multiple .CSV files that have: year, month, day, hour, value. I need to be able to sum up all the values that have the same date/time, then find which date and time has the maximum value.

 

The problem is that each file may or may not have same amount of days/hours as the rest. So I need to devise a way to handle this. 

 

Example:

File A   File B   File C
2018 1 1  1:00 10   2018 1 1 2:00 10   2018 1 1  1:00 10
2018 1 1  2:00 12   2018 1 1 3:00 12   2018 1 2 1:00 12
2018 1 1  3:00 14   2018 1 1 4:00 14   2018 2 1  1:00 16
2018 2 1  1:00 16   2018 2 1  1:00 16            

 

 Answer I want to be spit out is Feb 1st 2018 at 2:00 with value of 48

 

So far I've got code to store all .CSV files to an array, then a loop to go through each csv, but not sure how to effectively manipulate the data. Keep in mind each file has over 7000 time entry points.

 

If anyone can solve this that would be pretty awesome! 

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



RefineData()

Func RefineData()

    Local $i, $filenum, $file, $csvArray, $FilePath = @ScriptDir

    $fileList = _FileListToArrayRec($FilePath, "*.csv", 1) ;Create and array of all .csv files within folder

    Local $chkArray[UBound($fileList)][2]

    ;=====Loop through the .csv files within the folder======

    For $filenum = 1 To UBound($fileList) - 1 Step 1

        $file = $fileList[$filenum]
        $sFilePath = $FilePath & "\" & $file

        ;=====Create array based on csv file=====

        _FileReadToArray($sFilePath, $csvArray, $FRTA_NOCOUNT, ",")
        
        
        ;#### Operations here ######
        
        next
        
        msgbox(0,"", "Date: " & $date_of_max  & "Value: " & $maxVal );display solution
        
        endfunc

 

Edited by AnonymousX

Share this post


Link to post
Share on other sites

Maybe something like:

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

RefineData()

Func RefineData()
    Local $i, $i, $file, $csvArray, $FilePath = @ScriptDir & "\CSV"
    $aFileList = _FileListToArrayRec($FilePath, "*.csv", 1, 0, 0, 2) ;Create and array of all .csv files within folder
    Local $aFileData, $sFileData = "2000/1/1 12:00", $iFileData, $aFullData[0][6], $aSummary[0][2]
    ;=====Loop through the .csv files within the folder======
    For $i = 1 To $aFileList[0]
        ;=====Create array based on csv file=====
        _FileReadToArray($aFileList[$i], $aFileData, 0, ",")
        For $j = 0 To UBound($aFileData) - 1
            _ArrayAdd($aFullData, $aFileData[$j][0] & "/" & $aFileData[$j][1] & "/" & $aFileData[$j][2] & " " & $aFileData[$j][3] & "|" & $aFileData[$j][0] & "|" & $aFileData[$j][1] & "|" & $aFileData[$j][2] & "|" & $aFileData[$j][3] & "|" & $aFileData[$j][4])
        Next
        _ArraySort($aFullData)
    Next
    For $i = 0 To UBound($aFullData) - 1
        $iFileData = _ArraySearch($aSummary, $aFullData[$i][0], 0, 0, 0, 0, 1, 0)
        If @error Then
            _ArrayAdd($aSummary, $aFullData[$i][0] & "|" & $aFullData[$i][5])
        Else
            $aSummary[$iFileData][1] += $aFullData[$i][5]
        EndIf
    Next
    _ArraySort($aSummary, 1, 0, 0, 1)
    _ArrayDisplay($aSummary)
    MsgBox(0,"", "Date: " & $aSummary[0][0] & @CRLF & "Value: " & $aSummary[0][1])
EndFunc

 

Share this post


Link to post
Share on other sites

When I have to work with combining a lot of CSV files and searching for data, I use SQLite.  Its fast, and allows searching with SQL.  Here's an example that should help you.  

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

Global $sOutFile = "test.csv"
Global $sDir = @ScriptDir & "\Test"

Global $aFileList = _FileListToArrayRec($sDir, "*.csv", $FLTAR_FILES,  $FLTAR_RECUR)
If @error Then Exit 1
;~ _ArrayDisplay($aFileList) ;For testing.

;Use sqlite3.exe to import files.
;Make sure sqlite3.exe is in the @ScriptDir.

;Use CSV mode for importing and exporting data.
Global $sSQLiteExeInput = ".mode csv" & @CRLF

;Table name must not contain spaces, use underscores. 
Global $sTableName = "Data"
$sTableName = StringReplace($sTableName, " ", "_")
$sSQLiteExeInput &= "CREATE TABLE " & $sTableName & "(Year,Month,Day,Time,Data);" & @CRLF

;Import files into SQLite table.
For $i = 1 To $aFileList[0]
    $sSQLiteExeInput &= ".import '" & $sDir & "\" & $aFileList[$i] & "' " & $sTableName & @CRLF 
Next

;Remove NULL rows, if any from import.
$sSQLiteExeInput &= 'DELETE FROM "' & $sTableName & '" WHERE "Data" IS NULL;' & @CRLF 

;Add DateTime Column, and data to the column to allow for easier searching.
$sSQLiteExeInput &= 'ALTER TABLE "' & $sTableName & '" ADD COLUMN "DateTime";' & @CRLF 
$sSQLiteExeInput &= 'UPDATE "' & $sTableName & '" SET "DateTime" = "Year" || ''-'' || "Month" || ''-'' || "Day" || '' '' || "Time";' & @CRLF 
$sSQLiteExeInput &= 'CREATE INDEX idxTS ON "' & $sTableName & '"(DateTime);' & @CRLF 

;Quit SQLite3.exe
$sSQLiteExeInput &= ".quit" & @CRLF

Global $sOutFileDB = StringTrimRight($sOutFile, 3) & "db"
Global $sSQLiteExeOutput
ConsoleWrite($sSQLiteExeInput & @CRLF) ;For testing.

;Execute sqlite3.exe commands.
_SQLite_SQLiteExe($sOutFileDB, $sSQLiteExeInput, $sSQLiteExeOutput)
If @error Then
    If @error = 2 Then
        ConsoleWrite("ERROR: Sqlite3.exe file not found" & @CRLF)
    Else
        ConsoleWrite("ERROR: @error=" & @error & " when calling _SQLite_SQLiteExe" & @CRLF)
    EndIf

    Exit 1
EndIf

;Get data requested.
;Make sure sqlite3 DLL is in the @ScriptDir.
Global $iRows, $iColumns, $aOutFile
ConsoleWrite(_SQLite_Startup() & @CRLF) 
ConsoleWrite("_SQLite_LibVersion=" & _SQLite_LibVersion() & @CRLF)
_SQLite_Open($sOutFileDB)

Global $sSearchDateTime = "2018-2-1 1:00"

;Sum data values for date.
Global $sSQLQuery = 'SELECT Year,Month,Day,Time,sum(Data) FROM "' & $sTableName & '" WHERE "DateTime" = ''' & $sSearchDateTime & ''''
ConsoleWrite($sSQLQuery & @CRLF) ;For testing.

_SQLite_GetTable2d(-1, $sSQLQuery, $aOutFile, $iRows, $iColumns)
ConsoleWrite(@error & @CRLF)
_ArrayDisplay($aOutFile, "Sum Data") ;For testing.

MsgBox($MB_OK, "Sum Data", "Date: " & $aOutFile[1][1] & "-" & $aOutFile[1][2] & "-" & $aOutFile[1][0] & " " & $aOutFile[1][3] & @CRLF & "Value: " & $aOutFile[1][4])

;Get max value for date.
$sSQLQuery = 'SELECT Year,Month,Day,Time,max(Data) FROM "' & $sTableName & '" WHERE "DateTime" = ''' & $sSearchDateTime & ''''
ConsoleWrite($sSQLQuery & @CRLF) ;For testing.

_SQLite_GetTable2d(-1, $sSQLQuery, $aOutFile, $iRows, $iColumns)
ConsoleWrite(@error & @CRLF)
_ArrayDisplay($aOutFile, "Max Data for Date") ;For testing.

MsgBox($MB_OK, "Max Data for Date", "Date: " & $aOutFile[1][1] & "-" & $aOutFile[1][2] & "-" & $aOutFile[1][0] & " " & $aOutFile[1][3] & @CRLF & "Value: " & $aOutFile[1][4])

;Get max value for table.
$sSQLQuery = 'SELECT Year,Month,Day,Time,max(Data) FROM "' & $sTableName & '"'
ConsoleWrite($sSQLQuery & @CRLF) ;For testing.

_SQLite_GetTable2d(-1, $sSQLQuery, $aOutFile, $iRows, $iColumns)
ConsoleWrite(@error & @CRLF)
_ArrayDisplay($aOutFile, "Max Data for Table") ;For testing.

MsgBox($MB_OK, "Max Data for Table", "Date: " & $aOutFile[1][1] & "-" & $aOutFile[1][2] & "-" & $aOutFile[1][0] & " " & $aOutFile[1][3] & @CRLF & "Value: " & $aOutFile[1][4])

_SQLite_Close()
_SQLite_Shutdown()

;Delete DB file.
FileDelete($sOutFileDB)

 

Adam

 

Share this post


Link to post
Share on other sites
On 4/12/2018 at 11:04 PM, Subz said:

Maybe something like:

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

RefineData()

Func RefineData()
    Local $i, $i, $file, $csvArray, $FilePath = @ScriptDir & "\CSV"
    $aFileList = _FileListToArrayRec($FilePath, "*.csv", 1, 0, 0, 2) ;Create and array of all .csv files within folder
    Local $aFileData, $sFileData = "2000/1/1 12:00", $iFileData, $aFullData[0][6], $aSummary[0][2]
    ;=====Loop through the .csv files within the folder======
    For $i = 1 To $aFileList[0]
        ;=====Create array based on csv file=====
        _FileReadToArray($aFileList[$i], $aFileData, 0, ",")
        For $j = 0 To UBound($aFileData) - 1
            _ArrayAdd($aFullData, $aFileData[$j][0] & "/" & $aFileData[$j][1] & "/" & $aFileData[$j][2] & " " & $aFileData[$j][3] & "|" & $aFileData[$j][0] & "|" & $aFileData[$j][1] & "|" & $aFileData[$j][2] & "|" & $aFileData[$j][3] & "|" & $aFileData[$j][4])
        Next
        _ArraySort($aFullData)
    Next
    For $i = 0 To UBound($aFullData) - 1
        $iFileData = _ArraySearch($aSummary, $aFullData[$i][0], 0, 0, 0, 0, 1, 0)
        If @error Then
            _ArrayAdd($aSummary, $aFullData[$i][0] & "|" & $aFullData[$i][5])
        Else
            $aSummary[$iFileData][1] += $aFullData[$i][5]
        EndIf
    Next
    _ArraySort($aSummary, 1, 0, 0, 1)
    _ArrayDisplay($aSummary)
    MsgBox(0,"", "Date: " & $aSummary[0][0] & @CRLF & "Value: " & $aSummary[0][1])
EndFunc

 

Thanks Subz, I found this works great for small amounts of data but takes forever when doing full scale test of 8000ish values per file. 

The first _arrayadd line was taking over 5 mins for 1 file, before I stopped the script. I tried manipulating the code, to overwrite the first column instead of using the addarray; which dramatically speed up the first stage. But don't really understand what was happening with the summary portion and _arraysearch and ended up breaking this or not getting it to work

I'm going to fiddle with Adam's solution to see if I can understand whats happening and if it runs any quicker

Share this post


Link to post
Share on other sites
On 4/13/2018 at 12:25 PM, AdamUL said:

When I have to work with combining a lot of CSV files and searching for data, I use SQLite.  Its fast, and allows searching with SQL.  Here's an example that should help you.  

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

Global $sOutFile = "test.csv"
Global $sDir = @ScriptDir & "\Test"

Global $aFileList = _FileListToArrayRec($sDir, "*.csv", $FLTAR_FILES,  $FLTAR_RECUR)
If @error Then Exit 1
;~ _ArrayDisplay($aFileList) ;For testing.

;Use sqlite3.exe to import files.
;Make sure sqlite3.exe is in the @ScriptDir.

;Use CSV mode for importing and exporting data.
Global $sSQLiteExeInput = ".mode csv" & @CRLF

;Table name must not contain spaces, use underscores. 
Global $sTableName = "Data"
$sTableName = StringReplace($sTableName, " ", "_")
$sSQLiteExeInput &= "CREATE TABLE " & $sTableName & "(Year,Month,Day,Time,Data);" & @CRLF

;Import files into SQLite table.
For $i = 1 To $aFileList[0]
    $sSQLiteExeInput &= ".import '" & $sDir & "\" & $aFileList[$i] & "' " & $sTableName & @CRLF 
Next

;Remove NULL rows, if any from import.
$sSQLiteExeInput &= 'DELETE FROM "' & $sTableName & '" WHERE "Data" IS NULL;' & @CRLF 

;Add DateTime Column, and data to the column to allow for easier searching.
$sSQLiteExeInput &= 'ALTER TABLE "' & $sTableName & '" ADD COLUMN "DateTime";' & @CRLF 
$sSQLiteExeInput &= 'UPDATE "' & $sTableName & '" SET "DateTime" = "Year" || ''-'' || "Month" || ''-'' || "Day" || '' '' || "Time";' & @CRLF 
$sSQLiteExeInput &= 'CREATE INDEX idxTS ON "' & $sTableName & '"(DateTime);' & @CRLF 

;Quit SQLite3.exe
$sSQLiteExeInput &= ".quit" & @CRLF

Global $sOutFileDB = StringTrimRight($sOutFile, 3) & "db"
Global $sSQLiteExeOutput
ConsoleWrite($sSQLiteExeInput & @CRLF) ;For testing.

;Execute sqlite3.exe commands.
_SQLite_SQLiteExe($sOutFileDB, $sSQLiteExeInput, $sSQLiteExeOutput)
If @error Then
    If @error = 2 Then
        ConsoleWrite("ERROR: Sqlite3.exe file not found" & @CRLF)
    Else
        ConsoleWrite("ERROR: @error=" & @error & " when calling _SQLite_SQLiteExe" & @CRLF)
    EndIf

    Exit 1
EndIf

;Get data requested.
;Make sure sqlite3 DLL is in the @ScriptDir.
Global $iRows, $iColumns, $aOutFile
ConsoleWrite(_SQLite_Startup() & @CRLF) 
ConsoleWrite("_SQLite_LibVersion=" & _SQLite_LibVersion() & @CRLF)
_SQLite_Open($sOutFileDB)

Global $sSearchDateTime = "2018-2-1 1:00"

;Sum data values for date.
Global $sSQLQuery = 'SELECT Year,Month,Day,Time,sum(Data) FROM "' & $sTableName & '" WHERE "DateTime" = ''' & $sSearchDateTime & ''''
ConsoleWrite($sSQLQuery & @CRLF) ;For testing.

_SQLite_GetTable2d(-1, $sSQLQuery, $aOutFile, $iRows, $iColumns)
ConsoleWrite(@error & @CRLF)
_ArrayDisplay($aOutFile, "Sum Data") ;For testing.

MsgBox($MB_OK, "Sum Data", "Date: " & $aOutFile[1][1] & "-" & $aOutFile[1][2] & "-" & $aOutFile[1][0] & " " & $aOutFile[1][3] & @CRLF & "Value: " & $aOutFile[1][4])

;Get max value for date.
$sSQLQuery = 'SELECT Year,Month,Day,Time,max(Data) FROM "' & $sTableName & '" WHERE "DateTime" = ''' & $sSearchDateTime & ''''
ConsoleWrite($sSQLQuery & @CRLF) ;For testing.

_SQLite_GetTable2d(-1, $sSQLQuery, $aOutFile, $iRows, $iColumns)
ConsoleWrite(@error & @CRLF)
_ArrayDisplay($aOutFile, "Max Data for Date") ;For testing.

MsgBox($MB_OK, "Max Data for Date", "Date: " & $aOutFile[1][1] & "-" & $aOutFile[1][2] & "-" & $aOutFile[1][0] & " " & $aOutFile[1][3] & @CRLF & "Value: " & $aOutFile[1][4])

;Get max value for table.
$sSQLQuery = 'SELECT Year,Month,Day,Time,max(Data) FROM "' & $sTableName & '"'
ConsoleWrite($sSQLQuery & @CRLF) ;For testing.

_SQLite_GetTable2d(-1, $sSQLQuery, $aOutFile, $iRows, $iColumns)
ConsoleWrite(@error & @CRLF)
_ArrayDisplay($aOutFile, "Max Data for Table") ;For testing.

MsgBox($MB_OK, "Max Data for Table", "Date: " & $aOutFile[1][1] & "-" & $aOutFile[1][2] & "-" & $aOutFile[1][0] & " " & $aOutFile[1][3] & @CRLF & "Value: " & $aOutFile[1][4])

_SQLite_Close()
_SQLite_Shutdown()

;Delete DB file.
FileDelete($sOutFileDB)

 

Adam

 

Thanks Adam, I have never used SQLite before. Looks like this is an additional piece of software required? 

If I was to want to make an .exe file this wouldn't work for others unless they download SQLite too hey?

Share this post


Link to post
Share on other sites

Can you try the following:

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

Local $sTempName = "FileName.csv"
Local $aFileList = _FileListToArray(@ScriptDir & "\CSV", "*.csv", 1, True)
    If @error Then Exit
Local $aArray[0][6], $aFileName
For $i = 1 To $aFileList[0]
     _FileReadToArray($aFileList[$i], $aFileName, 0, ",")
    _ArrayAdd($aArray, $aFileName)
Next
Local $hFileOpen = FileOpen($sTempName, 10)
_FileWriteFromArray($hFileOpen, $aArray, Default, Default, ",")
FileClose($hFileOpen)

Local $oExcel = _Excel_Open()
Local $oWorkbook = _Excel_BookOpen($oExcel, @TempDir & "\" & $sTempName)
Local $oRange = $oWorkbook.ActiveSheet.Usedrange
Local $iLastRow = $oWorkbook.ActiveSheet.Range("A1").SpecialCells($xlCellTypeLastCell).Row
_Excel_RangeInsert($oWorkbook.ActiveSheet, "E:E", $xlShiftToRight)
_Excel_RangeWrite($oWorkbook, Default, '=TEXT(DATE(A1,B1,C1), "yyyy-m-d ")&TEXT(D1,"h:mm:ss")', "E1:E" & $iLastRow, False)
$oWorkbook.ActiveSheet.Range("I1").Select
With $oExcel.Application.Selection
    .Consolidate("'" & @TempDir & "\[" & $sTempName & "]FileName'!C5:C6", -4157, False, True, False)
EndWith
_Excel_RangeSort($oWorkbook, Default, "$A:$F", "E:E", Default, Default, Default, False, Default, "F:F", Default)
_Excel_RangeSort($oWorkbook, Default, "$I:$J", "J:J", Default, Default, Default, False, Default, "I:I", Default)

 

Share this post


Link to post
Share on other sites
21 minutes ago, Subz said:

Can you try the following:

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

Local $sTempName = "FileName.csv"
Local $aFileList = _FileListToArray(@ScriptDir & "\CSV", "*.csv", 1, True)
    If @error Then Exit
Local $aArray[0][6], $aFileName
For $i = 1 To $aFileList[0]
     _FileReadToArray($aFileList[$i], $aFileName, 0, ",")
    _ArrayAdd($aArray, $aFileName)
Next
Local $hFileOpen = FileOpen($sTempName, 10)
_FileWriteFromArray($hFileOpen, $aArray, Default, Default, ",")
FileClose($hFileOpen)

Local $oExcel = _Excel_Open()
Local $oWorkbook = _Excel_BookOpen($oExcel, @TempDir & "\" & $sTempName)
Local $oRange = $oWorkbook.ActiveSheet.Usedrange
Local $iLastRow = $oWorkbook.ActiveSheet.Range("A1").SpecialCells($xlCellTypeLastCell).Row
_Excel_RangeInsert($oWorkbook.ActiveSheet, "E:E", $xlShiftToRight)
_Excel_RangeWrite($oWorkbook, Default, '=TEXT(DATE(A1,B1,C1), "yyyy-m-d ")&TEXT(D1,"h:mm:ss")', "E1:E" & $iLastRow, False)
$oWorkbook.ActiveSheet.Range("I1").Select
With $oExcel.Application.Selection
    .Consolidate("'" & @TempDir & "\[" & $sTempName & "]FileName'!C5:C6", -4157, False, True, False)
EndWith
_Excel_RangeSort($oWorkbook, Default, "$A:$F", "E:E", Default, Default, Default, False, Default, "F:F", Default)
_Excel_RangeSort($oWorkbook, Default, "$I:$J", "J:J", Default, Default, Default, False, Default, "I:I", Default)

 

Thanks man! I'll give that a try tomorrow.

You gave me some really good ideas with the first code you sent me before, and been playing around with how to refine it. I now think I've got it working really quick (less 30 seconds for 4 files). I've have to put some extra exception handlers in, try some larger scale tests, and test robustness but I think I've got it now. *fingers crossed*

The key idea is getting 1 column in the format of yyyy/mm/dd HH, to minimize the amount of comparisons needed

There was a bit of a problem when times go above 9 because of code reading 3:00 greater than 10:00, but there is a work around I discovered. If use StringRight($string, 5) then it will treat this example like 03:00 compared to 10:00; which program correctly identifies as 03:00 < 10:00.

Thanks for the help!

 

Share this post


Link to post
Share on other sites

You could FileInstall sqlite3.dll and sqlite3.exe files when you compile the exe, and extract them to the @TempDir directory for use.  The files can be downloaded from here. You can just download sqlite3.exe and sqlite3.dll directly.  Here is the updated example script using FileInstall and using the @TempDir.  

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

;FileInstall needed to include SQLite files and place them in the @TempDir.
Global $sWorkingDir = @TempDir & "\"
FileInstall("sqlite3.exe", $sWorkingDir, $FC_OVERWRITE) ;Put sqlite3.exe in @ScriptDir.
FileInstall("sqlite3.dll", $sWorkingDir, $FC_OVERWRITE) ;Put sqlite3.dll in @ScriptDir.
FileChangeDir($sWorkingDir) ;Change @WorkingDir to @TempDir to work with files.

Global $sOutFile = "test.csv"
Global $sDir = @ScriptDir & "\Test"

Global $aFileList = _FileListToArrayRec($sDir, "*.csv", $FLTAR_FILES,  $FLTAR_RECUR)
If @error Then Exit 1
;~ _ArrayDisplay($aFileList) ;For testing.

;Use sqlite3.exe to import files.
;Make sure sqlite3.exe is in the @ScriptDir.

;Use CSV mode for importing and exporting data.
Global $sSQLiteExeInput = ".mode csv" & @CRLF

;Table name must not contain spaces, use underscores. 
Global $sTableName = "Data"
$sTableName = StringReplace($sTableName, " ", "_")
$sSQLiteExeInput &= "CREATE TABLE " & $sTableName & "(Year,Month,Day,Time,Data);" & @CRLF

;Import files into SQLite table.
For $i = 1 To $aFileList[0]
    $sSQLiteExeInput &= ".import '" & $sDir & "\" & $aFileList[$i] & "' " & $sTableName & @CRLF 
Next

;Remove NULL rows, if any from import.
$sSQLiteExeInput &= 'DELETE FROM "' & $sTableName & '" WHERE "Data" IS NULL;' & @CRLF 

;Add DateTime Column, and data to the column to allow for easier searching.
$sSQLiteExeInput &= 'ALTER TABLE "' & $sTableName & '" ADD COLUMN "DateTime";' & @CRLF 
$sSQLiteExeInput &= 'UPDATE "' & $sTableName & '" SET "DateTime" = "Year" || ''-'' || "Month" || ''-'' || "Day" || '' '' || "Time";' & @CRLF 
$sSQLiteExeInput &= 'CREATE INDEX idxTS ON "' & $sTableName & '"(DateTime);' & @CRLF 

;Quit SQLite3.exe
$sSQLiteExeInput &= ".quit" & @CRLF

Global $sOutFileDB = StringTrimRight($sOutFile, 3) & "db"
Global $sSQLiteExeOutput
ConsoleWrite($sSQLiteExeInput & @CRLF) ;For testing.

;Execute sqlite3.exe commands.
_SQLite_SQLiteExe($sOutFileDB, $sSQLiteExeInput, $sSQLiteExeOutput)
If @error Then
    If @error = 2 Then
        ConsoleWrite("ERROR: Sqlite3.exe file not found" & @CRLF)
    Else
        ConsoleWrite("ERROR: @error=" & @error & " when calling _SQLite_SQLiteExe" & @CRLF)
    EndIf

    Exit 1
EndIf

;Get data requested.
;Make sure sqlite3 DLL is in the @ScriptDir.
Global $iRows, $iColumns, $aOutFile
ConsoleWrite(_SQLite_Startup() & @CRLF) 
ConsoleWrite("_SQLite_LibVersion=" & _SQLite_LibVersion() & @CRLF)
_SQLite_Open($sOutFileDB)

Global $sSearchDateTime = "2018-2-1 1:00"

;Sum data values for date.
Global $sSQLQuery = 'SELECT Year,Month,Day,Time,sum(Data) FROM "' & $sTableName & '" WHERE "DateTime" = ''' & $sSearchDateTime & ''''
ConsoleWrite($sSQLQuery & @CRLF) ;For testing.

_SQLite_GetTable2d(-1, $sSQLQuery, $aOutFile, $iRows, $iColumns)
ConsoleWrite(@error & @CRLF)
_ArrayDisplay($aOutFile, "Sum Data") ;For testing.

MsgBox($MB_OK, "Sum Data", "Date: " & $aOutFile[1][1] & "-" & $aOutFile[1][2] & "-" & $aOutFile[1][0] & " " & $aOutFile[1][3] & @CRLF & "Value: " & $aOutFile[1][4])

;Get max value for date.
$sSQLQuery = 'SELECT Year,Month,Day,Time,max(Data) FROM "' & $sTableName & '" WHERE "DateTime" = ''' & $sSearchDateTime & ''''
ConsoleWrite($sSQLQuery & @CRLF) ;For testing.

_SQLite_GetTable2d(-1, $sSQLQuery, $aOutFile, $iRows, $iColumns)
ConsoleWrite(@error & @CRLF)
_ArrayDisplay($aOutFile, "Max Data for Date") ;For testing.

MsgBox($MB_OK, "Max Data for Date", "Date: " & $aOutFile[1][1] & "-" & $aOutFile[1][2] & "-" & $aOutFile[1][0] & " " & $aOutFile[1][3] & @CRLF & "Value: " & $aOutFile[1][4])

;Get max value for table.
$sSQLQuery = 'SELECT Year,Month,Day,Time,max(Data) FROM "' & $sTableName & '"'
ConsoleWrite($sSQLQuery & @CRLF) ;For testing.

_SQLite_GetTable2d(-1, $sSQLQuery, $aOutFile, $iRows, $iColumns)
ConsoleWrite(@error & @CRLF)
_ArrayDisplay($aOutFile, "Max Data for Table") ;For testing.

MsgBox($MB_OK, "Max Data for Table", "Date: " & $aOutFile[1][1] & "-" & $aOutFile[1][2] & "-" & $aOutFile[1][0] & " " & $aOutFile[1][3] & @CRLF & "Value: " & $aOutFile[1][4])

_SQLite_Close()
_SQLite_Shutdown()

;Delete DB file.
FileDelete($sOutFileDB)

;Delete SQLite files.
FileDelete("sqlite3.exe")
FileDelete("sqlite3.dll")

 

Adam

 

Share this post


Link to post
Share on other sites
2 hours ago, AdamUL said:

You could FileInstall sqlite3.dll and sqlite3.exe files when you compile the exe, and extract them to the @TempDir directory for use.  The files can be downloaded from here. You can just download sqlite3.exe and sqlite3.dll directly.  Here is the updated example script using FileInstall and using the @TempDir.  

Adam

 

Thanks! I pretty much have everything working well now without, but I might use this in the future. Thanks for the example code, appreciate you taking the time to teach

Share this post


Link to post
Share on other sites

Your welcome.  Glad I could help.  For working with a large number of CSV files, SQLite can help you a lot.  Your not limited by AutoIt's array size limit, and you can get arrays back from queries to work with.  

 

Adam

Share this post


Link to post
Share on other sites
3 hours ago, AdamUL said:

Your welcome.  Glad I could help.  For working with a large number of CSV files, SQLite can help you a lot.  Your not limited by AutoIt's array size limit, and you can get arrays back from queries to work with.  

 

Adam

Good to know.

I learned that with these CSV files of approximately 7000-9000 rows that when using AutoIt's array functions if I tried an algorithm that required a lot of adding or deleting rows from the 2D array this killed the processing time, going from several seconds to having to terminate script after several minutes of still not completing. Do you know if SQLite would better manage this sort of task?

The way I got around this is just by either:

i) Overwrite data instead of inserting a new columns/rows

ii) Create a new array of desired size and pass values into it

Using these methods kept processing time back into seconds. 

Share this post


Link to post
Share on other sites

Yes, SQLite would work faster with large arrays.  Resizing arrays in AutoIt is what killed your speed.  I try to only resize an array once, maybe twice in a script, usually near the end.  

@Chimp wrote a nice UDF for working with arrays using SQLite.  

 

Adam

 

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

  • Similar Content

    • By nooneclose
      I was running my script and everything was working fine and then it just stopped working. 
      I got this error message:
      !>12:51:55 AutoIt3.exe ended.rc:-1073741819 +>12:51:55 AutoIt3Wrapper Finished. >Exit code: 3221225477 Time: 354.6 I looked that error up and this article (https://www.autoitscript.com/trac/autoit/ticket/2541) said it was fixed a long time ago. 
      I am using version: SciTE Version 4.1.0, how am I getting this error? (first time I got it after running my code 50+ times)
      it crashed while trying to loop through emails. 
      Full output log:
      >"C:\Program Files (x86)\AutoIt3\SciTE\..\AutoIt3.exe" /run /prod /ErrorStdOut /in "C:\" /UserParams +>12:46:01 Starting AutoIt3Wrapper v.18.708.1148.0 SciTE v.4.1.0.0 Keyboard:00000409 OS:WIN_10/ CPU:X64 OS:X64 Environment(Language:0409) CodePage:0 utf8.auto.check:4 +> SciTEDir => C:\Program Files (x86)\AutoIt3\SciTE >Running AU3Check (3.3.14.5) from:C:\Program Files (x86)\AutoIt3 +>12:46:01 AU3Check ended.rc:0 >Running:(3.3.14.5): --> Press Ctrl+Alt+Break to Restart or Ctrl+Break to Stop Action 1: Create a connection to the outlook account at line: 192 Action 1: Finished at line: 205 Action 2: Scan for invaild E-mails at line: 215 Number of SKIPPED unread E-mails: at line: 226 Current email has been changed from Unread to Read. at line: 238 Current email has been changed from Unread to Read. at line: 238 Number of SKIPPED E-mails: 0 at line: 246 Action 2: Finished at line: 248 Action 3: Store vaild E-mails at line: 258 Number of unread E-mails: 17 at line: 290 Action 3: Finished at line: 292 Current number of processed Emails is: 0 at line: 2735 Action 2: Scan for invaild E-mails at line: 215 Number of SKIPPED unread E-mails: at line: 226 Number of SKIPPED E-mails: 0 at line: 246 Action 2: Finished at line: 248 Action 4: Loop through E-mails at line: 302 Action 4: Finished at line: 361 Action 5: Store E-mail's contents at line: 371 Action 5: Finished at line: 495 Action 6: Test to Skip based on location at line: 508 Action 6: Finished at line: 710 Action 7: Check if location needs to be a suite at line: 720 The 'Bathroom' is not mentioned in the Description. Suite not needed. at line: 2180 Action 7: Finished at line: 2186 Opening a new Chrome window. at line: 2747 Action 8: Open Web Browser at line: 2240 _WDStartup: "MicrosoftWebDriver.exe" --verbose Action 8: Finished at line: 2270 Action 9: Navigate to Work Order Tracking at line: 2281 Action 9: Finished at line: 2295 Action 10: Click on Create New Work Order at line: 2306 Action 10: Finished Action 11: Send E-mail Data to it's proper field at line: 2324 Sub-Action 3: Split Long Description at line: 2522 Sub-Action 3: Finished at line: 2576 Work Order Number: 660308 Action 11: Finished at line: 2511 Change status was called at line: 2758 Sub-Action 2: Change E-mail to Read at line: 2223 Current email has been changed from Unread to Read. at line: 2226 Sub-Action 2: Finished at line: 2230 Action 16: Create New Work Order at line: 2705 Action 16: Finished at line: 2719 Current number of processed Emails is: 1 at line: 2735 Action 2: Scan for invaild E-mails at line: 215 Number of SKIPPED unread E-mails: at line: 226 Number of SKIPPED E-mails: 0 at line: 246 Action 2: Finished at line: 248 Action 4: Loop through E-mails at line: 302 Action 4: Finished at line: 361 Action 5: Store E-mail's contents at line: 371 Action 5: Finished at line: 495 Action 6: Test to Skip based on location at line: 508 Action 6: Finished at line: 710 Action 7: Check if location needs to be a suite at line: 720 The 'Bathroom' is not mentioned in the Description. Suite not needed. at line: 2180 Action 7: Finished at line: 2186 Action 11: Send E-mail Data to it's proper field at line: 2324 Work Order Number: 660309 Action 11: Finished at line: 2511 Change status was called at line: 2758 Sub-Action 2: Change E-mail to Read at line: 2223 Current email has been changed from Unread to Read. at line: 2226 Sub-Action 2: Finished at line: 2230 Action 16: Create New Work Order at line: 2705 Action 16: Finished at line: 2719 Current number of processed Emails is: 2 at line: 2735 Action 2: Scan for invaild E-mails at line: 215 Number of SKIPPED unread E-mails: at line: 226 Number of SKIPPED E-mails: 0 at line: 246 Action 2: Finished at line: 248 Action 4: Loop through E-mails at line: 302 Action 4: Finished at line: 361 Action 5: Store E-mail's contents at line: 371 Action 5: Finished at line: 495 Action 6: Test to Skip based on location at line: 508 Action 6: Finished at line: 710 Action 7: Check if location needs to be a suite at line: 720 The 'Bathroom' is not mentioned in the Description. Suite not needed. at line: 2180 Action 7: Finished at line: 2186 Action 11: Send E-mail Data to it's proper field at line: 2324 Sub-Action 3: Split Long Description at line: 2522 Sub-Action 3: Finished at line: 2576 Work Order Number: 660310 Action 11: Finished at line: 2511 Change status was called at line: 2758 Sub-Action 2: Change E-mail to Read at line: 2223 Current email has been changed from Unread to Read. at line: 2226 Sub-Action 2: Finished at line: 2230 Action 16: Create New Work Order at line: 2705 Action 16: Finished at line: 2719 Current number of processed Emails is: 3 at line: 2735 Action 2: Scan for invaild E-mails at line: 215 Number of SKIPPED unread E-mails: at line: 226 Number of SKIPPED E-mails: 0 at line: 246 Action 2: Finished at line: 248 Action 4: Loop through E-mails at line: 302 Action 4: Finished at line: 361 Action 5: Store E-mail's contents at line: 371 Action 5: Finished at line: 495 Action 6: Test to Skip based on location at line: 508 Action 6: Finished at line: 710 Action 7: Check if location needs to be a suite at line: 720 The 'Bathroom' is not mentioned in the Description. Suite not needed. at line: 2180 Action 7: Finished at line: 2186 Action 11: Send E-mail Data to it's proper field at line: 2324 Work Order Number: 660311 Action 11: Finished at line: 2511 Change status was called at line: 2758 Sub-Action 2: Change E-mail to Read at line: 2223 Current email has been changed from Unread to Read. at line: 2226 Sub-Action 2: Finished at line: 2230 Action 16: Create New Work Order at line: 2705 Action 16: Finished at line: 2719 Current number of processed Emails is: 4 at line: 2735 Action 2: Scan for invaild E-mails at line: 215 Number of SKIPPED unread E-mails: at line: 226 Number of SKIPPED E-mails: 0 at line: 246 Action 2: Finished at line: 248 Action 4: Loop through E-mails at line: 302 Action 4: Finished at line: 361 Action 5: Store E-mail's contents at line: 371 Action 5: Finished at line: 495 Action 6: Test to Skip based on location at line: 508 Action 6: Finished at line: 710 Action 7: Check if location needs to be a suite at line: 720 The 'Bathroom' is not mentioned in the Description. Suite not needed. at line: 2180 Action 7: Finished at line: 2186 Action 11: Send E-mail Data to it's proper field at line: 2324 Work Order Number: 660312 Action 11: Finished at line: 2511 Change status was called at line: 2758 Sub-Action 2: Change E-mail to Read at line: 2223 Current email has been changed from Unread to Read. at line: 2226 Sub-Action 2: Finished at line: 2230 Action 16: Create New Work Order at line: 2705 Action 16: Finished at line: 2719 Current number of processed Emails is: 5 at line: 2735 Action 2: Scan for invaild E-mails at line: 215 Number of SKIPPED unread E-mails: at line: 226 Number of SKIPPED E-mails: 0 at line: 246 Action 2: Finished at line: 248 Action 4: Loop through E-mails at line: 302 Action 4: Finished at line: 361 Action 5: Store E-mail's contents at line: 371 Action 5: Finished at line: 495 Action 6: Test to Skip based on location at line: 508 Action 6: Finished at line: 710 Action 7: Check if location needs to be a suite at line: 720 The 'Bathroom' is not mentioned in the Description. Suite not needed. at line: 2180 Action 7: Finished at line: 2186 Action 11: Send E-mail Data to it's proper field at line: 2324 Work Order Number: 660313 Action 11: Finished at line: 2511 Change status was called at line: 2758 Sub-Action 2: Change E-mail to Read at line: 2223 Current email has been changed from Unread to Read. at line: 2226 Sub-Action 2: Finished at line: 2230 Action 16: Create New Work Order at line: 2705 Action 16: Finished at line: 2719 Current number of processed Emails is: 6 at line: 2735 Action 2: Scan for invaild E-mails at line: 215 Number of SKIPPED unread E-mails: at line: 226 Number of SKIPPED E-mails: 0 at line: 246 Action 2: Finished at line: 248 Action 4: Loop through E-mails at line: 302 Action 4: Finished at line: 361 Action 5: Store E-mail's contents at line: 371 Action 5: Finished at line: 495 Action 6: Test to Skip based on location at line: 508 Proper Building format used. Send Location: YT6508 Action 6: Finished at line: 710 Action 7: Check if location needs to be a suite at line: 720 The 'Bathroom' is not mentioned in the Long Description. Suite not needed. at line: 1475 Action 7: Finished at line: 2186 Action 11: Send E-mail Data to it's proper field at line: 2324 Work Order Number: 660314 Action 11: Finished at line: 2511 Change status was called at line: 2758 Sub-Action 2: Change E-mail to Read at line: 2223 Current email has been changed from Unread to Read. at line: 2226 Sub-Action 2: Finished at line: 2230 Action 16: Create New Work Order at line: 2705 Action 16: Finished at line: 2719 Current number of processed Emails is: 7 at line: 2735 Action 2: Scan for invaild E-mails at line: 215 Number of SKIPPED unread E-mails: at line: 226 Number of SKIPPED E-mails: 0 at line: 246 Action 2: Finished at line: 248 Action 4: Loop through E-mails at line: 302 Action 4: Finished at line: 361 Action 5: Store E-mail's contents at line: 371 Action 5: Finished at line: 495 Action 6: Test to Skip based on location at line: 508 Proper Building format used. Send Location: CN2116 Action 6: Finished at line: 710 Action 7: Check if location needs to be a suite at line: 720 The 'Bathroom' is not mentioned in the Long Description. Suite not needed. at line: 1475 Action 7: Finished at line: 2186 Action 11: Send E-mail Data to it's proper field at line: 2324 Sub-Action 3: Split Long Description at line: 2522 Sub-Action 3: Finished at line: 2576 Work Order Number: 660315 Action 11: Finished at line: 2511 Change status was called at line: 2758 Sub-Action 2: Change E-mail to Read at line: 2223 Current email has been changed from Unread to Read. at line: 2226 Sub-Action 2: Finished at line: 2230 Action 16: Create New Work Order at line: 2705 Action 16: Finished at line: 2719 Current number of processed Emails is: 8 at line: 2735 Action 2: Scan for invaild E-mails at line: 215 Number of SKIPPED unread E-mails: at line: 226 Number of SKIPPED E-mails: 0 at line: 246 Action 2: Finished at line: 248 Action 4: Loop through E-mails at line: 302 Action 4: Finished at line: 361 Action 5: Store E-mail's contents at line: 371 Action 5: Finished at line: 495 Action 6: Test to Skip based on location at line: 508 Proper Building format used. Send Location: DT4905 Action 6: Finished at line: 710 Action 7: Check if location needs to be a suite at line: 720 Action 7: Finished at line: 2186 Action 11: Send E-mail Data to it's proper field at line: 2324 Work Order Number: 660316 Action 11: Finished at line: 2511 Change status was called at line: 2758 Sub-Action 2: Change E-mail to Read at line: 2223 Current email has been changed from Unread to Read. at line: 2226 Sub-Action 2: Finished at line: 2230 Action 16: Create New Work Order at line: 2705 Action 16: Finished at line: 2719 Current number of processed Emails is: 9 at line: 2735 Action 2: Scan for invaild E-mails at line: 215 Number of SKIPPED unread E-mails: at line: 226 Number of SKIPPED E-mails: 0 at line: 246 Action 2: Finished at line: 248 Action 4: Loop through E-mails at line: 302 Action 4: Finished at line: 361 Action 5: Store E-mail's contents at line: 371 Action 5: Finished at line: 495 Action 6: Test to Skip based on location at line: 508 Proper Building format used. Send Location: YT6513 Action 6: Finished at line: 710 Action 7: Check if location needs to be a suite at line: 720 Young Tower has no suites! Action 7: Finished at line: 2186 Action 11: Send E-mail Data to it's proper field at line: 2324 Sub-Action 3: Split Long Description at line: 2522 Sub-Action 3: Finished at line: 2576 Work Order Number: 660317 Action 11: Finished at line: 2511 Change status was called at line: 2758 Sub-Action 2: Change E-mail to Read at line: 2223 Current email has been changed from Unread to Read. at line: 2226 Sub-Action 2: Finished at line: 2230 Action 16: Create New Work Order at line: 2705 Action 16: Finished at line: 2719 Current number of processed Emails is: 10 at line: 2735 Action 2: Scan for invaild E-mails at line: 215 Number of SKIPPED unread E-mails: at line: 226 Number of SKIPPED E-mails: 0 at line: 246 Action 2: Finished at line: 248 Action 4: Loop through E-mails at line: 302 Action 4: Finished at line: 361 Action 5: Store E-mail's contents at line: 371 Action 5: Finished at line: 495 Action 6: Test to Skip based on location at line: 508 Proper Building format used. Send Location: BT3310 Action 6: Finished at line: 710 Action 7: Check if location needs to be a suite at line: 720 The 'Bathroom' is not mentioned in the Long Description. Suite not needed. at line: 1475 Action 7: Finished at line: 2186 Action 11: Send E-mail Data to it's proper field at line: 2324 Sub-Action 3: Split Long Description at line: 2522 Sub-Action 3: Finished at line: 2576 Work Order Number: 660318 Action 11: Finished at line: 2511 Change status was called at line: 2758 Sub-Action 2: Change E-mail to Read at line: 2223 Current email has been changed from Unread to Read. at line: 2226 Sub-Action 2: Finished at line: 2230 Action 16: Create New Work Order at line: 2705 Action 16: Finished at line: 2719 Current number of processed Emails is: 11 at line: 2735 Action 2: Scan for invaild E-mails at line: 215 Number of SKIPPED unread E-mails: at line: 226 Number of SKIPPED E-mails: 0 at line: 246 Action 2: Finished at line: 248 Action 4: Loop through E-mails at line: 302 Action 4: Finished at line: 361 Action 5: Store E-mail's contents at line: 371 Action 5: Finished at line: 495 Action 6: Test to Skip based on location at line: 508 Proper Building format used. Send Location: BT3411 Action 6: Finished at line: 710 Action 7: Check if location needs to be a suite at line: 720 The 'Bathroom' is not mentioned in the Description. Suite not needed. at line: 2180 Action 7: Finished at line: 2186 Action 11: Send E-mail Data to it's proper field at line: 2324 Sub-Action 3: Split Long Description at line: 2522 Sub-Action 3: Finished at line: 2576 Work Order Number: 660319 Action 11: Finished at line: 2511 Change status was called at line: 2758 Sub-Action 2: Change E-mail to Read at line: 2223 Current email has been changed from Unread to Read. at line: 2226 Sub-Action 2: Finished at line: 2230 Action 16: Create New Work Order at line: 2705 Action 16: Finished at line: 2719 Current number of processed Emails is: 12 at line: 2735 Action 2: Scan for invaild E-mails at line: 215 Number of SKIPPED unread E-mails: at line: 226 Number of SKIPPED E-mails: 0 at line: 246 Action 2: Finished at line: 248 Action 4: Loop through E-mails at line: 302 Action 4: Finished at line: 361 Action 5: Store E-mail's contents at line: 371 Action 5: Finished at line: 495 Action 6: Test to Skip based on location at line: 508 Proper Building format used. Send Location: DT4519 Action 6: Finished at line: 710 Action 7: Check if location needs to be a suite at line: 720 The 'Bathroom' is not mentioned in the Description. Suite not needed. at line: 2180 Action 7: Finished at line: 2186 Action 11: Send E-mail Data to it's proper field at line: 2324 Sub-Action 3: Split Long Description at line: 2522 Sub-Action 3: Finished at line: 2576 Work Order Number: 660320 Action 11: Finished at line: 2511 Change status was called at line: 2758 Sub-Action 2: Change E-mail to Read at line: 2223 Current email has been changed from Unread to Read. at line: 2226 Sub-Action 2: Finished at line: 2230 Action 16: Create New Work Order at line: 2705 Action 16: Finished at line: 2719 Current number of processed Emails is: 13 at line: 2735 Action 2: Scan for invaild E-mails at line: 215 Number of SKIPPED unread E-mails: at line: 226 Number of SKIPPED E-mails: 0 at line: 246 Action 2: Finished at line: 248 Action 4: Loop through E-mails at line: 302 !>12:51:55 AutoIt3.exe ended.rc:-1073741819 +>12:51:55 AutoIt3Wrapper Finished. >Exit code: 3221225477 Time: 354.6  
    • By VinMe
      I am unable to execute the below script, my requirement is to copy the content from active excel sheet and to display the same.
      Please let me know where i am missing!
      #include <Excel.au3>
      #include <MsgBoxConstants.au3>
      #include <Array.au3>
      #include <StringConstants.au3>
      Local $oExcel = _Excel_Open()
      $LastRow2 = $oExcel.UsedRange.Rows.Count
      $Tissue = _Excel_RangeRead($oExcel, Default, "E1:E" & $LastRow2)
      $TshNr = _Excel_RangeRead($oExcel, Default, "F1:F" & $LastRow2)
      _ArrayDisplay($Tissue)
      _ArrayDisplay($TshNr)
    • By uncommon
      So I have been reading on how to use OAuth2 Service accounts(https://developers.google.com/identity/protocols/OAuth2ServiceAccount) and ran into an issue with autoit I can not solve. To keep this short to communicate to the Google API I need to use a JSON Web signature containing
      {Base64url encoded header}.{Base64url encoded claim set}.

      The Header and claim set work fine but I do not know how to convert them to a byte array using autoit. I have looked around on the forums and website but have not found anything that seems to work. Here is my code.
      #include-once #include <Array.au3> #include <Constants.au3> #include <Debug.au3> #include <File.au3> #include "Json.au3" #include <Process.au3> #include <ProgressConstants.au3> #include <String.au3> #include <WindowsConstants.au3> #include "WinHttp.au3" #include <UnixTime.au3> #include <StringConstants.au3> Global $UnixTime = _TimeGetStamp() $sJWTheader = '{"alg":"RS256","typ":"JWT"}' $sJWTclaimset = '{"iss":"Removed@forprivacy.com","scope":"https://www.googleapis.com/auth/spreadsheets","aud":"https://www.googleapis.com/oauth2/v4/token","exp":' & $UnixTime + 3600 & ',"iat":' & $UnixTime & '}' Local $taiData = DllStructCreate("BYTE[256]") Local $sText = _base64($sJWTheader)&'.'&_base64($sJWTclaimset) DllStructSetData($taiData, 1, StringToBinary($sText, 4)) Local $bvResult = DllStructGetData($taiData, 1) $sJWTSigature = $bvResult $sJWT = _base64($sJWTheader)&'.'&_base64($sJWTclaimset)&'.'&_base64($sJWTSigature) $POSTHeader = "Content-Type: application/x-www-form-urlencoded" $hOpen = _WinHttpOpen() $hConnect = _WinHttpConnect($hOpen, "https://www.googleapis.com/") $sRead = _WinHttpSimpleSSLRequest($hConnect, "POST", "oauth2/v4/token", Default, "grant_type=urn%3Aietf%3Aparams%3Aoauth%3Agrant-type%3Ajwt-bearer&assertion=" & $sJWT) _WinHttpCloseHandle($hConnect) _WinHttpCloseHandle($hOpen) ConsoleWrite($sRead & @LF) The whole bytes thing is beyond with what I know about auotit. I can't tell what I need to fix.
       
       
    • By JackER4565
      Hi, first of all thanks to all the guys who always help people in the forums, I wouldn't be able to do anything if wasn't for your help, even if I don't ask it myself.
       
      I've created this code to get some info on a monitoring network on my work. It relays on _IETableGetCollection and _IETableWriteToArray.
      It works well, but take around 3:25 minutes to get the info from 28 pages (some of them are large and take longer to load, but most of them are small and fast).
      My question is if you see a way to get the program to go faster...
       
      I've tried to make it easy for you to understand and edited somethings with sensitive info.
      (Some of the pages doesn't have the black divider with MIRA in the end, so I need to search if it is there or not.)
       
      #include <IE.au3> #include <array.au3> Local $oIE = _IECreate("about:blank", 0, 0) Local $paginas[28] = [89, 90, 91, 92, 93, 96, 105, 113, 119, 125, 126, 129, 131, 133, 135, 137, 139, 140, 141, 144, 145, 146, 148, 149, 150, 151, 158, 159] Local $Datos_array[0][2] Local $oTable Local $tabla Local $aux_x = 1 Local $ar = 1 Local $Numtables_datos = 0 MsgBox(0, "asd", "asd") For $pag = 0 To UBound($paginas) - 1 Step 1 _IENavigate($oIE, "<WEBSITE URL>" & $paginas[$pag]) ; <<< the pages to load are always the same except for the last digits. _ArrayAdd($Datos_array, $paginas[$pag] & "|" & "Entrante", 0, "|") ; <<<<<<<<<<<<<<<< adds the page number toarray [0, 0] ;############################################ START counts amount of tables with traffic $oTable = _IETableGetCollection($oIE) Local $iNumTables = @extended For $i = 3 To $iNumTables - 2 Step 1 $oTable = _IETableGetCollection($oIE, $i) $nomb_tabla2 = _IETableWriteToArray($oTable) ; <<<<<<<< TABLE TO ARRAY. $string2 = StringStripWS($nomb_tabla2[1][0], 8) If $string2 <> "MIRA" Then $Numtables_datos = $Numtables_datos + 1 Next $tabla_End = $iNumTables - $Numtables_datos ;############################################ FIN $tabla_Start = 4 $tabla_trafico = 2 For $for = 1 To $Numtables_datos Step 1 $oTable = _IETableGetCollection($oIE, $tabla_Start - 1) ; <<<<<<<<<<< NAME OF THE TABLE; row2 = mira $nomb_tabla = _IETableWriteToArray($oTable) ; <<<<<<<< TABLE TO ARRAY ;########################################### ADDS the traffic number into the row $string = StringStripWS($nomb_tabla[1][0], 8) If $string == "MIRA" Then ;si o si pasa por aca 1 vez _ArrayAdd($Datos_array, $nomb_tabla[0][0]) $nomb_aux = $nomb_tabla[0][0] $aux_x = 1 $tabla_trafico = $tabla_trafico + 2 Else ;esto deberia ser por row _ArrayAdd($Datos_array, $nomb_aux & " " & $aux_x) $aux_x = $aux_x + 1 $tabla_trafico = $tabla_trafico + 1 EndIf $oTable = _IETableGetCollection($oIE, $tabla_trafico) Local $aTableData = _IETableWriteToArray($oTable) $bps = _ArrayToString($aTableData, "|", 0, 0, @CRLF, 0, 0) $bps = StringRight($bps, 5) $bps = StringLeft($bps, 4) $trafico_actual = _ArrayToString($aTableData, "|", 0, 0, @CRLF, 2, 2) If $bps == "Gbps" Then $trafico_actual = $trafico_actual * 1000 If $bps == "Kbps" Then $trafico_actual = $trafico_actual / 1000 $Datos_array[$ar][1] = $trafico_actual $ar = $ar + 1 If $string == "MIRA" Then $tabla_Start = $tabla_Start + 2 Else $tabla_Start = $tabla_Start + 1 EndIf Next $ar = $ar + 1 ;~ ############# CAÍDA ############ ;~ If $actual_entrante = 0 Then ;~ $xxx = 0 ;~ Do ;~ MsgBox(0, "Tráfico Caído", $paginas[$i], 5) ;~ $xxx = $xxx + 1 ;~ Until $xxx = 10 ;~ EndIf ;~ ############# CAÍDA ############. Local $Numtables_datos = 0 Next _ArrayDisplay($Datos_array, "Array display") _IEQuit($oIE) Thanks!! 


      monitoria.html
    • By supraaxdd
      Hello,
       
      I have recently tried experimenting with a new feature that I want to add to my program. I wanted to test packet loss, so I opted into a command prompt that displays the necessary data that I want. Now I want to sort out the data by using column sorting if possible by using arrays. My question is: Are you able to sort them out on CMD or is it only Excel spreadsheets?
      Below find the column I want to sort out:
       
      Pinging google.ie [2a00:1450:400b:c01::5e] with 32 bytes of data: Reply from 2a00:1450:400b:c01::5e: time=1033ms Reply from 2a00:1450:400b:c01::5e: time=309ms Reply from 2a00:1450:400b:c01::5e: time=37ms Reply from 2a00:1450:400b:c01::5e: time=732ms Ping statistics for 2a00:1450:400b:c01::5e: Packets: Sent = 4, Received = 4, Lost = 0 (0% loss), Approximate round trip times in milli-seconds: Minimum = 37ms, Maximum = 1033ms, Average = 527ms I want to extract the Packet loss; the average along with all the results in the "time" column.
      Looking forward for your response!
       
      Kind Regards,
      Supra
×
×
  • Create New...