Jump to content
AnonymousX

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

Recommended Posts

AnonymousX

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
Subz

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

 

  • Like 1

Share this post


Link to post
Share on other sites
AdamUL

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

 

  • Like 1

Share this post


Link to post
Share on other sites
AnonymousX
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
AnonymousX
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
Subz

Do all of your users have Excel?

Share this post


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

Do all of your users have Excel?

Yes, 2007 to current

Share this post


Link to post
Share on other sites
Subz

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 1

Share this post


Link to post
Share on other sites
AnonymousX
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
AdamUL

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

 

  • Like 1

Share this post


Link to post
Share on other sites
AnonymousX
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
AdamUL

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
AnonymousX
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
AdamUL

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
AnonymousX

Yeah, I feel like I just learned that the hard way lol. 

Thanks for the reference!

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

    • corz
      By corz
      Associative Array Functions
      I've seen a couple of UDFs for this on the forum. One of them I quite like. But it's still nearly not as good as this method, IMHO.
      I don't recall if I discovered the "Scripting.Dictionary" COM object myself or if I got the original base code from somewhere online. I have recently searched the web (and here) hard for any AutoIt references to this, other than my own over the years I've been using this (in ffe, etc..), and I can find nothing, so I dunno. If anyone does, I'd love to give credit where it's due; this is some cute stuff! It could actually be all my own work! lol
      At any rate, it's too useful to not have posted somewhere at autoitscript.com, so I've put together a wee demo.
      For those who haven't heard of the COM "Scripting.Dictionary".. 
      If you've ever coded in Perl or PHP (and many other languages), you know how useful associative arrays are. Basically, rather than having to iterate through an array to discover it's values, with an associative array you simply pluck values out by their key "names".
      I've added a few functions over the years, tweaked and tuned, and this now represent pretty much everything you need to easily work with associative arrays in AutoIt. En-joy!
      The main selling point of this approach is its simplicity and weight. I mean, look at how much code it takes to work with associative arrays! The demo is bigger than all the functions put together! The other selling point is that we are using Windows' built-in COM object functions which are at least theoretically, fast and robust.
      I've used it many times without issues, anyhow, here goes..
      ; Associative arrays in AutoIt? Hells yeah! ; Initialize your array ... global $oMyError = ObjEvent("AutoIt.Error", "AAError") ; Initialize a COM error handler ; first example, simple. global $simple AAInit($simple) AAAdd($simple, "John", "Baptist") AAAdd($simple, "Mary", "Lady Of The Night") AAAdd($simple, "Trump", "Silly Man-Child") AAList($simple) debug("It is said that Trump is a " & AAGetItem($simple, "Trump") & ".", @ScriptLineNumber);debug debug("") ; slightly more interesting.. $ini_path = "AA_Test.ini" ; Put this prefs section in your ini file.. ; [test] ; foo=foo value ; foo2=foo2 value ; bar=bar value ; bar2=bar2 value global $associative_array AAInit($associative_array) ; We are going to convert this 2D array into a cute associative array where we ; can access the values by simply using their respective key names.. $test_array = IniReadSection($ini_path, "test") for $z = 1 to 2 ; do it twice, to show that the items are *really* there! for $i = 1 to $test_array[0][0] $key_name = $test_array[$i][0] debug("Adding '" & $key_name & "'..");debug ; key already exists in "$associative_array", use the pre-determined value.. if AAExists($associative_array, $key_name) then $this_value = AAGetItem($associative_array, $key_name) debug("key_name ALREADY EXISTS! : =>" & $key_name & "<=" , @ScriptLineNumber);debug else $this_value = $test_array[$i][1] ; store left=right value pair in AA if $this_value then AAAdd($associative_array, $key_name, $this_value) endif endif next next debug(@CRLF & "Array Count: =>" & AACount($associative_array) & "<=" , @ScriptLineNumber);debug AAList($associative_array) debug(@CRLF & "Removing 'foo'..");debug AARemove($associative_array, "foo") debug(@CRLF & "Array Count: =>" & AACount($associative_array) & "<=" , @ScriptLineNumber);debug AAList($associative_array) debug(@CRLF & "Removing 'bar'..");debug AARemove($associative_array, "bar") debug(@CRLF & "Array Count: =>" & AACount($associative_array) & "<=" , @ScriptLineNumber);debug AAList($associative_array) quit() func quit() AAWipe($associative_array) AAWipe($simple) endfunc ;; Begin AA Functions func AAInit(ByRef $dict_obj) $dict_obj = ObjCreate("Scripting.Dictionary") endfunc ; Adds a key and item pair to a Dictionary object.. func AAAdd(ByRef $dict_obj, $key, $val) $dict_obj.Add($key, $val) If @error Then return SetError(1, 1, -1) endfunc ; Removes a key and item pair from a Dictionary object.. func AARemove(ByRef $dict_obj, $key) $dict_obj.Remove($key) If @error Then return SetError(1, 1, -1) endfunc ; Returns true if a specified key exists in the associative array, false if not.. func AAExists(ByRef $dict_obj, $key) return $dict_obj.Exists($key) endfunc ; Returns a value for a specified key name in the associative array.. func AAGetItem(ByRef $dict_obj, $key) return $dict_obj.Item($key) endfunc ; Returns the total number of keys in the array.. func AACount(ByRef $dict_obj) return $dict_obj.Count endfunc ; List all the "Key" > "Item" pairs in the array.. func AAList(ByRef $dict_obj) debug("AAList: =>", @ScriptLineNumber);debug local $k = $dict_obj.Keys ; Get the keys ; local $a = $dict_obj.Items ; Get the items for $i = 0 to AACount($dict_obj) -1 ; Iterate the array debug($k[$i] & " ==> " & AAGetItem($dict_obj, $k[$i])) next endfunc ; Wipe the array, obviously. func AAWipe(ByRef $dict_obj) $dict_obj.RemoveAll() endfunc ; Oh oh! func AAError() Local $err = $oMyError.number If $err = 0 Then $err = -1 SetError($err) ; to check for after this function returns endfunc ;; End AA Functions. ; debug() (trimmed-down version) ; ; provides quick debug report in your console.. func debug($d_string, $ln=false) local $pre ; For Jump-to-Line in Notepad++ if $ln then $pre = "(" & $ln & ") " & @Tab ConsoleWrite($pre & $d_string & @CRLF) endfunc  
      ;o) Cor
    • TheWizEd
      By TheWizEd
      How do I work with 2D arrays.  I've tried this but get errors.
      Local $aTest[4][4] = [[1,2,3,4],[5,6,7,8],[9,10,11,12],[13,14,15,16]]
      ;$aTest[0][] = [10,11,12]  ; Error at []
      Local $sTest = ""
      For $i = 0 To UBound($aTest)-1
        Local $aExtract = _ArrayExtract($aTest,$i,$i)
        $sTest = $sTest & MyTest($aExtract)
      Next
      Func MyTest($aTemp)
        _ArrayDisplay($aTemp)
        ; Error at    v $aTemp
        Return String($aTemp[0]) & " - " & String($aTemp[1]) & " - " & String($aTemp[2]) & @CRLF
      EndFunc
       
       
    • Eli_jahbot
      By Eli_jahbot
      My esteemed Autoits I need your help once again.
      I'm trying to figure out how to create a loop that gets 1 value from an array and repeats until each value from the array has been used. I have never used arrays before and I know once I learn more things should get easier for me. 
      Here is what Im trying to do:
      -Have an array of values that determine what application i log into. ex: app1, app2, app3, app4 etc.
      -Have a loop that repeats a process sequentially using each value in the array to finish the process for each app1, app2, app 3 and so forth. I have 30 apps that I need to update on a regular basis and getting this sorted out is what I perceive to be the best way to do it.
      Here is my feeble attempt that obviously fails:
      #include <msgboxconstants.au3>
      #include <Constants.au3>
      #include <array.au3>
      Login()
      Func Login()
          local $array[30] = ["10", "11", "12",etc etc]
          ;;Local $site = InputBox("ERx Site","What site do you want to login as?","","")
          Local $userid = InputBox("ERx Login", "What is your username?", "", "")
          Local $Passwd = InputBox("Security Check", "Enter your UAT password.", "", "*")
      for $1 = 1 to 30(I need to do the same steps in 30 different apps)
      run("Z:launch.exe")
      WinWaitActive("Input")
      Send (Sequential ARRAY VALUE HERE)
      Send("{ENTER}")
      WinWaitActive("window")
      Send($userid)
      Send("{TAB}")
      send($Passwd)
      Send("{ENTER}")
      WinWaitActive("[CLASS:SunAwtDialog]")
      Sleep(500)
      WinClose("Home Page")
      Next
      EndFunc
       
      your help is greatly appreciated.
      Thanks for your time
    • 9252Survive
      By 9252Survive
      Hi All, 
       
      I am fairly new to AutoIT and I am still trying to learn, I have been using _FileListToArray to list all the files with a particular extension in an array and then loop through it for operation  (   For $i = 1 To UBound($FileArray) - 1).
      So far this has been working fine. But I am not able to figure out a problem that I have; what if I have 50 files but I only want to loop through first 10 files and then next ten and so on?  Or rather I should say, how I can I only feed max 10 files to the array at a time when I do _FileListToArray regardless of the total number of files in the folder?
      Any insight/help will be much appreciated 
    • NiftRex
      By NiftRex
      I'm trying to get an array from a website so that I can just get the url, but I am not sure how. I read a bit of arrays but I have a feeling I'd have to be writing a lot more than what I should be. I will include the script I have so far and the API url for what I want.
       
      API: https://api.fast.com/netflix/speedtest?https=true&token=YXNkZmFzZGxmbnNkYWZoYXNkZmhrYWxm&urlCount=1 (I want the 'url' array that contains the url)
       
      Code:
      #include <MsgBoxConstants.au3> #include <Inet.au3> #include <Array.au3> $site = _INetGetSource('http://api.fast.com/netflix/speedtest?https=true&token=YXNkZmFzZGxmbnNkYWZoYXNkZmhrYWxm&urlCount=1') MsgBox($MB_SYSTEMMODAL, "Title", $site[1])  
×