Results 1 to 6 of 6
  1. #1
    BeneDor is offline Novice
    Windows 10 Access 2013 32bit
    Join Date
    Sep 2017
    Posts
    3

    Question order file and order line Selection -raw data

    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'

  2. #2
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Using VBA wouldn't be hard. But I'm not sure what you want to do.

    You say
    Given the raw data of 10,000 sales orders and 30,000 order lines in one file
    what type of file? Excel, Access, text, CSV????

    Are you wanting to write the data (each row that meets requirements) to a new text/CSV file? Append to an Access table? To an Excel workbook??

  3. #3
    BeneDor is offline Novice
    Windows 10 Access 2013 32bit
    Join Date
    Sep 2017
    Posts
    3
    the file is text

    the data selection can be option 1 to copy all selected data in no specific file e.g. a new sheet / file / access table

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    What exactly does the data represent to you? What are you planning to do with the "finalized" data?
    For example, are these outstanding orders for products? Or Orders that have not been properly invoiced and Paid?

    In 4-5 lines of simple English what is the issue--why do you want to do whatever it is you are trying to do?

    I see Massive text file----->PROCESS--->structured tables for ???

    Your sample data is not helpful in its current form. You need to explain/describe some of the lines.
    The pattern must mean something to you or your business.
    How consistent is the pattern?I think this is one of those cases where few sample records along with the results you expect would be helpful to readers and you.

    It reminds me of old teletype messages with all control characters included in the printed message

  5. #5
    BeneDor is offline Novice
    Windows 10 Access 2013 32bit
    Join Date
    Sep 2017
    Posts
    3
    I am surprised by the confusion, thus I withdraw my question. Thank you all for your time.

  6. #6
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Sorry you feel that way.

    You have to realize we know nothing about your problem/project except what you write/provide.
    You might have worked on the project for a long time and know it inside and out; we have just just heard if it....

    Personally, I don't want to spend time trying to come up with a solution and find out after the fact that I wasted my time because my solution was not in the correct file format or I parsed the data wrong because I didn't understand what was required. So I ask questions to clarify as much as possible what is wanted.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 03-07-2016, 05:02 PM
  2. Replies: 3
    Last Post: 02-09-2016, 04:36 PM
  3. Help generating line item number in order table
    By acenumber5 in forum Queries
    Replies: 18
    Last Post: 08-23-2012, 12:50 PM
  4. New Line (New Order) on same form
    By Gaccesses in forum Access
    Replies: 2
    Last Post: 07-11-2012, 12:44 PM
  5. Adding line numbers to a purchase order report
    By jordanturner in forum Reports
    Replies: 1
    Last Post: 12-08-2010, 08:25 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums