Jump to content

Combine data from numerous .csv files into a single file


Recommended Posts

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_

Meds.  They're not just for breakfast anymore. :'(

Link to comment
Share on other sites

@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.

 

Signature - my forum contributions:

Spoiler

UDF:

LFN - support for long file names (over 260 characters)

InputImpose - impose valid characters in an input control

TimeConvert - convert UTC to/from local time and/or reformat the string representation

AMF - accept multiple files from Windows Explorer context menu

DateDuration -  literal description of the difference between given dates

Apps:

Touch - set the "modified" timestamp of a file to current time

Show For Files - tray menu to show/hide files extensions, hidden & system files, and selection checkboxes

SPDiff - Single-Pane Text Diff

 

Link to comment
Share on other sites

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.

#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

 

Link to comment
Share on other sites

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)

 

Meds.  They're not just for breakfast anymore. :'(

Link to comment
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
 Share

  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...