Patrick O Patrick O - 3 months ago 13
Python Question

How to parse a single-column text file into a table using python?

I'm new here to StackOverflow, but I have found a LOT of answers on this site. I'm also a programming newbie, so i figured i'd join and finally become part of this community - starting with a question about a problem that's been plaguing me for hours.

I login to a website and scrape a big body of text within the b tag to be converted into a proper table. The layout of the resulting Output.txt looks like this:

BIN STATUS
8FHA9D8H 82HG9F RECEIVED SUCCESSFULLY AWAITING STOCKING PROCESS


INVENTORY CODE: FPBC *SOUP CANS LENTILS

BIN STATUS
HA8DHW2H HD0138 RECEIVED SUCCESSFULLY AWAITING STOCKING PROCESS
8SHDNADU 00A123 #2956- INVALID STOCK COUPON CODE (MISSING).
93827548 096DBR RECEIVED SUCCESSFULLY AWAITING STOCKING PROCESS


There are a bunch of pages with the exact same blocks, but i need them to be combined into an ACTUAL table that looks like this:

BIN INV CODE STATUS
HA8DHW2HHD0138 FPBC-*SOUP CANS LENTILS RECEIVED SUCCESSFULLY AWAITING STOCKING PROCESS
8SHDNADU00A123 FPBC-*SOUP CANS LENTILS #2956- INVALID STOCK COUPON CODE (MISSING).
93827548096DBR FPBC-*SOUP CANS LENTILS RECEIVED SUCCESSFULLY AWAITING STOCKING PROCESS
8FHA9D8H82HG9F SSXR-98-20LM NM CORN CREAM RECEIVED SUCCESSFULLY AWAITING STOCKING PROCESS


Essentially, all separate text blocks in this example would become part of this table, with the inv code repeating with its Bin values. I would post my attempts at parsing this data(have tried Pandas/bs/openpyxl/csv writer), but ill admit they are a little embarrassing, as i cannot find any information on this specific problem. Is there any benevolent soul out there that can help me out? :)

(Also, i am using Python 2.7)

Answer

A simple custom parser like the following should do the trick.

from __future__ import print_function



def parse_body(s):
    line_sep = '\n'
    getting_bins = False
    inv_code = ''
    for l in s.split(line_sep):
        if l.startswith('INVENTORY CODE:') and not getting_bins:
            inv_data = l.split()
            inv_code = inv_data[2] + '-' + ' '.join(inv_data[3:])
        elif l.startswith('INVENTORY CODE:') and getting_bins:
            print("unexpected inventory code while reading bins:", l)
        elif l.startswith('BIN') and l.endswith('MESSAGE'):
            getting_bins = True
        elif getting_bins == True and l:
            bin_data = l.split()
            # need to add exception handling here to make sure:
            # 1) we have an inv_code
            # 2) bin_data is at least 3 items big (assuming two for
            #    bin_id and at least one for message)
            # 3) maybe some constraint checking to ensure that we have
            #    a valid instance of an inventory code and bin id
            bin_id = ''.join(bin_data[0:2])
            message = ' '.join(bin_data[2:])
            # we now have a bin, an inv_code, and a message to add to our table
            print(bin_id.ljust(20), inv_code.ljust(30), message, sep='\t')
        elif getting_bins == True and not l:
            # done getting bins for current inventory code
            getting_bins = False
            inv_code = ''
Comments