Solved

Create Header and Trailer Record in Text File

Posted on 2014-02-06
6
2,176 Views
Last Modified: 2014-02-07
I am thinking a batch file script can do this but I am open to suggestions...

I have a text file where I need to create a header row and trailer row. The header row is basic and does not require "reading" the text file. The footer row has a record count field.

Header:

Record Type - always "HD" varchar(2)
File Date - Current Date in YYYYMMDD format datetime(8)
Upload Date - Current Date in YYYYMMDD format datetime(8)
Filler - Blank fill remaining line to position 64

Footer:

Record Type - always "TR" varchar(2)
File Date - Current Date in YYYYMMDD format datetime(8)
Upload Date - Current Date in YYYYMMDD format datetime(8)
Count - Number of non header/trailer records in file varchar(9) [right justified zero fill]
Filler - Blank fill remaining line to position 65

My instructions also indicate there must be a carriage return after the trailer record.

I will use a batch file to save the files, run the queries, and FTP the file so I am hoping it is something I can incorporate into the same batch file.
HelperFile.txt
0
Comment
Question by:sparker1970
6 Comments
 
LVL 43

Accepted Solution

by:
Steve Knight earned 250 total points
Comment Utility
You can get the number of lines in the file easily with
@echo off
for /f %%a in ('find /c /v "#" ^< HelperFile.txt') do set LineCount=%%a

I'd use code from VBScript to be sure on the dates formatting rather than using %date% as this is based on the current user's date formats as in my article here:

http://www.experts-exchange.com/OS/Microsoft_Operating_Systems/MS_DOS/A_1153-Using-dates-in-batch-files-scripts.html

yyyymmdd.cmd is a start:

You might aswell get that to format the line for you, or could do it all in VBScript but if you want batch:

@echo off
cd /d d:\
Set Source=HelperFile.txt
Set Dest=DestFile.txt

REM Get count of lines in source file
for /f %%a in ('find /c /v "#" ^< "%Source%"') do set LineCount=%%a

REM Construct header and footer lines using VBScript
echo yyyymmdd = year(date) ^& right(100 + month(date),2) ^& right(100+day(date),2)> "%temp%\output.vbs"
echo wscript.echo chr(34) ^& "HD" ^& yyyymmdd ^& yyyymmdd ^& space(35) ^& chr(34) ^& "," ^& _>> "%temp%\output.vbs"
echo chr(34) ^& "FT" ^& yyyymmdd ^& yyyymmdd ^& right("000000000%LineCount%",9) ^& space(27) ^& chr(34)>> "%temp%\output.vbs"

REM Extract output of VBScript into two variables Header and Footer
for /f "tokens=1,2 delims=," %%a in ('cscript //nologo "%temp%\output.vbs"') do set header=%%~a&set footer=%%~b

REM Show these on the screen, remove if wanted
echo Example of output
echp =================
echo          1         2         3         4         5         6         7    
echo 123456789 123456789 123456789 123456789 123456789 123456789 123456789 12345
echo Footer is [%footer%]
echo Header is [%header%]

REM Write out to destination file
echo %Header%>"%Dest%"
Type "%Source%">>"%Dest%"
echo %Footer%>>"%Dest%"

REM Show destination file in Notepad, remove if wanted
START notepad "%Dest%"

Open in new window

0
 
LVL 51

Assisted Solution

by:Bill Prew
Bill Prew earned 250 total points
Comment Utility
For a little variety, here's how I might approach it.  Clearly the core is the same, it's just the approach to building the header and footer that are quite different.  See what you think.

@echo off
setlocal EnableDelayedExpansion

REM Define input and output files
set InFile=c:\temp\helperfile.txt
set OutFile=c:\temp\outfile.txt

REM Define templates for header and footer lines to add
set "Header=HD[DDDDDD][DDDDDD]                                              "
set "Footer=TR[DDDDDD][DDDDDD][CCCCCCC]                                     "

REM Get current date in YYYYMMDD format
set Stamp=%DATE:~-4%%DATE:~-10,2%%DATE:~-7,2%
set Stamp=%Stamp: =0%

