Bank account statement data efficient collection method

Abstract

The invention provides an efficient data collection method for bank statement of account. The method comprises the following steps: A, determining a standard table; b, data preprocessing; c, reading and processing data; d, judging a header and each field; e, date and time processing; f, performing large model interaction and account processing; g, processing amount and characters; h, data import and verified.According to the method, a standardized collection process is constructed, links of data preprocessing, format conversion, column name matching, high-speed import and the like are covered, and by means of a large-model intelligent analysis technology, bank account statement data in various formats can be effectively processed, and the accuracy and traceability of the data are ensured; according to the method, analysis and conversion of various file formats such as Excel, CSV and PDF are supported, rapid data import is achieved through the LOAD DATA INFILE function of MySQL, the efficiency and reliability of data collection are further improved in combination with large-model intelligent matching, powerful data support is provided for discipline inspection and supervision work, and the method is suitable for popularization and application. And the efficient and accurate requirements of the discipline inspection supervision department on data processing when the discipline inspection supervision department calls the evidence obtaining object bank account statement are met.

Description

Technical Field

[0001] The present application relates to the field of data processing technology, and in particular to a method for efficiently collecting bank statement data. Background Art

[0002] In the disciplinary inspection and supervision workflow, obtaining bank statements from individuals for evidence collection is a critical step in conducting investigations. However, a series of problems currently exist in the collection of bank statement data. On the one hand, traditional data collection methods are extremely inefficient, requiring staff to expend considerable time and effort on tedious data collation, which severely impacts the overall progress of disciplinary inspection and supervision work. On the other hand, the data formats of bank statements provided by different banks vary significantly and lack unified standards, making data integration and analysis extremely complex and significantly increasing the difficulty of data processing. Furthermore, existing processing methods also lack assurance of data accuracy and integrity, making it difficult to meet the stringent data requirements of disciplinary inspection and supervision work. Therefore, it is necessary to design an efficient method for collecting bank statement data. Summary of the Invention

[0003] This application provides a method for efficiently collecting bank statement data to address the problem that existing processing methods are insufficient in ensuring data accuracy and integrity, and are unable to meet the strict data requirements of disciplinary inspection and supervision work. It achieves this by building a standardized collection process that covers data preprocessing, format conversion, column name matching, and high-speed import, and with the help of large-scale intelligent analysis technology, it can effectively process bank statement data in various formats to ensure data accuracy and traceability.

[0004] This application provides a method for efficiently collecting bank statement data, including the following steps:

[0005] A. Determine the standard table;

[0006] B. Data preprocessing;

[0007] C. Data reading and processing;

[0008] D. Judgment of table header and fields;

[0009] E. Date and time processing;

[0010] F. Large model interaction and account processing;

[0011] G. Amount and character processing;

[0012] H. Data import and verification.

[0013] Preferably, the standard table structure for data collection is clearly defined in step A, and its fields include id (auto-increment primary key), case number, local account number, local account name, counterparty account number, counterparty account name, amount, transaction date, transaction time, summary, remarks, file interruption flag, currency, bank name, collection original file name, and data source. The collection original file name and data source are primarily used to trace the data source and ensure that information is not mishandled during the audit data processing process. The data types of each field are set as follows: collection original file name is varchar(1024), remarks is text type, and the remaining fields are all varchar.

[0014] Preferably, the data preprocessing in step B includes the following methods:

[0015] a. Create folders and name files: Create corresponding folders based on the case number, bank name, and account name. Retrieve statement records from financial institutions and name them according to the standard format of "account name - account name - currency" and place them in the corresponding folders.

[0016] b. Editing Excel files: For Excel files, first adjust the table header to the first row. If an Excel file contains multiple account data, merge the data into a single table. Delete any rows at the end of the table that do not contain the statement. Then save the processed file as "Modify - Original Excel File Name."

[0017] c. Format conversion: Use the pandas and openpyxl libraries to convert Excel files to CSV format. The specific steps are as follows: recursively obtain the Excel files in the case directory, use pandas to read the Excel files and obtain all worksheet names, then traverse each worksheet and save them as CSV files. The CSV file name is constructed according to the rule "_excel file_sheet name.csv". Finally, save the data frame as a CSV file. For PDF files, use the Tabula library to extract the table data and convert it to CSV format.

