KR20230023906A - Excel document ai conversion system - Google Patents

Abstract

Disclosed is an artificial intelligence (AI)-based Excel document conversion system. According to the present invention, the AI-based Excel document conversion system comprises: an Excel document input module receiving an Excel document; an Excel document pre-processing module performing pre-processing on the Excel document input from the Excel document input module; and an item classification prediction module classifying key values of the Excel document into a predetermined item names on the basis of a pre-processing result of the Excel document performed in the Excel document preprocessing module to perform prediction. Accordingly, the system is configured to automatically convert Excel documents in various formats created by each company into Excel documents in other standardized formats, thereby providing an effect of increasing the accuracy and efficiency of converting the Excel documents. In particular, the system is configured to perform automatic mapping between existing formats and report formats through linguistic analysis, morphological analysis, character analysis, and the like, thereby providing an effect of correctly converting various corporate inventory management documents used separately by each company without a separate manual mapping process.

Description

Excel document artificial intelligence conversion system {EXCEL DOCUMENT AI CONVERSION SYSTEM}

The present invention relates to an Excel document conversion system, and more specifically to an Excel document artificial intelligence conversion system.

In many cases, companies create and manage Excel documents in their own format for inventory management or tax management. In this case, as shown in FIG. 1, a process of converting the documents into standardized documents for various trade, foreign exchange, and reporting purposes such as invoices or customs is required.

In this process, there are many cases in which handwriting is converted one by one, and there are naturally many errors in the conversion process, and efficiency is reduced.

Accordingly, there is a need to reduce errors and increase efficiency by automatically converting Excel documents for inventory management and tax management in various formats possessed by existing companies into standardized documents for customs declaration and tax reporting.

Registered Patent Publication 10-0591474 Registered Patent Publication 10-0990846

An object of the present invention is to provide an artificial intelligence conversion system for Excel documents.

Excel document artificial intelligence conversion system according to the object of the present invention described above, Excel document input module for receiving an input Excel document; an Excel document pre-processing module that performs pre-processing on the Excel document input from the Excel document input module; a line classifier for classifying lines of the Excel document into one of predetermined line classifications based on a result of pre-processing the Excel document performed by the Excel document pre-processing module; a document classifier for classifying the Excel document into one of a predetermined document classification based on a preprocessing result of the Excel document performed by the Excel document preprocessing module; It may be configured to include an item classifier that classifies an item of the Excel document into one of a predetermined item classification based on a result of preprocessing the Excel document performed by the Excel document preprocessing module.

Here, the Excel document may be composed of an Excel file according to a company's own format.

In addition, the predetermined item name may be configured as an item name for reporting a fault.

The line classifier may include a line classification prediction module that classifies each line of the Excel document based on the preprocessing result of the Excel document performed by the Excel document preprocessing module and predicts one of an excluded line, an item line, and a data line; a line classification selection module for modifying and selecting a line classification result according to a prediction result of the line classification prediction module according to a user's input; It may be configured to further include a line classification model generation module for learning and generating a line classification model for classifying each line according to the line classification result selected by the line classification selection module.

The document classifier may include a document classification prediction module for classifying and predicting an Excel document based on a preprocessing result of the Excel document performed by the Excel document preprocessing module; a document classification selection module that modifies and selects a document classification result according to a prediction result of the document classification prediction module according to a user's input; It may be configured to further include a document classification model generation module for learning and generating a document classification model for classifying each document according to the document classification result selected by the document classification selection module.

The item classifier may include an item classification prediction module that classifies and predicts a key value of an Excel document into a predetermined item name based on a result of preprocessing the Excel document performed by the Excel document preprocessing module; an item classification selection module that modifies and selects an item classification result according to a prediction result of the item classification prediction module according to a user's input; It may be configured to include an item classification model generation module for learning and generating an item classification model for classifying key values corresponding to predetermined items according to the item classification result selected by the item classification selection module.

The Excel document pre-processing module may be configured to identify the language of the Excel document, analyze morphemes of the identified language, and recognize characters in units of characters.

