Formatting data by example

• US 10,409,892 B2
• Filed: 01/26/2011
• Issued: 09/10/2019
• Est. Priority Date: 01/26/2011
• Status: Active Grant

• Current Assignee: Microsoft Technology Licensing LLC (Microsoft Corporation)
• Original Assignee: Microsoft Technology Licensing LLC (Microsoft Corporation)
• Inventors: Rothschiller, Chad, Battagin, Daniel, Benedict, Christopher, Moreira-Silveira, Rodrigo, Danilov, Dmitri O., Cohen, Eric, Gulwani, Sumit, Rouhana, Dany, Singh, Rishabh, Zorn, Benjamin Goth, Venkatesan, Ramarathnam
• Primary Examiner(s): Patel, Manglesh M
• Application Number:
  US13/014,520
• Publication Number:
  US 20120192051A1
• Time in Patent Office: 3,149 Days
• Field of Search: 715212, 715220, 715200, 715217
• CPC Class Codes: G06F 16/26 Visual data mining; Browsing structured data
• CPC Class Codes: G06F 40/103 Formatting, i.e. changing of presentation of documents automatic justification G06F40/189; automatic line break hyphenation G06F40/191

Abstract

Data formatting rules to convert data from one form to another form are automatically determined based on a user'"'"'s edits. A machine learning heuristic is applied to a user'"'"'s edits to determine a data formatting rule that may be applied to data. For example, a user may make edits that add/remove characters from data, concatenate data, extract data, rename data, and the like. The machine learning heuristic may be automatically triggered in response to an event (e.g. after a predetermined number of edits are made to a same type of data) or manually triggered (e.g. selecting a user interface option). The data formatting rule may be applied to other data and the results of the formatting reviewable by the user. Based on further edits/reviews, the data formatting rule may be updated. The data formatting rules may be stored for later use.

Claims

1. A method for formatting data based on edits, comprising:

determining whether the edits have been made to a plurality of items within a first range of a document, wherein the plurality of items within the first range are related by a similar formatting, wherein the edits provide input examples of application of data formatting, and wherein the first range is located in a first column;

in response to determining that the edits have been made to the plurality of items within the first range of the document and that the edits provide the input examples of application of data formatting;

creating a data formatting rule based on the input examples for application to other items having the similar formatting within a second range of the document;

automatically applying the data formatting rule to the other items having the similar formatting within the second range of the document;

wherein when the first range is located in the first column, the second range is located in a second column offset at least one column from the first column; and

displaying the other items reflecting the application of the data formatting rule.

2. The method of claim 1, wherein creating the data formatting rule comprises submitting information relating to each of the edits to a machine learning heuristic that creates the data formatting rule.

3. The method of claim 1, wherein the document is a spreadsheet document and wherein the edits are made to different cells within a same column of the spreadsheet document.

4. The method of claim 1, further comprising displaying a graphical user interface next to at least one of the other items formatted by the data formatting rule that when selected provides options for performing operations relating to the formatted item.

5. The method of claim 4, wherein displaying the graphical user interface comprises displaying a menu that comprises options for undoing the formatting, redoing the formatting, stopping the formatting, reviewing potential errors and ignoring errors.

6. The method of claim 1, further comprising displaying an indicator with a formatted item when a confidence level is below a predetermined threshold.

7. The method of claim 1, wherein determining whether the edits have been made to the plurality of items comprises determining whether the first column includes data having a value that is also included in the second column.

8. The method of claim 1, further comprising displaying a user interface element that allows the data formatting rule to be saved for later use.

9. A computer-readable storage medium, excluding a signal, storing computer-executable instructions for formatting data based on examples, comprising:

determining output examples from a first plurality of items within an output range of a spreadsheet document, wherein the output range is a column or a row, and wherein each item of the first plurality of items includes output data;

determining input examples from a second plurality of items within an input range of the spreadsheet document, wherein the input range does not include the output range, and wherein each item of the second plurality of items includes input data, wherein the input examples are determined based on the output data and the input data sharing a relationship associated with an application of data formatting;

automatically creating a data formatting rule based on the relationship between the output examples and the input examples;

automatically applying the data formatting rule to other items within the output range of the spreadsheet document that do not include data prior to applying the data formatting rule;

wherein the data formatting rule selects the input data from the input range and formats the input data according to the relationship to create formatted data to include in the other items; and

displaying the other items reflecting the application of the data formatting rule.

10. The computer-readable storage medium of claim 9, wherein creating the data formatting rule comprises submitting information relating to each of the input examples and the output examples to a machine learning heuristic that creates the data formatting rule based on the input examples and the output examples.

11. The computer-readable storage medium of claim 9, further comprising displaying a graphical user interface next to at least one of the other items formatted by the data formatting rule that when selected provides options for reviewing formatting changes.

12. The computer-readable storage medium of claim 9, further comprising displaying a user interface element in the output range when a confidence level is below a predetermined threshold.

13. The computer-readable storage medium of claim 9, wherein determining the output examples comprises examining a first column that includes data values that are also included in a second column and a third column, wherein the second column and the third column are included in the input range.

14. The computer-readable storage medium of claim 9, further comprising displaying a user interface element that allows the data formatting rule to be saved for later use.

15. A system for formatting data based on edits, comprising:

a network connection that is configured to connect to a network;

a processor, memory, and a computer-readable storage medium, excluding a signal;

an operating environment stored on the computer-readable storage medium and executing on the processor;

a display;

a spreadsheet application;

a spreadsheet provided by the spreadsheet application, wherein the spreadsheet comprises items that are arranged in rows and columns; and

a formatting manager operating in conjunction with the spreadsheet application that is configured to perform actions comprising;

determining whether the edits have been made to a first plurality of items within a first range of the spreadsheet comprises one of a first column or a first row, wherein each item of the first plurality of items includes data, and wherein the edits provide input examples of a data formatting rule applied to the data of the first plurality of items;

automatically applying the data formatting rule to a second plurality of items within a second range of the spreadsheet that include other data having a same data type as the data of the first plurality of items, wherein when the first range comprises the first column, the second range comprises one or more columns offset from the first column by at least one column, and wherein when the first range comprises the first row, the second range comprises one or more rows offset from the first row by at least one row;

wherein an application of the data formatting rule formats the other data included in the second plurality of items to a format as defined by the edits made to the first plurality of items; and

displaying the other data included in the second plurality of items on the display reflecting the application of the data formatting rule.

16. The system of claim 15, further comprising displaying a graphical user interface next to at least one of item of the second plurality of items formatted by the data formatting rule that when selected provides options for reviewing formatting changes.

17. The system of claim 15, further comprising displaying a user interface element in the first range when a confidence level is below a predetermined threshold.

18. The system of claim 15, wherein the edits are determined to have been made to the first plurality of items when the first column is edited to include data that is also included in a second column and a third column.

19. The system of claim 15, further comprising displaying a user interface element that allows the data formatting rule to be saved for later use.

20. The system of claim 15, wherein the edits are determined to have been made to the first plurality of items when the first row is edited to include data also included in a second row and a third row.