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

AnonymousX · April 13, 2018

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 April 17, 2018 by AnonymousX

Subz · April 13, 2018

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

AdamUL · April 13, 2018

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

AnonymousX · April 16, 2018

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

AnonymousX · April 16, 2018

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?

Subz · April 17, 2018

Do all of your users have Excel?

AnonymousX · April 17, 2018

18 minutes ago, Subz said:

Do all of your users have Excel?

Yes, 2007 to current

Subz · April 17, 2018

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)

AnonymousX · April 17, 2018

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!

AdamUL · April 17, 2018

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

AnonymousX · April 17, 2018

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

AdamUL · April 17, 2018

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

AnonymousX · April 18, 2018

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.

AdamUL · April 18, 2018

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

AnonymousX · April 18, 2018

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

Thanks for the reference!

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

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Create an account or sign in to comment

Create an account

Sign in

Recently Browsing 0 members

Similar Content