The line classification prediction module may be configured to classify each line using the line classification model generated by the line model generation module and predict one of an excluded line, an item line, and a data line.

The line model generation module may be configured to learn and generate a line classification model for classifying each line according to the frequency of occurrence of words appearing in each line of the Excel document.

And the document classification prediction module is configured to classify each Excel document by using the document classification model generated in the document classification model generation module, classify and predict it as one of export declaration, invoice, sales transaction statement, and purchase transaction statement. It can be.

The item classification prediction module may be configured to classify and predict the key value of the Excel document into a predetermined item name using the item classification model generated by the item classification model generation module.

According to the above-mentioned Excel document artificial intelligence conversion system, it is configured to automatically convert Excel documents of various formats created in each company into Excel documents of other standardized formats, thereby increasing the accuracy and efficiency of Excel document conversion. there is

Specifically, it is configured to perform automatic mapping between the existing format and the reporting format through language analysis, morpheme analysis, character analysis, etc., so that various corporate inventory management documents used separately by each company can be accurately and without a separate manual mapping process. There is a transformative effect.

1 is a schematic diagram showing a handwriting conversion method of an Excel document according to a conventional method.
2 is a schematic diagram showing an automatic conversion method of an Excel document according to the present invention.
3 is a block diagram of an artificial intelligence conversion system for Excel documents according to an embodiment of the present invention.
4 to 10 are exemplary screen views of an AI conversion system for Excel documents according to an embodiment of the present invention.

Since the present invention can make various changes and have various embodiments, specific embodiments will be illustrated in the drawings and described in detail in specific contents for practicing the invention. However, this is not intended to limit the present invention to specific embodiments, and should be understood to include all modifications, equivalents, and substitutes included in the spirit and scope of the present invention. Like reference numerals have been used for like elements throughout the description of each figure.

Terms such as first, second, A, and B may be used to describe various components, but the components should not be limited by the terms. These terms are only used for the purpose of distinguishing one component from another. For example, a first element may be termed a second element, and similarly, a second element may be termed a first element, without departing from the scope of the present invention. The terms and/or include any combination of a plurality of related recited items or any of a plurality of related recited items.

It is understood that when an element is referred to as being "connected" or "connected" to another element, it may be directly connected or connected to the other element, but other elements may exist in the middle. It should be. On the other hand, when an element is referred to as “directly connected” or “directly connected” to another element, it should be understood that no other element exists in the middle.

Terms used in this application are only used to describe specific embodiments, and are not intended to limit the present invention. Singular expressions include plural expressions unless the context clearly dictates otherwise. In this application, the terms "include" or "have" are intended to designate that there is a feature, number, step, operation, component, part, or combination thereof described in the specification, but one or more other features It should be understood that the presence or addition of numbers, steps, operations, components, parts, or combinations thereof is not precluded.

Unless defined otherwise, all terms used herein, including technical or scientific terms, have the same meaning as commonly understood by one of ordinary skill in the art to which the present invention belongs. Terms such as those defined in commonly used dictionaries should be interpreted as having a meaning consistent with the meaning in the context of the related art, and unless explicitly defined in the present application, they should not be interpreted in an ideal or excessively formal meaning. don't

Hereinafter, preferred embodiments according to the present invention will be described in detail with reference to the accompanying drawings.

2 is a schematic diagram showing an automatic conversion method of an Excel document according to the present invention.

Referring to Figure 2, it can be configured to automatically convert the ERP of the company or the Excel document in the company's own format into an Excel document for trade, foreign exchange, and reporting using artificial intelligence.

3 is a block diagram of an artificial intelligence conversion system for Excel documents according to an embodiment of the present invention, and FIGS. 4 to 10 are screen views of an artificial intelligence conversion system for Excel documents according to an embodiment of the present invention.

