<gh_stars>10-100
#!/usr/bin/python
# -*- coding: utf-8 -*-

#
# Reads a schema specification and outputs at your option:
#  --dump --- the internal tables constructed by the schema.py package
#  --schema --- A nicely formatted SQL schema
#  --output_parser --- A Python class that will parser files formatted in the spec
#  --output_fake_data --- Fake data consistent with the spec.
#  --output_sas       --- A SAS definition for the spec
#
# You may optionally specify:
#  --ignore var1,var2,..    --- Ignore var1,var2 in the spec
#  --loglevel               --- Specifies the log level to read
#
#
# Supported input file formats:

# - Microsoft Word table layouts used by the Census Bureau, such as those used for the 2020 MDF.
# - Text files, such as those generated from the 2010 Census PDF files.
# - Excel Files, such as those for the 2020 CEF.
# - .SAS files generated by IPUMS. 
#
# Planned:
#
# XML DDI  support.
#

import os
import os.path
import pandas
import re
import sys
import logging
import time
import json

from schema import Range,Variable,Table,Schema,Recode,TYPE_VARCHAR,TYPE_INTEGER,vtype_for_numpy_type,convertRange
from word_table_reader import *

from ctools.dconfig import dopen

# The SAS template is used to generate a parser

SAS_TEMPLATE="""OPTIONS NOCENTER PAGENO=1 SOURCE MPRINT;
FILENAME RAWDATA "{}";
DATA {};
  INFILE RAWDATA DELIMITER='|' DSD;
  INPUT {};
RUN;

PROC PRINT DATA={};
RUN;
"""

TXT_TITLE_RE    = re.compile("Title:\s+([a-zA-Z0-9_]+)\s\s\s\s\s")
TXT_VERSION_RE  = re.compile("Version:\s*([0-9.]+)")
# VARIABLE_RE is used to parse text files. The fields are:
# VARIABLE.1 = position
# VARIABLE.2 = name
# VARIABLE.3 = description
# VARIABLE.4 = type
# VARIABLE.5 = column
VARIABLE_RE = re.compile("\s*(\d+)[.] ([A-Z_0-9]+)\s+(.*\S)\s+([A-Z0-9]+\(\d+\))\s*(\d+-\d+)")


INPUT='input'
LABEL='label'

rectype_re = re.compile('if RECTYPE = "(.)" then do;')
ipums_layout_re  = re.compile("([A-Z,0-9_]+)[ $]*(\d+)-(\d+)")
label_re   = re.compile('([A-Z,0-9]+)\s+= "([^"]*)"')

class IPUMS_SASParser:
    @staticmethod
    def is_ipums_sas_file(filename):
        data = dopen(filename).read(1024*1024) # read at most 1MiB
        return (data[0:2]=='/*') and ('ipums_directory') in data and data[-100:].strip().endswith("run;")

    @staticmethod
    def process_layout_line(*,table,line):
        m = ipums_layout_re.search(line)
        try:
            (variable_name,start,end) = m.group(1,2,3)
        except AttributeError:
            raise RuntimeError("Cannot parse: "+line)
        (name,start,end) = m.group(1,2,3)
        v = Variable(name=name, vtype=TYPE_VARCHAR)
        v.set_column( int(start)-1, int(end)-1 )
        table.add_variable(v)

    @staticmethod
    def load_schema_from_ipums_sas_file(*,schema,filename):
        if not IPUMS_SASParser.is_ipums_sas_file(filename):
            raise RuntimeError("{} is not an IPUMS SAS file".format(filename))

        # Only process H and P record types
        rectypes = ['H','P']

        state   = None
        rectype = None
        table   = None
        labels  = dict()
        for line in dopen(filename):
            line = line.strip()
            if line==INPUT and rectype!=None:
                if state==INPUT:
                    raise RuntimeError("{}: INPUT within INPUT???".format(filename))
                state = INPUT
                table = Table(name=rectype)
                continue
            if line==';':
                if state==INPUT:
                    if rectype:
                        schema.add_table(table)
                state = None    # end of SAS statement
                continue
            if line==LABEL:
                state = LABEL
                continue
            if state==INPUT:
                IPUMS_SASParser.process_layout_line(table=table,line=line)
            if state==LABEL:
                m = label_re.search(line)
                if m:
                    labels[m.group(1)] = m.group(2)
                    
            m = rectype_re.search(line)
            if m:
                rectype = m.group(1)
                if rectype not in rectypes:
                    raise RuntimeError("Record type '{}' not in list of approved rectypes ({})".
                                       format(rectype,str(rectypes)))

        # Now use the labels to set the description for all of the variables we have learned
        for table in schema.tables():
            for v in table.vars():
                if v.name in labels:
                    v.desc = labels[v.name]