REM Get count of lines in source file
for /f %%A in ('find /c /v "" ^< "%InFile%"') do set Lines=00000000%%A

REM Write out to destination file
(
  REM Insert Date into header, write it out
  set Header=!Header:[DDDDDD]=%Stamp%!
  echo !Header!

  REM Write out input file
  type "%InFile%"

  REM Insert Date and Lines into footer, write it out
  set Footer=!Footer:[DDDDDD]=%Stamp%!
  set Footer=!Footer:[CCCCCCC]=%Lines:~0,9%!
  echo !Footer!
) > "%OutFile%"

Open in new window

~bp
0
 

Author Closing Comment

by:sparker1970
Comment Utility
Thanks for the responses and excellent solutions. I tried both of your solutions and they worked perfectly. The REM statements were very helpful in describing the steps and pointing me to areas in case I want to make enhancements.
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 16

Expert Comment

by:AlexPace
Comment Utility
Here it is implemented as a Robo-FTP script that makes the file and then sends it to a remote server.  I added the ability to pass the source and destination filenames on the command line.  It also verifies that the first row in the source file does not begin with HD and have exactly 64 characters because that would indicate that the source file had already been processed.  
:identify_source_file
SET source_file = "HelperFile.txt"  ;; default source filename
SET source_file = %1  ;; allow passing source file name on command line
GETNEXTFILE source_file 
IFERROR GOTO done

:verify_source_file
READFILE ;; reset file pointer 
READFILE source_file rec
IFERROR GOTO done
SETLEFT two_chars = rec 2
IFnSTRCMPI two_chars "HD" GOTO identify_output_file
SETLEN rec_length = rec 
IFNUM!= rec_len 64 GOTO identify_output_file
;; Assume this source file has already be processed since 
;; the first row begins with "HD" and is exactly 64 chars!
GOTO done 

:identify_output_file
SET output_file = source_file + ".out"
SET output_file = %2  ;; allow passing output file name on command line

:build_formatted_date_string
SET today_raw = %date ;; get today's date
SETEXTRACT month = today "-" 1
SETEXTRACT day = today "-" 2
SETEXTRACT year = today "-" 3
SET today_formatted = "20" + year + month + day

:count_source_file_rows
READFILE ;; reset file pointer 
READFILE source_file rec /record=last
SET rows = "000000000" + %recordcount   ;; add zero padding
SETRIGHT rows = rows 9  ;; discard all but the last 9 chars

:build_header_and_footer
SET filler = "                                                  "
SET header = "HD" + today_formatted + today_formatted + filler
SETLEFT header = header 64  ;; discard all but first 64 chars
SET footer = "TR" + today_formatted + today_formatted + rows + filler
SETLEFT footer = footer 65  ;; discard all but first 65 chars

:make_output_file
WRITEFILE output_file header
APPEND source_file output_file
WRITEFILE output_file footer /append 

:upload_output_file
FTPLOGON "ftp.somedomain.com" /user="UserID" /pw="secret" 
SENDFILE output_file
FTPLOGOFF 

:done
EXIT 

Open in new window

I was gonna add an automatic retry loop for the FTP transfer and archiving of the source file but I got hungry.
0
 
LVL 43

Expert Comment

by:Steve Knight
Comment Utility
Glad it helped,

Steve
0
 
LVL 51

Expert Comment

by:Bill Prew
Comment Utility
Welcome.

~bp
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

It is becoming increasingly popular to have a front-page slider on a web site. Nearly every TV website,  magazine or online news has one on their site, and even some e-commerce sites have one. Today you can use sliders with Joomla, WordPress or …
I hope you'll find this tutorial useful and interesting. So let's try to extend Tcl with a new package.  For anyone more deeply interested please check out the book "Practical Programming in Tcl and Tk". It's really one of the best written books abo…
Learn the basics of modules and packages in Python. Every Python file is a module, ending in the suffix: .py: Modules are a collection of functions and variables.: Packages are a collection of modules.: Module functions and variables are accessed us…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now