First, referring to FIG. 3, the Excel document artificial intelligence conversion system 100 according to an embodiment of the present invention includes an Excel document input module 101, an Excel document preprocessing module 102, a line classification prediction module 103, a line Classification selection module (104), line classification model generation module (105), document classification prediction module (106), document classification selection module (107), document classification model generation module (108), item classification prediction module (109), item It may be configured to include a classification selection module 110 and an item classification model generation module 111 .

Hereinafter, a detailed configuration will be described.

The Excel document input module 101 may be configured to receive an Excel document.

Here, the Excel document may be a company's inventory management document, tax management document, personnel management document, export declaration, invoice, sales transaction statement, purchase transaction statement, etc., and there is no limit to the type thereof.

The Excel document pre-processing module 102 may be configured to perform pre-processing on the Excel document input from the Excel document input module 101 .

Specifically, the Excel document pre-processing module 102 may be configured to identify the language of the Excel document, analyze morphemes of the identified language, and recognize characters in units of characters. That is, analysis can be performed in the order of language, morpheme, and character.

Table 1 below illustrates the preprocessing result of an Excel document.

Referring to Table 1, the language of the original content of the Excel document may be identified as Vietnamese, English, Korean, etc., and after analyzing the morpheme of each item according to the language, it may be configured to recognize each character. Since each item in an Excel document often uses words that are only used by itself, such as "Im.-Ex. Product Code", it is necessary to accurately understand the meaning of these words through morphological analysis and character recognition.

The line classifier is configured to classify each line of the Excel document into an exclusion line, an item line, and a data line, and the line classification can be used to identify the type of the corresponding Excel document and each item on the document.

The line classifier may include a line classification prediction module 103 , a line classification selection module 104 , and a line classification model generation module 105 .

The line classification prediction module 103 may be configured to classify each line of the Excel document based on the preprocessing result of the Excel document performed by the Excel document preprocessing module 102 and predict one of an excluded line, an item line, and a data line. there is.

4 illustrates classification of each line of the Excel document into an exclusion line, an item line, and a data line. It can be seen that the exclusion line consists of completely meaningless contents, the item line corresponds to the key value of Excel, and the data line corresponds to the value of the corresponding key value. For example, an item line may be “received quantity of part #1” and a corresponding data line may be displayed as “120 pieces”. And FIG. 5 illustrates a prediction result according to the prediction of FIG. 4 .

The line classification selection module 104 may be configured to modify and select the line classification result according to the prediction result of the line classification prediction module 103 according to a user's input. Since there may be errors in the prediction of the line classification prediction module 103, the line classification selection module 104 may select a line classification by modifying the prediction result according to the user's input. 6 illustrates a line classification selection screen. Line 1 of FIG. 6 is predicted and classified as an item line and lines 2-10 are predicted and classified as an excluded line, but it illustrates that the user can manually change lines 2-10 to item lines or data lines. .

The line classification model generation module 105 may be configured to learn and generate a line classification model for classifying each line according to the line classification result selected in the line classification selection module 104 . The line classification model generation module 105 may generate a line classification model for classifying each line using artificial intelligence, particularly machine learning. Accuracy of the line classification model increases as line classification prediction and classification work by user modification are cumulatively performed.

In this way, using the line classification model generated in the line classification model generation module 105, the line classification prediction module 103 may be configured to classify each line and predict one of an excluded line, an item line, and a data line.

The line classification model generation module 105 may be configured to learn and generate a line classification model for classifying each line according to the occurrence frequency of words appearing in each line of the Excel document. There may be a word that frequently appears in a specific item line, and the frequency of occurrence of such word can determine whether the corresponding line is an item line and which item line it is.

The line classification model generation module 105 may be configured to generate a data set that is a classification criterion based on a result of morpheme analysis through preprocessing. Table 2 below shows data sets according to the results of morpheme analysis and corresponding line classification items.

The appearance frequency of each line of words in this data set may be an important criterion for classifying the corresponding line.

As shown in Table 3 below, the criterion can be expressed as a document term matrix (DTM) for calculating the probability that a specific word is included in each line classification.

And Table 4 below shows that the occurrence frequency table for each word is generated based on the DTM of Table 3.

