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>*
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>*
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.
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=
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.