class CensusSpec(Schema):
    """A subclass of Schema that understands how to read Census specification files in .docx and .txt format"""

    debug = False
    CENSUS_COLUMN_HEADERS={0:"COLUMN ID",
                           1:"COLUMN NAME",
                           2:"ORACLE DATATYPE",
                           3:"DESCRIPTION",
                           4:"LEGAL VALUES"}


    @staticmethod
    def is_variable_start(docx_row):
        """Returns True if row is the start of a variable"""
        if len(docx_row)<5:
            return False        # need at least 4 rows
        cols = [r.strip() for r in docx_row] # remove spaces
        if len(cols[0]) not in [1,2,3]:
            return False # We only allow tables with 1..999 variables
        if any([ch.isdigit()==False for ch in cols[0]]):
            return False        # all characters must be digits
        if len(cols[1])==0:
            return False # must have a name
        if " " in cols[1]:
            return False # name may not have any spaces
        return True

    @staticmethod
    def load_table_from_docx_table(docx_table):
        """Returns a table name and the list of the variables"""
        tableName = docx_table[0][0].replace(" ","_").replace(".","_")
        if CensusSpec.debug:
            print("DEBUG: Reading table '{}'".format(tableName))
        table = Table(name=tableName)
        v = None                    # current variable
        for row in docx_table[1:]:        # don't process the first line
            cols = [x.replace("\n"," ").strip() for x in row] # change all newlines to spaces
            if sum([len(x) for x in cols])==0:                # blank row
                continue
            if CensusSpec.debug:
                print("DEBUG:  cols: {}".format(cols))
            if CensusSpec.is_variable_start(cols):
                if CensusSpec.debug:
                    print("DEBUG:    defining variable {}".format(cols[0]))
                v = Variable( position = cols[0],
                              name   = cols[1],
                              desc   = cols[2],
                              vtype  = cols[3])

                # if there is a fifth column, it may be allowable values
                if len(cols)==5: 
                    v.add_valid_data_description(cols[4])
                table.add_variable(v)
                continue

            # If defining a variable and we have an extra cols, it may have an allowable value
            # in the cols[2] or cols[4]
            if v:
                if len(cols)>2 and len(cols[2]):
                    v.add_valid_data_description(cols[2])
                    contine
                if len(cols)>4 and len(cols[4]):
                    v.add_valid_data_description(cols[4])
                    continue
                print("Not sure what to do with this:",cols)
                assert False
        return table


    def __init__(self,name=''):
        super().__init__(name=name)
        self.docx_tables = []

    def load_schema_from_file(self,filename,prefix=""):
        logging.info("load_schema_from_file(%s)",filename)
        print("a:",IPUMS_SASParser.is_ipums_sas_file(filename))
        if filename.endswith(".docx"):
            return self.load_table_schema_from_docx_file(filename=filename,prefix=prefix)
        elif filename.endswith(".txt"):
            t = self.load_table_schema_from_census_txt_spec(filename=filename,prefix=prefix)
            if t:
                return t
        elif filename.endswith(".sas") and IPUMS_SASParser.is_ipums_sas_file(filename):
            return IPUMS_SASParser.load_schema_from_ipums_sas_file(schema=self,filename=filename)
        elif filename.endswith(".xlsx"):
            return self.load_table_schema_from_census_xlsx_file(filename=filename,prefix=prefix)
        return super().load_schema_from_file(filename)


    def load_table_schema_from_docx_file(self,*,filename,prefix=""):
        """Read multiple tables from a docx file. Only ingest the tables that have the prefix"""
        for docx_table in get_docx_tables(filename):
            data = get_text_for_table(docx_table)
            if CensusSpec.debug:
                print("DEBUG: Found table '{}' cols:{} rows:{}".format(data[0][0], len(data[0]), len(data)))
            if data and len(data)>2 and (data[1][0] in ['Var #','#']):
                if CensusSpec.debug:
                    print("DEBUG: ** processing table **")
                table = self.load_table_from_docx_table(data)
                if table.name.startswith(prefix):
                    table.add_comment("Parsed from {}".format(filename))
                    self.docx_tables.append(docx_table)
                    self.add_table(table)

    def verify_census_worksheet(self,ws):
        from openpyxl import load_workbook
        from openpyxl.cell.read_only import EmptyCell
        for row in ws.iter_rows():
            # Ignore empty rows
            if type(row[0])==EmptyCell:
                continue
            # Validate headers
            errors = 0
            for (col,label) in self.CENSUS_COLUMN_HEADERS.items():
                if row[col].value != label:
                    logging.error("Column %s: found '%s' expected '%s'",col,row[col].value,label)
                    errors += 1
            if errors:
                return False
            return True         # it validates
            
            

    def load_table_schema_from_census_xlsx_file(self,*,filename,prefix=""):
        """Read multiple tables from a xlsx file. Only ingest the tables that have the prefix. We only look at the first 5 columns"""
        from openpyxl import load_workbook
        from openpyxl.cell.read_only import EmptyCell
        wb = load_workbook(filename=filename, read_only='True')
        for ws in wb.worksheets:
            print("Processing worksheet {}".format(ws.title))
            if not self.verify_census_worksheet(ws):
                logging.error("This does not appear to be a US Census Bureau XLSX specification file")
                raise RuntimeError("This does not appear to be a US Census Bureau XLSX specification file")
            column = 0
            table = Table(name=ws.title)
            self.add_table(table)
            header_skipped = False
            for row in ws.iter_rows():
                # Skip empty rows
                if type(row[0])==EmptyCell: 
                    continue
                # skip the header
                if header_skipped==False:
                    header_skipped=True 
                    continue
                # Only the first five columns are considered
                values = []
                for i in range(0,5):
                    if type(row[i])==EmptyCell:
                        values.append("")
                    elif type(row[i].value)==str:
                        values.append(row[i].value.strip())
                    else:
                        values.append(row[i].value)
                (column_id,column_name,oracle_datatype,desc,legal_values) = values
                if (not column_id) and not (legal_values):
                    # Ignore the blank line
                    continue
                if column_id:
                    if type(column_id)!=int:
                        logging.info("Column ID is invalid; skipping")
                        continue
                    v = Variable(position=column_id, name=column_name, desc=desc, vtype=oracle_datatype, column=column)
                    # advance to the next column
                    column += v.width 
                    table.add_variable(v)
                if v and legal_values:
                    for possible_legal_value in legal_values.split("\n"):
                        r = Range.extract_range_and_desc(possible_legal_value)
                        if r:
                            v.add_range(r)
                        
        

    MAXLINES=20                 # max lines to read before giving up
    def load_table_schema_from_census_txt_spec(self,*,filename,prefix=""):
        """Read a single table from a txt file."""
        table = None
        for (ll,line) in enumerate(dopen(filename),1):
            if ll>self.MAXLINES:
                if (table==None) or len(table.vars())==0:
                    logging.info("{} is not a Census text specification".format(filename))
                    return None
                
            # Get a table name if we do not have one
            if not table:
                m = TXT_TITLE_RE.search(line)
                if m:
                    table = Table(name=m.group(1))
                    table.add_comment("Parsed from {}".format(filename))
                    continue
            # Get the table version if we do not have one
            if table and not table.version:
                m = TXT_VERSION_RE.search(line)
                if m:
                    table.version = m.group(1)
                    continue
            # Is this a variable name within the table?
            m = VARIABLE_RE.search(line)
            if m:
                (position,name,desc,vtype,column) = m.group(1,2,3,4,5)
                oname = name
                count = 2
                v = Variable(position=row[0], name=row[1], desc = row[2], vtype = row[3])
                while name in [v.name for v in table.vars()]:
                    name = "{}{}".format(oname,count)
                    count += 1
                if "-" in column:
                    v.column = [int(x) for x in column.split("-")]
                table.add_variable(v)
        if len(table.vars())==0:
            return None
        self.add_table(table)


"""This is a test program that is used for running the scanner with actual files."""

if __name__=="__main__":
    from argparse import ArgumentParser,ArgumentDefaultsHelpFormatter
    parser = ArgumentParser( formatter_class = ArgumentDefaultsHelpFormatter,
                             description="Demo program that prints details about a census spec file or SAS file. This shows how we can parse these files." )
    parser.add_argument('--debug', action='store_true')
    parser.add_argument('--dump',  action='store_true', help='Dump schema and internal functions; do not run the transfer')
    parser.add_argument('--schema', action='store_true', help='display the SQL schema')
    parser.add_argument('--default', help='Specify a default value and see what it looks like')
    parser.add_argument('--output_parser', help='If specified, output a python parser for the spec')
    parser.add_argument("--output_fake_data", action='store_true', help='if specificed, output fake data')
    parser.add_argument("--output_sas", help='If specified, output a SAS definition to this file')
    parser.add_argument("--ignore", help='ignore these comma-separated values variables', default='')

    parser.add_argument("--loglevel", help="Set logging level",
                        choices=['CRITICAL','ERROR','WARNING','INFO','DEBUG'],
                        default='INFO')    
    parser.add_argument('spec', help='Spec file; may be a .docx, .xlsx, .txt, or .sas7bat')

    args   = parser.parse_args()

    loglevel = logging.getLevelName(args.loglevel)
    logging.basicConfig(format="%(asctime)s %(filename)s:%(lineno)d (%(funcName)s) %(message)s", level=loglevel)
    logging.info("START {}  log level: {} ({})".format(os.path.abspath(__file__), args.loglevel,loglevel) )

    cs = CensusSpec()
    cs.load_schema_from_file(args.spec)

    if args.default:
        cs.find_default_from_allowable_range_descriptions(args.default)
        for table in cs.tables():
            print("Table: {}".format(table.name))
            for var in table.vars():
                print("  {:20}  {}".format(var.name,var.default))
            print("--------------------------------")
                
    if args.dump:
        cs.dump(print)

    if args.schema:
        print(cs.sql_schema())

    if args.output_parser:
        print("Writing Python parser to {}".format(args.output_parser))
        with open(args.output_parser,"w") as f:
            f.write("#!/usr/bin/env python3\n")
            f.write("# -*- coding: utf-8 -*-\n")
            f.write("# This file was automatically generated by {} on {}\n".format(sys.argv[0],time.asctime()))
            f.write("# Command line: {}\n".format(" ".join(sys.argv)))
            f.write("\n")
                    

            for table in cs.tables():
                f.write(table.python_class(ignore_vars=args.ignore.split(",")))
                f.write("\n\n")

            f.write("SPEC_CLASS_OBJECTS = [")
            f.write(",".join( "{}()".format(table.python_name()) for table in cs.tables()))
            f.write("]\n")

            # Metadata dictionary
            f.write("null = None\n")
            f.write("SPEC_DICT = ")
            f.write(json.dumps(cs.json_dict()))
            f.write("\n")


    if args.output_fake_data:
        for table in cs.tables():
            fname = "FAKEDATA-"+table.name.replace(" ",".") + ".txt"
            print("Writing fake data to {}".format(fname))
            with open(fname,"w") as f:
                for i in range(100):
                    f.write(table.random_file_record())
                    f.write("\n")

    if args.output_sas:
        print("Writing SAS to {}".format(args.output_sas))
        with open(args.output_sas,"w") as f:
            for table in cs.tables.values():
                f.write(table.sas_definition())
                f.write("\n\n")

