How to add a date (other txt) to each line in a saved report

Make comments, ask questions, or just complain about the software on this site. Or comment on any educational software.
Please note that by clicking on links that may appear in these posts that you may be leaving the Dale Harris Educational Software website and that the content of those sites is the sole resposibility of the authors of those sites.

Moderators:daleadmin, Dale Harris, Alan, Andrew

Post Reply
User avatar
brucef2112
Forum Regular
Posts:336
Joined:Mon Mar 06, 2006 11:19 pm
Location:Broward County, Floriduhh
Contact:
How to add a date (other txt) to each line in a saved report

Post by brucef2112 » Sun Mar 31, 2013 4:04 am

Ever wish each line in a DHPOS report also included the date?
DHPOS reports are great. Auto reports are even better! You can output them as text or as a CSV data file.
But what if you want to import the daily reports or Register Close report into a spreadsheet or database.
Each report has the date near the top but how can you "normalize" the data by associating each line in the report with a date?

With some clever use of some DOS commands, I put together a batch file to append a date (or any string) to the beginning of each line in a saved DHPOS report.
This works great with the auto reports because of Dale's file naming convention and saving them as CSV data files.
My immediate need was for the "E" report ED######.txt (stock sold) reports. But this will really work great with any text file, even the Close Register report.

Example of an "E" report from DHPOS saved as CSV. The date is in the 3rd line of report but that doesn't help us import into a database:

Code: Select all

"BEGIN REPORT"
"Returns within 30 days and a Receipt"
"TOTAL","01-01-2013 - - 16:47:58"
"STOCK #","DESCRIPTION","PIECES","$ AMOUNT"
7385401938,"Congress Plstc Golf Jmbo",1,16.99
8317639393,"Puppy In A Purse 7.5in",1,11.99
8576104511,"Gumby!",1,7.19
8576104512,"Pokey!",1,7.19
9168310539,"PZ-1000 Sweets & Treats",1,18.99
9351400041,"Sock Monkey Mag Draw",1,2
*<cut off here>*
After running the batch file you get a date and a comma added to the beginning of each line:

Code: Select all

01-01-2013,"BEGIN REPORT"
01-01-2013,"Returns within 30 days and a Receipt"
01-01-2013,"TOTAL","01-01-2013 - - 16:47:58"
01-01-2013,"STOCK #","DESCRIPTION","PIECES","$ AMOUNT"
01-01-2013,7385401938,"Congress Plstc Golf Jmbo",1,16.99
01-01-2013,8317639393,"Puppy In A Purse 7.5in",1,11.99
01-01-2013,8576104511,"Gumby!",1,7.19
01-01-2013,8576104512,"Pokey!",1,7.19
01-01-2013,9168310539,"PZ-1000 Sweets & Treats",1,18.99
01-01-2013,9351400041,"Sock Monkey Mag Draw",1,2
*<cut off here>*
You now have useful data to import into a database!

To get the results shown above, you would use the batch file like this:
Usage: AddTxt [drive:][path]filename "String_to_add"
Example: AddTxt.bat ED010113.TXT "01-01-2013,"   <-- note the comma after the date, within the quotes.
I am working with a CSV file, so to preserve the 'fields' in the file I need to include a comma after the date..
If you are working with a fixed width data, then you would need to pad your string with the appropriate spacing in the quoted string.