[0018] Preferably, in step C, python is used to read the first row of header and the second to fifth rows of data of the file. If the data is less than 5 rows, all the data is read and the number of columns is recorded; each column in the second to fifth rows of data is checked with the local account number and the local account, and the ones with the same name are determined to be the local account number and the local account, and the local large model is called to query the header table first. If a template exists, the mysql load data infile import statement is generated to the original table based on the load and insert original sentences defined in the template, and then the insert into select statement is used to import the data into the standard table; in the data processing process, the time format is standardized, requiring the transaction date to be in YYYY-MM-DD format and the transaction time to be in hh24:mi:ss format; for the amount incurred, when the local account is income, it is marked as "+", and when it is expenditure, it is marked as "-".

[0019] Preferably, the step D includes the following process:

[0020] 1) Header determination: By iteratively using a string statistics algorithm, all rows with more than 90% Chinese or English characters are filtered out and labeled with a header label;

[0021] 2) Field matching: The case number and bank name are obtained from the directory name; the original file name and the local account number are obtained from the file name; the balance is obtained from the synonym table; the transaction date and time are obtained based on the synonym and data representation format; the counterparty account number and name are obtained from the synonym table by checking the first five rows of the audit data for non-local data. If no match is found, the corresponding column is obtained through dialogue with the large model and the relevant information is recorded in the header table and synonym table. The summary is also obtained from the synonym table;

[0022] 3) Other field processing: The remarks field is organized and stored in the json format of the header column according to other columns; the data source is uniformly set to "Letter sending method". If the interface is customized for local bank statements, it is "xx bank interface method". The interface is called through name, ID number, bank account number, and unit name to pull the statement record; the currency defaults to RMB, which is obtained from the file name. If it is not clear in the file name, the default value is used; the file name of the foreign currency account must add "-currency" at the end.

[0023] Preferably, in step E, a custom function of MySQL is used to match and process multiple date formats, including yyyymmdd, yyyy.mm.dd, mm / dd / yyyy, yyyy / mm / dd, and the above formats of two digits of yy, and the formats are uniformly returned in yyyy-mm-dd format. In terms of time processing, yymmddhhmiss, hh:mi:ss and yyyymmdd yyyy.mm.ddmm / dd / yyyy yyyy / mm / ddhh:mi:ss formats are supported.

[0024] Preferably, in step F, the first 5 rows of data after desensitization are transmitted to the large model interface, and the standard table data columns are informed to the large model, requiring the large model to determine whether it is a bank statement, and provide the column correspondence with the standard table as well as the date and time format, and verify the account according to the account verification rules. If the card number exists and the local account exists at the same time, the card number is written into the remarks field, and the card number in the remarks field is used as a virtual column of the standard table.

[0025] Preferably, in step G, the REPLACE function of MySQL is used to replace commas, currency symbols, spaces and other special characters in the amount and convert it into a digital form.

[0026] Preferably, in the step H, the load data infile statement generated by the large model is executed to import the data into the original table. If the import fails, the Python language is used to communicate with the large model to generate code, and the code is executed to complete the data import. The data is imported into the original table, as well as the currency, bank name, collection original file name, case number, and data source using the determined delimiter and line feed character, and the currency, bank name, collection original file name, case number, and default data source extracted from the folder and file name are replaced with the update original statement. According to the established correspondence and rules, the MYSQL select statement is used to check whether there is a problem with the amount of occurrence. Specifically, by comparing the number of rows of "select occurrence amount 1.0from standard table where occurrence amount 1.0>-100000000000000and case number = this case number" with the result of "select count(*)from standard table where case number = this case number", and at the same time, sorting according to the local account number and id, checking whether the sum of the balance of the previous row and the occurrence amount is consistent with the balance of this bank. If it is inconsistent, it is marked as a file break mark and requires manual auditing. Load data infile and insert into The select statement is recorded in the header table, including column correspondence rules and column replacement rules.

[0027] Beneficial effects: The present invention constructs a standardized collection process covering data preprocessing, format conversion, column name matching, and high-speed import, and with the help of large-scale intelligent analysis technology, can effectively process bank statement data in various formats to ensure data accuracy and traceability; this method supports parsing and conversion of various file formats such as Excel, CSV, PDF, and uses MySQL's LOAD DATA INFILE function to achieve fast data import. Combined with large-scale intelligent matching, it further improves the efficiency and reliability of data collection, provides strong data support for disciplinary inspection and supervision work, and meets the disciplinary inspection and supervision departments' requirements for efficient and accurate data processing when retrieving bank statements of evidence collection objects.

[0028] The above description is only an overview of the technical solution of the embodiment of the present application. In order to more clearly understand the technical means of the embodiment of the present application, it can be implemented in accordance with the contents of the specification. In order to make the above and other purposes, features and advantages of the embodiment of the present application more obvious and easy to understand, the specific implementation methods of the present application are listed below. BRIEF DESCRIPTION OF THE DRAWINGS

[0029] In order to more clearly illustrate the technical solutions of the embodiments of the present application, a brief introduction will be given below to the drawings required for use in the description of the embodiments. Obviously, the drawings described below are some embodiments of the present application. For ordinary technicians in this field, other drawings can be obtained based on these drawings without any creative work.

[0030] Figure 1 It is the workflow diagram of the present invention. DETAILED DESCRIPTION

[0031] To make the purpose, technical solutions, and advantages of the embodiments of this application more clear, the technical solutions in the embodiments of this application will be clearly and completely described below in conjunction with the drawings in the embodiments of this application. Obviously, the described embodiments are part of the embodiments of this application, not all of the embodiments. Based on the embodiments in this application, all other embodiments obtained by ordinary technicians in this field without making creative efforts are within the scope of protection of this application.

[0032] References to "embodiments" herein mean that a particular feature, structure, or characteristic described in connection with the embodiments may be included in at least one embodiment of the present application. The appearance of the phrase "embodiment" in various places in the specification does not necessarily refer to the same embodiment, nor does it necessarily refer to independent or alternative embodiments that are mutually exclusive of other embodiments. It is understood, both explicitly and implicitly, by those skilled in the art that the embodiments described herein may be combined with other embodiments.

[0033] In order to enable those skilled in the art to better understand the solution of the present application, the technical solution in the embodiments of the present application will be clearly and completely described below in conjunction with the accompanying drawings.

[0034] See also Figure 1 The present invention discloses a method for efficiently collecting bank statement data, comprising the following steps:

[0035] A. Determine the standard table;

[0036] B. Data preprocessing;

[0037] C. Data reading and processing;

[0038] D. Judgment of table header and fields;

[0039] E. Date and time processing;

[0040] F. Large model interaction and account processing;

[0041] G. Amount and character processing;

[0042] H. Data import and verification.

[0043] In the present invention, step A specifies a standard table structure for data collection, including fields such as ID (auto-incrementing primary key), case number, current account number, current account name, counterparty account number, counterparty account name, amount, transaction date, transaction time, summary, remarks, file interruption indicator, currency, bank name, original collection file name, and data source. The original collection file name and data source are primarily used to trace the data source and ensure that information is not mishandled during data audit processing. The data types of each field are set as follows: original collection file name is varchar(1024), remarks is text, and all other fields are varchar.

[0044] In the present invention, the data preprocessing in step B includes the following methods:

[0045] a. Create folders and name files: Create corresponding folders based on the case number, bank name, and account name. Retrieve statement records from financial institutions and name them according to the standard format of "account name - account name - currency" and place them in the corresponding folders.

[0046] b. Editing Excel files: For Excel files, first adjust the table header to the first row. If an Excel file contains multiple account data, merge the data into a single table. Delete any rows at the end of the table that do not contain the statement. Then save the processed file as "Modify - Original Excel File Name."