#!/usr/bin/python
# -*- coding: utf-8 -*-

#
# Reads a schema specification and outputs at your option:
#  --dump --- the internal tables constructed by the schema.py package
#  --schema --- A nicely formatted SQL schema
#  --output_parser --- A Python class that will parser files formatted in the spec
#  --output_fake_data --- Fake data consistent with the spec.
#  --output_sas       --- A SAS definition for the spec
#
# You may optionally specify:
#  --ignore var1,var2,..    --- Ignore var1,var2 in the spec
#  --loglevel               --- Specifies the log level to read
#
#
# Supported input file formats:

# - Microsoft Word table layouts used by the Census Bureau, such as those used for the 2020 MDF.
# - Text files, such as those generated from the 2010 Census PDF files.
# - Excel Files, such as those for the 2020 CEF.
# - .SAS files generated by IPUMS. 
#
# Planned:
#
# XML DDI  support.
#

import os
import os.path
import pandas
import re
import sys
import logging
import time
import json

from schema import Range,Variable,Table,Schema,Recode,TYPE_VARCHAR,TYPE_INTEGER,vtype_for_numpy_type,convertRange
from word_table_reader import *

from ctools.dconfig import dopen

# The SAS template is used to generate a parser

SAS_TEMPLATE="""OPTIONS NOCENTER PAGENO=1 SOURCE MPRINT;
FILENAME RAWDATA "{}";
DATA {};
  INFILE RAWDATA DELIMITER='|' DSD;
  INPUT {};
RUN;

PROC PRINT DATA={};
RUN;
"""

