Shulou(Shulou.com)06/03 Report--

TXT text file is one of the common ways to transfer data between applications, because it has many advantages, such as versatility, flexibility, easy maintenance and so on. However, not all applications provide the ability to generate txt files, which often requires additional programming and development work. At this time, if we can have a general tool software to flexibly generate text in the target format according to the needs, it will be able to greatly help our business work. The aggregator introduced in this paper is such an efficient and flexible general tool software, which can read, calculate and export txt files from different data sources.

This article will focus on the data export capability of the aggregator, but the powerful computing power of the aggregator itself is not the focus of this article, so there is no deliberate introduction to the data source access and computing process. For the functions used in this article, please refer to the aggregator online document "function reference".

1. Simply export data

Let's start with a simple data export:

1.1 Export New Fil

In the following example, the export from the data source to the TXT file is completed through two lines of simple read and output.

The A1 unit is read into the student scores of Class 1 of 5 years in the excel file to simulate the data that may be obtained by calculation.

The expression in A2 exports the data of A1 to a new "student score sheet. Txt" file. The export function export is used in the example. However, in this simplest example, we do not specify additional parameters. Because no x and F are specified, all fields in A1 are exported, leaving the field name unchanged. Since the column delimiter parameter s is not specified, it is separated by the default tab. However, the function uses the option @ t, so the field name (the header line of the excel file) is exported to the first line.

A

one

= file ("51.xlsx") .xlsimport@t ()

two

= file ("student transcript .txt") .export @ t (A1)

The exported txt file is shown in the following figure:

1.2 additional data

If the student transcript .txt file already exists and we need to add another class's score to the file, what should we do?

Similar to the above example, the data structure remains the same when reading the scores of the students in Class 2 of 5 years to be added in A1.

A2 exports the data to the existing "student score sheet .txt" file, but at this time, because the file already has a title, you only need to export the data, so do not add the function option @ t. At the same time, the additional data is indicated by the option @ a.

A

one

= file ("52.xlsx") .xlsimport@t ()

two

= file ("student transcript .txt") .export @ a (A1)

1.3Exporting csv

Csv files are also common plain text files, where table data is stored separated by commas. If you want to export a csv file, there are two ways:

L add the option @ c as shown below on export, or

L increase the separation parameter and write it as export@t (A1; ",")

The result is the same in both ways.

A

one

= file ("51.xlsx") .xlsimport@t ()

two

= file ("student transcript .txt") .export @ tc (A1)

The export results are as follows:

Student number, name, class, gender, Chinese, math, English

110210, Xu Zhaoya, 5 (1), male, 80, 60, 86

110211, Wang Chunli, 5 (1), male, 81, 72, 67

110212, Shen Huarong, 5 (1), female, 97pr 91pr 87

110213, Li Xiaomei, 5 (1), female, 86, 69, 73

two。 Complex exported data

Now, let's take a look at the slightly more complex export operation:

In the above example, we introduce some new requirements:

L add an ordinal column to the result file

Add a grade point average column at the end, and format the grade point average to keep one decimal place

L do not export student ID

The delimiter between columns is "\ t |".

A

one

= file ("51.xlsx") .xlsimport@t ()

two

= file ("Student score sheet .txt") .export @ t (A1 grade: serial number, name, class, gender, Chinese, math, English, string ((Chinese + math + English) / 3, "#. 0"): average; "\ t |")

The export result is shown in the following figure:

Or two lines to do it! A1 needless to say, let's take a look at the changes in A2: # represents the record number in the sequence table and exports it to the serial number column in the result; indicates the exported name, class, sex, Chinese, math, English column In the expression "string ((Chinese + Mathematics + English) / 3," #. 0 "): average, the averages of Chinese, mathematics, and English are calculated and formatted to retain only one decimal place, and the new column is named" average "; the last parameter specifies that the column separator is"\ t | ".

3. Export a large amount of data

Data export is often faced with another important problem: what if the amount of data is large?

For this reason, the cursor function provided by the aggregator can be used to deal with the situation of a large amount of data. When reading the data, the cursor traverses the data forward and backward, reading the data from the data source one by one, rather than reading all the data into memory at once. Therefore, it will not be limited by insufficient memory. Moreover, aggregator cursors can be applied not only to databases, but also to data files or memory arrangements.

The script is shown in the following figure:

A

one

= connect ("demo")

two

= A1.cursor ("select * from order Table")

three

> nasty 0

four

