Pax
(Getting the hang of it)
2009-10-21 12:54 PM
Consolidating multiple files into 1 - but header from file 1 wanted

I get a bunch of CSV file extracts each month that I am need to consolidate to save time for the person processing them. Each file sits in a subdirectory from the root level, and has a header row in each file. I've got the code working fine, but I've been informed that they need the header row included in the consolidation file.

The question is how do I include a header in the file? My current loop is below.

 Code:
$headerline = "yes"

While $CutoverLog <> "" and @Error = 0
	Open (1, $pcclist + $CutoverLog + "\" + $CutoverLog + "_Details_" + $year + $month + ".csv")
	? "Opening " + $pcclist + $CutoverLog + "\" + $CutoverLog + "_Details_" + $year + $month + ".csv"
	?
	Open (2, $TransactLog, 5)

	$Complete = False
	
	If $headerline = "no"
		$Line = Readline (1)
		$line = ""
	endIf
	
	$headerline = "no"
	
	While $Complete = False
		$Line = Readline (1)
			
			If $Line <> " " and @Error = 0
				Gosub WriteLog
			Else
				$Complete = True
			EndIf			
	Loop
	
	Close (1)
	Close (2)
	
	Del $CutoverLog
	
	$CutoverLog = Dir()
Loop


Many thanks in advance for any ideas, as I'm a bit stuck.

Pax


Richard H.Administrator
(KiX Supporter)
2009-10-21 01:33 PM
Re: Consolidating multiple files into 1 - but header from file 1 wanted

A lot of your script is missing so I've had to make some assumptions, but here you go:
 Code:
$headerline = ""

Open (2, $TransactLog, 5)
While $CutoverLog <> "" and @Error = 0
	Open (1, $pcclist + $CutoverLog + "\" + $CutoverLog + "_Details_" + $year + $month + ".csv")

	$Line=Readline(1)
	If $headerline=""
		$headerline="done"
		Gosub WriteLog
	Endif
	
	$Line=Readline(1)
	While @ERROR=0
		Gosub WriteLog
		$Line=Readline(1)
	Loop
	
	Close(1)
	
	Del $CutoverLog
	
	$CutoverLog = Dir()
Loop
Close (2)


You really need to get rid of the GOSUB, add error checking to the OPENs, user FreeFileHandle() for the handles and a host of other code cleanups, but the above should get you started.


Pax
(Getting the hang of it)
2009-10-21 01:51 PM
Re: Consolidating multiple files into 1 - but header from file 1 wanted

Ahh yeah I should have posted the whole code as I forgot about the gosub.

 Code:
Break On

Color +y/b
$Tab = Chr(009)
$return =chr(13) + chr(10)
$CutoverLog = Dir("C:\Salesforce\Receive\*")
$pcclist = "C:\Salesforce\Receive\"
? "Working directory is .." + $pcclist
? "Script directory is " + @scriptdir

;
;
;
; Consolidating the Transactional data.
;
;
;

? "Month is " + @monthno
?
If @MonthNo = 1
        $Year = @YEAR - 1
Else
        $Year = @YEAR
EndIf

$Month = Right('0' + @MonthNo, 2)
? "The month is " + $month
? "The year is " + $year
?

$TransactLog = @ScriptDir + ".\Transact_FCBT_" + $Month + "_" + $Year + ".csv"


If Exist (@ScriptDir + ".\done") = 0
	MD @ScriptDir + ".\done"
EndIf

If Exist ($TransactLog) = 1
	Del $TransactLog
EndIf

$headerline = "yes"


While $CutoverLog <> "" and @Error = 0
	Open (1, $pcclist + $CutoverLog + "\" + $CutoverLog + "_Details_" + $year + $month + ".csv")
	? "Opening " + $pcclist + $CutoverLog + "\" + $CutoverLog + "_Details_" + $year + $month + ".csv"
	?
	Open (2, $TransactLog, 5)

	$Complete = False
	
	If $headerline = "no"
		$Line = Readline (1)
		$line = ""
	endIf
	
	$headerline = "no"
	
	While $Complete = False
		$Line = Readline (1)
			
			If $Line <> " " and @Error = 0
				Gosub WriteLog
			Else
				$Complete = True
			EndIf			
	Loop
	
	Close (1)
	Close (2)
	
	Del $CutoverLog
	
	$CutoverLog = Dir()
Loop

;
;
; Consolidating the Client details data
;
;
;

If @MonthNo = 1
    $Month = 12
    $Year = @YEAR - 1
	$ld = 31
Else
    $Month = @MonthNo - 1
    $Year = @YEAR
	;Create an array of Last day of each month with built-in leapyar check
	$mld = 31,28+(Not(@Year Mod 400) Or (@Year Mod 100) And Not(@Year Mod 4)),31,30,31,30,31,31,30,31,30,31
 
	;Get Last day of the current month from $mld array
	$ld = $mld[@MonthNo-2]
EndIf

$ClientLog = @ScriptDir + ".\Client_FCBT_" + $Month + "_" + $Year + ".csv"

$year = Right($year, 2)
? "The new year is " + $year
$Month = Right('0' + $Month, 2)
? "The new month is " + $month
?

? "The last date of last month is " $ld
?


If Exist ($ClientLog) = 1
	Del $ClientLog
EndIf
$CutoverLog = Dir("C:\Salesforce\Receive\*")

While $CutoverLog <> "" and @Error = 0
	Open (1, $pcclist + $CutoverLog + "\" + "Client_" + $year + $month + $ld + ".csv")
	? "Opening " + $pcclist + $CutoverLog + "\" + "Client_" + $year + $month + $ld + ".csv"
	?
	Open (2, $ClientLog, 5)

	$Complete = False
	
	If $headerline = "no"
		$Line = Readline (1)
		$line = ""
	endIf
	
	$headerline = "no"
	
	While $Complete = False
		$Line = Readline (1)
			
			If $Line <> " " and @Error = 0
				Gosub WriteLog
			Else
				$Complete = True
			EndIf			
	Loop
	
	Close (1)
	Close (2)
	
	Del $CutoverLog
	
	$CutoverLog = Dir()
Loop

copy $ClientLog @Scriptdir + "\done"
copy $TransactLog @Scriptdir + "\done"

:WriteLog
WriteLine (2, $Line + $return)


I see what you've done so that headerline would only be processed once and forgotten about for each loop afterwards.

Let me give it a go.

I know this code is not neat, but I'd taken code I was given which wasn't working for our situation and adapting it as best as I have given my limited knowledge.

Thanks for the comments so far.

Pax


Pax
(Getting the hang of it)
2009-10-21 02:52 PM
Re: Consolidating multiple files into 1 - but header from file 1 wanted

hmmm giant loop somewhere. I got 72MB of the first line of the first file with no header at all before I terminated it.

Pax


Pax
(Getting the hang of it)
2009-10-21 06:24 PM
Re: Consolidating multiple files into 1 - but header from file 1 wanted

I cheated....

The header is static, so I wrote the header outside the main While loop.

I might go back and revisit this later, but I was getting coding headaches in between everyone coming to my desk and hassling me for other jobs.

Pax


Richard H.Administrator
(KiX Supporter)
2009-10-22 08:46 AM
Re: Consolidating multiple files into 1 - but header from file 1 wanted

Cheating's fine, sometimes good enough is ... good enough \:\)

Drop by and post if you'd like it looked at again.