| During the rapid development of the information technology industry,many companies have noticed that data is of vital importance to corporate decision-making.Enterprises can use this data to analyze the deficiencies of their own composition,predict the future direction and adjust the strategic direction in real time.However,due to historical reasons,the past data of many enterprises is still stored in spreadsheets in the form of pictures,and extracting these data manually is a time-consuming and labor-intensive task.At the same time,because different enterprises have different recording formats for unified data,“data islands” are formed among enterprises,and data results cannot be shared quickly and effectively,and the extraction of the same data will also cause repetitive labor.In order to solve the problem that it is difficult for enterprises to efficiently extract data from such tables in the form of pictures,this thesis studies and implements the key technology of automatic data extraction from spreadsheets based on pictures.This thesis divides these key technologies into two stages: Optical Character Recognition(OCR)and Extraction-FilterLoading(ETL).Among them,OCR can be divided into three stages: text detection,table recognition and text recognition.In text detection,this thesis first studies the application of traditional object detection represented by YOLO v3 on text detection tasks.On the self-labeled dataset in this thesis,YOLO v3 achieved 58.9% m AP,which is not enough to meet the needs of text detection.Then,this thesis studies the connection pre-selected box network(CTPN),which combines the image features of the text,and achieves 84.5% m AP on the same data set,which basically meets the requirements.Finally,this thesis studies the influence of slight rotation on the detection results,and the experiment proves that there is a slight influence but still has use value.In table recognition,the method based on projection method is used to recognize the table without frame.For the table image after preprocessing and noise reduction,the life cycle of the row and column is designed to identify the text on the horizontal and vertical axes.The projection of the detection frame is divided to restore the original arrangement ideas of the author of the table as much as possible,and experiments are carried out on the self-built data set and the Pub Tab Net data set,and the results of Teds value of 92.6% and 84.2% are obtained respectively,which verifies the algorithm.effectiveness.In the text recognition stage,this thesis divides the task into two parts: digital recognition and non-digital recognition.For digital recognition,considering the characteristics of uniform printed digital style,small number of classifications,and no regularity of appearance,this thesis uses a method based on convolutional neural network(CNN)after character segmentation for recognition;for non-digital recognition,Due to the regularity of the appearance of Chinese words,the text uses a method based on a combination of long shortterm memory network(LSTM)and CTC for non-digital recognition.For the combination of these two algorithms,this thesis has verified on the self-generated dataset and the Chinese OCR dataset,respectively,and achieved 93.6% and 82.4% accuracy on the validation set,respectively.In the ETL stage,this thesis proposes a semantic tree-based unstructured table ETL method.First,two semantic tree models are established based on the correspondence between data and table row names and column names,and the two semantic trees are used to generate a metadata set containing data items and the row names and column names to which the data items belong;then,through regular expressions semantically matches the row and column names to which each metadata belongs,and deletes unnecessary itemized or aggregated metadata from the set;then,further data cleaning is performed through three dictionary-based filtering strategies,and the remaining The metadata is imported into the data warehouse;finally,the text is manually annotated with some datasets and experiments are performed,and an accuracy rate of 95.15% and a recall rate of 86.51% are obtained.The experimental results show that the data extracted by this method can meet the needs of investigating the status quo of enterprise development and compiling and developing future plans. |
