SQL-based Excel data import and export system and method

Through SQL-based system design, the task manager, template parser and executor are used to implement asynchronous processing, which solves the time-consuming and development efficiency problems of Excel data import and export system in large file processing, and improves the scalability of the system and customer-friendly.

CN114416789BActive Publication Date: 2024-11-08JIANGSU CARZONE AUTO PARTS CO LTD
View PDF 6 Cites 0 Cited by

Patent Information

Application Number
CN202111576217.3
Authority / Receiving Office
CN · China
Patent Type
Patents(China)
Current Assignee / Owner
Filing Date
2021-12-21
Publication Date
2024-11-08
Estimated Expiration
2041-12-21

AI Technical Summary

Technical Problem

The existing Excel data import and export system takes a long time to process large files, fails synchronization, and has a long development cycle and is prone to errors. The code is redundant and cannot be public.

Method used

Adopt SQL-like system design, including presentation layer, general import and export system and business system, and implement asynchronous task processing through task managers, template parsers and executors to reduce system load and improve business development efficiency.

Benefits of technology

It solves the problem of too long import and export tasks and the difficulty of failed query, improves customer friendliness and development efficiency, shortens the development cycle, and increases the scalability of the system.

✦ Generated by Eureka AI based on patent content.

Smart Images

  • Figure CN114416789B_ABST
    Figure CN114416789B_ABST
Patent Text Reader

Abstract

The present invention discloses a system and method for implementing Excel data import and export based on SQL class, including a display layer, a universal import and export system and a business system. The universal import and export system includes a task manager, a template parser, an executor and an outputter. The universal import and export system realizes the execution function of the overall task. The present invention avoids the problems of long waiting time for synchronous import and export, and failure of synchronous response of import and export through production verification. For repeated historical data, there is no need to increase the load on the system again, and it can be queried directly through history, which greatly improves the customer-friendly use and lowers the threshold of use. The process of business development has been greatly accelerated. The development tasks of the past 2-3 days have been shortened to less than one day, and most logics that are prone to errors have been avoided, ensuring fast and stable support for business. After the import and export logic is incorporated, for later system upgrades, new functions are added, and more general extensibility is obtained.
Need to check novelty before this filing date? Find Prior Art

Description

Technical Field

[0001] The present invention belongs to the technical field of data processing, and in particular relates to a system and method for implementing Excel data import and export based on SQL-like. Background Art

[0002] As a management backend for merchants and operations, there are usually more management requirements for configuration data and sales data, such as bulk listing / delisting of products, order reconciliation, etc. This requires the system to provide more functions for uploading and downloading data through files. The existing Excel data import and export has the following problems: importing and exporting large files takes a long time and synchronization fails; the development cycle of a single function is long and prone to errors, and code redundancy cannot be shared. Summary of the invention

[0003] The technical problem solved by the present invention is to provide a system and method for implementing Excel data import and export based on a SQL-like structure, which can reduce system load, improve business development progress, and be customer-friendly.

[0004] Technical solution: In order to solve the above technical problems, the technical solution adopted by the present invention is as follows:

[0005] A SQL-based Excel data import and export system includes: a display layer, a general import and export system and a business system; the general import and export system includes a task manager, a template parser, an executor and an outputter, and the general import and export system realizes the execution function of the overall task.

[0006] The presentation layer enables users to create tasks and query results, and interact with the general import and export system to perform tasks. First, it interacts with the task manager, which implements specific task creation.

[0007] After receiving the instruction to create a task, the task manager searches for a task template and submits the asynchronous task to the template parser for parsing;

[0008] The template parser implements template parsing, first obtains the template cache, then determines whether there is a template, and if there is no template, uses the parser to parse the template and stores the parsing result in the template cache;

[0009] The executor implements task execution, and during execution, it should be executed according to different business scenarios, import the parsing file and generate an error record file based on the business results; export the generated business result file and output the result.

[0010] Preferably, the specific business execution of the task execution module is realized by calling an external business execution module. When importing, the file is parsed and uploaded to the business execution module through a remote call. When exporting, the data is directly downloaded to the executor through a remote call to the business execution module. Finally, the execution information is saved and the results are recorded.

