;;; CHANGE THIS TO THE DIRECTORY THAT CONTAINS THE CSV FILES
$csvFiles = DirList("\\share\folder\*.csv",2)
;;; CHANGE THIS TO THE DIRECTORY THAT YOU WANT THE OUPUT EXCEL FILE SAVED TO
$xlFile = "\\share\folder\output.xlsx"
$xlObj = CreateObject('EXCEL.Application')
$xlObj.SheetsInNewWorkbook = 1
$xlBookCreate = $xlObj.Workbooks.Add()
$row = 1
for each $csv in $csvfiles
$csvdata = LoadFile($csv,@CRLF)
ReDim preserve $csvdata[ubound($csvdata)-1]
for each $line in $csvdata
$data = Split($line,",")
;;; THIS IS WHERE YOU CAN SPECIFY WHICH COLUMNS TO COPY OVER.
$xlObj.WorkSheets($xlObj.ActiveSheet.Name).Cells($row,1).Value = $data[0]
$xlObj.WorkSheets($xlObj.ActiveSheet.Name).Cells($row,2).Value = $data[2]
$row = 1+$row
next
next
$nul = $xlObj.ActiveWorkbook.SaveAs($xlFile)
$xlObj.DisplayAlerts = 0
$xlObj.Quit
$xlObj = 0
;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
;;;; NO NEED TO EDIT UDFS BELOW THIS LINE
;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
;Function LoadFile()
;
;Author Bryce Lindsay Bryce@isorg.net
;
;Action Loads a file in to a variable or an array.
;
;Syntax LoadFile("Filename",[Array Delim], [UNI/ANSI file type])
;
;Version 1.1
;
;Date Revised 9:14 AM 8/15/2006
;
;Parameters Filename
; name of the file that you want to load.
;
; Optional Array
; set this option if you want to return the
; information as an array split on the given value
;
; Unicode/ANSI file type
; 3 = force file open as ANSI
; 2 = forse file open as UNI
; 1 = Opens the file using the system default. (the UDF will use this as default)
;
;Remarks finaly made this into a UDF and posted it... got tired
; of having to hunt it down.
;
;Returns if the array flag is not set it returns a variable
; containing the contents of the file.
;
; if the array flag is set, it will return an array
; of the file split on the value of the given variable
;
;Dependencies Scripting.FileSystemObject
;
;KiXtart Ver 4.51
;
;Example(s)
; ;load the file test.txt into a variable
; $Data=loadfile('test.txt')
; ? $data
;
; ;load the file "test.txt" into an array split on @crlf
; $data = loadfile('test.txt',@crlf)
; for each $line in $data
; ? $line
; next
;
;KIXTART BBS http://www.kixtart.org/forums/ubbthreads.php?ubb=showflat&Board=7&Number=165959
;
Function loadfile($file, optional $array, $Uni)
DIM $fso,$f,$fs
if $uni $uni = $uni-3 else $uni = -2 endif
if not $uni $uni = -2 endif
$fso = CreateObject("Scripting.FileSystemObject")
$f = $fso.GetFile($file)
If @ERROR Exit 2 EndIf
$fs = $f.OpenAsTextStream(1,$uni)
if not $array
$loadfile = $fs.Read($f.size)
else
$loadfile = Split($fs.Read($f.size),$array)
endif
Exit @ERROR
EndFunction
;ACTION Returns an array with a list of files in a given directory
;
;AUTHOR Jens Meyer (sealeopard@usa.net)
;
;VERSION 1.3 fixed bug that did not pass $options into the recursive call
; 1.2
;
;DATE CREATED 2002/01/18
;
;DATE MODIFIED 2007/02/22
;
;KIXTART 4.12
;
;SYNTAX DIRLIST(DIRNAME [,OPTIONS])
;
;PARAMETERS DIRNAME
; Required string containing the directory name
;
; OPTIONS
; Optional value for additional options, options are set bitwise
; 1 = include directories (denoted by a backslash) that match the search mask
; 2 = include full path
; 4 = search all subdirectories
;
;RETURNS array with a list of files, otherwise an empty string
;
;REMARKS none
;
;DEPENDENCIES none
;
;EXAMPLE $dirlist = DIRLIST("c:\*.*",1+2+4)
;
;KIXTART BBS http://www.kixtart.org/ubbthreads/showflat.php?Cat=&Board=UBB12&Number=82077
;
Function dirlist($dirname, optional $options)
dim $filename, $counter, $filepath, $mask
dim $list, $sublist, $subcounter
$counter=-1
$dirname=trim($dirname)
if $dirname=''
$dirname=@CURDIR
endif
if right($dirname,1)='\'
$dirname=left($dirname,len($dirname)-1)
endif
if getfileattr($dirname) & 16
$mask='*.*'
else
$mask=substr($dirname,instrrev($dirname,'\')+1)
$dirname=left($dirname,len($dirname)-len($mask)-1)
endif
redim $list[10]
$filename=dir($dirname+'\'+$mask)
while $filename<>'' and @ERROR=0
if $filename<>'.' and $filename<>'..'
select
case (getfileattr($dirname+'\'+$filename) & 16)
if $options & 1
$counter=$counter+1
if $options & 2
$list[$counter]=$dirname+'\'+$filename+'\'
else
$list[$counter]=$filename+'\'
endif
endif
if ($options & 4)
$sublist=dirlist($dirname+'\'+$filename+'\'+$mask,$options)
if ubound($sublist)+1
redim preserve $list[ubound($list)+ubound($sublist)+1]
for $subcounter=0 to ubound($sublist)
$counter=$counter+1
if $options & 2
$list[$counter]=$dirname+'\'+$filename+'\'+$sublist[$subcounter]
else
$list[$counter]=$filename+'\'+$sublist[$subcounter]
endif
next
endif
endif
case ($options & 2)
$counter=$counter+1
$list[$counter]=$dirname+'\'+$filename
case 1
$counter=$counter+1
$list[$counter]=$filename
endselect
if $counter mod 10
redim preserve $list[$counter+10]
endif
endif
$filename = dir('')
loop
if $counter+1
redim preserve $list[$counter]
else
$list=''
endif
if $mask<>'*.*' and ($options & 4)
$filename=dir($dirname+'\*.*')
while $filename<>'' and @ERROR=0
if $filename<>'.' and $filename<>'..'
if (getfileattr($dirname+'\'+$filename) & 16)
$sublist=dirlist($dirname+'\'+$filename+'\'+$mask,4)
if ubound($sublist)+1
redim preserve $list[ubound($list)+ubound($sublist)+1]
for $subcounter=0 to ubound($sublist)
$counter=$counter+1
if $options & 2
$list[$counter]=$dirname+'\'+$filename+'\'+$sublist[$subcounter]
else
$list[$counter]=$filename+'\'+$sublist[$subcounter]
endif
next
endif
endif
endif
$filename = dir('')
loop
endif
if $counter+1
redim preserve $list[$counter]
else
$list=''
endif
$dirlist=$list
endfunction