TXT_TITLE_RE    = re.compile("Title:\s+([a-zA-Z0-9_]+)\s\s\s\s\s")
TXT_VERSION_RE  = re.compile("Version:\s*([0-9.]+)")
# VARIABLE_RE is used to parse text files. The fields are:
# VARIABLE.1 = position
# VARIABLE.2 = name
# VARIABLE.3 = description
# VARIABLE.4 = type
# VARIABLE.5 = column
VARIABLE_RE = re.compile("\s*(\d+)[.] ([A-Z_0-9]+)\s+(.*\S)\s+([A-Z0-9]+\(\d+\))\s*(\d+-\d+)")


INPUT='input'
LABEL='label'

rectype_re = re.compile('if RECTYPE = "(.)" then do;')
ipums_layout_re  = re.compile("([A-Z,0-9_]+)[ $]*(\d+)-(\d+)")
label_re   = re.compile('([A-Z,0-9]+)\s+= "([^"]*)"')

class IPUMS_SASParser:
    @staticmethod
    def is_ipums_sas_file(filename):
        data = dopen(filename).read(1024*1024) # read at most 1MiB
        return (data[0:2]=='/*') and ('ipums_directory') in data and data[-100:].strip().endswith("run;")

    @staticmethod
    def process_layout_line(*,table,line):
        m = ipums_layout_re.search(line)
        try:
            (variable_name,start,end) = m.group(1,2,3)
        except AttributeError:
            raise RuntimeError("Cannot parse: "+line)
        (name,start,end) = m.group(1,2,3)
        v = Variable(name=name, vtype=TYPE_VARCHAR)
        v.set_column( int(start)-1, int(end)-1 )
        table.add_variable(v)

    @staticmethod
    def load_schema_from_ipums_sas_file(*,schema,filename):
        if not IPUMS_SASParser.is_ipums_sas_file(filename):
            raise RuntimeError("{} is not an IPUMS SAS file".format(filename))

        # Only process H and P record types
        rectypes = ['H','P']

        state   = None
        rectype = None
        table   = None
        labels  = dict()
        for line in dopen(filename):
            line = line.strip()
            if line==INPUT and rectype!=None:
                if state==INPUT:
                    raise RuntimeError("{}: INPUT within INPUT???".format(filename))
                state = INPUT
                table = Table(name=rectype)
                continue
            if line==';':
                if state==INPUT:
                    if rectype:
                        schema.add_table(table)
                state = None    # end of SAS statement
                continue
            if line==LABEL:
                state = LABEL
                continue
            if state==INPUT:
                IPUMS_SASParser.process_layout_line(table=table,line=line)
            if state==LABEL:
                m = label_re.search(line)
                if m:
                    labels[m.group(1)] = m.group(2)
                    
            m = rectype_re.search(line)
            if m:
                rectype = m.group(1)
                if rectype not in rectypes:
                    raise RuntimeError("Record type '{}' not in list of approved rectypes ({})".
                                       format(rectype,str(rectypes)))

        # Now use the labels to set the description for all of the variables we have learned
        for table in schema.tables():
            for v in table.vars():
                if v.name in labels:
                    v.desc = labels[v.name]


