Multiple CSV into same Excel sheet

[DrewSS]

Hello,

Sorry this seems like it should be simple but I'm having a hard time...

I'm trying to run some concatenation on every file in a folder, then import the results separated with a comma delimiter into a single excel sheet. 

The file concatenation removes quotation marks from the files which works fine, and the import into excel part works fine, but I cannot get it to perform those tasks on every file in the target folder.

#include <Excel.au3>
#include <Array.au3>
#include <File.au3>
#include <MsgBoxConstants.au3>
Dim $x = 1
Dim $y = 1


Local $oExcel = _ExcelBookNew() 
$FileList = _FileListToArray("C:\Program Files\AutoIt3\GETSYS2\")

$sFile = $FileList & @CRLF

For $i1 = 0 To UBound($FileList) - 1
$sExcel = @ScriptDir & "Excel.xls"
$read = FileRead($sFile)
StringReplace($sFile, '"', '')
$sArray = StringSplit($read, @CRLF)

For $i = 1 To $sArray[0]
    If $sArray = "" Then ContinueLoop
    $aTemp = StringSplit($sArray, ",")

    For $a = 1 To $aTemp[0]
        _ExcelWriteCell($oExcel, $aTemp[$a], $y, $x) ;Write to the Cell
        $x += 1

    Next
    $y += 1
    $x = 1

Next
_ExcelBookSaveAs($oExcel, @ScriptDir & $sExcel, "xls", 0, 1) 
_ExcelBookClose($oExcel)

Any thoughts or recommendations?

Edit: that code is botched, was trying new things.

Edited November 19, 2014 by DrewSS

[water]

You add @Scriptdir in two different places in your script to $sExcel and a '' is missing after @Scriptdir.

[DrewSS]

#include <Excel.au3>
#include <Array.au3>
#include <File.au3>
#include <MsgBoxConstants.au3>
Dim $x = 1
Dim $y = 2
$sFilePath1 = @ScriptDir & "\wes2009.xls"  ; source targets
Local $oExcel = _ExcelBookOpen($sFilePath1) ;Create new book, make it visible

;$xml_path_in = InputBox("CSV files", "Enter complete folder path for your folder")
$xml_path_in = @ScriptDir & '\'

$FileList = _FileListToArray($xml_path_in, "*", 1)

For $fileInf in $FileList
$sFile = $fileInf
$read = FileRead($sFile)
StringReplace($read, '"', '')
$sArray = StringSplit($read, @CRLF)

For $i = 1 To $sArray[0]
    If $sArray[$i] = "" Then ContinueLoop
    $aTemp = StringSplit($sArray[$i], ",")
    For $a = 1 To $aTemp[0]
        _ExcelWriteCell($oExcel, $aTemp[$a], $y, $x) ;Write to the Cell
        $x += 1
    Next
    $y += 1
    $x = 1
Next

;MsgBox(0, "Exiting", "Press OK to Save File and Exit")

Next

I got the import of multiple CSV files into 1 excel sheet going with the code above; however, the StringReplace statement (line 18) is not saving the results to the files before the files are read into an array (line 19).

[water]

Needs to be:

$read = StringReplace($read, '"', '')

[DrewSS]

Durp >.<

Thank you :-D

Now if only it wouldnt take 45 minutes to copy 30,000 files!

[water]

That's about 4.5 files per second. Not too bad.

To improve performance, try to avoid I/Os.

[water]

I just noticed that you use the old Excel UDF. If possible update to the latest version of AutoIt. The new Excel UDF that comes witjh this new version allows to write a single cell, a 1D array or a 2D array with function _Excel_RangeWrite. This function is about 20 to 100 times faster compared to the old _ExcelWriteCell.

[BrewManNH]

Here's another way of combining all of the files into one before processing.

Run(@ComSpec & ' /k copy "' & $xml_path_in & '* ' & $$xml_path_out & 'NewFile.csv', "", @SW_HIDE)

This way you only have to process the file once instead of 30,000 times opening and closing files. Plus in the latest release version of _FileReadToArray can do the CSV splitting internally.