I like to always have my original data untouched, so the program just reads the original file and creates a copy with "_TxtAdd" appended to the file name. So the above would produce an output file named ED010113_TxtAdd.TXT (Sorry, if you are in the 8.3 file world still, you'll need to modify the code to maintain the short file names. The rest of us using windows will be just fine.)

Here's the code. Click Select All and copy it exactly as is and save as a .BAT file. I saved mine in a file called AddTxt.bat.
If you are using Windows NotePad to create the batch file, be sure to change the Save as type drop down from "Text Documents (*.txt)" to "All Files" and include .bat to the end of your file name.
:arrow: Or you can just download the batch file from here.

Code: Select all

::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
:Adds a text string onto the begining of every line in a file that is not blank.
:Save code as a .bat file. Example: AddTxt.bat
:usage: AddTxt [drive:][path]filename "String_to_add"
:If filename or string_to_add contain spaces, they must be in quotes
:example if AddTxt.bat and source are in same folder: AddTxt myfile.txt Hello
:example with spaces: AddTxt "C:\This Folder\my file.txt" "Hello World, today is 1/1/2013,"
:::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
@echo OFF
REM EMBER WHERE YOU GOT IT.
REM ARKABLY CLEVER CODE HACKED, SLICED, DICED BY BRUCE!

If [%1]==[] If [%2]==[] echo.USAGE: & echo %0 [drive:][path]filename "String_to_add" & Echo. & 

Echo Note if [path]filename OR String_to_add contain spaces, they MUST be in quotes. &goto:eof
Set _t0=%~dp1
Set _t1=%~nx1
Set _t2=%2
Set _t2=%_t2:"=%
set _t3=%~n1
set _t4=%~x1
set _t5=%_t3%_TxtAdd%_t4%

echo.
echo. Text To Add: "%_t2%"
echo. Source Path: "%_t0%"
echo. Input File : "%_t1%"
echo. Output File: "%_t5%"
echo.
echo. I'm Working.
echo. Please wait..........................

If EXIST %_t0%~_f0.txt del %_t0%~_f0.txt

For /F "usebackq skip=2 tokens=1* delims=]" %%A in (`find /v /n "" "%_t0%%_t1%"`) do (if 

[%%B]==[] (echo.>>%_t0%~_f0.txt) Else  echo.%_t2%%%B>>%_t0%~_f0.txt)

echo. Write output file: "%_t0%%_t5%"
rename %_t0%~_f0.txt "%_t5%"

if %ERRORLEVEL% NEQ 0 goto _cleanup

echo. Done!

:_cleanup
FOR /F "usebackq delims==" %%a in (`SET _`) DO SET %%a=
The batch file and the txt files do not have to be in the same folder. If they are not, you must specify the full path to the source data file.
Also if the file path or file name contain spaces, you must enclose it in quotes.
Example: AddTxt.bat "C:\This Folder\my file.txt" "Hello World, today is 1/1/2013,"

If you need help on how to use the program just type its name and hit [Enter] and it will show you the correct usage.
Example: AddTxt [Enter]
USAGE:
addtxt.bat [drive:][path]filename "String_to_add"
Note if [path]filename OR String_to_add contain spaces, they MUST be in quotes.


Because of Dale's clever report naming convention, this batch file can be modified or you could write another wrapper batch file to do a whole months of auto reports at one time! Example: for January reports - DoFiles.BAT ED01??13.TXT
EDIT 8-11-13: I have created the wrapper batch file. See the next post below for details.

And of course once all the January reports have dates in the "_AddTxt" versions you could then use the DOS copy command to copy them all in one file.
COPY ED01??13_AddTxt.TXT January_2013.txt

You now have a January_2013.txt file to import all of January's data into your database or spreadsheet at one time.

You can use this batch with any plan text file to add a text string to the front of each line (that is not blank) in the file.
Later,
Bruce

They that can give up essential liberty to obtain a little temporary safety deserve neither liberty nor safety."
Benjamin Franklin - Historical Review of Pennsylvania, 1759

User avatar
brucef2112
Forum Regular
Posts:336
Joined:Mon Mar 06, 2006 11:19 pm
Location:Broward County, Floriduhh
Contact:

Re: Process multiple DHPOS Auto Reports (saved as data CSV)

Post by brucef2112 » Sun Aug 11, 2013 3:16 pm

This is a wrapper batch file that will allow you to process a series of Auto Reports using the ADDTXT.BAT (see previous post).

In short, the ADDTXT.BAT will read a CSV file and create a copy that adds a date to each line (other text too) and saves to a new file. The CSV file now can be used in a database now that each line in the file has a date value added to each line. This wrapper batch file helps to automate the process of working with a series of Auto Reports that need to have a date column added to each of the report files.

So how does this wrapper batch work?
So if you have Auto Reports set up to run at the close of each day, you'll have a series of reports saved with a date formatted in the file's name. example: The Auto Reports will save the Total by Stock# report for Aug 11, 2013 as ED081113.TXT (if set to save as data).
At the end of a month you'll have up to 31 report files in your C:\POS\MyRegister\ folder.
You could run ADDTXT.BAT one at a time. Specifying the file name and the date to add to the data.
OR you could use this wrapper batch file and let it process the entire month at one time!
You do this by passing it the report name and a wildcard for the date.
So to process all 31 'Total by Stock#' reports for January 2013, you would type at the command line:
DOFILES.BAT C:\POS\MyRegister\ED01??13.TXT

It will parse the date from each file name and then pass it to ADDTXT.BAT which will create the new CSV file which will now include the report's date as the first column for all lines in the report.
In no time at all it will process them and you'll be on your way to importing them into your database!

You could even do a whole year by changing the wild card to:DOFILES.BAT C:\POS\MyRegister\ED????13.TXT

NOTE: you must have the ADDTXT.BAT file also.

Here's the code: Download DoFiles.bat from here, save it to your C:\POS\MyRegister\ folder, You'll also need to download the ADDTXT.BAT file from here.

Code: Select all

@ECHO OFF
SETLOCAL EnableDelayedExpansion
::Ver1.0
::Code by: bruce friedman 8/10/2013
:::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
:: This batch expects the ADDTXT.BAT to be in the same directory.
:: Go here for details about ADDTXT.BAT: 
:: http://www.dhpos.com/discuss/phpbb3/viewtopic.php?f=1&t=2713#p12359
:: Or download ADDTXT.BAT from here:http://www.pahandmahjongg.com/pos/ADDTXT.BAT
:: 
:: This batch allows multiple DHPOS Data Auto Report file(s) to be processed at one time.
:: It creates a date string based on the DHPOS Auto Reports file name format(rDmmddyy.TXT)
:: and uses it in congunction with ADDTXT.BAT to process a single or series of 
:: DHPOS Auto Reports by passing the report date to ADDTXT.BAT which adds it to the
:: begining of each line in the CSV file(xDmmddyy.TXT).
:: (See DHPOS documentation for how DHPOS Auto Report formats file names & where they're saved.)
::
:: You can use wildcards (?*) in file name to process multiple report files at one time.
:: Examples; To process all Auto 'ED' reports for January 2013 use ED01??13.TXT as
:: the report name(C:\POS\REG_NAME\ED01??13.TXT).
:: Or to process all 'ED' reports for the year 2013 use ED*13.TXT or ED????13.TXT.
:: Care should be used when specifying '*' in a file name, as it may process files you didn't
:: intend to include. Only you know what's in your specific POS file directory!
:: Note that ADDTXT.BAT doesn't change your original file. It reads it and creates a new copy,
:: so no harmed done by a wayward wildcard value. But still use care. I prefer to use the "?"
:::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::

IF [%1]==[] ECHO.USAGE: & ECHO %0 [drive:] [path] DHPOSAutoRptName.TXT ^| rr01??13.TXT & ECHO. & 

ECHO Please note: IF [path]filename contains spaces, they MUST be in quotes. & ECHO. &  ECHO You can 

use wildcards (?,*) in file name to process multiple & ECHO files at one time. Example, to process 

all 'ED' reports & ECHO for January 2013, use ED01??13.TXT as the report name. &GOTO:eof

for %%F in (%1) do (
	set _strx=%%~nF
	set _strM=!_strx:~2,2!
	set _strD=!_strx:~4,2!
	set _strY=!_strx:~6,2!
	REM For your location you may need to change the date format on next line.
        REM NOTE: THERE IS A COMMA AFTER THE DATE on the end!!
	REM This keeps your date as a new column in the CSV. Don't forget it!
	set _strRptDt="!_strM!/!_strD!/!_strY!,"
	Call ADDTXT.BAT %%~dpnxF !_strRptDt!
)


:_cleanup
REM Cleanup _vars from memory.
FOR /F "usebackq delims==" %%a in (`SET _`) DO SET %%a=
any questions, feel free to ask.....
Later,
Bruce

They that can give up essential liberty to obtain a little temporary safety deserve neither liberty nor safety."
Benjamin Franklin - Historical Review of Pennsylvania, 1759

Post Reply

Who is online

Users browsing this forum: No registered users and 38 guests