Jump to content
Sign in to follow this  
slaughter

multiple excel to csv

Recommended Posts

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.

#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

Share this post


Link to post
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
Sign in to follow this  

×