slaughter Posted October 10, 2012 Share Posted October 10, 2012 Had some stuf to do so here script. Simply put excels filesin folder with script it will generate text.csv of all combined excels. expandcollapse popup#cs ---------------------------------------------------------------------------- AutoIt Version: 3.3.8.1 Author: myName Script Function: Template AutoIt script. #ce ---------------------------------------------------------------------------- ; Script Start - Add your code below here #include #include #include FileDelete("test.csv") Local $FileList = _FileListToArray(@ScriptDir) If @error = 1 Then MsgBox(0, "", "No Folders Found.") Exit EndIf If @error = 4 Then MsgBox(0, "", "No Files Found.") Exit EndIf _ArrayDelete($FileList, 0) Local $avArrayTarget[5] = ["JPM", "Holger", "Jon", "Larry", "Jeremy"] $string = '' For $element In $FileList If $element == @ScriptName Then ELSE $data = xls_to_css($element) _ArrayDelete($data, 0) $string = $string & @CRLF & ArrayToString($data) EndIf Next $array = StringSplit($string, @CRLF, 1) $string = '' For $element In $array $string = $string & '"' & $element & '"' & @CRLF Next FileDelete("test.csv") Local $file = FileOpen("test.csv", 1) ; Check if file opened for writing OK If $file = -1 Then MsgBox(0, "Error", "Unable to open file.") Exit EndIf FileWrite($file, $string) FileClose($file) Func ArrayToString($array) ConsoleWrite("A: " & UBound($array) & @CRLF) ;_ArrayDisplay($array, "$FileList") $string = _ArrayToString2D($array,'";"') Return $string EndFunc Func xls_to_css($file) Local $sFilePath1 = @ScriptDir & "\" & $file ;This file should already exist ConsoleWrite ($sFilePath1 & @LF) Local $oExcel = _ExcelBookOpen($sFilePath1,0) If @error = 1 Then MsgBox(0, "Error!", "Unable to Create the Excel Object") Exit ElseIf @error = 2 Then MsgBox(0, "Error!", "File does not exist - Shame on you!") Exit EndIf $xls_array=_ExcelReadSheetToArray2($oExcel); _ExcelBookClose($oExcel,0) Return $xls_array EndFunc Func _ArrayToString2D(Const ByRef $avArray, $sDelimCol = "|", $sDelimRow = @CRLF, $iStart = 0, $iEnd = 0) If Not IsArray($avArray) Then Return SetError(1, 0, "") If UBound($avArray, 0) <> 2 Then Return SetError(2, 0, "") Local $sResult, $iUBound = UBound($avArray) - 1 ; Bounds checking If $iEnd < 1 Or $iEnd > $iUBound Then $iEnd = $iUBound If $iStart < 0 Then $iStart = 0 If $iStart > $iEnd Then Return SetError(3, 0, "") ; Combine For $i = $iStart To $iEnd ; rows For $j = 0 To UBound($avArray,2) - 1 ; columns $sResult &= $avArray[$i][$j] & $sDelimCol Next $sResult = StringTrimRight($sResult, StringLen($sDelimCol)) $sResult &= $sDelimRow Next Return StringTrimRight($sResult, StringLen($sDelimRow)) EndFunc ; #FUNCTION# ==================================================================================================================== ; Name...........: _ExcelReadSheetToArray ; Description ...: Create a 2D array from the rows/columns of the active worksheet. ; Syntax.........: _ExcelReadSheetToArray($oExcel[, $iStartRow = 1[, $iStartColumn = 1[, $iRowCnt = 0[, $iColCnt = 0]]]]) ; Parameters ....: $oExcel - excel object opened by a preceding call to _ExcelBookOpen() or _ExcelBookNew() ; $iStartRow - Row number to start reading, defaults to 1 (first row) ; $iStartColumn - Column number to start reading, defaults to 1 (first column) ; $iRowCnt - Count of rows to read, defaults to 0 (all) ; $iColCnt - Count of columns to read, defaults to 0 (all) ; $iColShift - Match R1C1 column position, or start array in column 0. Default is 0 (match R1C1 values) ; Return values .: Success - Returns a 2D array with the specified cell contents by [$row][$col] ; Failure - Returns 0 and sets @error on errors: ; |@error=1 - Specified object does not exist ; |@error=2 - Start parameter out of range ; |@extended=0 - Row out of range ; |@extended=1 - Column out of range ; |@error=3 - Count parameter out of range ; |@extended=0 - Row count out of range ; |@extended=1 - Column count out of range ; Author ........: SEO ; Modified.......: PsaltyDS 01/04/08 - 2D version, litlmike - Column shift parm, Golfinhu - Speed enhancement, Spiff59 - Allow protected sheets ; Remarks .......: Returned array has row count in [0][0] and column count in [0][1] (unless ; Except for the counts above, row 0 and col 0 of the returned array are empty, as actual ; cell data starts at [1][1] to match R1C1 numbers. ; By default the entire sheet is returned. ; If the sheet is empty [0][0] and [0][1] both = 0. ; Related .......: ; Link ..........: ; Example .......: No ; =============================================================================================================================== Func _ExcelReadSheetToArray2($oExcel, $iStartRow = 1, $iStartColumn = 1, $iRowCnt = 0, $iColCnt = 0, $iColShift = 0) ; Parameter edits If Not IsObj($oExcel) Then Return SetError(1, 0, 0) If $iStartRow < 1 Then Return SetError(2, 0, 0) If $iStartColumn < 1 Then Return SetError(2, 1, 0) If $iRowCnt < 0 Then Return SetError(3, 0, 0) If $iColCnt < 0 Then Return SetError(3, 1, 0) Local $iLastRow = $oExcel.Activesheet.UsedRange.Rows.Count Local $iLastColumn = $oExcel.Activesheet.UsedRange.Columns.Count If ($iLastRow + $iLastColumn = 2) And $oExcel.Activesheet.Cells(1, 1).Value = "" Then ; empty result Local $avRET[1][2] = [[0, 0]] Return $avRET EndIf ; Parameter edits (continued) If $iStartRow > $iLastRow Then Return SetError(2, 0, 0) If $iStartColumn > $iLastColumn Then Return SetError(2, 1, 0) If $iStartRow + $iRowCnt - 1 > $iLastRow Then Return SetError(3, 0, 0) If $iStartColumn + $iColCnt - 1 > $iLastColumn Then Return SetError(3, 1, 0); Check for defaulted counts If $iRowCnt Then $iLastRow = $iStartRow + $iRowCnt - 1 Else $iRowCnt = $iLastRow - $iStartRow + 1 EndIf If $iColCnt Then $iLastColumn = $iStartColumn + $iColCnt - 1 Else $iColCnt = $iLastColumn - $iStartColumn + 1 EndIf ; Read data Local $aArray = $oExcel.ActiveSheet.Range($oExcel.Cells($iStartRow, $iStartColumn), $oExcel.Cells($iLastRow, $iLastColumn)).Value If Not IsArray($aArray) Then ; single-cell result Local $avRET[2][2] = [[1, 1],["", $aArray]] Return $avRET EndIf ; Convert Col/Row array from Excel to Row/Col Local $avRET[$iRowCnt + 1][$iColCnt + ($iColCnt = 1 Or $iColShift = 0)] = [[$iRowCnt, $iColCnt]] For $i = 1 To $iColCnt For $j = 1 To $iRowCnt $avRET[$j][$i - $iColShift] = $aArray[$i - 1][$j - 1] Next Next Return $avRET EndFunc ;==>_ExcelReadSheetToArray Func __ArrayConcatenate(ByRef $avArrayTarget, Const ByRef $avArraySource) If Not IsArray($avArrayTarget) Then Return SetError(1, 1, -1); $avArrayTarget is not an array If Not IsArray($avArraySource) Then Return SetError(1, 2, -1); $avArraySource is not an array Local $iUBoundTarget0 = UBound($avArrayTarget, 0), $iUBoundSource0 = UBound($avArraySource, 0) If $iUBoundTarget0 <> $iUBoundSource0 Then Return SetError(1, 3, -1); 1D/2D dimensionality did not match If $iUBoundTarget0 > 2 Then Return SetError(1, 4, -1); At least one array was 3D or more Local $iUBoundTarget1 = UBound($avArrayTarget, 1), $iUBoundSource1 = UBound($avArraySource, 1) Local $iNewSize = $iUBoundTarget1 + $iUBoundSource1 If $iUBoundTarget0 = 1 Then ; 1D arrays ReDim $avArrayTarget[$iNewSize] For $i = 0 To $iUBoundSource1 - 1 $avArrayTarget[$iUBoundTarget1 + $i] = $avArraySource[$i] Next Else ; 2D arrays Local $iUBoundTarget2 = UBound($avArrayTarget, 2), $iUBoundSource2 = UBound($avArraySource, 2) If $iUBoundSource2 > $iUBoundTarget2 Then Return SetError(1, 5, -1); 2D boundry of source too large for target ReDim $avArrayTarget[$iNewSize][$iUBoundTarget2] For $r = 0 To $iUBoundSource1 - 1 For $c = 0 To $iUBoundSource2 - 1 $avArrayTarget[$iUBoundTarget1 + $r][$c] = $avArraySource[$r][$c] Next Next EndIf Return $iNewSize - 1 EndFunc;==>__ArrayConcatenate In progress: Windows Server bruteforce GUARD Admin Tools (Remote client control) Bypasing firewall Old stuff: [font="Verdana;"]MD5 Auto Update Calendar XP SS multi usser server & client Autoit Remote Control (ARC)[/font] 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