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

Question

I am new to StackOverflow, but I have found many answers on this site. I am also new to programming, so I decided that I was joining and finally becoming a part of this community - starting with the question about the problem that kept me busy for hours.

I went to the site and cleared a large piece of text in the b tag, which needs to be converted to the correct table. The layout of the resulting Output.txt file 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 same blocks, but I need them to be combined into an ACTUAL table, which 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  

, , inv Bin. ( Pandas/bs/openpyxl/csv writer), , , , . - , ?:)

( , Python 2.7)

Answer 1

, , .

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 = ''

Answer 2

, :

import re, pandas as pd
from pandas import DataFrame

rx = re.compile(r'''
                (?:INVENTORY\  CODE:)\s*
                (?P<inv>.+\S)
                [\s\S]+?
                ^BIN.+[\n\r]
                (?P<bin_msg>(?:(?!^\ ).+[\n\r])+)
                ''', re.MULTILINE | re.VERBOSE)

string = your_string_here

# set up the dataframe
df = DataFrame(columns = ['BIN', 'INV', 'MESSAGE'])

for match in rx.finditer(string):
    inv = match.group('inv')
    bin_msg_raw = match.group('bin_msg').split("\n")

    rxbinmsg = re.compile(r'^(?P<bin>(?:(?!\ {2}).)+)\s+(?P<message>.+\S)\s*$', re.MULTILINE)
    for item in bin_msg_raw:
        for m in rxbinmsg.finditer(item):
            # append it to the dataframe
            df.loc[len(df.index)] = [m.group('bin'), inv, m.group('message')]

print(df)

INVENTORY CODE (inv bin_msg) afterwork() ( : , bin/msg ).
bin msg df.

Answer 3

I had code written to recycle a website that might help you. Basically, what you need to do is write a click on a web page, go to html and try to find the tag for the table you are looking for and use the module (I use a beautiful soup) to extract the information. I am creating json as I need to save it in mongodb which you can create a table.

#! /usr/bin/python

import sys
import requests
import re
from BeautifulSoup import BeautifulSoup
import pymongo

def req_and_parsing():
        url2 = 'http://businfo.dimts.in/businfo/Bus_info/EtaByRoute.aspx?ID='

        list1 = ['534UP','534DOWN']
        for Route  in list1:
                final_url = url2 + Route
                #r = requests.get(final_url)
                #parsing_file(r.text,Route)

        outdict = []
        outdict = [parsing_file( requests.get(url2+Route).text,Route)  for Route in list1 ]
        print outdict
        conn = f_connection()
        for i in range(len(outdict)):
                insert_records(conn,outdict[i])


def parsing_file(txt,Route):
        soup = BeautifulSoup(txt)
        table = soup.findAll("table",{"id" : "ctl00_ContentPlaceHolder1_GridView2"})
        #trtags = table[0].findAll('tr')

        tdlist = []
        trtddict = {}
        """
        for trtag in trtags:
                print 'print trtag- ' ,  trtag.text
                tdtags =  trtag.findAll('td')
                for tdtag in tdtags:
                        print tdtag.text
        """

        divtags = soup.findAll("span",{"id":"ctl00_ContentPlaceHolder1_ErrorLabel"})

        for divtag in divtags:
        for divtag in divtags:
                print "div tag - " , divtag.text
                if  divtag.text ==  "Currently no bus is running on this route" or "This is not a cluster (orange bus) route":
                        print "Page not displayed Errored with below meeeage for Route-", Route," , " , divtag.text
                        sys.exit()

        trtags = table[0].findAll('tr')
        for trtag in trtags:
                tdtags =  trtag.findAll('td')
                if len(tdtags) == 2:
                        trtddict[tdtags[0].text] = sub_colon(tdtags[1].text)
        return trtddict


def sub_colon(tag_str):
        return re.sub(';',',',tag_str)

def f_connection():
        try:
                conn=pymongo.MongoClient()
                print "Connected successfully!!!"
        except pymongo.errors.ConnectionFailure, e:
                print "Could not connect to MongoDB: %s" % e
        return conn

def insert_records(conn,stop_dict):
        db = conn.test
        print db.collection_names()
        mycoll = db.stopsETA
        mycoll.insert(stop_dict)

if __name__ == "__main__":
        req_and_parsing()