Method and system for automatically extracting email attachment data based on RPA technology

Abstract

The present invention provides a method and system for automatically extracting email attachment data based on RPA technology, including: obtaining to-do tasks in the RPA robot task list, generating the current target task and assigning the target task parameters in the RPA robot; querying whether there is a mapping relationship according to the target task parameters and the mapping relationship table, if so, extracting key information into the key information data list, and saving the key information data list in the operation result ledger; if not, recording it in the exception ledger; repeating steps S2 to S3 until all the to-do tasks in the RPA robot task list are processed; sending the data of the operation result ledger and the exception ledger to relevant personnel. The present invention achieves periodic automatic extraction of scattered information of email spreadsheet attachments based on the RPA robot by mapping the mail system address with the position parameters of the valid information of the spreadsheet attachment, thereby improving work efficiency.

Description

Technical Field

[0001] The present invention relates to the field of robot automation technology, and in particular to a method and system for automatically extracting email attachment data based on RPA technology, and in particular to a method for periodically automatically extracting scattered information from email spreadsheet attachments based on RPA technology. Background Art

[0002] RPA (Robotic Process Automation) refers to the use of software automation in various industries to implement operations that were originally completed by computers operated by humans. It allows software robots to automatically handle a large number of repetitive, rule-based workflow tasks. For example, in the business processes of banks, there are usually paper document input, document ticket verification, data extraction from emails and files, cross-system data migration, automated IT application operations, etc. These tasks can be completed accurately and quickly by software robots, which can reduce human errors, ensure zero errors, improve efficiency, and greatly reduce operating costs. RPA relies on advanced software automation technology, RPA relies on advanced software automation technology, greatly reduces development costs and development cycles, realizes the automation of cross-systems and workflows within banks, improves productivity, and reduces costs.

[0003] Patent document CN113177474A discloses a method and system for automatic data reading and entry based on RPA technology. The method uses a data processing robot to perform the following steps: locate the Excel file and determine the location of the Excel table to be processed; automatically open the Excel table, and determine the location of the currently read row in the Excel table according to the data entry progress; use a single whole row reading method to read the Excel data in the row to obtain a row vector data set; locate the row vector data in the row vector data set, and perform data cleaning on the row vector data; enter the cleaned row vector data row by row into the browser Form table; the data processing robot is established based on RPA technology.

[0004] However, the existing technology can only realize batch operations without differentiation of data or solve the problem of extracting target data at a single fixed address, which affects the stability of data extraction and work efficiency. Summary of the invention

[0005] In view of the defects in the prior art, the purpose of the present invention is to provide a method and system for automatically extracting email attachment data based on RPA technology.

[0006] A method for automatically extracting email attachment data based on RPA technology provided by the present invention includes:

[0007] Step S1: Establishing a mapping relationship table between the mail system address and the location parameters of the key information of the attachment data;

[0008] Step S2: Obtain to-do tasks in the RPA robot task list, generate the current target task, and assign target task parameters to the RPA robot;

[0009] Step S3: query whether there is a mapping relationship according to the target task parameters and the mapping relationship table; if so, extract key information into a key information data list, and save the key information data list in the operation result ledger; if not, record it in the exception ledger;

[0010] Repeat steps S2 to S3 until all pending tasks in the RPA robot task list are processed;

[0011] Step S4: Send the data of the operation result ledger and the abnormality ledger to relevant personnel.

[0012] Preferably, the environment in which the RPA robot is located is initialized before establishing the mapping relationship table;

[0013] Initializing the environment of the RPA robot includes cleaning the operating environment and establishing global variables required for the operation of the RPA robot according to system parameter configuration;

[0014] The global variables include: initialization parameter dictionary, email task dictionary, key information data, and template address parameter dictionary.

[0015] Preferably, the template address parameter dictionary stores a mapping relationship table between the mail system address and the location parameters of the key information of the attachment data;

