Page 1 of 1 1
Topic Options
#205388 - 2012-06-12 10:24 AM read multiple .csv file and extract data to an another . excel file
nxtman Offline
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
#205390 - 2012-06-12 06:14 PM Re: read multiple .csv file and extract data to an another . excel file [Re: nxtman]
Lonkero Administrator Offline
KiX Master Guru
*****

Registered: 2001-06-05
Posts: 22346
Loc: OK
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.
_________________________
!

download KiXnet

Top
#205391 - 2012-06-12 07:10 PM Re: read multiple .csv file and extract data to an another . excel file [Re: Lonkero]
ShaneEP Moderator Offline
MM club member
*****

Registered: 2002-11-29
Posts: 2125
Loc: Tulsa, OK
Also you'll need the COM Automation section.
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 Administrator Offline
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! \:D

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 Offline
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
#205401 - 2012-06-13 03:33 PM Re: read multiple .csv file and extract data to an another . excel file [Re: nxtman]
BradV Offline
Seasoned Scripter
****

Registered: 2006-08-16
Posts: 686
Loc: Maryland, USA
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

Top
#205402 - 2012-06-13 05:42 PM Re: read multiple .csv file and extract data to an another . excel file [Re: nxtman]
Les Offline
KiX Master
*****

Registered: 2001-06-11
Posts: 12734
Loc: fortfrances.on.ca
 Originally Posted By: nxtman
If you give me a ready made script...
That is essentially what Glenn is giving you in the form of ready made UDFs. I don't see this becoming a silver platter job.
_________________________
Give a man a fish and he will be back for more. Slap him with a fish and he will go away forever.

Top
#205403 - 2012-06-13 09:43 PM Re: read multiple .csv file and extract data to an another . excel file [Re: Les]
ShaneEP Moderator Offline
MM club member
*****

Registered: 2002-11-29
Posts: 2125
Loc: Tulsa, OK
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?
Top
#205404 - 2012-06-13 10:08 PM Re: read multiple .csv file and extract data to an another . excel file [Re: ShaneEP]
ShaneEP Moderator Offline
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.

 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

Top
#205405 - 2012-06-14 11:26 AM Re: read multiple .csv file and extract data to an another . excel file [Re: ShaneEP]
nxtman Offline
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 Administrator Offline
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! \:D

Top
#205407 - 2012-06-14 04:55 PM Re: read multiple .csv file and extract data to an another . excel file [Re: Glenn Barnas]
Lonkero Administrator Offline
KiX Master Guru
*****

Registered: 2001-06-05
Posts: 22346
Loc: OK
"I'm a novice in computers but would like to get a complete code. thank you."

that's just too funny.
_________________________
!

download KiXnet

Top
#205408 - 2012-06-15 12:18 PM Re: read multiple .csv file and extract data to an another . excel file [Re: Lonkero]
Glenn Barnas Administrator Offline
KiX Supporter
*****

Registered: 2003-01-28
Posts: 4396
Loc: New Jersey
Yeah - I thought about rewriting Shane's post using Pseudocode so the OP would have to put SOME effort forth... ;\)

Glenn
_________________________
Actually I am a Rocket Scientist! \:D

Top
#205409 - 2012-06-15 05:51 PM Re: read multiple .csv file and extract data to an another . excel file [Re: Glenn Barnas]
ShaneEP Moderator Offline
MM club member
*****

Registered: 2002-11-29
Posts: 2125
Loc: Tulsa, OK
Yeah I know. But I like Kix, and sadly I'm just trying to hook what little people that still use it.
Top
Page 1 of 1 1


Moderator:  Jochen, Allen, Radimus, Glenn Barnas, ShaneEP, Ruud van Velsen, Arend_, Mart 
Hop to:
Shout Box

Who's Online
2 registered (morganw, mole) and 414 anonymous users online.
Newest Members
gespanntleuchten, DaveatAdvanced, Paulo_Alves, UsTaaa, xxJJxx
17864 Registered Users

Generated in 0.065 seconds in which 0.023 seconds were spent on a total of 13 queries. Zlib compression enabled.

Search the board with:
superb Board Search
or try with google:
Google
Web kixtart.org