[0011] A SQL-based Excel data import and export method.

[0012] S1: User creates a task: The user sends a task creation instruction to the general import and export system;

[0013] S2: The general import and export system creates tasks according to user tasks: first, it searches for task templates and determines whether there is a template. If there is a template, it persists the task and submits the task to the template parser. If there is no template, the task creation fails and a failure warning is fed back to the user.

[0014] S3: Template parsing: first obtain the template cache, then determine whether there is a template. If there is a template, proceed to the next step: task execution. If there is no template, use the parser to parse the template and store the parsing result in the template cache. Template parsing includes import template parsing and export template parsing.

[0015] S4: Task execution: When importing, the file is parsed and uploaded to the business execution module of the business system remotely, and the remote result generates a prompt file; when exporting, the business execution module of the business system is directly called, and the remote result generates an export file. Finally, the execution information is saved and the results are recorded. The user can query the results by polling on the result page or query the task results at the specified system location. Task execution specifically includes import task execution and export task execution.

[0016] Preferably, in step S3, the specific steps of importing template parsing are:

[0017] S311: Read the template string, and then split the string by space;

[0018] S312: Read the position of "by", read the character before by and the character after by, and assemble them into detailed input information;

[0019] S313: Read the "parent-by" position, read the character before parent-by and the character after parent-by, and assemble them into parent class packaging input information;

[0020] S314: Read the "form" position, take the next character, and obtain the uploaded file parameter field;

[0021] S315: Read the "unique" position, remove the last character, and obtain the unique configuration;

[0022] S316: read the "exception" position, and then read the as position, including the character before as and the character after as, and assemble them into the exception output information;

[0023] S317: Read the “#” position and assemble it into an additional condition Map.

[0024] Preferably, in step S3, the specific steps of exporting template parsing are:

[0025] S321: Export template parsing, read the template string and split the string by space;

[0026] S322: Read the position of "as", read the character before as and the character after as, and assemble them into output information configuration;

[0027] S323: Read the position of "form", take the next character, and assemble the data acquisition method;

[0028] S324: Read the "where" position and determine whether the position exists. If so, read until the keyword into is encountered, assemble the connector and the field to form a conditional mapping;

[0029] S325: If the "where" position does not exist, read the "into" position, take the next character, and assemble it into an output device;

[0030] S326: Read the “#” position and assemble it into an additional condition Map.

[0031] Preferably, in step S4, the specific steps of importing the task execution are as follows:

[0032] S411: Import execution tasks and read template configuration;

[0033] S412: Download the imported file from the file system;

[0034] S413: Generation of failed excel file: the generated excel file contains error information;

[0035] S414: Read the uploaded Excel file: read the uploaded Excel file line by line to determine whether it meets the batch setting. If it meets the batch setting, jump to step S416; if it does not meet the batch setting, jump to step S415;

[0036] S415: Determine whether the read data is the Excel end-of-row data. If it is the Excel end-of-row data, proceed to step S416 and step S417; if it is not the Excel end-of-row data, return to step S414;

[0037] S416: Data processing, business interface parameters, upload data to the business system; if all succeeded at once, continue to step S414, if not all succeeded at once, write the failure reason to the Excel file;

[0038] S417: After the file is read, the Excel file stream is closed;

[0039] S418: Upload the Excel file to the file server, delete the local Excel file, and then persist the task result.

[0040] Preferably, in step S4, the specific steps of executing the export task are as follows:

[0041] S421: export execution tasks and read template configuration;

[0042] S422: Create an empty excel file;

[0043] S423: Assemble interface parameters, execute and call the business execution module of the business system to execute specific tasks; after the execution is completed, return data, process the data, and write the file into an Excel file;

[0044] S424: Determine whether the returned data meets the continuing condition. If so, repeat step S423. If not, it means that the data export is complete, and the Excel file stream is closed.

[0045] S425: Upload the excel file to the file server, delete the local excel file, and then persist the task result. Beneficial effects: Compared with the prior art, the present invention has the following advantages:

