#196420 - 2009-10-21 12:54 PM
Consolidating multiple files into 1 - but header from file 1 wanted
|
Pax
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.
$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
   
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:
$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
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.
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
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
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
   
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
|
|
|
|
Moderator: Jochen, Allen, Radimus, Glenn Barnas, ShaneEP, Arend_, Mart
|
0 registered
and 190 anonymous users online.
|
|
|