Through Table 3, it is possible to calculate the frequency of whether specific words such as GUIDANCE, ADDRESS, PRODUCT, COTTON, and FABRIC are included in each line, and depending on the line appearance of each word, whether the line is an exclusion line, an item line, or a data line can know whether As a result, Table 4 can be generated using the DTM of Table 3, and each line can ultimately be classified as shown in Table 2.

Further, the line classification model generation module 105 may calculate a probability corresponding to a specific line classification based on a specific word included in each line by Equation 1 below.

Here, P(B/A) is the probability of item classification (B) when a specific word (A) is included, P(B) is the probability of a specific category (B), and P(A/B) is a specific category (B) is the probability that a specific word (A) is included, and P(A) is the probability that a specific word (A) is included.

The line classification model generation module 105 may learn and establish a machine learning model while passing through the above process of generation of DTM and classification through probability calculation.

The results of cross-validation of the machine learning model of the line classification model generation module 105 showed high accuracy as shown in Table 5 below.

Based on the table of occurrence frequency by word in Table 4 above, the probability of whether the Excel line having the word “product” belongs to the item classification can be obtained by Equation 2 below. In the case of Table 4, the total frequency is 6, the item classification frequency is 2, and the frequency of occurrence of the word “product” is 2, so the calculation probability is as follows.

Here, P(B) is 2/6 as the probability of item classification, P(A/B) is 2/2 as the probability that the product word is included in the item class, and P(A) is the probability that the word "product" will be included. The probability is 2/6.

The document classifier may be configured to identify and classify types of Excel documents.

The document classifier may include a document classification prediction module 106, a document classification selection module 107, and a document classification model generation module 108.

The document classification prediction module 106 may be configured to classify and predict the type of the Excel document based on the preprocessing result of the Excel document performed by the Excel document preprocessing module 102 . That is, the document classification prediction module 106 is a component that classifies and predicts the types of various Excel documents such as export declarations, invoices, sales transaction statements, and purchase transaction statements. 7 is a result of predicting the type of document, and this prediction result can be achieved through a document classification model to be described later.

The document classification selection module 107 may be configured to modify and select a document classification result according to a prediction result of the document classification prediction module 106 according to a user's input. 8 illustrates a document classification selection screen.

The document classification model generation module 108 may be configured to learn and generate a document classification model for classifying each document according to the document classification result selected by the document classification selection module 107 .

And the document classification prediction module 106 classifies each Excel document using the document classification model generated in the document classification model generation module 108 and classifies it into one of export declaration, invoice, sales transaction statement, and purchase transaction statement It can be configured to predict.

The document classification model generation module 108 may generate a document classification model for classifying documents according to a morphological analysis result included in the item line finally selected in the line classification selection module 104 . Table 6 below illustrates a data set corresponding to the morphological analysis result of the final item line and a corresponding document classification.

Table 7 below illustrates a Document Term Matrix (DTM) for calculating the probability that a specific word is included in each document.

Table 8 below illustrates an appearance frequency table for each word generated based on the DTM of Table 7.

Based on the words included in each morpheme, the probability of a specific document when a specific word is included can be calculated through Equation 3 below.

Here, P(B/A) is the probability of a specific document (B) when a specific word (A) is included, P(B) is the probability of a specific document (B), and P(A/B) is a specific document (B) is the probability that a specific word (A) is included, and P(A) is the probability that a specific word (A) is included.

Table 9 below shows the results of cross-validation for the above algorithm, showing a high accuracy of 93.59%.

The item classifier may be configured to classify the items of the Excel document into one of the predetermined item classifications.

The item classifier may be configured to include an item classification prediction module 109 , an item classification selection module 110 , and an item classification model generation module 111 .