[0046] (1) Regarding the task execution mode (asynchronous), the method of the present invention has been verified by production, and there is no longer the problem of long waiting time for synchronous import and export (gateway timeout), direct failure of import and export tasks, and inability to obtain execution results.

[0047] (2) Regarding repeated historical data, there is no need to increase the load on the system again. Past data can be queried directly through history, which greatly improves customer friendliness.

[0048] (3) Regarding interactive design, the problem of not being able to display friendly information on the page when partial data import fails and the amount of data is too large has been solved.

[0049] (4) Regarding ease of development, the business development process has been greatly accelerated. The development tasks that used to take 2-3 days have been shortened to less than one day, and many error-prone areas (such as Excel reading and writing occupying system memory) have been avoided, ensuring fast and stable support for the business.

[0050] (5) Regarding function upgrades, after the import and export logic is incorporated, there is more general scalability for later system upgrades and new functions. BRIEF DESCRIPTION OF THE DRAWINGS

[0051] Figure 1 It is a system diagram for Excel data import and export based on SQL;

[0052] Figure 2 It is a flowchart of Excel data import and export method based on SQL;

[0053] Figure 3 It is a flow chart for parsing Excel data import template based on SQL;

[0054] Figure 4 This is an example diagram of Excel data import template parsing based on SQL-like implementation;

[0055] Figure 5 It is a flow chart for parsing Excel data export template based on SQL;

[0056] Figure 6 This is an example diagram of Excel data export template parsing based on SQL-like implementation;

[0057] Figure 7 It is a flowchart for implementing Excel data import tasks based on SQL-like;

[0058] Figure 8 It is a flowchart for implementing Excel data export tasks based on SQL. DETAILED DESCRIPTION

[0059] The present invention is further illustrated below in conjunction with specific examples. The examples are implemented based on the technical solutions of the present invention. It should be understood that these examples are only used to illustrate the present invention and are not used to limit the scope of the present invention.

[0060] like Figure 1 As shown, the Excel data import and export system based on SQL-like implementation of the present application includes a display layer (user), an access import and export system and a business system. The general import and export system includes a task manager, a template parser, an executor and an outputter (result record).

[0061] The presentation layer enables users to create tasks and query results, and interact with the general import and export system for tasks. First, it interacts with the task manager, which creates specific tasks. After the import and export system completes the task and outputs the results, the user can query the results.

[0062] The general import and export system realizes the execution function of the overall task. After receiving the instruction to create a task, the task manager searches for the task template and determines whether there is a template. If there is a template, the task is persisted and submitted to the template parser. If there is no template, the task creation fails and a failure warning is fed back to the user.

[0063] The template parser implements template parsing. It first obtains the template cache, then determines whether there is a template. If there is no template, the parser is used to parse the template and the parsing result is stored in the template cache.

[0064] The executor implements task execution. When executing, it should be executed according to different business scenarios. It imports parsed files and generates error log files based on business results; it exports and generates business result files. The specific business execution of the task execution module is realized by calling an external business execution module. When importing, it parses the file and uploads it to the business execution module through remote calls. When exporting, it directly downloads data to the executor through remote calls to the business execution module. Finally, it saves the execution information and records the results. The present invention uses asynchronous tasks to decouple, and no longer obtains results synchronously. The results are interactively guided by the front end to the task management office for viewing. If you need to view the synchronization results in real time (for example, after uploading data, refresh the current page), the front end polls the task results until success or failure.

[0065] The present invention also discloses a method for implementing Excel data import and export based on SQL. Figure 2 As shown, the specific steps include:

[0066] S1: User creates a task: The user sends a task creation instruction to the general import and export system;

[0067] S2: The general import and export system creates tasks based on user tasks: first, it searches for task templates and determines whether there is a template. If there is a template, it persists the task and submits the asynchronous task to the template parser. If there is no template, the task creation fails and a failure warning is fed back to the user.

[0068] S3: Template parsing: First, get the template cache, then determine whether there is a template. If there is a template, proceed to the next step: task execution; if there is no template, use the parser to parse the template and store the parsing result in the template cache. The specific content of template parsing includes import template parsing and export template parsing.

