Method for processing a file to generate a database

[0002] The present application includes a paper appendix attached hereto setting forth an exemplary implementation of an embodiment of the method according to the present invention. A portion of the disclosure of the present application contains material which is subject to copyright protection. The copyright owner has no objection to the facsimile reproduction by anyone of the patent disclosure, as it appears in the Patent and Trademark Office patent files or records, but otherwise reserves all copyright rights whatsoever.

[0003] The present invention relates to a method for automatically processing an HTML document or an ASCII file in order to treat the data contained within a HTML document or ASCII file as a database with one or more database management system tables.

[0004] The Internet provides tremendous amounts of information that is made available to the public via web sites of various organizations. A web site includes one or more web pages providing information from the sponsoring organization. For example, the United States Congress makes information on pending legislation and votes available to the public on its website. As is known in the art, Hypertext Markup Language (HTML) is the authorized language used on the Internet for creating web pages. HTML includes ASCII text surrounded by HTML commands in angle brackets which can be interpreted by an Internet web browser.

[0005] While a tremendous amount of information is available to the public via the Internet, the information is generally organized and presented in a manner selected by the owner or sponsor of the website. However, a user of the website may need to manipulate data available from a website in a particular manner. For example, a user may want to reorganize the data so that it can be manipulated using standard SQL queries. However, current web browsers, such as MICROSOFT INTERNET EXPLORER or NETSCAPE NAVIGATOR do not provide the capability to mine web pages to extract the data contained therein so that the data can be manipulated in the particular manner desired by the user, thus freeing the user from the format provided by the web site.

[0006] According to an exemplary embodiment of the present invention, a method for automatically processing a file, such as a web page or an ASCII file, is provided to treat the file as a database with one or more database tables. For example, the method according to an embodiment of the present invention automatically processes an HTML page or group of related HTML pages in an HTML frameset in order to identify data and treat the data contained within the HTML file(s) as a database with one or more database management system tables. In another embodiment of the method according to the present invention, an ASCII file can be processed to identify data and treat the data contained within the ASCII file as a database with one or more database management system tables.

[0007] The method according to the present invention can, for example, retrieve an HTML page or a group of related HTML pages in an HTML frameset from a user specified URL or from a disk file. If the source HTML document contains an HTML <FRAMESET> (e.g., a group of HTML pages each loaded into a separate frame in the browser), the method according to the present invention retrieves the HTML page associated with each frame and thus treats the entire frameset as a single database. The method according to an embodiment of the present invention scans each HTML page for any HTML tables and translates each HTML table into a database table in a database representation of the HTML page (e.g., as a DB2 database management system representation of the HTML page).

[0008] According to an embodiment of the present invention, processing is performed on each HTML table identified in an HTML page so that the HTML table can be used in a database representation. For example, if the HTML table contains an HTML <CAPTION> tag, then the caption text is used to generate the database table name. If the HTML table contains HTML <TH> tags (e.g., table header tags), then the table header text is used to generate the database table column names. If the HTML table contains <ROW SPAN> or <COL SPAN> tags (e.g., a label applied to multiple rows or columns as a category label), then the text value of the cell is replicated over the <ROW SPAN> rows or the <COL SPAN> columns to create tables which are consistent with relational database tables. All HTML escape sequences are translated to their corresponding ASCII representations. Any carriage returns and/or line feeds are, for example, removed from the data in the HTML table. Also, all HTML tags are removed from the data except for the <BR> (e.g., break) tag which is translated into, for example, a <CR> <LF> line break in the data. Leading and trailing white spaces are removed from the data in the HTML table and all internal white spaces are compressed into a single space. As a result of the processing of the data in the HTML table, the underlying data in the HTML table can be identified and extracted for inclusion in a database representing the underlying data.

[0009] The method according to an embodiment of the present invention can also identify data to be translated into a database table that is contained in a web page (e.g., an HTML document) but is not contained in an HTML table. For example, in an embodiment of the method of the present invention, each HTML page is scanned for any blocks of fixed length lines. A block is defined, for example, by five or more lines of the same length that do not contain a separator line. A separator line is defined, for example, as a line containing the same repeating character (e.g., “--------------”). For each block identified by the method according to an embodiment of the present invention, the method also identifies field breaks (e.g., column breaks) in the block. For every set of, for example, five or more contiguous lines within the block for which two or more columns are identified, a database table is created.

[0010] Similar to the processing of HTML tables, processing is performed on the text tables so that the data contained therein can be used in a database representation. For example, all HTML escape sequences are translated to their corresponding ASCII representation. Any carriage returns and/or line fields are removed from the data. All HTML tags are removed from the data except for <BR> tags which are translated into a carriage return/line feed (<CR> <LF>) line break in the data. Also, leading and trailing white spaces are removed from the data and all internal white spaces are compressed to a single space. The method according to an embodiment of the present invention also provides a user definable HeaderRows=n parameter which allows the user to specify that the top n rows of the text block are treated as column titles and are used to create the database table column names (e.g., a default behavior could be to use all of the text block as data—HeaderRows=0). Similarly, a user definable SkipBlankRow=Yes parameter allows a user to specify if blank rows are to be removed from the data (e.g., a default behavior could be that blank rows are skipped—SkipBlankRow=Yes). After a block has been processed, scanning resumes on the next line past the block and the process resumes until the entire file has been scanned.

[0011] The entire database created by the method according to an embodiment of the present invention (e.g., based on the HTML tables and text tables contained on the source file) is then reviewed and any blank tables are removed from the database. Blank tables can occur, for example, because HTML tables are often used as a page layout mechanism and not as a columnar data delivery mechanism. All of the tablenames in the database are also reviewed to make sure they are unique, without regard to case. If there are conflicting names, they are modified to make them unique (e.g., if there are two tables named “Resources,” they would be renamed “Resources_—1 ” and “Resources_—2”). In addition, to conform to normal database naming conventions, any punctuation is removed from the names and spaces are changed to underscores.

[0012] FIG. 1 illustrates an exemplary HTML source document to be processed according to an embodiment of the present invention.

[0013] FIG. 2 illustrates an exemplary HTML document containing blocks of fixed length text.

[0014] FIGS. 3A-3C illustrate an exemplary HTML document containing two HTML tables.

[0015] FIG. 4 illustrates a view of an exemplary HTML source document from an Internet browser.

[0016] FIGS. 5A and 5B illustrate a view based on an exemplary HTML source document from an Internet browser.

[0017] FIG. 6 illustrates an exemplary flowchart of a process for assembling a source document for parsing according to an embodiment of the present invention.

[0018] FIGS. 7A-7D illustrate an exemplary output from the process illustrated in FIG. 6.

[0019] FIGS. 8A-8C illustrate an exemplary flowchart for parsing HTML tables according to an embodiment of the present invention.

[0020] FIG. 9 illustrates an exemplary view of a resultant database obtained via an embodiment of the present invention, including an exemplary SQL query to be run against the database.

[0021] FIG. 10 illustrates an exemplary output according to an embodiment of the present invention from a SQL query.

[0022] FIGS. 11A and 11B illustrate an exemplary flowchart for parsing a text table according to an embodiment of the present invention.

[0023] FIG. 1 illustrates an exemplary HTML document to be processed by an embodiment of the present invention. As shown in FIG. 1, the exemplary HTML document is a frame set which references two other web pages: TEXTTBL.HTM; and HTMLTBL.HTM. FIG. 2 illustrates the HTML document TEXTTBL.HTM identified in FIG. 1 which contains blocks of fixed length text. For example, web page TEXTTBL.HTM describes items for public sale and identifies the location of the items in column 210, a description of the item in column 220, a hyperlink in field 230, an identifying number for the item in column 240, the available quantity of the item in field 250 and the availability date of the item in field 260.

[0024] FIGS. 3A-3C illustrate the HTML document HTMLTBL.HTM containing two HTML tables. For example, the two tables in HTMLTBL.HTM contain U.S. Senate Roll Call Votes, one table having a border and another table having no border, as illustrated in FIGS. 5A and 5B, which is a view of the HTML document of FIG. 1 from a typical Internet browser, such as MICROSOFT INTERNET EXPLORER 4.0. As shown in FIGS. 5A and 5B, both HTML documents TEXTTBL.HTM and HTMLTBL.HTM are included in the view from the web browser as the two documents are treated by the web browser as one document with two frames (thus the term “frame set”). FIG. 4 also illustrates a view from a typical web browser of the HTML document of FIG. 1, the browser combining the HTML documents referenced in the HTML document of FIG. 1 each into its own scrolling frame.

[0025] FIG. 6 illustrates an exemplary flowchart for preparing a source HTML file for parsing according to an embodiment of the present invention. The exemplary flowchart of FIG. 6 can be implemented, for example, in software that is stored in a memory of a general purpose computer and that executes on a central processing unit (cpu) of the general purpose computer. For example, a conventional personal computer including an INTEL PENTIUM processor and operating with the MICROSOFT WINDOWS operating system can carry out the method according to an embodiment of the present invention. Exemplary C++ code implementing an embodiment of the present invention is provided in the paper appendix attached hereto.

[0026] As shown in FIG. 6, in step 600, a source HTML document is retrieved from a specified universal resource locator (URL) or file. In step 610, it is determined if the HTML document has a <FRAMESET> (e.g., other pages containing the actual data to be viewed by a user). If the HTML document does not have a <FRAMESET>, then the complete source HTML document has been retrieved as indicated in step 650. If the HTML document does have a <FRAMESET>, then in step 620 the next <FRAME SRC> tag is searched for (e.g., the location of the pages containing the additional content). If a <FRAME SRC> tag is not found in step 630, then the complete source HTML document has been retrieved as indicated in step 650. If a <FRAME SRC> tag is found in step 630, then the frame source is retrieved in step 640 and the frame source is concatenated onto a working document. The method then loops back to step 620 to find the next <FRAME SRC> tag, the looping continuing until no <FRAME SRC> tag is found in step 630.

[0027] FIGS. 7A-7D illustrate an exemplary HTML working document generated by the process of FIG. 6 with regard to the exemplary HTML document shown in FIG. 1. For example, step 610 would identify the <FRAMESET tag 611 shown in FIG. 1 which would cause step 620 to search for the <FRAME SRC tag 631 shown in FIG. 1. <FRAME SRC tag 631 would be found in step 630 and the frame source referenced by <FRAME SRC 631, TEXTTBL.HTM, would be retrieved and stored into a memory in step 640, the memory being allocated for operation of the method according to an embodiment of the present invention, for example the memory of the general purpose computer that stores the executable code. The file TEXTTBL.HTM could be retrieved from, for example, a subdirectory of the host computer for the URL. Accordingly, the content of TEXTTBL.HTM shown in FIG. 2 is concatenated into a working document as shown in FIGS. 7A and 7B.

[0028] Looping back to step 620, the method according to the present invention would look for another <FRAME SRC tag in the HTML document of FIG. 1 and would find <FRAME SRC tag 632 in step 630. Thus, in step 640, the frame source reference by tag 632, HTMLTBL.HTM, would be retrieved and stored in the allocated memory as described above. Accordingly, the content of HTMLTBL.HTM shown in FIGS. 3A-3C would be concatenated into the working document with TEXTTBL.HTM in step 640 as shown in FIGS. 7B, 7C and 7D. Similar to TEXTTBL.HTM, the file HTMLTBL.HTM could be retrieved, for example, from a subdirectory of the host computer for the URL.

[0029] FIGS. 8A and 8B and 8C illustrate an exemplary flowchart for parsing an HTML table from an HTML document according to an embodiment of the present invention. The exemplary flowchart of FIGS. 8A and 8B and 8C can be implemented, for example, in software that is stored in the memory of a general purpose computer and that executes on a central processing unit (cpu) of the general purpose computer as described previously. Exemplary C++ code implementing an embodiment of the present invention is provided in the paper appendix attached hereto.

[0030] In step 800, the method according to an embodiment of the present invention points to the top of an HTML source, such as the concatenated working file illustrated in FIGS. 7A-7D obtained via the exemplary flowchart shown in FIG. 6. Also in step 800, a new database object is created for the database to be created for the source HTML document. For example, any database software package can be used to establish the database object, such as standard database packages from IBM, ACCESS, ORACLE or SYBASE. In step 802 the HTML document is read and in step 804 it is determined if a HTML table is in the HTML document. If no table tag is found, then the HTML table parsing is completed in step 806.