The item classification prediction module 109 may be configured to classify and predict a key value of an Excel document into a predetermined item name based on a result of preprocessing the Excel document performed by the Excel document preprocessing module 102 . An Excel document can be viewed as being composed of a set of keys and values. Keys in an Excel document can be viewed as items, and values can be viewed as data. However, since the names of the keys in the Excel document are separately designated and used, the names of various keys may be configured to be converted into previously designated items for customs declaration or designated items for other purposes. 9 shows that key values, that is, item codes are mapped by corresponding to predetermined item names. In FIG. 9, the item of the Excel document is a warehouse number, but it is mapped to a pre-designated item name, a warehouse number. That is, item names that can be used in various ways, such as a warehouse number and a warehouse receipt number, can be mapped to one item name called a warehouse number for each excel document of companies.

The item classification selection module 110 may be configured to modify and select the item classification result according to the predicted result of the item classification prediction module 109 according to a user's input. Since there may be errors in item classification prediction, the item classification prediction result may be modified by the user to finally select the item classification. 10 illustrates an item classification selection screen of a user. 10 is a screen showing an operation of selecting an item classification in English, not Korean, unlike the table above. Receipt number, which is an Excel field, exemplifies the operation of being finally selected as an In-Warehouse No. field (Standard field).

The item classification model generation module 111 may be configured to learn and generate an item classification model for classifying key values corresponding to predetermined items according to the item classification result selected by the item classification selection module 110 .

The item classification prediction module 109 may be configured to classify and predict the key value of the Excel document into a predetermined item name using the item classification model generated by the item classification model generation module 111 .

Meanwhile, the item classification model of the item classification model generation module 111 may be generated as follows.

First, Table 10 shows that the item classification model generation module 111 generates a data set based on the results of morpheme analysis included in the item line selected by the line classification selection module 104 and the document selected by the document classification selection module 107. foreshadowing one thing.

And Table 11 illustrates that a data set is created by additionally applying cell character unit decomposition results for classification accuracy.

Table 11 shows the cell value (eg warehouse number) described in the Excel document, the analysis result in the form of a cell obtained by morphologically analyzing it (eg warehouse number), and the cell character unit decomposition result obtained by decomposing the cell morphological analysis result into character units (eg, warehouse number). : warehouse number), and document items (eg, receipt number) mapped to pre-specified items are displayed in order.

Based on the words included in each morpheme, the probability of being a specific item when a specific word is included can be calculated through Equation 4 below.

Here, P(B/A) is the probability of a specific item (B) when a specific word (A) is included, P(B) is the probability of a specific item (B), and P(A/B) is a specific item (B) is the probability that a specific word (A) is included, and P(A) is the probability that a specific word (A) is included.

In the case of the item classification model, the probability of word appearance frequency must be calculated in addition to the probability of word appearance frequency, and the calculation can be calculated by Equation 5 below.

Here, C(B/A) is the probability of being a specific item (B) in the document if a specific character (A) is included, C(B) is the probability of being a specific item (B) in the document, and C(A /B) is the probability that a specific character (A) is included in a specific item (B) in the document, and C(A) is the probability that a specific character (A) is included in the document.

Finally, the probability of being a specific item in the document is shown in Equation 6 below.

Table 12 below shows the results of cross-validation for the above algorithm. That is, it can be seen that the probability of executing both document classification prediction and item classification prediction in the predicted document is high at 89.23%. As mentioned above, item classification calculates both the probability P(B/A) of a specific word and the probability C(B/A) of a specific letter, and calculates the product as the probability of final item prediction. It can be seen that the result of the product of the two probabilities is also a very high value.

Meanwhile, an optimal embodiment with the highest classification prediction accuracy may be a document classifier classifying documents using the line classification result value of the line classifier and item classification using the document classification result value of the document classifier. That is, it may be most accurate to find an item line through line classification, and to perform document classification and item classification based on the item line.

However, although the accuracy may be lower, there may be an embodiment in which the line classifier, the document classifier, and the item classifier each perform a classification operation using the Excel document preprocessing result. Without separately classifying the item line, it is possible to sort out words corresponding to the item line ignoring all numerical data through morphological analysis of the entire document, and through this, it is possible to classify the document or item.