class CensusSpec(Schema):
    """A subclass of Schema that understands how to read Census specification files in .docx and .txt format"""

    debug = False
    CENSUS_COLUMN_HEADERS={0:"COLUMN ID",
                           1:"COLUMN NAME",
                           2:"ORACLE DATATYPE",
                           3:"DESCRIPTION",
                           4:"LEGAL VALUES"}


    @staticmethod
    def is_variable_start(docx_row):
        """Returns True if row is the start of a variable"""
        if len(docx_row)<5:
            return False        # need at least 4 rows
        cols = [r.strip() for r in docx_row] # remove spaces
        if len(cols[0]) not in [1,2,3]:
            return False # We only allow tables with 1..999 variables
        if any([ch.isdigit()==False for ch in cols[0]]):
            return False        # all characters must be digits
        if len(cols[1])==0:
            return False # must have a name
        if " " in cols[1]:
            return False # name may not have any spaces
        return True

    @staticmethod
    def load_table_from_docx_table(docx_table):
        """Returns a table name and the list of the variables"""
        tableName = docx_table[0][0].replace(" ","_").replace(".","_")
        if CensusSpec.debug:
            print("DEBUG: Reading table '{}'".format(tableName))
        table = Table(name=tableName)
        v = None                    # current variable
        for row in docx_table[1:]:        # don't process the first line
            cols = [x.replace("\n"," ").strip() for x in row] # change all newlines to spaces
            if sum([len(x) for x in cols])==0:                # blank row
                continue
            if CensusSpec.debug:
                print("DEBUG:  cols: {}".format(cols))
            if CensusSpec.is_variable_start(cols):
                if CensusSpec.debug:
                    print("DEBUG:    defining variable {}".format(cols[0]))
                v = Variable( position = cols[0],
                              name   = cols[1],
                              desc   = cols[2],
                              vtype  = cols[3])

                # if there is a fifth column, it may be allowable values
                if len(cols)==5: 
                    v.add_valid_data_description(cols[4])
                table.add_variable(v)
                continue

            # If defining a variable and we have an extra cols, it may have an allowable value
            # in the cols[2] or cols[4]
            if v:
                if len(cols)>2 and len(cols[2]):
                    v.add_valid_data_description(cols[2])
                    contine
                if len(cols)>4 and len(cols[4]):
                    v.add_valid_data_description(cols[4])
                    continue
                print("Not sure what to do with this:",cols)
                assert False
        return table


    def __init__(self,name=''):
        super().__init__(name=name)
        self.docx_tables = []

    def load_schema_from_file(self,filename,prefix=""):
        logging.info("load_schema_from_file(%s)",filename)
        print("a:",IPUMS_SASParser.is_ipums_sas_file(filename))
        if filename.endswith(".docx"):
            return self.load_table_schema_from_docx_file(filename=filename,prefix=prefix)
        elif filename.endswith(".txt"):
            t = self.load_table_schema_from_census_txt_spec(filename=filename,prefix=prefix)
            if t:
                return t
        elif filename.endswith(".sas") and IPUMS_SASParser.is_ipums_sas_file(filename):
            return IPUMS_SASParser.load_schema_from_ipums_sas_file(schema=self,filename=filename)
        elif filename.endswith(".xlsx"):
            return self.load_table_schema_from_census_xlsx_file(filename=filename,prefix=prefix)
        return super().load_schema_from_file(filename)


    def load_table_schema_from_docx_file(self,*,filename,prefix=""):
        """Read multiple tables from a docx file. Only ingest the tables that have the prefix"""
        for docx_table in get_docx_tables(filename):
            data = get_text_for_table(docx_table)
            if CensusSpec.debug:
                print("DEBUG: Found table '{}' cols:{} rows:{}".format(data[0][0], len(data[0]), len(data)))
            if data and len(data)>2 and (data[1][0] in ['Var #','#']):
                if CensusSpec.debug:
                    print("DEBUG: ** processing table **")
                table = self.load_table_from_docx_table(data)
                if table.name.startswith(prefix):
                    table.add_comment("Parsed from {}".format(filename))
                    self.docx_tables.append(docx_table)
                    self.add_table(table)

    def verify_census_worksheet(self,ws):
        from openpyxl import load_workbook
        from openpyxl.cell.read_only import EmptyCell
        for row in ws.iter_rows():
            # Ignore empty rows
            if type(row[0])==EmptyCell:
                continue
            # Validate headers
            errors = 0
            for (col,label) in self.CENSUS_COLUMN_HEADERS.items():
                if row[col].value != label:
                    logging.error("Column %s: found '%s' expected '%s'",col,row[col].value,label)
                    errors += 1
            if errors:
                return False
            return True         # it validates
            
            

    def load_table_schema_from_census_xlsx_file(self,*,filename,prefix=""):
        """Read multiple tables from a xlsx file. Only ingest the tables that have the prefix. We only look at the first 5 columns"""
        from openpyxl import load_workbook
        from openpyxl.cell.read_only import EmptyCell
        wb = load_workbook(filename=filename, read_only='True')
        for ws in wb.worksheets:
            print("Processing worksheet {}".format(ws.title))
            if not self.verify_census_worksheet(ws):
                logging.error("This does not appear to be a US Census Bureau XLSX specification file")
                raise RuntimeError("This does not appear to be a US Census Bureau XLSX specification file")
            column = 0
            table = Table(name=ws.title)
            self.add_table(table)
            header_skipped = False
            for row in ws.iter_rows():
                # Skip empty rows
                if type(row[0])==EmptyCell: 
                    continue
                # skip the header
                if header_skipped==False:
                    header_skipped=True 
                    continue
                # Only the first five columns are considered
                values = []
                for i in range(0,5):
                    if type(row[i])==EmptyCell:
                        values.append("")
                    elif type(row[i].value)==str:
                        values.append(row[i].value.strip())
                    else:
                        values.append(row[i].value)
                (column_id,column_name,oracle_datatype,desc,legal_values) = values
                if (not column_id) and not (legal_values):
                    # Ignore the blank line
                    continue
                if column_id:
                    if type(column_id)!=int:
                        logging.info("Column ID is invalid; skipping")
                        continue
                    v = Variable(position=column_id, name=column_name, desc=desc, vtype=oracle_datatype, column=column)
                    # advance to the next column
                    column += v.width 
                    table.add_variable(v)
                if v and legal_values:
                    for possible_legal_value in legal_values.split("\n"):
                        r = Range.extract_range_and_desc(possible_legal_value)
                        if r:
                            v.add_range(r)
                        
        

    MAXLINES=20                 # max lines to read before giving up
    def load_table_schema_from_census_txt_spec(self,*,filename,prefix=""):
        """Read a single table from a txt file."""
        table = None
        for (ll,line) in enumerate(dopen(filename),1):
            if ll>self.MAXLINES:
                if (table==None) or len(table.vars())==0:
                    logging.info("{} is not a Census text specification".format(filename))
                    return None
                
            # Get a table name if we do not have one
            if not table:
                m = TXT_TITLE_RE.search(line)
                if m:
                    table = Table(name=m.group(1))
                    table.add_comment("Parsed from {}".format(filename))
                    continue
            # Get the table version if we do not have one
            if table and not table.version:
                m = TXT_VERSION_RE.search(line)
                if m:
                    table.version = m.group(1)
                    continue
            # Is this a variable name within the table?
            m = VARIABLE_RE.search(line)
            if m:
                (position,name,desc,vtype,column) = m.group(1,2,3,4,5)
                oname = name
                count = 2
                v = Variable(position=row[0], name=row[1], desc = row[2], vtype = row[3])
                while name in [v.name for v in table.vars()]:
                    name = "{}{}".format(oname,count)
                    count += 1
                if "-" in column:
                    v.column = [int(x) for x in column.split("-")]
                table.add_variable(v)
        if len(table.vars())==0:
            return None
        self.add_table(table)


