aleph01 Posted May 6, 2016 Share Posted May 6, 2016 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 More sharing options...
orbs Posted May 7, 2016 Share Posted May 7, 2016 @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 More sharing options...
Malkey Posted May 7, 2016 Share Posted May 7, 2016 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 Link to comment Share on other sites More sharing options...
aleph01 Posted May 7, 2016 Author Share Posted May 7, 2016 Thanks, guys. It never occurred to me to not use the Excel functions. I'll look this over when I wake up. Now java... Meds. They're not just for breakfast anymore. Link to comment Share on other sites More sharing options...
aleph01 Posted May 7, 2016 Author Share Posted May 7, 2016 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 More sharing options...
AutoBert Posted May 8, 2016 Share Posted May 8, 2016 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)) Link to comment Share on other sites More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now