[0031] If a table tag is found in step 804, then in step 808 a new DataTable Object would be created and added to the Database Object created in step 800. Thus, step 808 creates a physical database object that can be subsequently populated with data extracted from the HTML table as described below. As is known in the art, the DataTable Object can be created by any standard database package and can utilize, for example, a set of cooperating C++ objects to manage the data. In the method according to the present invention, initial preparation for the loading of data into the DataTable Object is performed in step 810 to initialize the starting conditions of the DataTable Object so that data can be added to the object in an organized manner, for example, by adding rows and columns as necessary. For example, the variables bInTable, bInRow, currentRow and rowCount can be used to track the current location of the exemplary method illustrated in FIGS. 8A-8C with respect to the DataTable Object. In step 812, the HTML document is read so that, for example, particular HTML tags can be found that are utilized to parse the source HTML document to identify data to be extracted, such as a caption tag (<CAPTION>), a start or end of table row tag (<TR> or </TR>), a table header tag (<TH>), a data item tag (<TD>) or a start or end of table tag (<TABLE>, </TABLE>). If one of these particular tags is not found in step 814, then the end of the table is identified in step 844. By searching for particular tags in step 812, an HTML document can be parsed according to an embodiment of the invention so that only HTML tags that are likely to lead to data appropriate for extraction into a database are processed and other HTML tags can be ignored.

[0032] If one of the particular HTML tags is found in step 814, then in step 816 it is determined if it is a caption tag. If the HTML tag is a caption tag, then in step 818 the table name of the DataTable Object created in step 808 is determined as a function of the caption tag text, for example by conforming the caption tag text to standard database table naming rules (e.g., replacing a space with an underscore), and the process returns to step 812 to find the next particular HTML tag. If the HTML tag is not a caption tag, then in step 820, it is determined if the HTML tag is an end of table row tag (e.g., </TR>). If the HTML tag is an end of table row tag, then in step 822, the current table row in the DataTable Object is ended and bInRow is set to FALSE (thus indicating that the DataTable Object is no longer in a row) and the process returns to step 812 to find the next particular HTML tag. If the HTML tag is not an end of row tag, in step 824 it is determined if the HTML tag is a start of table row tag (e.g., <TR>). If the HTML tag is a start of table row tag, then in step 826, a new table row is started in the DataTable Object and bInRow is set to TRUE, currentRow is set to rowCount and rowCount is set to rowCount+1 to indicate that a new row has been created and also the identity of the current row and the process returns to step 812 to find the next particular HTML tag.

[0033] If the HTML tag is not a start of table row tag, then in step 828, it is determined if the HTML tag is a header or data item tag (e.g., <TH> or <TD>). If the HTML tag is a header or data item tag, then in step 830 it is determined if bInRow is set to TRUE (e.g., whether or not the DataTable Object is currently in a row). If bInRow is not set to TRUE, then a new table row is started in the DataTable object in step 832 and bInRow is set to TRUE, currentRow is set to rowCount and rowCount is set to rowCount+1. Following step 832, or if bInRow is set to TRUE in step 830, then in step 834 the number of rows and columns of the data in the HTML header or data item tag is determined (e.g., to determine if the tag has a ROWSPAN or a COLSPAN attribute and thus spans more than one row or column) so that the appropriate number of rows and columns are added to the DataTable Object to hold the underlying data.

[0034] Before adding the data from the header or data item tag to the DataTable Object, the end of the data is determined by looking ahead in the HTML document until the corresponding end tag is found (e.g., </TR> or </TD>). In addition, the data is cleaned before it is added to the DataTable Object. For example, all HTML escape sequences are translated to their corresponding ASCII representations and any carriage returns and/or line feeds are removed from the data in the HTML table. Also, all HTML tags are removed from the data except for the <BR> (e.g., break) tag which is translated into, for example, a <CR> <LF> line break in the data. Leading and trailing white spaces are removed from the data in the HTML table and all internal white spaces are compressed into a single space.

[0035] Then, in step 836, the data extracted from the HTML header or data item tag is loaded into the DataTable object. For example, starting with the current row, the data is added to the next available column in the current row. If there is a COLSPAN attribute, then the data is replicated (e.g., repeated a number of times defined by the COLSPAN value) and then added to the current table row beginning at the next available column. If there is a ROWSPAN attribute, then the data is added to the next available column of the current row and additional rows are added (e.g., additional rows are added equal to the ROWSPAN value minus one) and the data is added to the same column in each of the newly added rows. If the data has ROWSPAN and a COLSPAN attributes, then both actions are combined so that the data is replicated for columns and rows.