"""This is a test program that is used for running the scanner with actual files."""

if __name__=="__main__":
    from argparse import ArgumentParser,ArgumentDefaultsHelpFormatter
    parser = ArgumentParser( formatter_class = ArgumentDefaultsHelpFormatter,
                             description="Demo program that prints details about a census spec file or SAS file. This shows how we can parse these files." )
    parser.add_argument('--debug', action='store_true')
    parser.add_argument('--dump',  action='store_true', help='Dump schema and internal functions; do not run the transfer')
    parser.add_argument('--schema', action='store_true', help='display the SQL schema')
    parser.add_argument('--default', help='Specify a default value and see what it looks like')
    parser.add_argument('--output_parser', help='If specified, output a python parser for the spec')
    parser.add_argument("--output_fake_data", action='store_true', help='if specificed, output fake data')
    parser.add_argument("--output_sas", help='If specified, output a SAS definition to this file')
    parser.add_argument("--ignore", help='ignore these comma-separated values variables', default='')

    parser.add_argument("--loglevel", help="Set logging level",
                        choices=['CRITICAL','ERROR','WARNING','INFO','DEBUG'],
                        default='INFO')    
    parser.add_argument('spec', help='Spec file; may be a .docx, .xlsx, .txt, or .sas7bat')

    args   = parser.parse_args()

    loglevel = logging.getLevelName(args.loglevel)
    logging.basicConfig(format="%(asctime)s %(filename)s:%(lineno)d (%(funcName)s) %(message)s", level=loglevel)
    logging.info("START {}  log level: {} ({})".format(os.path.abspath(__file__), args.loglevel,loglevel) )

    cs = CensusSpec()
    cs.load_schema_from_file(args.spec)

    if args.default:
        cs.find_default_from_allowable_range_descriptions(args.default)
        for table in cs.tables():
            print("Table: {}".format(table.name))
            for var in table.vars():
                print("  {:20}  {}".format(var.name,var.default))
            print("--------------------------------")
                
    if args.dump:
        cs.dump(print)

    if args.schema:
        print(cs.sql_schema())

    if args.output_parser:
        print("Writing Python parser to {}".format(args.output_parser))
        with open(args.output_parser,"w") as f:
            f.write("#!/usr/bin/env python3\n")
            f.write("# -*- coding: utf-8 -*-\n")
            f.write("# This file was automatically generated by {} on {}\n".format(sys.argv[0],time.asctime()))
            f.write("# Command line: {}\n".format(" ".join(sys.argv)))
            f.write("\n")
                    

            for table in cs.tables():
                f.write(table.python_class(ignore_vars=args.ignore.split(",")))
                f.write("\n\n")

            f.write("SPEC_CLASS_OBJECTS = [")
            f.write(",".join( "{}()".format(table.python_name()) for table in cs.tables()))
            f.write("]\n")

            # Metadata dictionary
            f.write("null = None\n")
            f.write("SPEC_DICT = ")
            f.write(json.dumps(cs.json_dict()))
            f.write("\n")


    if args.output_fake_data:
        for table in cs.tables():
            fname = "FAKEDATA-"+table.name.replace(" ",".") + ".txt"
            print("Writing fake data to {}".format(fname))
            with open(fname,"w") as f:
                for i in range(100):
                    f.write(table.random_file_record())
                    f.write("\n")

    if args.output_sas:
        print("Writing SAS to {}".format(args.output_sas))
        with open(args.output_sas,"w") as f:
            for table in cs.tables.values():
                f.write(table.sas_definition())
                f.write("\n\n")