= file ("big.txt") .export @ t (A2 number, order ID, company name, shipper region, order date, string (order amount, "# .00"): order amount)

The export results are as follows:

A1 connects to demo database

A2 opens the order table as a cursor

A3 defines the serial number variable n and assigns the initial value to 0

A4 is the specific export process, the cursor refers to big data exported to the big.txt file.

In the case of a large amount of data, we use the cursor as the export data source, while in the previous general export case, we use the order table as the export data source. The use of the two is exactly the same, except that a # cannot be used to represent a record number in the cursor.

In order to generate the sequence number, the variable n defined in A3 is used for export, and the sequence number column can be exported after adding 1 to each piece of data exported.

4. Salary assistant

Finally, let's look at a real example of actual combat:

Nowadays, enterprises usually pay their employees through the bank. Banks provide online services, and enterprises can complete the payment of self-help wages through this channel. The specific methods are as follows:

The bank provides a text file format for payroll, and enterprise users can complete the payroll as long as they write the file according to the format and upload it through the online bank.

Let's take a look at how to use the aggregator to easily complete the generation of payroll text files.

Let us take Minsheng Bank as an example. Its document format is as follows:

ATNU:0019999

MICN:

CUNM: Beijing XXXX Technology Co., Ltd

MIAC:0110014180030254

EYMD:1

TOAM:80576.39

COUT:5

-

6226220101871111 | 19944.65 | Zhao Airun | |

6226220101872222 | 18349.08 | Sun Xueqian | |

6226220101873333 | 15955.72 | Wang Laoji | |

6226220101874444 | 14360.15 | Zhang Xiaoshu | |

6226220101875555 | 11966.79 | Li Da | |

The first 8 lines of this document are the head of the document, the contents of lines 1, 2, 5 and 8 are fixed, line 3 is the name of the enterprise, line 4 is the account number of the enterprise in Minsheng Bank, line 6 is the total amount of this payroll, and line 7 is the total number of payroll. Starting from line 9, there is specific salary information, the first item is the employee's salary account number, the second item is the salary amount, the third item is the employee's name, and the fourth and fifth items are left blank. Items are separated by vertical bars.

The format of this text file is very strict and can not make mistakes, so it is not suitable for financial staff to edit directly and needs to be generated by program.

There are two excel tables related to salary in an enterprise, one is the employee table, and the other is the payroll table, as shown in the following two figures.

After the accountant is responsible for filling out the employee payroll, you can open the aggregator ide and run the pre-written dfx program:

A

B

one

= file ("employee table .xlsx") .xlsimport@t ()

= file ("payroll .xlsx") .xlsimport@t ()

two

= A1.join (name, B1: name, bank issued, remarks)

three

= file ("payroll .txt")

four

= A3.write ("ATNU:0019999")

= A3.write@a ("MICN:")

five

= A3.write@a ("CUNM: Beijing XXXX Technology Co., Ltd")

= A3.write@a ("MIAC:0110014180030254")

six

= A3.write@a ("EYMD:1")

= A3.write@a ("TOAM:" + string (A2.sum (issued by bank), "# .00"))

seven

= A3.write@a ("COUT:" + string (A2.count ()

= A3.write@a (- -)

eight

= A3.export@a (A2, payroll card number, string (bank actually issued, "# .00"), name, remarks, remarks; "|")

Read the written employee table in A1

Read and write the payroll in B1

Merge two tables by name into one table in A2

A3 Open the payroll file to be saved

Write the file header line by line from A4 to B7: where: B6 is the total salary, calculate the total amount issued by the bank from A2, and A7 is the total number of letters issued this time.

Export the generation of payroll documents in A8, which are payroll number, salary amount, name, empty column and empty column (the last two columns do not need to be filled in, so note column representatives).

In the script, with the exception of A4, which is written with substitution, all the other boxes use the @ an option, indicating that it is an additional write.

As for other banks, the process is much the same. As long as according to the bank's description of the text file format, write a good aggregator dfx program on it.

To sum up, during the data export process, the aggregator provides two functions, write () and export (), which are line-by-line writes and batch writes. Function provides two control methods of parameters and function options, using different parameters or function options, we can specify whether to export field names / titles, whether to export all fields, whether to use new field names, append or replace files, which characters to use for delimiting parameters, and so on.

After witnessing the powerful and flexible ability of the aggregator in the process of data export, do you also have an inexplicable impulse? Download the aggregator as soon as possible and join the ranks of exploring together and becoming stronger together.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.