I’ve been reading a lot but cant get this to work. found a script on EE but I don’t know what I’m doing. I need a script to open a Text file (it has a RPT extension…), search for VISA for example and copy that line with the $100 on a separate column on a CSV file.
What I need is a Report (In Receipt formatting) to be dissected and extract some totals from it and copy that to a new file with the date. would automate some things using Cobian. Its an old POS system.
Dim oFSO
Dim str_Input
Dim str_Output
Dim str_CurrentLine
Dim str_CSV
' the path to the input and output file
str_Input = "Z_TAPE.txt"
str_Output = "output.csv"
Set oFSO = CreateObject("Scripting.FileSystemObject")
' If the file exists
If oFSO.FileExists(str_Input) Then
' open the text file for input
Set oInFile = oFSO.OpenTextFile(str_Input)
' create the csv file for output
Set oOutFile = oFSO.CreateTextFile(str_Output)
' for each line in the input file
While Not oInFile.AtEndOfStream
' read the line
str_CurrentLine = oInFile.ReadLine()
' if the string total is found in the file then remove it and trim of any white spaces
If inStr(str_CurrentLine,"total") >0 Then
str_CSV = str_CSV &Trim(replace(str_CurrentLine,"location:","")) &","
' if the string date AMEX is found in the file then remove it and trim of any white spaces
ElseIf inStr(str_CurrentLine,"AMEX") >0 Then
str_CSV = str_CSV &Trim(replace(str_CurrentLine,"date booked:","")) &","
' if the string VISA is found in the file then remove it and trim of any white spaces
ElseIf inStr(str_CurrentLine,"VISA") >0 Then
str_CSV = str_CSV &Trim(replace(str_CurrentLine,"datetravel:",""))
End If
WEnd
' output the data to the csv file
oOutFile.WriteLine(str_CSV)
' Close the input and output file
oOutFile.Close
oInFile.Close
End IF
' Tidy up
Set oFSO = Nothing
StoreKare POS System
Z-Tape by Date Report for 02/13/17
02/13/17 09:50:57
Store ID: 0023
Store Name: SK TECHNOLOGIES CORP.
============Z-TAPE DETAIL=============
Date 02/13/17 Time 09:45 Rec # 1
Clerk 123 Register 0 D1
--------------------------------------
==== CASH IN RECEIPT ====
DESCRIPTION
Clerk "123 "
Drawer 1 $100.00
======================================
Date 02/13/17 Time 09:45 Rec # 2
Clerk 123 Register 0 D1
--------------------------------------
MISC ** MISC ITEM **
M 1@ $ 20.00 $ 20.00
Sub-Total $ 20.00
Misc.Charge $ 0.00
Discount $ 0.00
Tax $ 0.00
Total $ 20.00
Cash Amount $ 20.00
Balance Due $ 0.00
Total Payments $ 20.00
======================================
Date 02/13/17 Time 09:47 Rec # 3
Clerk 123 Register 0 D1
--------------------------------------
==== CASH IN RECEIPT ====
DESCRIPTION
Clerk "123 "
Drawer 1 $100.00
======================================
Date 02/13/17 Time 09:47 Rec # 4
Clerk 123 Register 0 D1
--------------------------------------
MISC ** MISC ITEM **
M 1@ $ 20.00 $ 20.00
Sub-Total $ 20.00
Misc.Charge $ 0.00
Discount $ 0.00
Tax $ 0.00
Total $ 20.00
Cash Amount $ 20.00
Balance Due $ 0.00
Total Payments $ 20.00
======================================
Date 02/13/17 Time 09:48 Rec # 5
Clerk 123 Register 0 D1
--------------------------------------
MISC ** MISC ITEM **
M 1@ $ 20.00 $ 20.00
Sub-Total $ 20.00
Misc.Charge $ 0.00
Discount $ 0.00
Tax $ 0.00
Total $ 20.00
Check Amount $ 20.00
Drvrs Lic # 121231213 22
Bank # 1235245463453221
Balance Due $ 0.00
Total Payments $ 20.00
======================================
Date 02/13/17 Time 09:49 Rec # 6
Clerk 123 Register 0 D1
--------------------------------------
==== CREDIT CARD CHARGE ====
MISC ** MISC ITEM **
M 1@ $ 20.00 $ 20.00
Sub-Total $ 20.00
Misc.Charge $ 0.00
Discount $ 0.00
Tax $ 0.00
Total $ 20.00
Credit Card Number
433333333333333333 $ 20.00
Balance Due $ 0.00
Total Payments $ 20.00
======================================
============Z-TAPE SUMMARY============
Date 02/13/17
======================================
Number of Receipts 6
First Receipt Number 1
Last Receipt Number 6
Taxable Sales $ 80.00
Non-taxable Sales $ 0.00
============
Gross Sales $ 80.00
Gift Cert Sold $ 0.00
Misc. Charges $ 0.00
Tax $ 0.00
Gift Cert Redeemed $ 0.00
============
Total Income $ 80.00
Tot. Sale Discount $ 0.00
--------------------------------------
TENDER TYPE AMOUNT PCT TRANS
Cash Amount $ 40.00 50 2
Check Amount $ 20.00 25 1
GIFT Amount $ 0.00 0 0
MISC Amount $ 0.00 0 0
=======
Sub-Total $ 60.00 75
Credit Card Tender
ATH2 $ 0.00 0 0
AMEX $ 0.00 0 0
VISA $ 20.00 25 1
MASTER CARD $ 0.00 0 0
MULTI MALL $ 0.00 0 0
ATH $ 0.00 0 0
======= ====
CC Sub-Total $ 20.00 25 1
=======
Tender Total $ 80.00
REFUNDS
Cash Refund $ 0.00 0
Check Refund $ 0.00 0
GIFT Refund $ 0.00 0
MISC Refund $ 0.00 0
======= ====
Sub-Total $ 0.00
Credit Card Refund
ATH2 $ 0.00 0
AMEX $ 0.00 0
VISA $ 0.00 0
MASTER CARD $ 0.00 0
MULTI MALL $ 0.00 0
ATH $ 0.00 0
======= ====
CC Sub-Total $ 0.00 0
======= ====
Refund Total $ 0.00 0
EXCEPTIONS
Paid In Cash $ 0.00 0
Paid In Check$ 0.00 0
Paid Out $ 0.00 0
Cash Drop $ 0.00 0
Store Transfer 0
No Sale 0
Voided Receipts 0
Suspended Receipts 0
Resumed Receipts 0
Modified Receipts 0
Unused Receipts 0
TAX TABLE SALES TAXES
No Tax Table 0.00 0.00
ESTATAL 10.5% 0.00 0.00
MUNICIPAL 1% 0.00 0.00
NO TAX / NO IV 80.00 0.00
Food Stamp 0.00 0.00
=========
0.00
====== THE END OF Z-TAPE BY DATE =====
3 Spice ups
Neally
(Neally)
2
…and what has this to do with powershell?
Do you want to use powershell, rather than VB for that?
Neally
(Neally)
3
If you’d be looking for ‘VISA’ you would not get the$100 though.
Get-Content C:\input.rpt |
Select-String -Pattern "VISA"
VISA $ 20.00 25 1
VISA $ 0.00 0
martin9700
(Martin9700)
4
Cross posted to IT Programming, we can remove the PowerShell group if you want to stay with vbScript (not recommended, but hey). Pretty simple with PowerShell, doable in vb but a bit more complex (isn’t everything).
1 Spice up
VB, didn’t find a Category for VB, and it suggested Powershell… not sure where to put it… It can be with Powershell, but since they are XP Computers would prefer to use VB so I don’t have to install Powershell. but If you can help me to get this going we can install it manually, there are only like 40 computers out of domain I would have to do this.
the $100 was an example. here it would be $20. but also would like to get CASH, Check, AMEX and the others…
martin9700
(Martin9700)
6
Are you going to be running the script on each POS machine, or pulling the files into a central location for processing?
It would run on each machine so the exported CSV would get uploaded to FTP to a specific folder by Store and by Date.
martin9700
(Martin9700)
8
Ok, looks like you’re sticking with vb (XP, really?! lol)
I would solve this with RegEx. In PowerShell I used this pattern to find the “VISA” lines: “visa +$ +(?\d+.\d{2})”. Which sets the matches variable with the cash value ($Matches.Amt), taking advantage of RegEx’s ability to name matches. Pretty sure vb can do this too, or you can use the replace statement like you were to get rid of the cruft.
Good luck!
1 Spice up
Yes, its a Chain Store that prefers to stay with what they have. it is working great, barely any issues, but they are now asking for this report for accounting and I’m trying to make this work a bit automated to prevent human error. Like I said, would like to do this in VB for simplicity, they are all XP, but if you get this working with Powershell, I would install it on every machine. I just have no idea how to properly write this… I’m not a programmer, just a Copy\Paste Script kiddie. 
1 Spice up
davidauth
(David Auth)
10
Well, nice first post, 3.5 years in, haha! Welcome to the community!
Oblig .
You’re already in better hands than mine, so carry on.
1 Spice up
Yeah, I’ve always lurked in here… 3.5 years ago is when I decided to make the account, who know how much before… jeje.