[0016] The assigned data of the address parameters in the template address parameter dictionary include the sender's mail system address and the location parameters of the key information of the attachment data.

[0017] Preferably, step S2 comprises:

[0018] Step S2.1: The RPA robot logs in to the mailbox and reads the inbox information in the mailbox, generates an RPA robot task list and stores it in the email task dictionary;

[0019] Step S2.2: saving the attachment data of the corresponding email in the email task dictionary; the attachment data includes a spreadsheet;

[0020] Step S2.3: The RPA robot moves all emails out of the specified inbox folder.

[0021] Preferably, step S3 comprises:

[0022] Step S3.1: Query the sender's email system address of the current target task in the RPA robot task list;

[0023] Step S3.2: obtaining key information contained in the email spreadsheet attachment of the target task according to the template address parameter dictionary;

[0024] Step S3.3: Verify the key information. If the verification passes, add the key information to the key information data list, and save the key information data list in the operation result ledger; if the verification fails, record it in the exception ledger.

[0025] A system for automatically extracting email attachment data based on RPA technology provided by the present invention includes:

[0026] Module M1: Establish a mapping relationship table between the mail system address and the location parameters of the key information of the attachment data;

[0027] Module M2: Obtains pending tasks in the RPA robot task list, generates the current target task, and assigns target task parameters to the RPA robot;

[0028] Module M3: query whether there is a mapping relationship according to the target task parameters and the mapping relationship table. If so, extract key information into a key information data list, and save the key information data list in the operation result ledger; if not, record it in the exception ledger;

[0029] Repeat triggering modules M2 to S3 until all pending tasks in the RPA robot task list are processed;

[0030] Module M4: Send the data of the operation result ledger and the abnormal ledger to relevant personnel.

[0031] Preferably, the environment in which the RPA robot is located is initialized before establishing the mapping relationship table;

[0032] Initializing the environment of the RPA robot includes cleaning the operating environment and establishing global variables required for the operation of the RPA robot according to system parameter configuration;

[0033] The global variables include: initialization parameter dictionary, email task dictionary, key information data, and template address parameter dictionary.

[0034] Preferably, the template address parameter dictionary stores a mapping relationship table between the mail system address and the location parameters of the key information of the attachment data;

[0035] The assigned data of the address parameters in the template address parameter dictionary include the sender's mail system address and the location parameters of the key information of the attachment data.

[0036] Preferably, module M2 comprises:

[0037] Module M2.1: The RPA robot logs in to the mailbox and reads the inbox information in the mailbox, generates the RPA robot task list and stores it in the email task dictionary;

[0038] Module M2.2: save the attachment data of the corresponding email in the email task dictionary; the attachment data includes a spreadsheet;

[0039] Module M2.3: The RPA robot moves all emails out of the specified inbox folder.

[0040] Preferably, module M3 comprises:

[0041] Module M3.1: Query the sender's email system address of the current target task in the RPA robot task list;

[0042] Module M3.2: obtaining key information contained in the email spreadsheet attachment of the target task according to the template address parameter dictionary;

[0043] Module M3.3: Verify the key information. If the verification passes, add the key information to the key information data list, and save the key information data list in the operation result ledger; if the verification fails, record it in the exception ledger.

[0044] Compared with the prior art, the present invention has the following beneficial effects:

[0045] 1. The present invention establishes a mapping between the mail system address and the position parameters of the valid information of the electronic form attachment, and then based on the RPA robot, realizes the periodic automatic extraction of scattered information in the mail electronic form attachment, thereby improving work efficiency.

[0046] 2. The present invention establishes a mapping relationship between the sender's email system address and the key information position parameter information of the electronic form attachment, so that the RPA robot shields the differences in key valid information of different sender email system addresses, and can accurately extract scattered valid information in the email attachment.

