?
Solved

Create Header and Trailer Record in Text File

Posted on 2014-02-06
6
Medium Priority
?
2,487 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 43

Accepted Solution

by:
Steve Knight earned 1000 total points
ID: 39840617
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 56

Assisted Solution

by:Bill Prew
Bill Prew earned 1000 total points
ID: 39840780
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
ID: 39842889
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
Setting up LaraDock for Laravel

Learn how to set up LaraDock in a Laravel project - LaraDock gives us an easy way to run a Laravel application using Docker in a single command.

 
LVL 16

Expert Comment

by:AlexPace
ID: 39842931
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
ID: 39843149
Glad it helped,

Steve
0
 
LVL 56

Expert Comment

by:Bill Prew
ID: 39843483
Welcome.

~bp
0

Featured Post

Learn by Doing. Anytime. Anywhere.

Do you like to learn by doing?
Our labs and exercises give you the chance to do just that: Learn by performing actions on real environments.

Hands-on, scenario-based labs give you experience on real environments provided by us so you don't have to worry about breaking anything.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This is a fine trick which I've found useful many times, when you just don't want to accidentally run a batch script or the commands needs administrator rights.
Originally, this post was published on Monitis Blog, you can check it here . In business circles, we sometimes hear that today is the “age of the customer.” And so it is. Thanks to the enormous advances over the past few years in consumer techno…
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…
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.
Suggested Courses

800 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