[0047] c. Format Conversion: Use the pandas and openpyxl libraries to convert Excel files to CSV format. The specific steps are: recursively retrieve the Excel files in the case directory, use pandas to read the Excel files and obtain all worksheet names, then iterate through each worksheet and save it as a CSV file. The CSV file name follows the pattern "_excel file_sheet name.csv", and finally save the data frame as a CSV file. For PDF files, use the Tabula library to extract the table data and convert it to CSV format. The operation method is to recursively retrieve the PDF files in the case directory, read them using tabula.read_pdf(pdf file, pages = 'all', multiple_tables = True), output or save each table, and save it as a CSV file. For CSV and TXT files, intelligent delimiter determination (delimiters in the asc list, such as | and tab, are used to determine the delimiter for CSV files using frequency statistics) is used to convert them to standard CSV format, save them as "modified_csv (or TXT) file", and record the number of columns.

[0048] In the present invention, in step C, python is used to read the first row of the header and the second to fifth rows of data of the file. If the data is less than 5 rows, all the data is read and the number of columns is recorded; each column in the second to fifth rows of data is checked with the local account number and the local account number, and the ones with the same name are determined to be the local account number and the local account number. The local large model is called to query the header table first. If a template exists, the load data infile import statement of MySQL is generated according to the load and insert original sentences defined in the template to the original table, and then the insert into select statement is used to import the data into the standard table; in the data processing process, the time format is standardized, requiring the transaction date to be in YYYY-MM-DD format and the transaction time to be in hh24:mi:ss format; for the amount incurred, when the local account number is income, it is marked as "+", and when it is expenditure, it is marked as "-". The remarks field is used to store data that cannot be matched to other columns in the original statement, and is stored in the format of {column name: content separated by commas} json.

[0049] In the present invention, step D includes the following process:

[0050] 1) Header determination: By iteratively using a string statistics algorithm, all rows with more than 90% Chinese or English characters are filtered out and labeled with a header label;

[0051] 2) Field matching: The case number and bank name are obtained from the directory name; the original file name and the local account number are obtained from the file name; the balance is obtained from the synonym table; the transaction date and time are obtained based on the synonym and data representation format; the counterparty account number and name are obtained from the synonym table by checking the first five rows of the audit data for non-local data. If no match is found, the corresponding column is obtained through dialogue with the large model and the relevant information is recorded in the header table and synonym table. The summary is also obtained from the synonym table;

[0052] 3) Other field processing: The remarks field is organized and stored in the json format of the header column according to other columns; the data source is uniformly set to "Letter sending method". If the interface is customized for local bank statements, it is "xx bank interface method". The interface is called through name, ID number, bank account number, and unit name to pull the statement record; the currency defaults to RMB, which is obtained from the file name. If it is not clear in the file name, the default value is used; the file name of the foreign currency account must add "-currency" at the end.

[0053] In the present invention, step E uses MySQL's custom function to match and process multiple date formats, including yyyymmdd, yyyy.mm.dd, mm / dd / yyyy, yyyy / mm / dd, and the above-mentioned format of two yy digits, and uniformly returns them to the yyyy-mm-dd format. In terms of time processing, yymmddhhmiss, hh:mi:ss and yyyymmdd yyyy.mm.ddmm / dd / yyyy yyyy / mm / dd hh:mi:ss formats are supported. At the same time, it can intelligently judge Unix (seconds or milliseconds starting from January 1, 1970) and Excel date and time representation (number of days from 1900 to the present), and judge validity within the date range of 2000 to 2099.

[0054] In the present invention, in step F, the first 5 rows of data after desensitization are transmitted to the large model interface, and the standard table data columns are informed to the large model, requiring the large model to determine whether it is a bank statement, and provide the column correspondence with the standard table as well as the date and time format, and verify the account according to the account verification rules. If the card number exists and the local account exists at the same time, the card number is written into the remarks field, and the card number in the remarks field is used as a virtual column of the standard table.

[0055] In the present invention, the REPLACE function of MySQL is used in step G to replace commas, currency symbols, spaces (including full-width spaces, full-width commas, ordinary spaces, "", etc.) and other special characters in the amount and convert them into digital form.

