Solved

Create Header and Trailer Record in Text File

Posted on 2014-02-06
6
2,418 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 250 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 55

Assisted Solution

by:Bill Prew
Bill Prew earned 250 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
The Ultimate Checklist to Optimize Your Website

Websites are getting bigger and complicated by the day. Video, images, custom fonts are all great for showcasing your product/service. But the price to pay in terms of reduced page load times and ultimately, decreased sales, can lead to some difficult decisions about what to cut.

 
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 55

Expert Comment

by:Bill Prew
ID: 39843483
Welcome.

~bp
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Recently I have been answering a lot of questions like this in IT forums that I frequent. The question posed is usually something along the lines of "We have software X installed and need to uninstall it for reason Y" or some other variant of the sa…
When you receive another warning that your shared drive is almost full and you have asked your users to clean out old files again and again, here is a single command that may help. This command will place all the files that have not been used rec…
Learn the basics of strings in Python: declaration, operations, indices, and slicing. Strings are declared with quotations; for example: s = "string": Strings are immutable.: Strings may be concatenated or multiplied using the addition and multiplic…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

717 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