[0047] 3. The present invention is based on RPA technology. The low-code mode has low requirements for implementation personnel. It conveniently connects daily office tools such as Excel and mailboxes through the interface-free mode. Daily business operation and maintenance and business parameter entry do not require loading other special tool environments. The construction cost is not high and the operation and maintenance are simple. BRIEF DESCRIPTION OF THE DRAWINGS

[0048] Other features, objects and advantages of the present invention will become more apparent from the detailed description of non-limiting embodiments made with reference to the following drawings:

[0049] Figure 1 It is a schematic diagram of the workflow of the present invention. DETAILED DESCRIPTION

[0050] The present invention is described in detail below in conjunction with specific embodiments. The following embodiments will help those skilled in the art to further understand the present invention, but are not intended to limit the present invention in any form. It should be noted that, for those of ordinary skill in the art, several changes and improvements can also be made without departing from the concept of the present invention. These all belong to the protection scope of the present invention.

[0051] Based on the uniqueness of the mail system address, the present invention maps the mail system address with the position parameters of the valid information of the electronic form attachment, and builds a mapping relationship table with all the mail system addresses related to the business and the valid information position parameters of the electronic form attachment mapped with the above address, and accurately extracts the effective scattered information of the electronic form attachment through the mapping relationship table. Based on the low-code operation of RPA technology, the receiving mailbox is scanned regularly, the mailbox mail is operated, the mail processing ledger is made, the abnormal information is registered, and the processing results are notified, so as to realize the periodic automatic extraction of scattered information of the mail electronic form attachment.

[0052] Embodiment 1

[0053] According to a method for automatically extracting email attachment data based on RPA technology provided by the present invention, Figure 1 As shown, including:

[0054] Step S1: Establishing a mapping relationship table between the mail system address and the location parameters of the key information of the attachment data;

[0055] Step S2: Obtain the pending tasks in the RPA robot task list, generate the current target task and assign the target task parameters in the RPA robot; Step S2 includes:

[0056] Step S2.1: The RPA robot logs in to the mailbox and reads the inbox information in the mailbox, generates an RPA robot task list and stores it in the email task dictionary;

[0057] Step S2.2: saving the attachment data of the corresponding email in the email task dictionary; the attachment data includes a spreadsheet;

[0058] Step S2.3: The RPA robot moves all emails out of the specified inbox folder.

[0059] Step S3: query whether there is a mapping relationship according to the target task parameters and the mapping relationship table. If so, extract the key information into the key information data list, and save the key information data list in the operation result ledger; if not, record it in the exception ledger; Step S3 includes:

[0060] Step S3.1: Query the sender's email system address of the current target task in the RPA robot task list;

[0061] Step S3.2: obtaining key information contained in the email spreadsheet attachment of the target task according to the template address parameter dictionary;

[0062] Step S3.3: Verify the key information. If the verification passes, add the key information to the key information data list, and save the key information data list in the operation result ledger; if the verification fails, record it in the exception ledger.

[0063] Repeat steps S2 to S3 until all pending tasks in the RPA robot task list are processed;

[0064] Step S4: Send the data of the operation result ledger and the abnormality ledger to relevant personnel.

[0065] Before establishing the mapping relationship table, the environment in which the RPA robot is located is initialized; the initialization of the environment in which the RPA robot is located includes cleaning the operating environment and establishing the global variables required for the operation of the RPA robot according to the system parameter configuration; the global variables include: initialization parameter dictionary, email task dictionary, key information data, template address parameter dictionary. The template address parameter dictionary stores a mapping relationship table between the email system address and the location parameters of the key information of the attachment data; the assignment data of the address parameters in the template address parameter dictionary include the sender's email system address and the location parameters of the key information of the attachment data.

[0066] Specifically, the workflow of the present invention is further described with reference to the accompanying drawings:

[0067] The RPA robot system parameter configuration that needs to be initialized in the present invention is shown in Table 1, including: local parameter storage directory, email account, email password credentials, robot operation result ledger storage address, and specified email subject keywords;

