multiple excel to csv

[slaughter]

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