[0069] like Figure 3 The figure shows the import template parsing flow chart. The specific steps of import template parsing are as follows:

[0070] S311: Read the template string, and then split the string by space;

[0071] S312: Read the position of "by", read the character before by and the character after by, and assemble them into detailed input information; the syntax used is a custom syntax that simulates the SQL syntax.

[0072] For example: Figure 4 As shown in the figure, labels ① to ④ are the information read from the "by" position, including order number, effective time, expiration time, price, listing and delisting information, etc. The information in the by position is assembled into detailed input information. Label ①: indicates that the orderNo input parameter of the business interface when importing Excel is taken from the data of the Excel column named "Order Number"; Label ②: Based on label ①, it supports the deserialization of field types, and "YYYY-MM-DD HH:mm:ss" is deserialized into Date type; Label ③: Based on label ①, it supports the deserialization of field types, and the read digital string is deserialized into BigDecimal type; Label ④: Based on label ①, it supports field mapping, and the read "1" is mapped to true.

[0073] S313: Read the "parent-by" position, read the character before parent-by and the character after parent-by, and assemble them into the parent class packaging input information.

[0074] For example Figure 4 Label ⑤: operatorId and operatorName are reserved fields. The insertion conditions are entered by default. The insertion conditions are the original information of the imported file, including the creator information, etc.

[0075] S314: Read the "form" position, take the next character, and obtain the uploaded file parameter field;

[0076] For example Figure 4 Label ⑥: describes that the source URL of the imported file is the creation task parameter field, and the content is the address of the server where the imported file is uploaded.

[0077] S315: Read the "unique" position, remove the last character, and obtain the unique configuration;

[0078] For example, label ⑦: describes which fields in the uploaded file can ensure the uniqueness of the data.

[0079] S316: read the "exception" position, and then read the as position (loop processing), including the character before as and the character after as, and assemble them into the exception output information;

[0080] For example, label ⑧: describes what information the error message needs to display when the import of data fails partially.

[0081] S317: Read the “#” position and assemble it into an additional condition Map.

[0082] For example The various types of information defined: Label ⑨: describes the business interface related information; Label ⑩: describes the amount of data in a batch when batch importing; Label Describes the routing location of error information when some imports return failures; label Describes the header information of the uploaded file; insert / by / insert-parent / parent-byfrom / unique / exception are reserved keywords, describing the framework of the entire template.

[0083] like Figure 5 The figure shows the export template parsing flow chart. The specific steps of export template parsing are as follows:

[0084] S321: Export template parsing, read the template string and split the string by space;

[0085] S322: Read the position of "as": read the character before as and the character after as, and assemble them into output information configuration;

[0086] like Figure 6 As shown, the information in the "as" position read by numbers ①~⑥ includes order number, effective time, expiration time, price, reason for return, price, etc. This information is assembled into the output information configuration; Number ①: It explains that the fields when exporting Excel are taken from the orderNo of the business interface, and the header information of the Excel column is "order number" Number ②: Based on number ①, it supports serialization of field types, and displays the Date type as "YYYY-MM-DD HH:mm:ss" Number ③: Based on number ①, it supports serialization of field types, and displays the BigDecimal type as a number with 2 decimal places Number ④: Based on number ①, it supports field mapping, and displays 1->"I don't want it anymore" Number ⑤: Based on number ③, the Excel column becomes a digital type, and supports quick data pivoting Number ⑥: Based on number ①, it supports dynamically changing data columns.

[0087] S323: Read the position of "form", take the next character, and assemble the data acquisition method;

[0088] S324: Read the "where" position and determine whether the position exists. If it exists, read it until the keyword into is encountered, assemble the connector and the field to form a conditional mapping. Figure 6 Labels ⑦ to ⑩, label ⑦: describes a parameter conversion mapping label; ⑧: based on label ⑦, the parameter type is converted, "YYYY-MM-DD HH:mm:ss" is deserialized into the Date type; label ⑨: based on label ①, it is a reserved field to record the operator ID; label ⑩: based on label ①, it is a reserved field to record the operator name.