[0068] Table 1

[0069]

[0070] First, initialize the robot's scene environment, including ending the browser and Excel processes, and establish the global variables required for the RPA robot to run. The global variables and corresponding parameter types are shown in Table 2. The robot obtains initialization parameters. For example, read the RPA robot system parameter configuration file that needs to be initialized to generate an initialization parameter dictionary:

[0071] Table 2

[0072] Serial number name variable Parameter Type 1 Initialization parameter dictionary Dic_Config Dictionanry<String,Object> 2 Mail Task Dictionary Dic_Mail_Task Dictionanry<String,Object> 3 Key information data DT_Mail_Key_Message DataTable 4 Template address parameter dictionary Dic_Model_Dir_Message Dictionanry<String,Object>

[0073] Then, the template address parameter dictionary is assigned. The source of the address parameter assignment data in the template address parameter dictionary is the email template address parameter spreadsheet stored in the local parameter storage directory. The result is stored in the template address parameter dictionary Dic_Model_Dir_Message. The parameter type is Dictionary<String,Object> . Among them, the email template address parameter spreadsheet includes: the sender's email system address Mail_Address_From, the key information position Address_Key_Message, the sender's email system address is a required column, the key information position contains at least 1 column, and the email template address parameter spreadsheet style is shown in Table 3, where the letters in the key information position represent the number of columns of the key information in the spreadsheet attachment, and the numbers represent the number of rows of the key information in the spreadsheet attachment:

[0074] Table 3

[0075]

[0076] The template address parameter dictionary is a dictionary of mapping relationship between the sender's mail system address and the key information position parameter information of the electronic form attachment. The key is the sender's mail system address Mail_Address_From, and the value is the table object DataTable formed by the key valid information and its position parameter in the electronic form. By adding and modifying the corresponding parameters of the mail template address parameter electronic form mail system address and key information position, the position change of the sender's mail electronic form key valid information is recorded and modified.

[0077] After the above basic work is completed, the robot logs in to the mailbox through the page or client, scans the specified inbox regularly, and obtains the inbox information of all emails in the mailbox. The inbox information and corresponding parameter types are shown in Table 4:

[0078] Table 4

[0079] Serial number name variable Parameter Type 1 Sender's address Mail_Address_From String 2 Email sending time Mail_Time_Arrived String 3 Email Cc Mail_CC String 4 Email Subject Mail_Subject String

