Hi,
Given the raw data of 10,000 sales orders and 30,000 order lines in one file, structured to the below shown pattern, I would need to find an approach or solution to the following task ( Excel Access or VBA would be fine- one time task):
all known orders (2000) are in a separate file or sheet. all order data and order line data can be found in one txt file.
option 1
IF sales header contains in row 2 a known order number here: 5011111
Then take all 10 rows of sales header
and if order line contains in row 1 of orderlines 5123123123123
then take all 6 rows of this specific order line
check all order lines
take the two row end of the first sales order
copy all taken data into a new file, so that sales header (10 rows) is followed by the selected one order line (6 rows) and ended by 2 rows.
repeat for all sales orders
Option 2 would be to erase all orders and orderliness if the order is not known,
or if the order is known and the order line is not identified by the number 5123123123123 ( its only this number) than erase all 6 rows of this order line.
Which option would you prefer and what could be a manageable solution. Will it be hard? Do you think this can be programmed?
PATTERN:UNH+12092+ORDERS
:96A:UN:EAN008' {Sales Header-Start}
BGM+220::9+5011111+9'
DTM+137:20170912:102'
DTM+2:20170912'
NAD+BY+42123::9'
RFF+API:4123'
NAD+SU+4031234000001::9'
CTA+SR+3344'
CUX+2:EUR:9'
PAT+22++66' {Sales Header-End 10 rows}
LIN+2++5123123123123:EN' {Order line-Begin}
PIA+1+8888888:IN'
QTY+21:12'
PRI+AAB:100.62'
ALC+A+TD:9'
PCD+3:99.00' {Orderline 1-End –6 rows always }
LIN+3++5999999999999:EN' { next order line n, upto (1-40) }
PIA+1+9999999:IN'
QTY+21:6'
PRI+AAB:30.77'
ALC+A+TD:9'
PCD+3:88.00'
UNS+S' {Sales Order end first order}
UNT+24+12092017090201' {Sales Order end first order}
UNH+12092017090201+ORDERS
:96A:UN:EAN008'
{Begin Second Order}
BGM+220::9+5777777+9'
DTM+137:20170912:102'