#!/usr/bin/python # -*- coding: utf-8 -*- # # Reads a schema specification and outputs at your option: #  --dump --- the internal tables constructed by the schema.py package #  --schema --- A nicely formatted SQL schema #  --output_parser --- A Python class that will parser files formatted in the spec #  --output_fake_data --- Fake data consistent with the spec. #  --output_sas       --- A SAS definition for the spec # # You may optionally specify: #  --ignore var1,var2,..    --- Ignore var1,var2 in the spec #  --loglevel               --- Specifies the log level to read # # # Supported input file formats: # - Microsoft Word table layouts used by the Census Bureau, such as those used for the 2020 MDF. # - Text files, such as those generated from the 2010 Census PDF files. # - Excel Files, such as those for the 2020 CEF. # - .SAS files generated by IPUMS. # # Planned: # # XML DDI  support. # # The SAS template is used to generate a parser OPTIONS NOCENTER PAGENO=1 SOURCE MPRINT; FILENAME RAWDATA "{}"; DATA {};   INFILE RAWDATA DELIMITER='|' DSD;   INPUT {}; RUN;  PROC PRINT DATA={}; RUN; # VARIABLE_RE is used to parse text files. The fields are: # VARIABLE.1 = position # VARIABLE.2 = name # VARIABLE.3 = description # VARIABLE.4 = type # VARIABLE.5 = column # read at most 1MiB # Only process H and P record types # end of SAS statement # Now use the labels to set the description for all of the variables we have learned A subclass of Schema that understands how to read Census specification files in .docx and .txt format Returns True if row is the start of a variable # need at least 4 rows # remove spaces # We only allow tables with 1..999 variables # all characters must be digits # must have a name # name may not have any spaces Returns a table name and the list of the variables # current variable # don't process the first line # change all newlines to spaces # blank row # if there is a fifth column, it may be allowable values # If defining a variable and we have an extra cols, it may have an allowable value # in the cols[2] or cols[4] Read multiple tables from a docx file. Only ingest the tables that have the prefix #','#']): # Ignore empty rows # Validate headers # it validates Read multiple tables from a xlsx file. Only ingest the tables that have the prefix. We only look at the first 5 columns # Skip empty rows # skip the header # Only the first five columns are considered # Ignore the blank line # advance to the next column # max lines to read before giving up Read a single table from a txt file. # Get a table name if we do not have one # Get the table version if we do not have one # Is this a variable name within the table? This is a test program that is used for running the scanner with actual files. # Metadata dictionary