[0089] S325: If the "where" position does not exist, read the "into" position, take the last character, and assemble it into an output device. into Excel means that the data is output in Excel format

[0090] S326: Read the “#” position and assemble it into an additional condition map. For example: label Context information, describing the details of the business interface; label Data cursor, describing the location of the business interface reverse reference data. select / from / where / into / as / and / = are reserved keywords, describing the framework of the entire template.

[0091] S4: Importing is done by parsing the file and uploading it remotely to the business execution module of the business system. The remote result generates a prompt file. Exporting directly calls the business execution module of the business system. The remote result generates an export file. Finally, the execution information is saved and the result is recorded. The user can query the result by polling on the result page or query the task result at the specified system location. Task execution specifically includes import task execution and export task execution.

[0092] Task execution specifically includes import task execution and export task execution.

[0093] like Figure 7 The following is a flowchart of the import task execution. The specific steps of the import task execution are as follows:

[0094] S411: Import execution tasks and read template configuration;

[0095] S412: Download the import file from the file system;

[0096] S413: Generation of Excel file failed: the generated Excel file contains error information.

[0097] S414: Read the uploaded Excel file: read the uploaded Excel file line by line to determine whether it meets the batch setting. If it meets the batch setting, jump to step S416; if it does not meet the batch setting, jump to step S415;

[0098] S415: Determine whether the read data is the Excel end-of-row data. If it is the Excel end-of-row data, proceed to step S416 and step S417; if it is not the Excel end-of-row data, return to step S414;

[0099] S416: Data processing, business interface parameters, upload data to the business system; if all succeeded at once, continue to step S414, if not all succeeded at once, write the failure reason to the Excel file;

[0100] S417: After the file is read, the Excel file stream is closed;

[0101] S418: Upload the Excel file to the file server, delete the local Excel file, and then persist the task result.

[0102] like Figure 8 The figure below is a flowchart of the export task execution. The specific steps of the export task execution are as follows:

[0103] S421: export execution tasks and read template configuration;

[0104] S422: Create an empty excel file;

[0105] S423: Assemble interface parameters, execute and call the business execution module of the business system to execute specific tasks; after the execution is completed, return data, process the data, and write the file into an Excel file;

[0106] S424: Determine whether the returned data meets the continuing condition. If so, repeat step S423. If not, it means that the data export is complete, and the Excel file stream is closed.

[0107] S425: Upload the Excel file to the file server, delete the local Excel file, and then persist the task result.

[0108] The versatility of the present invention is mainly reflected in the development of various businesses. No additional code processing is required. Different results can be executed based on different configurations using universal codes. However, how to describe the configuration is a heavy task. Through research and investigation, JSON is very complex in configuration and cannot intuitively describe a scenario. However, the SQL-like style greatly improves readability and ease of use for research and development. By developing a parser that parses SQL-like, the configuration can be interpreted and the execution of the process can be completed.

[0109] The above is only a preferred embodiment of the present invention. It should be pointed out that for ordinary technicians in this technical field, several improvements and modifications can be made without departing from the principle of the present invention. These improvements and modifications should also be regarded as the scope of protection of the present invention.

Claims