Although described with reference to the above embodiments, those skilled in the art can understand that the present invention can be variously modified and changed without departing from the spirit and scope of the present invention described in the claims below. There will be.

101: Excel document input module
102: Excel document pre-processing module
103: line classification prediction module
104: line classification selection module
105: line classification model generation module
106: document classification prediction module
107: document classification selection module
108: document classification model generation module
109: item classification prediction module
110: item classification selection module
111: item classification model generation module

Claims (11)

An Excel document input module that receives input of an Excel document;
an Excel document pre-processing module that performs pre-processing on the Excel document input from the Excel document input module;
a line classifier for classifying lines of the Excel document into one of predetermined line classifications based on a result of pre-processing the Excel document performed by the Excel document pre-processing module;
a document classifier for classifying the Excel document into one of a predetermined document classification based on a preprocessing result of the Excel document performed by the Excel document preprocessing module;
An artificial intelligence conversion system for an Excel document comprising an item classifier for classifying an item of the Excel document into one of a predetermined item classification based on a preprocessing result of the Excel document performed by the Excel document preprocessing module.
The method of claim 1, wherein the Excel document,
Excel document artificial intelligence conversion system, characterized in that composed of Excel (Excel) file according to the company's own format.
The method of claim 1, wherein the predetermined item name,
Excel document artificial intelligence conversion system composed of item names for reporting faults.
The method of claim 1, wherein the line classifier,
a line classification prediction module that classifies each line of the Excel document based on the preprocessing result of the Excel document performed by the Excel document preprocessing module and predicts one of an excluded line, an item line, and a data line;
a line classification selection module for modifying and selecting a line classification result according to a prediction result of the line classification prediction module according to a user's input;
Excel document artificial intelligence conversion system, characterized in that it is configured to further include a line classification model generation module for learning and generating a line classification model for classifying each line according to the line classification result selected by the line classification selection module.
The method of claim 1, wherein the document classifier,
a document classification prediction module for classifying and predicting an Excel document based on a preprocessing result of the Excel document performed by the Excel document preprocessing module;
a document classification selection module that modifies and selects a document classification result according to a prediction result of the document classification prediction module according to a user's input;
Excel document artificial intelligence conversion system, characterized in that it is configured to further include a document classification model generation module for learning and generating a document classification model for classifying each document according to the document classification result selected by the document classification selection module.
The method of claim 1, wherein the item classifier,
an item classification prediction module that classifies and predicts a key value of an Excel document into a predetermined item name based on a preprocessing result of the Excel document performed by the Excel document preprocessing module;
an item classification selection module that modifies and selects an item classification result according to a prediction result of the item classification prediction module according to a user's input;
An artificial intelligence excel document, characterized in that it is configured to include an item classification model generation module for learning and generating an item classification model for classifying key values corresponding to predetermined items according to the item classification result selected by the item classification selection module. conversion system.
The method of claim 1, wherein the Excel document pre-processing module,
Excel document artificial intelligence conversion system, characterized in that configured to identify the language of the Excel document, analyze the morpheme of the identified language, and recognize characters in character units.
The method of claim 4, wherein the line classification prediction module,
Excel document artificial intelligence conversion system, characterized in that configured to classify each line using the line classification model generated by the line model generation module and predict one of an excluded line, an item line, and a data line.
The method of claim 4, wherein the line model generation module,
Excel document artificial intelligence conversion system, characterized in that configured to learn and generate a line classification model for classifying each line according to the frequency of occurrence of words appearing in each line of the Excel document.
The method of claim 5, wherein the document classification prediction module,
Using the document classification model generated by the document classification model generation module, each Excel document is classified and classified into one of export declaration, invoice, sales transaction statement, and purchase transaction statement, characterized in that for prediction. intelligent conversion system.
The method of claim 6, wherein the item classification prediction module,
Excel document artificial intelligence conversion system, characterized in that configured to predict by classifying the key value of the Excel document into a predetermined item name using the item classification model generated by the item classification model generation module.

Images