Combine data from numerous .csv files into a single file

[aleph01]

I have been tasked with creating a monthly report, which, in the past has been created by manually copying and pasting daily .csv files into a single file.  That's the first thing I want to automate.  The second part is that there is another data set which allows the wireless logins at each library branch to be broken down into percentages.  Odd, I know.  Happy, I am not.   That's the extent of Yoda you'll get from me this post, I almost promise.

As you can see from the code below, I use _FileListToArrayRec to get an array of the files.  Works like a charm.  Then I open my new Excel spreadsheet successfully. 

Now I'm at my problem.  I don't know how to open another spreadsheet, get the populated rows (minus the 1st, which is a header row) and paste it into my first spreadsheet, appending to already populated rows.

Also, I find it odd that I can't find how to name a spreadsheet - _Excel_Close doesn't seem to do it.

If someone with automating Excel can give me a hand, I would be eternally grateful.

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

$sFilePath = "C:\Users\Sal\Desktop\excel"
;$sFilePath = "\\trackit10\wirelessstats\Wireless2016\May2016"

$aArray = _FileListToArrayRec ($sFilePath)

_ArrayDisplay ($aArray)

Do
    Global $oExcel = _Excel_Open ();(False)
    Global $oWorkbook = _Excel_BookNew($oExcel, 1)
Until ProcessExists ("EXCEL.EXE")

;For $i = 1 to $aArray[0]

;$sFilePath is work path - too lazy to delete it.  In the Do loop,  ;(False) indicates that I intend to have this done in the background if possible.  _ArrayDisplay will go away for the working script, for now, I like to know the array is populating properly.

Thanks for any help.  Point me in the right direction and be benevolent when I come back totally confused.

_aleph_

[orbs]

@aleph01,

using excel You insist on, why do?

a simple FileRead() on each .csv file in order will give you the entire .csv content as a single string, then FileWrite() each string into the same output file.

 

[Malkey]

Before pressing "Ok"  in this example which deletes the created test folder, use Windows Explorer to examine the csv files in the created folder, $sFldr.

expandcollapse popup

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

Local $sFldr = "C:\AATestCSVDir\"
_CSVCreateTestData()

_CombineFiles($sFldr, "*.csv", "CVS_2016_03.csv")

ShellExecute($sFldr & "/" & "CVS_2016_03.csv")

Local $iButId = MsgBox(1, "Clean up created Folder", 'To delete Folder press "Ok"')
If $iButId = 1 Then DirRemove($sFldr, 1) ;clean up test folder with files.


;$sFilePath Folder to generate filelist for.
;$sFilter [optional] the filter to use, default is *. (* and ? wildcards accepted - See Remarks)
;$MonthFile Name of file that all the csv files are appended to
;
Func _CombineFiles($sFilePath, $sFilter, $MonthFile)
    Local $aFileList = _FileListToArray($sFilePath, $sFilter)
    If @error = 1 Then
        MsgBox($MB_SYSTEMMODAL, "", "Path was invalid.")
        Exit
    EndIf
    If @error = 4 Then
        MsgBox($MB_SYSTEMMODAL, "", "No file(s) were found.")
        Exit
    EndIf
    ;_ArrayDisplay($aFileList)
    Local $hFileOpenWrite = FileOpen($sFilePath & "/" & $MonthFile, 1) ; $FO_APPEND (1) = Write mode (append to end of file)
    FileWrite($hFileOpenWrite, FileRead($sFilePath & "/" & $aFileList[1])) ; First file keep header

    If $aFileList[0] > 1 Then
        Local $hFileOpenRead, $iFirstChar2ndLine
        For $i = 2 To $aFileList[0]
            $iFirstChar2ndLine = StringInStr(FileRead($sFilePath & "/" & $aFileList[$i]), @LF, 0, 1)
            $hFileOpenRead = FileOpen($sFilePath & "/" & $aFileList[$i], 0)
            FileSetPos($hFileOpenRead, $iFirstChar2ndLine, 0) ; set file pointer to 1st character of 2nd line. (To remove header)
            FileWrite($hFileOpenWrite, @LF & FileRead($hFileOpenRead)) ; Reads $hFileOpenRead from pointer to end of file. And appendeds to $hFileOpenWrite.
            FileClose($hFileOpenRead)
        Next
    EndIf
    FileClose($hFileOpenWrite)