[0036] If step 828 determines that the HTML tag is not a header or data item tag, then in step 838 it is determined if the HTML tag is an HTML table tag (e.g., to determine if another table is nested in the table identified in step 804). If the HTML tag is not a table tag, then in step 844 the end of the table is reached. If the HTML tag is a table tag, however, then in step 840 it is determined if the process is already in an HTML table (e.g., if bInTable is set to TRUE, the process is already in a table and a nested table has now been identified). If the process is not in a table, then bInTable is set to TRUE in step 842 and the process returns to step 812 to identify the next particular HTML tag.

[0037] However, if the process is already in a table, then the process returns to point 1 identified in FIG. 8A to recursively call the table processing routine so that a new DataTable Object is created for the new table that has been identified in steps 838 and 840. The process then proceeds through, for example, steps 808-838 for the new table until the end of the new table is reached or another table identified. In this manner, nested HTML tables in an HTML document can be identified and treated as individual database tables according to an embodiment of the present invention.

[0038] When the end of a table is reached in step 844, the process returns to point 2 shown on FIG. 8C which returns to step 812 to find the next particular HTML tag in the HTML document to resume parsing the outer HTML table.

[0039] FIG. 9 illustrates an exemplary view of a resultant database obtained via an embodiment of the present invention, including an exemplary SQL query to be run against the database. For example, using the exemplary HTML working document illustrated in FIGS. 7A-7D, FIG. 9 shows the SQL keywords that can be used to formulate a SQL query as well as the tables and columns that have been identified by the parsing of the HTML document according to an exemplary embodiment of the present invention. In this case a SELECT query for the date, bill, result and title description (e.g., the fields selected for display by a user of a database system operating in conjunction with an embodiment of the method according to the present invention) is requested via the SQL query from the database table US SENATE ROLL CALL VOTES. FIG. 10 illustrates an exemplary output according to an embodiment of the present invention from the SQL query shown in FIG. 9. Accordingly, FIG. 10 shows the data originally obtained from the HTML source document but that has been processed as a database table that can now be viewed via a standard SQL query.

[0040] FIGS. 11A and 11B illustrates an exemplary flowchart for parsing a text table according to an embodiment of the present invention. For example, if an HTML source document contains, in addition to an HTML table that can be parsed as described above, a block of text containing data suitable for representation as a database, then an embodiment of the present invention can also parse through the HTML document to identify the text block and process the text block for representation as a database table.

[0041] As shown in FIGS. 11A and 11B, in step 900 the method according to the present invention points to the top of the HTML source document, for example the HTML document obtained via the exemplary method according to the present invention illustrated in FIG. 6. Also in step 900, a new database object is created for the database to be created for the source HTML document. For example, any database software package can be used to establish the database object, such as standard database packages from IBM, ACCESS, ORACLE or SYBASE. If a database object has already been created for the HTML document, for example due to a prior parsing of the HTML document to process HTML tables contained in the HTML document, then step 900 will utilize the database object that has already been created.

[0042] In step 902, various parameters are initialized so that the parsing process can keep track of the progress through the HTML document as the HTML document is read. For example, the variable matchLen is set to the length of the first line in the HTML document and the variable LineCount is set to 0. In addition, a pointer for the block of text is set to the first line of the HTML document. In step 904, it is determined if the line of the HTML document contains an end of file (EOF) indicator. If an EOF indicator is identified in step 904, then in step 906 the parsing of the text table is completed. If no EOF indicator is identified in step 904, then in step 908 the next line in the HTML document is read. In step 910, it is determined if an EOF indicator is identified, if the line length of the current line in the text block is different from the matchlen value (e.g., if the line length differs from the length of the first line) or if the current line is a separator line (e.g., a repeating character such as “----------”). If none of the conditions in step 910 are satisfied (e.g., there is a match of the current line and the previous line), then in step 912 the LineCount variable is incremented and the process returns to step 908 to read the next line in the HTML document. If any of the conditions in step 910 are satisfied (e.g., the current line does not match the previous line), then it is determined in step 914 if the LineCount variable is equal to or greater than, for example, five. If the LineCount variable is less than five, a text block has not been identified and then in step 916 the LineCount variable is reset to 0, the matchLen variable is set to the length of the next line and the block pointer is set to the current line so that a search can begin for another text block.