1. A SQL-based Excel data import and export system. It is characterized in that include: Presentation layer, general import and export system and business system; the general import and export system includes task manager, template parser, executor and outputter, and the general import and export system realizes the execution function of the overall task; The presentation layer enables users to create tasks and query results, and interact with the general import and export system to perform tasks. First, it interacts with the task manager, which implements specific task creation. After receiving the instruction to create a task, the task manager searches for a task template and submits the asynchronous task to the template parser for parsing; The template parser implements template parsing, first obtains the template cache, then determines whether there is a template, and if there is no template, uses the parser to parse the template and stores the parsing result in the template cache; The executor implements task execution, and during execution, it must be executed according to different business scenarios, import parsing files and generate error log files based on business results; Export and generate business result files and output the results; A method for importing and exporting Excel data based on SQL, comprising: S1: User creates a task: The user sends a task creation instruction to the general import and export system; S2: The general import and export system creates tasks based on user tasks: first, it searches for task templates and determines whether there is a template. If there is a template, it persists the task and submits the asynchronous task to the template parser. If there is no template, the task creation fails and a failure warning is fed back to the user. S3: Template parsing: First, get the template cache, then determine whether there is a template. If there is a template, proceed to the next step: task execution. If there is no template, use the parser to parse the template and store the parsing result in the template cache. Template parsing includes import template parsing and export template parsing; S4: Task execution: When importing, the file is parsed and uploaded to the business execution module of the business system remotely, and the remote result generates a prompt file; when exporting, the business execution module of the business system is directly called, and the remote result generates an export file, and finally the execution information is saved and the result is recorded. The user can query the result by polling on the result page or query the task result at the specified system location; Task execution specifically includes import task execution and export task execution; The specific steps for executing the import task are as follows: S411: Import execution tasks and read template configuration; S412: Download the import file from the file system; S413: Generation of failed excel file: the generated excel file contains error information; S414: Read the uploaded Excel file: read the uploaded Excel file line by line to determine whether it meets the batch setting. If it meets the batch setting, jump to step S416; if it does not meet the batch setting, jump to step S415; S415: Determine whether the read data is the Excel end-of-row data. If it is the Excel end-of-row data, proceed to step S416 and step S417; if it is not the Excel end-of-row data, return to step S414; S416: Data processing, business interface parameters, upload data to the business system; if all succeeded at once, continue to step S414, if not all succeeded at once, write the failure reason to the Excel file; S417: After the file is read, the Excel file stream is closed; S418: Upload the Excel file to the file server, delete the local Excel file, and then persist the task result; The specific steps for executing the export task are as follows: S421: export execution tasks and read template configuration; S422: Create an empty excel file; S423: Assemble interface parameters, execute and call the business execution module of the business system to execute specific tasks; after the execution is completed, return data, process the data, and write the file into an Excel file; S424: Determine whether the returned data meets the continuing condition. If so, repeat step S423. If not, it means that the data export is complete, and the Excel file stream is closed. S425: Upload the Excel file to the file server, delete the local Excel file, and then persist the task result.

2. The Excel data import and export system based on SQL as claimed in claim 1, Features: The specific business execution of the task execution module is realized by calling the external business execution module. When importing, the file is parsed and uploaded to the business execution module through remote calling. When exporting, the data is directly downloaded to the executor through remote calling of the business execution module. Finally, the execution information is saved and the results are recorded.

3. The Excel data import and export system based on SQL as claimed in claim 1, Features: In step S3, the specific steps of importing template parsing are: S311: Read the template string, and then split the string by space; S312: Read the position of "by", read the character before by and the character after by, and assemble them into detailed input information; S313: Read the "parent-by" position, read the character before parent-by and the character after parent-by, and assemble them into parent class packaging input information; S314: Read the "form" position, take the next character, and obtain the uploaded file parameter field; S315: Read the "unique" position, take the next character, and obtain the unique configuration; S316: read the "exception" position, and then read the as position, including the character before as and the character after as, and assemble them into the exception output information; S317: Read the "#" position and assemble it into an additional condition map.

4. The Excel data import and export system based on SQL as claimed in claim 1, Features: In step S3, the specific steps of exporting template parsing are: S321: Export template parsing, read the template string and split the string by space; S322: Read the "as" position, read the character before as and the character after as, and assemble them into output information configuration; S323: Read the "form" position, take the next character, and assemble the data acquisition method; S324: Read the "where" position and determine whether the position exists. If so, read until the keyword into is encountered, assemble the connector and the field to form a conditional mapping; S325: If the "where" position does not exist, read the "into" position, take the next character, and assemble it into an output device; S326: Read the "#" position and assemble it into an additional condition map.

Citation Information

Patent Citations

  • Method for importing Excel data into database

    CN103744982A

  • Work order data batch exporting method and device, electronic equipment and medium

    CN111914008A

  • Business electronic file development method

    CN112559837A

  • Method, device and equipment for importing spreadsheet into database and storage medium

    CN112949275A

  • Conversion Automation through Data and Object Importer

    US20170011135A1