You are not logged in. [Log In] KiXtart.org website » Forums » KiXtart » Basic Scripting » Consolidating multiple files into 1 - but header from file 1 wanted
Register User       Forum List       Calendar       FAQ
Page 1 of 1 1
Topic Options
#196420 - 2009-10-21 12:54 PM Consolidating multiple files into 1 - but header from file 1 wanted
Pax Offline
Getting the hang of it

Registered: 2006-12-01
Posts: 51
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
Top
#196423 - 2009-10-21 01:33 PM Re: Consolidating multiple files into 1 - but header from file 1 wanted [Re: Pax]
Richard H. Administrator Offline
Administrator
*****

Registered: 2000-01-24
Posts: 4945
Loc: Leatherhead, Surrey, UK
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.
Top
#196430 - 2009-10-21 01:51 PM Re: Consolidating multiple files into 1 - but header from file 1 wanted [Re: Richard H.]
Pax Offline
Getting the hang of it

Registered: 2006-12-01
Posts: 51
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
Top
#196433 - 2009-10-21 02:52 PM Re: Consolidating multiple files into 1 - but header from file 1 wanted [Re: Pax]
Pax Offline
Getting the hang of it

Registered: 2006-12-01
Posts: 51
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
Top
#196448 - 2009-10-21 06:24 PM Re: Consolidating multiple files into 1 - but header from file 1 wanted [Re: Pax]
Pax Offline
Getting the hang of it

Registered: 2006-12-01
Posts: 51
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
Top
#196451 - 2009-10-22 08:46 AM Re: Consolidating multiple files into 1 - but header from file 1 wanted [Re: Pax]
Richard H. Administrator Offline
Administrator
*****

Registered: 2000-01-24
Posts: 4945
Loc: Leatherhead, Surrey, UK
Cheating's fine, sometimes good enough is ... good enough \:\)

Drop by and post if you'd like it looked at again.
Top
Page 1 of 1 1


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

Who's Online
1 registered (Allen) and 191 anonymous users online.
details
Newest Members
thequeen, ameliaryan, shalomsalami, dkdssdk, Vroon
17639 Registered Users
My Cookies · Mark all read Powered by UBB.threads™ Contact Us · KiXtart.org website · Top

Generated in 0.03 seconds in which 0.011 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