[0043] If the LineCount is equal to or greater than five in step 916, a text block has been identified and the method according to an embodiment of the present invention looks for column breaks. For example, in step 918 a blank pattern array is created, the blank pattern array having the same length as the text block (e.g., the same value as matchLen). In step 920, beginning at the start of the block pointer, which is set for the first line in the text block, the non-space characters in the next LineCount lines are merged into the pattern array. According to an embodiment of the present invention, any HTML tags that are included in a line of the text block are treated as a character (e.g., a non-space character) for merging into the pattern array. For example, assuming a matchLen of 80 characters, for example, each line of the text block would merge its non-space characters into the same 80 characters of the pattern array. In step 922, it is determined if the pattern array contains any spaces. For example, if there were common locations in each line of the text block that contained spaces, then the merging of all of the text block lines into the pattern array would leave spaces in the pattern array. Similarly, if there were no common locations having spaces in the lines of the text block, then the merging into the pattern array would not leave any spaces in the pattern array. Therefore, it can be determined in step 922 whether there are any locations in the pattern array containing a space and thus indicating a discernable column. If there are no spaces in the merged pattern array in step 922, then there are no discernable columns and the process returns to step 916 to start a search for a new text block.

[0044] If there are spaces in the merged pattern array, then in step 924 a new DataTable Object is created for the text block that has been identified and the new DataTable Object is added to the Database Object for the HTML source document. In step 926, the data from the text block that has been identified is added to the DataTable object. Prior to adding any data to the DataTable Object, however, the data is processed cleaned. For example, all HTML escape sequences are translated to their corresponding ASCII representations and any carriage returns and/or line feeds are removed from the data in the text block. Also, all HTML tags are removed from the data except for the <BR> (e.g., break) tag which is translated into, for example, a <CR> <LF> line break in the data. Leading and trailing white spaces are removed from the data in the text table and all internal white spaces are compressed into a single space.

[0045] To add the data to the DataTable Object in step 926, the method according to an embodiment of the present invention starts at the top of the block pointer and adds the data from each column of each line in the text block into the DataTable Object. For example, the start location, stop location and length of each column in the text block can be determined as a function of the merged pattern array (e.g., the spaces in the merged pattern array define the columns in the text block and column information can be stored in a pattern array buffer). Therefore, for each line in the text block and for each column in the pattern buffer, the method according to the present invention extracts the length of data starting at the start position for the current column and adds the data to the DataTable Object. Each column in the text block is processed in the same manner. Unlike adding data from an HTML table, there are no ROWSPAN or COLSPAN attributes in a text block and thus each data value is written to a single location in the DataTable Object.

[0046] In step 928, any blank rows in the DataTable Object can be removed if, for example, a variable such as SkipBlankRows is set to Yes. In step 930, unique column names can be created or the column names can be set from the data rows if, for example, a variable such as HeaderRows is not equal to 0. For example, the method according to an embodiment of the present invention provides a user definable HeaderRows=n parameter which allows the user to specify that the top n rows of the text block are treated as column titles and thus can be used to create the database table column names (e.g., a default behavior could be to use all of the text block as data—HeaderRows=0). Similarly, a user definable SkipBlankRow=Yes parameter allows a user to specify if blank rows are to be removed from the data (e.g., a default behavior could be that blank rows are skipped—SkipBlankRow=Yes). After a text block has been processed, scanning resumes on the next line past the block and the process resumes in step 916 until the entire file has been scanned.

[0047] The method according to an embodiment of the present invention can also be used to parse an ASCII file to identify any data in the ASCII file that can be represented as a database. For example, to parse an ASCII file, the method described with regard to FIGS. 11A and 11B could be used with the modification of eliminating the steps in the cleaning process relating to translating HTML escape sequences and removing all HTML tags as an ASCII file would not contain HTML data. Furthermore, there would be no need to parse the ASCII file for an HTML table as described with regard to FIGS. 8A and 8B and 8C as the ASCII file would not contain an HTML table.