|
|
|||||||
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 |
||||||||
|
|
|||||||
check the manual for open(), readline(), close(), substr(), instr(), dir(), FOR loops, WHILE loops and so forth. as you posted in advanced scripting I assume you have some knowledge of scripting or otherwise you would have not taken on such a task or posted here. |
||||||||
|
|
|||||||
Also you'll need the COM Automation section. |
||||||||
|
|
|||||||
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 |
||||||||
|
|
|||||||
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 |
||||||||
|
|
|||||||
I would simplify the task by taking out the create excel file portion. Excel is quite capable of reading a csv file. So, I would keep your task to reading and creating csv files. Brad |
||||||||
|
|
|||||||
Originally Posted By: nxtman If you give me a ready made script... |
||||||||
|
|
|||||||
Which columns numbers are you wanting to get from the csv files? Do you want them all put into one single column in the excel file? Or individual columns? |
||||||||
|
|
|||||||
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. Code: ;;; 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 |
||||||||
|
|
|||||||
Thanks Shane for the help. It works for me. |
||||||||
|
|
|||||||
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 |
||||||||
|
|
|||||||
"I'm a novice in computers but would like to get a complete code. thank you." that's just too funny. |
||||||||
|
|
|||||||
Yeah - I thought about rewriting Shane's post using Pseudocode so the OP would have to put SOME effort forth... Glenn |
||||||||
|
|
|||||||
Yeah I know. But I like Kix, and sadly I'm just trying to hook what little people that still use it. |