#205388 - 2012-06-12 10:24 AM
read multiple .csv file and extract data to an another . excel file
|
nxtman
Just in Town
Registered: 2011-08-10
Posts: 3
Loc: Tabu,Philippines
|
friends, I need your help. I have an requirement to read mutiple .csv file kept in a single folder. The task is the read two specific columns of the csv file and copy those data to an execl file. This will help me to get an consolidated data in a single file. I like to know how to accomplish this through kixtart scripting.
Thanks in advance,
Nxt
|
Top
|
|
|
|
#205393 - 2012-06-12 07:35 PM
Re: read multiple .csv file and extract data to an another . excel file
[Re: ShaneEP]
|
Glenn Barnas
KiX Supporter
Registered: 2003-01-28
Posts: 4396
Loc: New Jersey
|
Fairly simple, actually... it will require reading the manual and building upon the examples for directory enumeration (DIR command) and using the For/Each construct. A basic understanding of arrays is also required.
Locate the following UDFs: FileIO, CSV, XLLib (optional) Place the content of these UDFs in your script - DO NOT modify them.
FileIO loads an entire file into an array, making it easy to search or enumerate. CSV translates between CSV format and a Kix array (and back) xlLib is a library of ready to use Excel interface functions.. a bit advanced for step 1
Use the DIR command to enumerate the files in the folder.
For Each file, load it into an array using the FileIO function $aFileData = FileIO($Filename, 'R')
Use a For Each loop to enumerate the array. Pass each array element (which is a CSV string) to the CSV function For Each $DataLine in $aFileData $aTemp = CSV($DataLine)
$aTemp now contains an array created from the CSV format data. If you need columns 3 and 6 from the CSV record, that would be array elements 2 and 5. Add these to an output array.
$aOut = $aTemp[2], $aTemp[5] ; temporary array holding desired CSV columns If you use the xlLib Excel library, you can write this array directly to an Excel file.
If you instead want to create an output CSV file to load into Excel (easier, but extra manual step), write the output to a CSV file using the CSV function to convert the array back to CSV format: $Rc = RedirectOutput('output.csv') CSV($aOut) @CRLF $Rc = RedirectOutput('')
End the for loop, then close the directory enumeration loop.
There's a lot to do, so start by writing the loop to enumerate the CSV files in the directory and just print their names. When that works, call the FileIO and then print the first element of the array (first line of the file). When that works, proceed to the next step of converting the CSV line to an array and display the fields you want..
If you approach this step by step, it won't be too difficult.
Glenn
_________________________
Actually I am a Rocket Scientist!
|
Top
|
|
|
|
#205400 - 2012-06-13 06:51 AM
Re: read multiple .csv file and extract data to an another . excel file
[Re: Glenn Barnas]
|
nxtman
Just in Town
Registered: 2011-08-10
Posts: 3
Loc: Tabu,Philippines
|
Thanks guys for the hints...that's but I am novice in kixtart and need this script in a very short span of time. If you give me a ready made script that will be a great help for me. I can do minor modification for my requirement if needed.
Thanks again,
Nxt
|
Top
|
|
|
|
#205404 - 2012-06-13 10:08 PM
Re: read multiple .csv file and extract data to an another . excel file
[Re: ShaneEP]
|
ShaneEP
MM club member
Registered: 2002-11-29
Posts: 2125
Loc: Tulsa, OK
|
Here is one way to do it. This takes columns 1 and 3 from all csv files in a directory and saves it into two columns in an excel file.
;;; 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
|
Top
|
|
|
|
#205405 - 2012-06-14 11:26 AM
Re: read multiple .csv file and extract data to an another . excel file
[Re: ShaneEP]
|
nxtman
Just in Town
Registered: 2011-08-10
Posts: 3
Loc: Tabu,Philippines
|
Thanks Shane for the help. It works for me.
|
Top
|
|
|
|
#205406 - 2012-06-14 01:37 PM
Re: read multiple .csv file and extract data to an another . excel file
[Re: nxtman]
|
Glenn Barnas
KiX Supporter
Registered: 2003-01-28
Posts: 4396
Loc: New Jersey
|
Just be careful, since a simple "split" won't work 100% for CSV Data. That's why I suggested the CSV function.
CSV Data: a,b,c,d,e Split will work, returning [b] and [d] for columns 2 and 4.
CSV Data: a,b,"I think, therefore I am",d,e Split will fail, returning [b] and [ therefore I am"] for columns 2 and 4.
Glenn
_________________________
Actually I am a Rocket Scientist!
|
Top
|
|
|
|
Moderator: Jochen, Allen, Radimus, Glenn Barnas, ShaneEP, Ruud van Velsen, Arend_, Mart
|
2 registered
(morganw, mole)
and 414 anonymous users online.
|
|
|