Reading The Last Record In a File

Post any question you may have in regards to GoAnywhere Director and let our talented support staff and other users assist you.
4 posts Page 1 of 1

GA_Forum

Posts: 11
Joined: Mon Nov 10, 2014 3:05 pm

Post by GA_Forum » Tue Dec 09, 2014 3:23 pm
I have a text file that consists of multiple detail records and one footer record (the detail and footer records have different layouts). I needed to create a project to check the file for a footer record and if there is a footer record, then I want to transfer the file to a different server. If there is not a footer record in the file, then I want to send an email notification stating that the file did not have a footer record and then I want to exit the project.

I created a project that does what I need, but it seem cumbersome to me. Basically I used the readFixedWidth task along with the writeFixedWidth task to generate an output rowset and then I check to see if there are any records in that rowset and if there are, then I know that there is a footer record in the file and I can upload it. If the file is empty, then I know there is no footer record in the file and I send the email notification. I've included this code below.

The first column in the text file will identify the record as either a detail record or a footer record. The first column for a footer record is '99', so I'd like to read just the last record in the file, and then check the first column of that record to see if it's equal to '99' and then proceed as needed. Is this possible?

I am using GoAnywhere Director, Version 4.6.2.
Code: Select all
<project name="code_for_gaSupport" mainModule="Main" version="2.0" logLevel="debug">

	<module name="Main">
		<!--Create temporary workspace-->

		<createWorkspace version="1.0" />

		<!--Log on to enQuesta production server (enqapp1) to get HomeServe file(s). The "get - file(s) from enqapp1" task contains a regex filter to select the correct file(s). The file(s) are placed in the temporary workspace.-->

		<sftp label="sftp to enqapp1" resourceId="enqapp1-sftp-ga" outputSessionId="SessionID" version="1.0" logLevel="debug">
			<get label="get file(s) from enqapp1" destinationDir="${system.job.workspace}" suffix="-${CurrentTimestamp(&apos;yyyyMMddhhmmss&apos;)}" destinationFilesVariable="LocFiles" numFilesDownloadedVariable="FileDwnldCount" processedSourceFilesVariable="RemoteFiles">
				<fileset dir="/test/sftp/send">
					<regexFilter>
						<include pattern="^testHSTransResp-[0-9]{8}.txt\Z" caseSensitive="true" />
					</regexFilter>
				</fileset>
			</get>
		</sftp>

		<forEachLoop itemsVariable="${LocFiles}" currentItemVariable="CurrentFile" disabled="false">

			<readFixedWidth inputFilesVariable="${CurrentFile}" outputRowSetVariable="outData" skipInvalidRecords="true" skipFirstRow="false" recordDelimiter="LF" version="1.0" disabled="false">
				<data>
					<column index="1" name="TransCode" size="2" />
					<column index="2" name="Date" size="8" />
					<column index="3" name="TransCode1" size="2" />
					<column index="4" name="Trans1Cnt" size="6" />
					<column index="5" name="TransCode2" size="2" />
					<column index="6" name="Trans2Cnt" size="6" />
					<column index="7" name="TransCode3" size="2" />
					<column index="8" name="Trans3Cnt" size="6" />
					<column index="9" name="TransCode4" size="2" />
					<column index="10" name="Trans4Cnt" size="6" />
					<column index="11" name="TransCode5" size="2" />
					<column index="12" name="Trans5Cnt" size="6" />
					<column index="13" name="TransCode6" size="2" />
					<column index="14" name="Trans6Cnt" size="6" />
					<column index="15" name="TransCode12" size="2" />
					<column index="16" name="Trans12Cnt" size="6" />
					<column index="17" name="TransCode14" size="2" />
					<column index="18" name="Trans14Cnt" size="6" />
					<column index="19" name="TransCode15" size="2" />
					<column index="20" name="Trans15Cnt" size="6" />
					<column index="21" name="TransCode29" size="2" />
					<column index="22" name="Trans29Cnt" size="6" />
					<column index="23" name="TotalCnt" size="6" />
					<column index="24" name="EndOfRecord" size="1" />
				</data>
			</readFixedWidth>


			<writeFixedWidth inputRowSetVariable="${outData}" outputFile="${system.job.workspace}\NewHSTransResp" includeHeadings="false" version="1.0" disabled="false" />


			<createFileList fileListVariable="fileList" numFilesFoundVariable="fileCount" version="1.0" disabled="false">
				<fileset dir="${system.job.workspace}" />
			</createFileList>

			<forEachLoop itemsVariable="${fileList}" currentItemVariable="thisFile" disabled="false">

				<setVariable name="FileSize" value="${thisFile:size}" version="2.0" />


				<setVariable name="FileName" value="${thisFile:name}" version="2.0" />


				<print label="print to log file" version="1.0">
					<![CDATA[${system.emptyString}
Found file: ${thisFile:name}]]>
				</print>

				<if condition="${FileName EQ "NewHSTransResp"}">
					<if condition="${FileSize > 0}">

						<print version="1.0">
							<![CDATA[${system.emptyString}
*** Footer record exists in file ${thisFile:name}. The file size is ${thisFile:size} ***]]>
						</print>

						<!--This is where the file would be uploaded to different server.-->
						<else>

							<print version="1.0">
								<![CDATA[${system.emptyString}
*** Footer record does not exists in file ${thisFile:name}. The file size is ${thisFile:size} ***]]>
							</print>

							<!--This is where an email would be sent.-->
						</else>
					</if>
				</if>
			</forEachLoop>
		</forEachLoop>
	</module>

</project>
Thank you in advance for any suggestions!

Support_Rick

Support Specialist
Posts: 592
Joined: Tue Jul 17, 2012 2:12 pm
Location: Phoenix, AZ

Post by Support_Rick » Tue Dec 09, 2014 5:21 pm
There is no way to just read the last record of the file.

You could create a temp SQL table and insert the Rowset into that temp table, then select where RecordID = 99. If it doesn't exists ... send email, if it does, process the file. This would be your easiest way to determine what you need. In most cases, you should have at least 1 of each record type.

So, after inserting the records into a Temp Table ... Select Count of each and see if all 3 are Greater Than or Equal to 1. If not, send email notifying that the table is missing records that are needed.

Otherwise, process as normal.

HTH...
Rick Elliott
Lead Solutions Consultant
(402) 944.4242
(800) 949-4696

GA_Forum

Posts: 11
Joined: Mon Nov 10, 2014 3:05 pm

Post by GA_Forum » Wed Dec 10, 2014 9:53 am
Thank you Rick. What does HTH... mean?

Support_Rick

Support Specialist
Posts: 592
Joined: Tue Jul 17, 2012 2:12 pm
Location: Phoenix, AZ

Post by Support_Rick » Wed Dec 10, 2014 10:01 am
HTH = Hope this helps
Rick Elliott
Lead Solutions Consultant
(402) 944.4242
(800) 949-4696
4 posts Page 1 of 1