[0080] At the same time, a spreadsheet attachment of all emails is saved. The spreadsheet attachment is named by concatenating the sender, subject, and sending time, for example: Mail_Address_From+Mail_Subject+Mail_Time_Arrived+'.xlsx'. The RPA robot task list Dic_Mail_Task is generated, for example: [(Mail_Address_From1:[Mail_Subject1,Mail_Time_Arrived1],[Mail_Address_From2:[Mail_Subject2,Mail_Time_Arrived2],...] After obtaining the above information, the robot moves all emails out of the specified inbox folder.

[0081] Next, traverse the Dic_Mail_Task sender mail system address set in the RPA robot task list. In the traversal loop, obtain the key valid information contained in all email spreadsheet attachments from the template address parameter dictionary obtained above. Specifically, the robot grabs the key information stored in the local spreadsheet attachments one by one according to the list of key information locations and saves them in the mail list List_Mail_Message, for example: [Mail_Message1, Mail_message2,...]. Based on the template address parameter dictionary obtained above, the RPA robot shields the differences in key valid information of different sender mail system addresses, and realizes the accurate extraction of scattered valid information.

[0082] Next, the obtained mail list List_Mail_Message information is verified according to the regular verification rule regex. If the verification fails, the verification result will be recorded in the abnormal ledger. If the verification passes, the result of the regular verification rule regex will be saved and the List_Mail_Message information will be added to the template address parameter dictionary DT_Mail_Key_Message.

[0083] Finally, after all tasks in the RPA robot task list are traversed, the results generated by the robot are saved and the operation results are notified to relevant personnel by email. Specifically, the template address parameter dictionary DT_Mail_Key_Message is saved in the robot operation result ledger storage address Str_Dir_Record_Got, and the naming format is, for example: "'operation result'+Now.ToString(yyyyMMdd).xlsx". If a file with the same name already exists, the result of this operation is added to the end. The robot sends the operation results to business-related personnel by email, and sends the exception ledger to the process operation and maintenance personnel by email.

[0084] Embodiment 2

[0085] The present invention also provides a system for automatically extracting email attachment data based on RPA technology. Those skilled in the art can implement the system for automatically extracting email attachment data based on RPA technology by executing the step flow of the method for automatically extracting email attachment data based on RPA technology, that is, the method for automatically extracting email attachment data based on RPA technology can be understood as a preferred implementation of the system for automatically extracting email attachment data based on RPA technology.

[0086] According to a system for automatically extracting email attachment data based on RPA technology provided by the present invention, the environment in which the RPA robot is located is first initialized; the initialization of the environment in which the RPA robot is located includes cleaning the operating environment and establishing global variables required for the operation of the RPA robot according to the system parameter configuration; the global variables include: initialization parameter dictionary, email task dictionary, key information data, template address parameter dictionary. The template address parameter dictionary stores a mapping relationship table between the email system address and the location parameters of the key information of the attachment data; the assignment data of the address parameters in the template address parameter dictionary include the sender's email system address and the location parameters of the key information of the attachment data.

[0087] Then include the following modules:

[0088] Module M1: Establish a mapping relationship table between the mail system address and the location parameters of the key information of the attachment data;

[0089] Module M2: Obtains pending tasks in the RPA robot task list, generates the current target task, and assigns target task parameters to the RPA robot;

[0090] Specifically, module M2 includes: Module M2.1: The RPA robot logs in to the mailbox and reads the inbox information in the mailbox, generates an RPA robot task list and stores it in the email task dictionary; Module M2.2: Saves the attachment data of the corresponding email in the email task dictionary; the attachment data includes a spreadsheet; Module M2.3: The RPA robot moves all emails out of the designated inbox folder.

[0091] Module M3: query whether there is a mapping relationship according to the target task parameters and the mapping relationship table. If so, extract key information into a key information data list, and save the key information data list in the operation result ledger; if not, record it in the exception ledger;

[0092] Specifically, module M3 includes: Module M3.1: querying the sender's email system address of the current target task in the RPA robot task list; Module M3.2: obtaining the key information contained in the email spreadsheet attachment of the target task according to the template address parameter dictionary; Module M3.3: verifying the key information, and if the verification passes, adding the key information to the key information data list, and saving the key information data list in the operation result ledger; if the verification fails, recording it in the exception ledger.

[0093] Repeat triggering modules M2 to S3 until all pending tasks in the RPA robot task list are processed;

[0094] Module M4: Send the data of the operation result ledger and the abnormal ledger to relevant personnel.

[0095] Those skilled in the art know that, in addition to implementing the system, device and its various modules provided by the present invention in a purely computer-readable program code, it is entirely possible to implement the same program in the form of logic gates, switches, application-specific integrated circuits, programmable logic controllers and embedded microcontrollers by logically programming the method steps. Therefore, the system, device and its various modules provided by the present invention can be considered as a hardware component, and the modules included therein for implementing various programs can also be considered as structures within the hardware component; the modules for implementing various functions can also be considered as both software programs for implementing the method and structures within the hardware component.

[0096] The above describes the specific embodiments of the present invention. It should be understood that the present invention is not limited to the above specific embodiments, and those skilled in the art can make various changes or modifications within the scope of the claims, which does not affect the essence of the present invention. In the absence of conflict, the embodiments of the present application and the features in the embodiments can be combined with each other arbitrarily.

Claims

1. A method for automatically extracting email attachment data based on RPA technology, characterized in that: include: Step S1: Establishing a mapping relationship table between the mail system address and the location parameters of the key information of the attachment data; Step S2: Obtain to-do tasks in the RPA robot task list, generate the current target task, and assign target task parameters to the RPA robot; Step S3: query whether there is a mapping relationship according to the target task parameters and the mapping relationship table; if so, extract key information into a key information data list, and save the key information data list in the operation result ledger; if not, record it in the exception ledger; Repeat steps S2 to S3 until all pending tasks in the RPA robot task list are processed; Step S4: Send the data of the operation result ledger and the abnormal ledger to relevant personnel; Initializing the environment of the RPA robot before establishing the mapping relationship table; Initializing the environment of the RPA robot includes cleaning the operating environment and establishing global variables required for the operation of the RPA robot according to system parameter configuration; The global variables include: initialization parameter dictionary, email task dictionary, key information data, template address parameter dictionary; The template address parameter dictionary stores a mapping relationship table between the mail system address and the location parameters of the key information of the attachment data; The address parameter assignment data in the template address parameter dictionary includes the sender's mail system address and the location parameters of the key information of the attachment data; The step S2 comprises: Step S2.1: The RPA robot logs in to the mailbox and reads the inbox information in the mailbox, generates an RPA robot task list and stores it in the email task dictionary; Step S2.2: saving the attachment data of the corresponding email in the email task dictionary; the attachment data includes a spreadsheet; Step S2.3: The RPA robot moves all emails out of the specified inbox folder; Step S3 includes: Step S3.1: Query the sender's email system address of the current target task in the RPA robot task list; Step S3.2: obtaining key information contained in the email spreadsheet attachment of the target task according to the template address parameter dictionary; Step S3.3: Verify the key information. If the verification passes, add the key information to the key information data list, and save the key information data list in the operation result ledger; if the verification fails, record it in the exception ledger.

2. A system for automatically extracting email attachment data based on RPA technology, characterized in that: include: Module M1: Establish a mapping relationship table between the mail system address and the location parameters of the key information of the attachment data; Module M2: Obtains pending tasks in the RPA robot task list, generates the current target task, and assigns target task parameters to the RPA robot; Module M3: query whether there is a mapping relationship according to the target task parameters and the mapping relationship table. If so, extract key information into a key information data list, and save the key information data list in the operation result ledger; if not, record it in the exception ledger; Repeat triggering modules M2 to S3 until all pending tasks in the RPA robot task list are processed; Module M4: Sending the data of the operation result ledger and the abnormal ledger to relevant personnel; Initializing the environment of the RPA robot before establishing the mapping relationship table; Initializing the environment of the RPA robot includes cleaning the operating environment and establishing global variables required for the operation of the RPA robot according to system parameter configuration; The global variables include: initialization parameter dictionary, email task dictionary, key information data, template address parameter dictionary; The template address parameter dictionary stores a mapping relationship table between the mail system address and the location parameters of the key information of the attachment data; The address parameter assignment data in the template address parameter dictionary includes the sender's mail system address and the location parameters of the key information of the attachment data; Module M2 includes: Module M2.1: The RPA robot logs in to the mailbox and reads the inbox information in the mailbox, generates the RPA robot task list and stores it in the email task dictionary; Module M2.2: save the attachment data of the corresponding email in the email task dictionary; the attachment data includes a spreadsheet; Module M2.3: The RPA robot moves all emails out of the specified inbox folder; Module M3 includes: Module M3.1: Query the sender's email system address of the current target task in the RPA robot task list; Module M3.2: obtaining key information contained in the email spreadsheet attachment of the target task according to the template address parameter dictionary; Module M3.3: Verify the key information. If the verification passes, add the key information to the key information data list, and save the key information data list in the operation result ledger; if the verification fails, record it in the exception ledger.

Images