EndFunc   ;==>_CombineFiles

; Create data files for checking purposes only
Func _CSVCreateTestData()
    If DirGetSize($sFldr) <> -1 Then
        MsgBox($MB_SYSTEMMODAL, "", "Directory already exists!")
        Exit
    EndIf
    DirCreate($sFldr)
    Local $sFileHeader = "Date,Day,Number" & @CRLF
    Local $StartDate = "2016/03/01"
    For $i = 0 To 5
        $aArray = StringSplit($StartDate, "/", 2) ; $STR_NOCOUNT (2)
        FileWrite($sFldr & "CSV" & StringReplace($StartDate, "/", "_") & ".csv", _ ; File name to write to
                $sFileHeader & _ ; Header
                StringRegExpReplace($StartDate, "(\d{4})/(\d{2})/(\d{2})", "${3}/${2}/${1}") & "," & _ ; Date converted to "DD/MM/YYYY"
                _DateDayOfWeek(_DateToDayOfWeek($aArray[0], $aArray[1], $aArray[2]), 0) & "," & _ ; $DMW_LOCALE_USER (0) - Long name of the weekday (Default)
                Random(100000, 999999, 1)) ; A random number
        $StartDate = _DateAdd("D", 1, $StartDate)
    Next
EndFunc   ;==>_CSVCreateTestData

 

[aleph01]

Thanks, guys.  It never occurred to me to not use the Excel functions.  I'll look this over when I wake up.  Now java...

[aleph01]

Wow, it didn't take long to clear up my foggy head enough to see how simple this really is (without the Excel functions.)  All I needed was the number of wireless connections during the month.  I didn't really need a combined spreadsheet - that's just how it had been done in the past.  So I open a file, get my $Count (minus 1 for the header), add it to $Total, reset $Count to 0, and loop.  Then pop up  a msgbox with the total.  The @MON section is just so I don't have to modify the script every month before running.  A couple of mods for use at work, and I'm ready to go!

Thanks again for setting me straight.

_aleph_

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

Dim $i

If @MON=01 Then $MON = "Jan"
If @MON=02 Then $MON = "Feb"
If @MON=03 Then $MON = "Mar"
If @MON=04 Then $MON = "Apr"
If @MON=05 Then $MON = "May"
If @MON=06 Then $MON = "Jun"
If @MON=07 Then $MON = "Jul"
If @MON=08 Then $MON = "Aug"
If @MON=09 Then $MON = "Sep"
If @MON=10 Then $MON = "Oct"
If @MON=11 Then $MON = "Nov"
If @MON=12 Then $MON = "Dec"

$sFilePath = "C:\Users\Sal\Desktop\excel"
;$sFilePath = "\\trackit10\wirelessstats\Wireless2016\" & $MON & @YEAR
$Count = 0
$Total = 0
$aArray = _FileListToArrayRec ($sFilePath)

;_ArrayDisplay ($aArray)

For $i = 1 to $aArray[0]
    FileOpen ($aArray[$i])
    $Count = _FileCountLines ("C:\Users\Sal\Desktop\excel\" & $aArray[$i]) -1
    $Total = $Total + $Count
    $Count = 0
Next

MsgBox (0, "Total Count", $Total)

 

[AutoBert]

20 hours ago, aleph01 said:

The @MON section is just so I don't have to modify the script every month before running. 

You can use:

#include <Date.au3>
$m=9
MsgBox(0,"Month: "& $m,_DateToMonth($M,1))