[0056] In the present invention, in step H, the load data infile statement generated by the large model is executed to import the data into the original table. If the import fails, the Python language is used to communicate with the large model to generate code, and the code is executed to complete the data import. The determined delimiter and line feed character are used to import the data into the original table, as well as the currency, bank name, collection original file name, case number, and data source. The update original sentence is used to replace the currency, bank name, collection original file name, case number, and default data source extracted from the folder and file name. According to the established correspondence and rules, the MYSQL select statement is used to check whether there is a problem with the amount of occurrence. Specifically, by comparing the number of rows of "select occurrence amount 1.0from standard table where occurrence amount 1.0>-100000000000000and case number = this case number" with the result of "select count(*)from standard table where case number = this case number", and at the same time, sorting according to the local account number and id, checking whether the sum of the balance of the previous row and the occurrence amount is consistent with the balance of this bank. If it is inconsistent, it is marked as a file break mark and requires manual auditing. Load data infile and insert into The select statement is recorded in the header table, including column correspondence rules and column replacement rules.

[0057] The present invention constructs a standardized collection process covering data preprocessing, format conversion, column name matching, and high-speed import, and with the help of large-scale intelligent analysis technology, it can effectively process bank statement data in various formats to ensure data accuracy and traceability. This method supports the parsing and conversion of various file formats such as Excel, CSV, and PDF, and uses the LOAD DATA INFILE function of MySQL to achieve fast data import. Combined with large-scale intelligent matching, it further improves the efficiency and reliability of data collection, provides strong data support for disciplinary inspection and supervision work, and meets the disciplinary inspection and supervision departments' requirements for efficient and accurate data processing when retrieving bank statements of evidence collection objects.

[0058] As described above, the above embodiments are only used to illustrate the technical solutions of the present application, rather than to limit them. Although the present application has been described in detail with reference to the above embodiments, those skilled in the art should understand that they can still modify the technical solutions described in the above embodiments, or make equivalent replacements for some of the technical features therein. However, these modifications or replacements do not deviate the essence of the corresponding technical solutions from the spirit and scope of the technical solutions of the embodiments of the present application.

Claims

1. A method for efficiently collecting bank statement data, characterized in that: The steps include: A. Determine the standard table; B. Data preprocessing; C. Data reading and processing; D. Judgment of table header and fields; E. Date and time processing; F. Large model interaction and account processing; G. Amount and character processing; H. Data import and verification.

2. The method for efficiently collecting bank statement data according to claim 1, characterized in that: Step A specifies a standard table structure for data collection, including fields such as ID (auto-incrementing primary key), case number, current account number, current account name, counterparty account number, counterparty account name, amount, transaction date, transaction time, summary, notes, file gap indicator, currency, bank name, original collection file name, and data source. The original collection file name and data source are primarily used to trace the data source and ensure that information is not mishandled during the audit data processing process. The data types of each field are set as follows: original collection file name is varchar(1024), notes is text, and all other fields are varchar.

3. The method for efficiently collecting bank statement data according to claim 1, characterized in that: The data preprocessing in step B includes the following methods: a. Create folders and name files: Create folders based on the case number, bank name, and account name. Retrieve statement records from financial institutions and name them using the standard "account name - account name - currency" format and place them in the corresponding folders. b. Editing Excel files: For Excel files, first adjust the table header to the first row. If an Excel file contains multiple account data, merge the data into a single table. Delete any rows at the end of the table that do not contain the statement. Then, save the processed file as "Modify - Original Excel File Name." c. Format conversion: Use the pandas and openpyxl libraries to convert Excel files to CSV format. The specific steps are as follows: recursively obtain the Excel files in the case directory, use pandas to read the Excel files and obtain all worksheet names, then traverse each worksheet and save them as CSV files. The CSV file name is constructed according to the rule "_excel file_sheet name.csv". Finally, save the data frame as a CSV file. For PDF files, use the Tabula library to extract the table data and convert it to CSV format.

4. The method for efficiently collecting bank statement data according to claim 1, characterized in that: In the step C, Python is used to read the first row of the header and the second to fifth rows of data in the file. If the data is less than 5 rows, all the data is read and the number of columns is recorded; each column in the second to fifth rows of data is checked against the local account number and the local account, and the ones with the same name are determined to be the local account number and the local account, and the local large model is called to query the header table first. If a template exists, the MySQL load data infile import statement is generated to the original table based on the load and insert original sentences defined in the template, and then the insert in to select statement is used to import the data into the standard table; in the data processing process, the time format is standardized, requiring the transaction date to be in the YYYY-MM-DD format and the transaction time to be in the hh24:mi:ss format; for the amount incurred, when the local account is income, it is marked as "+", and when it is expenditure, it is marked as "-".

5. The method for efficiently collecting bank statement data according to claim 1, characterized in that: The step D includes the following process: 1) Header determination: By iteratively using a string statistics algorithm, all rows with more than 90% Chinese or English characters are filtered out and labeled with a header label; 2) Field matching: The case number and bank name are obtained from the directory name; the original file name and the local account number are obtained from the file name; the balance is obtained from the synonym table; the transaction date and time are obtained based on the synonym and data representation format; the counterparty account number and name are obtained from the synonym table by checking the first five rows of the audit data for non-local data. If no match is found, the corresponding column is obtained through dialogue with the large model and the relevant information is recorded in the header table and synonym table. The summary is also obtained from the synonym table; 3) Other Field Processing: The Remarks field is organized and stored in the same JSON format as other columns through the header column: Content. The data source is uniformly set to "Letter Sent Method." If the interface for local bank statements is customized, it is set to "XX Bank Interface Method." The interface is called to pull statement records based on name, ID number, bank account number, and company name. The default currency is RMB, which is obtained from the file name. If the file name is not specified, the default value is used. Foreign currency account file names must have "-Currency" appended to the end.

6. The method for efficiently collecting bank statement data according to claim 1, characterized in that: In step E, a custom function of MySQL is used to match and process multiple date formats, including yyyymmdd, yyyy.mm.dd, mm / dd / yyyy, yyyy / mm / dd, and the above formats of two digits of yy, and the formats are uniformly returned in yyyy-mm-dd format. In terms of time processing, yymmddhhmiss, hh:mi:ss and yyyymmdd yyyy.mm.dd mm / dd / yyyy yyyy / mm / dd hh:mi:ss formats are supported.

7. The method for efficiently collecting bank statement data according to claim 1, characterized in that: In step F, the first five rows of data after desensitization are transmitted to the large model interface, and the standard table data columns are informed to the large model. The large model is required to determine whether it is a bank statement, and provide the column correspondence with the standard table as well as the date and time format. The account is verified according to the account verification rules. If the card number exists and the local account exists at the same time, the card number is written into the remarks field, and the card number in the remarks field is used as a virtual column of the standard table.

8. The method for efficiently collecting bank statement data according to claim 1, characterized in that: In step G, the MySQL REPLACE function is used to replace commas, currency symbols, spaces and other special characters in the amount and convert it into a digital form.

9. The method for efficiently collecting bank statement data according to claim 1, characterized in that: In the step H, the load data infile statement generated by the large model is executed to import the data into the original table. If the import fails, the Python language is used to communicate with the large model to generate code, and the code is executed to complete the data import. The data is imported into the original table, as well as the currency, bank name, collection original file name, case number, and data source using the determined delimiter and newline character, and the currency, bank name, collection original file name, case number, and default data source extracted from the folder and file name are replaced with the update original statement. According to the established correspondence and rules, the MYSQL select statement is used to check whether there is a problem with the amount of occurrence. Specifically, the number of rows of "select occurrence amount 1.0 from standard table where occurrence amount 1.0>-100000000000000 and case number = this case number" is compared with the result of "select count(*) from standard table where case number = this case number". At the same time, according to the sorting of the local account number and id, check whether the sum of the balance of the previous row and the occurrence amount is consistent with the balance of this bank. If it is inconsistent, it is marked as a file break mark and requires manual audit. Load data infile and insert into The select statement is recorded in the header table, including column correspondence rules and column replacement rules.

Images