JP5814480B2 - Data conversion system - Google Patents

Description

The present invention relates to a data conversion system for converting data of a file created by a spreadsheet into a text file to be taken in by another computer system.

When using a computer, spreadsheet software is used in various situations. Spreadsheet software has a grid in the X-axis and Y-axis directions called a spreadsheet, and various calculations can be performed by entering numerical values and mathematical formulas in the grid. . Because of its convenience, it is widely used for data management purposes in addition to the simulation field.

On the other hand, when a business system is newly introduced in a company or the like, it is necessary to transfer the file data that has been created in the spreadsheet to the business system. In this case, data could be taken in by the business system, and it was necessary to manually edit the data in text format or csv format. For example, when using Microsoft Excel as spreadsheet software, select the data entered in the Excel file, copy it to a text file, delete rows and columns that you do not want to transfer to the business system, Manual work such as importing into a business system is required.

There are cases where necessary data is deleted manually or unnecessary data is not deleted. It also takes time and effort.

  Then, automation of a process is proposed by using the system of the following patent document 1.

JP2011-248617A

The system of Patent Document 1 is a system that converts a plurality of xls format files into csv format files and outputs them in accordance with the contents set in the xml file. By using this system, an xml format file can be automatically converted into a csv format file. However, it is necessary to directly describe each cell position to be imported in the xml file.

Therefore, it takes time and labor for the setting work. In addition, there is still a possibility that mistakes will occur during the setting.

In addition, for example, as shown in FIG.
Related data is often input. In the case of FIG. 20, the order number and order amount of the product are associated with each customer. In this way, when related data is input across multiple rows and columns, when converting it to text file data, the number of orders and the order value must be converted for each customer. I must. However, in the above-mentioned system of Patent Document 1, it is assumed that one data corresponds to one cell. Therefore, when related data is input across a plurality of rows and columns, I can't respond. Therefore, there is a problem that lacks versatility.

In view of the above problems, the present inventor eliminates the occurrence of mistakes during data conversion by automating the conversion process of data created in a spreadsheet into a text file, and the effort and time required for the work. Invented a data conversion system that greatly reduces Also,
Even when related data is input across multiple rows and multiple columns, the data can be converted by associating them.

A first invention is a data conversion system for converting spreadsheet data into which related data is input over a plurality of rows and / or a plurality of columns into a text file, wherein the data conversion system converts data in the spreadsheet. A pre-setting processing unit that accepts input of setting values for processing, and obtains data in a range to be processed in the setting values from the data in the spreadsheet, and for each data group that is a unit based on the setting values, A matrix decomposition conversion processing unit that determines whether or not a condition of a cell to be determined in the set value is satisfied, and outputs the data of the acquired data group to a text file according to the determination result of the satisfaction of the condition And a data output processing unit.

In a spreadsheet, related data may be input as a group of data (a group of data) across multiple rows and multiple columns. In that case, the related data must be associated with and converted to text file data. Therefore, by executing processing as in the present invention, it is possible to automatically convert only necessary data after determining whether or not conversion processing to text file data is performed for each data group as a unit. Can do. As a result, mistakes during data conversion do not occur, and work time and labor can be greatly reduced.

In the above-described invention, the matrix decomposition conversion processing unit extracts item names from the spreadsheet based on the set values and arranges them in a row direction, and is processed in the set values from the data in the spreadsheet. The data output processing unit obtains data of a range, determines whether or not a condition of a cell to be determined in the set value is satisfied for each data group as a unit based on the correspondence in the set value, and the data output processing unit Can be configured as a data conversion system that outputs data of the acquired data group in association with the arranged item names to a text file in accordance with the determination result of the satisfaction of the condition.

In the case of spreadsheets that have been input with data associated with multiple rows and multiple columns, there are various types of processing, but by executing the processing as described above, data in a text file that has one record as one row The conversion process to can be easily realized.

The data conversion system of the first invention can be realized by reading the data conversion program of the present invention into a computer and executing it. That is, a data conversion program for converting spreadsheet data into which related data is input across a plurality of rows and / or columns into a text file, the data conversion program comprising: A pre-setting processing unit that accepts input of setting values, and obtains data in a range to be processed in the setting values from the data in the spreadsheet;
For each data group that is a unit based on the set value, a matrix decomposition conversion processing unit that determines whether or not the condition of the cell to be determined in the set value is satisfied, according to the determination result of the satisfaction of the condition , A data conversion program that functions as a data output processing unit that outputs data of the acquired data group to a text file.

By using the data conversion program of these inventions, data conversion processing can be executed even for spreadsheets stored on the cloud, for example, regardless of the storage location or creation location of the spreadsheet. .

In addition, the data capturing system realized by this program can be operated on a server on the network, and the business system can be operated on the same server or a different server connected to the network. As a result, the spreadsheet data can be taken in via the network, so that data created by, for example, a mobile terminal can be taken into the business system via the network.

The data conversion system of the present invention can automate the process of converting data created in a spreadsheet into text file data. As a result, errors during data conversion can be eliminated, and the labor and time required for the work can be greatly reduced. Even if data related to multiple rows and columns is entered,
These can be processed as a single data group and converted into text file data.

It is a figure which shows typically an example of a structure of the data conversion system of this invention. It is a figure which shows typically an example of a structure of the conversion process part in the data exchange system of this invention. It is a figure which shows typically an example of the hardware constitutions of the computer which implement | achieves the data exchange system of this invention. It is a flowchart which shows typically an example of the process of the data exchange system of this invention. It is a flowchart which shows typically an example of the process of a data conversion process. It is a figure which shows an example of an acquisition setting screen typically. It is a figure which shows an example of a text taking-in screen typically. It is a figure which shows typically an example of the spreadsheet selected as text conversion. It is a figure which shows typically the case where a value is stored in variable rangeData. It is a figure which shows typically the case where the variable excludeFlgColData is stored. It is a figure which shows typically the case where the variable excludeFlgRowData is stored. It is a figure which shows typically the process which determines with respect to the value stored in the variable excludeFlgColData, and stores the value of "TRUE" or "FALSE" in the variable excludeColFlgList. It is a figure which shows typically the process which determines with respect to the value stored in the variable excludeFlgRowData, and stores the value of "TRUE" or "FALSE" in the variable excludeRowFlgList. It is a figure which shows typically the process of a data generation process part. It is a figure which shows an example of the text file to output typically. FIG. 10 is a diagram schematically illustrating an example of a system configuration of a data conversion system according to a third embodiment. It is a figure which shows typically an example which takes in the text file output after data conversion to a business system. FIG. 10 is a diagram schematically illustrating an example of a system configuration of a data conversion system according to a fourth embodiment. 10 is a flowchart schematically illustrating an example of a processing process of a data conversion system according to a fourth embodiment. It is a figure which shows typically an example of a spreadsheet in case a content value is two vertically. It is a figure which shows an example of a setting value typically. It is a figure which shows an example of a setting value input screen typically. It is a figure which shows typically an example at the time of acquiring the heading of an item from a spreadsheet. It is a figure which shows typically an example of the process which outputs a headline part to a text file. It is a figure which shows typically an example of the correspondence table of the Roman letter and number of a row | line | column. It is a figure which shows typically an example of the process which acquires a data range from the content value range in a setting value. It is a figure which shows typically an example of the calculation method of a data group. It is a figure which shows an example of the acquisition process of a data group typically. It is a figure which shows typically an example of the process which acquires the data of the range of a data group using a spreadsheet. It is a figure which shows typically an example of the text file which output the data of the acquired data group when the argument X = 0 and the argument Y = 0. It is a figure which shows typically an example of the text file which output the data of the acquired data group when the argument X = 1 and the argument Y = 0. It is a figure which shows typically an example of the text file output by performing the process of Example 4 on the spreadsheet of FIG. It is a figure which shows typically an example of a spreadsheet in case the content value is one. It is a figure which shows typically an example of the setting value input screen in the case of FIG. It is a figure which shows typically an example of the text file output by performing the process of Example 4 with respect to the spreadsheet of FIG. 33 based on the setting value of FIG. It is a figure which shows typically an example of a spreadsheet in case a content value is two horizontally. It is a figure which shows typically an example of the setting value input screen in the case of FIG. FIG. 38 is a diagram schematically illustrating an example of a text file output by executing the processing of the fourth embodiment on the spreadsheet of FIG. 36 based on the setting values of FIG. It is a figure which shows typically an example of a spreadsheet in case a content value is vertical and horizontal. It is a figure which shows typically an example of the setting value input screen in the case of FIG. FIG. 41 is a diagram schematically illustrating an example of a text file output by executing the processing of the fourth embodiment on the spreadsheet of FIG. 39 based on the setting values of FIG.

An example of the system configuration of the data conversion system 1 of the present invention is shown in FIG.
An example of the conversion processing unit 12 is schematically shown in FIG.

The data conversion system 1 of the present invention functions with a computer used by a user who desires to perform data conversion. The computer includes an arithmetic device 70 such as a CPU for executing arithmetic processing of a program, a storage device 71 such as a RAM and a hard disk for storing information, a display device 72 such as a display, a keyboard and a pointing device (such as a mouse and a numeric keypad). And a communication device 74 that transmits and receives processing results of the arithmetic device 70 and information stored in the storage device 71 via a network such as the Internet or a LAN.

Although FIGS. 1 and 2 show the case where the various computers are one, the functions may be distributed and implemented in a plurality of computers. Each means in the present invention is only logically distinguished in function, and may be physically or virtually the same area.

The data conversion system 1 according to the present invention, for example, in a company or the like, when the data previously created with a spreadsheet of spreadsheet software such as Excel is imported into a business system used by the company as a text file. Output.
As a result, it can be easily imported into the business system.

A business system is a computer system for managing business execution of a company. There are various business systems, including, but not limited to, HR,
An example is a computer system for performing salary, financial accounting, sales, purchasing, and the like.

The data conversion system 1 includes a capture range input reception processing unit 10, a target file input reception processing unit 11, and a conversion processing unit 12.

The import range input acceptance processing unit 10 is configured to perform the conversion on the spreadsheet to be converted.
Accept which range to convert to text file and input the range. For example, on the acquisition setting screen in FIG. 6, inputs such as an acquisition method, a target sheet, an acquisition range (start position and end position), an exclusion row and an exclusion column are accepted.

The “loading method” is to select which sheet is the target because the spreadsheet is often composed of a plurality of sheets. In other words, only the specified sheet is imported,
Accepts the designation of whether to exclude the specified sheet or to import all. “Specify sheet” specifies the target sheet when “load specified sheet” or “exclude specified sheet” is selected as the loading method. “Acquisition range” accepts designation of which range of data is to be captured on the sheet. In the case of FIG. 6, since “A2” is designated as the start position and “E7” is designated as the end position, the cell “A2”
A range composed of the cell “E7” is designated. Note that “cells” refer to “cells” arranged in the row and column directions constituting the spreadsheet.

Excluded rows and excluded columns are columns and rows that serve as a reference for determining whether or not to include the rows and columns. In FIG. 6, designation of “A column” as an excluded row and “first row” as an excluded column are accepted.
Therefore, it is determined whether or not there is data in column “A” (whether it is blank (NULL)) or a predetermined value (for example, “0”), and there is data or is not a predetermined value. If it can be determined, the line is taken into the target. On the other hand, there is no data (NULL
If the value is a predetermined value, the line is excluded from the target to be captured. Similarly, in the “excluded column”, it is determined whether there is data in the “first row” (whether it is not blank (NULL)) or a predetermined value (for example, “0”), and there is data, or If it can be determined that the value is not the predetermined value, the column is included. On the other hand, there is no data (NUL
L) or a predetermined value, the column is excluded from the capture target.

Further, the acquisition date / time information may be stored for the row or column where the acquisition is performed, and when the next acquisition is performed, the acquisition date / time information may be used as the exclusion information. In this way, even a spreadsheet that is operated by adding rows and columns can be automatically imported except for the previously imported one.

The target file input acceptance processing unit 11 accepts designation of a spreadsheet to be converted into data. For example, in the text capture screen shown in FIG. 7, the specification of the spreadsheet storage location is accepted by accepting the specification of the spreadsheet storage location.

The conversion process executes the conversion process on the spreadsheet specified by the target file input reception processing unit 11 according to each condition specified by the import range input reception processing unit 10, and converts the converted data into a text file. To output as data. Conversion processing is executed by executing “text conversion” on the text capture screen of FIG.

The conversion processing unit 12 includes a capture range data extraction processing unit 120, a capture range data storage unit 121, a determination target data extraction processing unit 122, a determination target data storage unit 123, an exclusion target data determination processing unit 124, and an exclusion target data storage. Unit 125, data generation processing unit 126, and data output processing unit 127.

The capture range data extraction processing unit 120 receives the input of the specified sheet whose input has been received by the capture range input reception processing unit 10 for the spreadsheet to be captured that has been input by the target file input reception processing unit 11. The cell value in the specified capture range is set to the given variable rangeData
And stored in the capture range data storage unit 121. If a merged cell is detected within the capture range, the merged cell value may be copied to all cells that are blank at the time of storage, and data capture may be performed. Also, information outside the capture range (cell values outside the capture range, sheet name,
(File name) may be imported. For example, a capture range may be input in advance in a spreadsheet to be captured, and the input capture range may be acquired instead of specifying the capture range. Since the import range is entered in advance in the spreadsheet to be imported, it can be easily imported without making a mistake in the import range.

The capture range data storage unit 121 stores the predetermined variable rangeData stored by the capture range data extraction processing unit 120. An example of the variable rangeData stored in the capture range data storage unit 121 is schematically shown in FIG.

The determination target data extraction processing unit 122 reads the cell values of the excluded row and the excluded column that have been input by the acquisition range input reception processing unit 10 from the spreadsheet to be acquired, and the variable ex
It is stored in cludeFlgColData (in the case of an excluded row) and excludeFlgRowData (in the case of an excluded column), and is stored in the determination target data storage unit 123.

The determination target data storage unit 123 stores the predetermined variables excludeFlgColData and excludeFlgRowData stored by the determination target data extraction processing unit 122. An example of the variable excludeFlgColData stored in the determination target data storage unit 123 is shown in FIG. 10, and an example of the variable excludeFlgRowData is shown in FIG.
Is shown schematically.

The exclusion target data determination processing unit 124 uses the variable excl stored in the determination target data storage unit 123.
Based on udeFlgColData and excludeFlgRowData, determine which columns are excluded and which rows are excluded. Whether or not to be excluded is determined based on whether the value of the row or cell is blank (NULL) or a predetermined value (for example, 0). “TRUE” if not subject to exclusion, “FALSE” if subject to exclusion, variable
Stored as a value in excludeColFlgList and excludeRowFlgList, and excluded data storage unit 125
Remember me.

The exclusion target data storage unit 125 stores the predetermined variables excludeColFlgList and excludeRowFlgList stored in the exclusion target data determination processing unit 124. An example of the variable excludeColFlgList stored in the exclusion target data storage unit 125 is shown in FIG. 12, and an example of the variable excludeRowFlgList is shown in FIG.
Is shown schematically.

The data generation processing unit 126 uses the variable rangeData stored in the capture range data storage unit 121.
Variable excludeColFlgList stored in the exclusion target data storage unit 125 among the rows or columns of
, Extract the row or column that contains the value of "TRUE" in excludeRowFlgList, variable d
The data generation process is executed by storing in t. With this process, the variable dt can be generated by excluding the row or column to be excluded (the row or column of “FALSE”).

The data output processing unit 127 outputs a text file based on the variable dt generated by the data generation processing unit 126.

By executing the above processing in the conversion processing unit 12, the spreadsheet data to be converted can be output to a text file.

Next, an example of the processing process of the data conversion system 1 of the present invention will be described with reference to the flowcharts of FIGS.

A user who desires to convert spreadsheet data using the data conversion system 1 activates the capture setting screen shown in FIG. 6 by performing a predetermined operation on the computer.

After startup, the user inputs each information on the capture setting screen. Here, FIG.
Suppose that information such as the capture range shown in is input. The input information is received by the capture range input reception processing unit 10 (S100).

For example, each sheet of “April 2013 and April 2013_2” is imported as a sheet, and cells “A2” to “E7” are included as an acquisition range, and “A column” is set for an excluded row.
For the excluded column is accepted as the determination target column and row.

In this way, when the input of the information such as the spreadsheet import range is received by the capture range input reception processing unit 10 on the capture setting screen, the user performs a predetermined operation to display the text capture screen (FIG. 7). to start.

Then, as shown in FIG. 7, the folder and file name in which the target spreadsheet is stored are specified on the text capture screen, and the input is input to the target file input reception processing unit 11.
(S110).

As a file name, a wild card (“*” (asterisk) is used as a wild card in FIG. 7, but any wild card such as “?” (Exclamation mark) may be used). . In that case, for the portion corresponding to the wild card, all files that match it are designated as targets.

Then, when “text conversion” is pressed, it is received by the conversion processing unit 12, it is determined that the start input of the conversion process has been received (S 200), and the conversion process is started.

For example, the spreadsheet selected for text conversion is shown in FIGS. 8 (a) and 8 (b).
Suppose that FIG. 8A shows the sheet “April 2013”, and FIG. 8B shows the sheet “April 2013_2”. Since the same processing is performed as the data conversion processing, a case where processing is performed on the sheet in FIG. 8A will be described below.

When the conversion process is started, the spreadsheet is read from the spreadsheet storage location specified in S110, and the data is read according to the conditions specified in S100. At this time, when a plurality of spreadsheets and a plurality of sheets are designated, the data reading process is executed by “the number of spreadsheets × the number of sheets”.

Specifically, first, from the acquisition range “A2” of the sheet “April 2013” to be acquired,
The range data extraction processing unit 120 extracts the value of the cell in the range of “E7” (S210) and stores it in the internal variable rangeData. The value of the variable rangeData is stored in the capture range data storage unit 121. FIG. 9 schematically shows a case where a value is stored in the variable rangeData.

Next, the determination target data extraction processing unit 122 extracts the value of the row or column specified as the excluded row and the excluded column (S220), the internal variable excludeFlgColData (in the case of the excluded row), and exc
Store in ludeFlgRowData (for excluded columns). Variable excludeFlgColData, excludeFlgRowDa
Each value of ta is stored in the determination target data storage unit 123. Figure 10 shows the variable excludeFlgColDat
FIG. 11 schematically shows a case where the variable excludeFlgRowData is stored when a is stored.

Since the determination target data extraction processing unit 122 determines whether to exclude a row based on the value of “A column”, the value of “A column” is extracted and stored in the variable excludeFlgColData. On the other hand, whether or not to exclude a column is determined based on the value of “first row”. Therefore, the value of “first row” is extracted and stored in the variable excludeFlgRowData.

Next, the exclusion target data determination processing unit 124 counts the number of rows and the number of columns of the variable rangeData stored in S210 (S230). In the case of FIG. 9, the number of rows is counted as 6 and the number of columns is counted as 5.

Then, the exclusion target data determination processing unit 124 determines which column is the exclusion target and which row is the exclusion target for the variables excludeFlgColData and excludeFlgRowData stored in the determination target data storage unit 123 (S240). The result of judgment is an internal variable excl
The data is stored in udeColFlgList and excludeRowFlgList and stored in the exclusion target data storage unit 125.

For a column, an internal variable that determines whether the value stored in the variable excludeFlgColData is a blank (NULL) or a predetermined value such as “0”, and if it is blank or not a predetermined value, an internal variable that stores the determination result “TRUE” is stored in excludeColFlgList, and “FALSE” is stored as a value in variable excludeColFlgList when it is blank or a predetermined value. This process is S2
Repeat for the number of columns counted at 30. That is, since S230 counts as five columns, the value determination process is performed five times in order for the value stored in the variable excludeFlgColData. FIG. 12 schematically shows a process for determining the value stored in the variable excludeFlgColData and storing the value “TRUE” or “FALSE” in the variable excludeColFlgList.

In the case of the variable excludeFlgColData, “customer code” in the first column, “product code” in the third column,
Since the value of “Order quantity” is entered in the fifth column and the others are “blank”, the variable excludeCol
In the FlgList, “TRUE” is assigned to Item (0), Item (2), Item (4), and “F” is assigned to Item (1), Item (3).
ALSE "is stored as a value.

For the row, the value stored in the variable excludeFlgRowData is blank (NULL).
Or if it is a predetermined value such as “0”, if it is blank or not a predetermined value, “TRUE” is set in an internal variable excludeRowFlgList that stores the determination result, and if it is blank or a predetermined value, a variable Store “FALSE” as a value in excludeRowFlgList. This process is repeated for the number of rows counted in S230. That is, since it counts as 6 rows in S230, the value determination process is performed 6 times in order for the value stored in the variable excludeFlgRowData. FIG. 13 schematically shows a process for determining the value stored in the variable excludeFlgRowData and storing the value “TRUE” or “FALSE” in the variable excludeRowFlgList.

In the case of the variable excludeFlgRowData, “001005” is displayed on the first line and “001005” is displayed on the second line.
"," 001008 "on the third line," 001008 "on the fourth line," 001010 "on the fifth line
, Line 6 is "blank", so in the excludeRowFlgList, Item (0), Item (1), Ite
“TRUE” is stored as a value in m (2), Item (3), Item (4), and “FALSE” is stored as a value in Item (5).

As described above, when the exclusion target data determination processing unit 124 determines the row and column to be excluded, the data generation processing unit 126 displays the variable excludeCol stored in the exclusion target data storage unit 125.
Based on the values of FlgList and excludeRowFlgList, a record to be captured is extracted from the capture range data storage unit 121 and added to an internal variable dt. That is, the variable excludeColFlgL
Of the values stored in ist and excludeRowFlgList, for the row or column that is TRUE, ra
Add a record of ngeData to the variable dt, and for a row or column that is "FALSE", rangeDat
By executing a process that does not add the record a to the variable dt, data to be captured is generated (S250). The processing of the data generation processing unit 126 is schematically shown in FIG.

Normally, the value of the record of rangeData is stored in the variable dt. However, if the calculation formula is stored instead of the value as the record, the calculation formula may be stored as the variable dt.

Since the variable dt generated by the data generation processing unit 126 is the converted data, the data output processing unit 127 outputs it as a text file (S260). FIG. 15 is an example of a text file output.

As described above, necessary row and column data in the spreadsheet can be converted into a text file and output.

When it is desired to use the text file data thus converted in the business system or the like, it can be imported into the business system by pressing the “execute” button in the text capture screen of FIG.

In the import range input reception processing unit 10 and the target file input reception processing unit 11 of the first embodiment, input of values such as the capture range, excluded rows, and excluded columns is displayed on the capture setting screen, and folders and The case where values such as the file name are input and various settings such as the capture range are accepted by accepting the input has been explained. However, this setting is applied to the cells, rows or columns of the spreadsheet to be imported. The user can specify directly and may be configured to accept it.

First, when a spreadsheet to be imported is opened by performing a predetermined operation in the data conversion system 1, a file name to be imported and a folder name as a storage location are stored in the target file input reception processing unit 11. . Then, the capture range input reception processing unit 10 displays the image of the spreadsheet on the capture range setting screen.

When the user designates a capture range for the spreadsheet image displayed on the capture range setting screen using a mouse or a touch panel, the capture range input reception processing unit 10 becomes the start position of the capture range. The upper left cell (the cell with the smallest row number and the smallest column number among the combinations of the row number and column number of each cell that composes the specified range) and the lower right cell (specified) Of the combination of the row number and the column number of each cell constituting the range is acquired, and input is accepted as the capture range.

Next, the user takes the spreadsheet image displayed on the capture range setting screen,
When an excluded row and an excluded column are specified using a mouse or a touch panel, the capture range input acceptance processing unit 10 accepts input of such information.

Through the processing as described above, the user can specify directly on the spreadsheet to be imported, so that it is not necessary to input a row number or a column number. In addition, the operation is simplified and erroneous input can be prevented.

In this way, the capture range input reception processing unit 10 displays the spreadsheet and accepts the input of the capture range designation on the display, thereby specifying the range to be captured as a data conversion target. Because it can be done on the spreadsheet display, the operation becomes easy.

In addition, the capture range input reception processing unit 10 is configured to display a spreadsheet, and on the display, to accept input of designation of rows and / or columns to be excluded from data conversion. In addition, since it is possible to specify rows and columns that need not be imported on the spreadsheet display, the operation becomes easy.

Although the spreadsheet to be imported may be activated on the data conversion system 1, it is opened on another application program, for example, spreadsheet software using the spreadsheet, and the cell information specified by the user is You may comprise so that it may acquire from an application program.

The text file output by the data conversion system 1 can be imported by a business system used in a company or the like. An example of the system configuration of the data conversion system 1 in the third embodiment is shown in FIG. The data conversion system 1 in the present embodiment further includes a data capture processing unit 13.

The data capture processing unit 13 causes the business system to capture the text file output by the data output processing unit 127 in the conversion processing unit 12. Note that when the data import processing unit 13 imports into an existing database of a business system, a text file,
Correspondence with the data structure of the record of the business system is defined in advance and imported based on it.

For each data item in the text file, the data capture processing unit 13 captures data in association with the data structure of the business system, adds it as a record value of the business system, and stores it. For example, the output text file is shown in FIG. 15, the first item in the text file is the customer code (CUSU0010001), and the second item is the product code (CUSU001002).
, If the third item corresponds to each record of the number of orders (CUSU001003), the first item in the text file (the data in the left column) is the second as the value of the business system customer code record. Item (middle column data) is the value of the business system product code record, and the third item (right column data) is the value of the business system order quantity record. Data is extracted and stored in the business system record. This is schematically shown in FIG.

When a new database is created and imported into the business system, it may be as follows. In other words, it accepts the setting of the item row for the spreadsheet file to be imported, acquires the item name in each column based on this setting information, determines the attribute for each column data other than the item name, The table definition may be performed by calculating the number of digits by adding a predetermined number of digits to the maximum number of digits of data, and setting the obtained item name, attribute, and number of digits. The attribute is usually judged by judging whether it is a numerical value or a character.

Since the database table definition is performed based on the information of the spreadsheet file to be imported, it is possible to reduce the effort for the user to perform the table definition in advance on the database.

Note that all or part of the file name may be used for the table name in the table definition. By doing in this way, a user's effort can further be reduced.

Next, as another embodiment of the present invention, a description will be given of a case where a spreadsheet into which data related to a plurality of rows and / or a plurality of columns is input is output to a text file.
An example of the system configuration of the data conversion system in this case is shown in the conceptual diagram of FIG. An example of the processing process of this embodiment is shown in the flowchart of FIG.

An example of a spreadsheet to be processed in this embodiment is shown in FIG. In the case of FIG. 20, the case where two content values of the number of orders and the order amount are associated for each product is shown.

The data conversion system in this embodiment includes a preset processing unit 14, a matrix decomposition conversion processing unit 15, and a data output processing unit 16.

The prior setting processing unit 14 is a process that receives and stores in advance settings of setting values such as correspondence between items and data in order to execute the processing of the present embodiment. The setting values include the heading when outputting to a text file, the content value (data value), the range of cells that contain the content value to be read, the content value item, and the judgment target of whether or not to exclude There is information such as cells to be performed. An example of the setting value is shown in FIG.

The vertical item in the setting value is related data arranged in the vertical direction in the spreadsheet, and includes its heading and content value. In the case of FIG. 20, “
“Customer code” and “customer name” have cell positions “A4” and “B4” as content values of the vertical items. The horizontal item is related data arranged in the horizontal direction in the spreadsheet, and includes a heading and a content value. In the case of FIG. 20, the cell positions “B1”, “B2”, “B3” are used as the heading of the horizontal item, and the cell positions “C1”, “C2” are used as the content value of the horizontal item.
”And“ C3 ”. Further, since the content value range is a range of a cell having data to be read, in the case of FIG. 20, the cell start position is “C4” and the end position is “G13”. Furthermore, the content value item indicates a group of data items to be read.
As the headings, the cell positions are “A1” and “A2”, and the content values are “C4” and “C5”. The cell to be determined is a cell for determining whether or not it is a cell to be excluded from acquisition, and the section indicates whether or not a predetermined condition is satisfied. The set value indicates that it is determined whether or not a predetermined condition is satisfied based on the content value of the position of the cell. Therefore,
If there is data as a predetermined condition (whether it is blank (NULL)) or a predetermined value (for example, “0”), whether or not the predetermined condition is satisfied based on the cell value of the number of orders Will be determined.

Unlike the first to third embodiments, the matrix decomposition conversion processing unit 15 has items and data associated with a plurality of cells in this embodiment, so that the matrix decomposition conversion processing unit 15 is based on the preset received by the preset processing unit 14. After disassembling the association, text conversion processing is performed.

The data output processing unit 16 outputs the data output by the matrix decomposition conversion processing unit 15 as a text file.

Next, an example of the processing process of the present embodiment will be described with reference to the flowchart of FIG. A spreadsheet will be described with reference to FIG.

First, the user activates a setting value input screen shown in FIG. 22 by performing a predetermined operation on the computer. Then, the user inputs each set value on this input screen, and the preset processing unit 14 accepts the input (S300) and stores it. The setting values received in this way are shown in FIG.

When the user starts data conversion processing from the spreadsheet to the text file, the matrix decomposition conversion processing unit 15 performs the predetermined operation on the computer, so that the matrix decomposition conversion processing unit 15 sets the setting value stored in the pre-setting processing unit 14. Based on this, the value of the heading part is acquired from the spreadsheet (S310). In the case of FIG. 21, since “customer code” and “customer name” are set as set values for the vertical item headings, “customer code” and “customer name” are acquired as the values. Since cell positions “B1”, “B2”, and “B3” are set as set values for the heading of the horizontal item, “product code”, “product name”, and “unit price” are acquired as the values. Further, since the cell positions “A1” and “A2” are set as the setting values as the headings of the content value items, “order number” and “order amount” are acquired as the values. This is schematically shown in FIG.

Then, the data output processing unit 16 creates a “heading row” based on the value of the heading part acquired by the matrix decomposition conversion processing unit 15 in S310, and outputs it to a text file (S320). That is, the heading of the vertical item, the heading of the horizontal item, and the heading of the content value item are arranged in a predetermined direction and output to a text file. This process is schematically shown in FIG.

Next, the matrix decomposition conversion processing unit 15 acquires the range information based on the content value range information in the set value (S330). In the case of the set values in FIG.
Since the cell “C13” and the cell “G13” are set as the end position, the information is acquired. At this time, the number of columns and rows in the data acquisition range is also counted. In a spreadsheet, the number of rows is originally managed numerically and can be counted as it is, but the number of columns is often managed in Roman letters such as “A”, “B”, and “C”. Therefore, it is preferable to prepare a correspondence table of Roman letters and numbers of columns as shown in FIG. 25 and count the number of columns based on the correspondence table. This process is shown in FIG.

The matrix decomposition conversion processing unit 15 acquires data from the spreadsheet based on the content value range information acquired in S330 (S340). In the above case, the data of the cells “G13” to “G13” are acquired and stored in the internal variables.

Next, the matrix decomposition conversion processing unit 15 acquires a data group as one unit (S35).
0). In the data group acquisition process, the minimum cell and the maximum cell set by the content value in the content value item are specified, and based on this, the range is set as the data group. That is, when the set value is FIG. 21, since the cell positions “C4” and “C5” are set as the content value of the content value item, the minimum column is “C”, the minimum row is “4”, and the maximum column Is “C” and the maximum row is “5”. Therefore, since “C4” can be specified as the minimum cell and “C5” as the maximum cell, “C4” and “C5” are set as the range of the data group. In the data group acquisition process, the number of data group columns (the number of columns in the data group) and the number of data group rows (the number of rows in the data group) are calculated. In the above case, the number of data group columns can be calculated as 1, and the number of data group rows can be calculated as 2. FIG. 27 schematically shows a data group calculation method, and FIG. 28 schematically shows data group acquisition processing.

Then, the matrix decomposition processing unit substitutes the initial value “0” for the arguments X and Y serving as counters (S360). The counter argument X is for a column and the argument Y is for a row. When both X and Y exceed the number of data range rows and the number of data range columns, this indicates the end of the text conversion process.

If the value of the argument X is less than the number of data range columns (S370), referring to the set value, S340
The value (data) in the range of the data group is acquired from the values acquired in step 1 and stored as internal variables (S
380). Specifically, “A4” + Y row value is set as one vertical item, and “B” is set as two items.
4 ”+ Y row value,“ C1 ”+ X column value as one horizontal item, and“ C2 ”as two items
+ C column value as the X column value, “C3” as the 3 item value, and “C4” as the 1 content item item
The value of + X column + Y row is acquired as two items, the value of “C5” + X column + Y row is acquired, and the value of “C4” + X column + Y row is acquired as the determination target cell. This process is schematically shown in FIG.

In the above case, since the initial values 0 are assigned to the arguments X and Y, one vertical item is “001001”, two items are “customer A”, one horizontal item is “00100001”, 2
The item is “product A”, the three items are “100”, the content value item is “10”, the second item is “1”
, 000 ”, and the determination target cell is“ 10 ”.

The matrix decomposition determination processing unit determines whether the value of the determination target cell is a predetermined condition, for example, blank (NULL) or “0” (S390). In the above example, since the determination target cell is “10”, the condition is not satisfied, and the data output processing unit 16 outputs each value acquired in S380 to a text file (S400). FIG. 30 schematically shows this state. If the value of the determination target cell is “NULL”, the process of S390 is not executed (that is, the output process to the text file in the data output processing unit 16 is not performed), and the process of S410 described later is performed. Execute the process.

Then, the value of the number of data group columns, “1” in the above example, is added to the argument X (S410).
That is, the argument X is updated to 1. As a result, the data group to be processed is shifted to the next data group.

Then, the processes after S370 are executed again. Then, since the argument X is 1, it is less than the number of data range columns (S370), so as in the above case, referring to the set value, among the values acquired in S340 and stored as internal variables, the range of the data group Value (data) is acquired (S3
80). Specifically, since the arguments X are 1 and Y are assigned with an initial value 0, one vertical item is “001001”, two are “customer A”, one horizontal item is “00100002”,
2 items are “product B”, 3 items are “200”, 1 content item is “20”, 2 items are “
The determination target cell is “20”.

Then, since there is “20” in the determination target cell, the condition is not satisfied (S390), and the data output processing unit 16 outputs each value acquired in S380 to the text file (S400). FIG. 31 schematically shows this state. And
The number of data group columns “1” is added to the argument X, and the argument X is updated to 2 (S410).

As described above, when the processing of S370 to S410 is repeated until the argument X becomes 5, S3
In 70, since the number of data range columns and the argument X have the same value, the process of S420 is executed. That is, the argument X is set to 0, the value “2” of the number of data group rows is added to the argument Y, and the argument Y is updated to 2. Since the argument Y is less than the number of rows in the data range (S430), S3
The process after 80 is executed. The process of S420 is a process of changing the data group to be processed in order to execute the next data group row because the data group to be processed has reached the maximum column.

In the same manner as described above, the value (data) in the range of the data group is acquired from the values acquired in S340 and stored as internal variables (S380). In the above case, since the argument X is an initial value 0 and Y is set to 2, the vertical item 1 is “001002”, the 2 items are “customer B”, the horizontal item 1 is “00100001”, 2 The item is “Product A” and the 3 items are “
"100", one item of the content value item is "blank (NULL)", and two items are "blank (NULL)"
, The determination target cell is “blank (NULL)”. Then, since the value of the determination target cell satisfies the predetermined condition, the data output processing unit 16 discards the value acquired in S380 without outputting it to the text file. Then, 1 is added to the argument X (S4).
10), the process after S370 is executed.

The above processing is repeated until the argument Y becomes 10. When argument Y is updated to 10, (
S420) Since the number of rows in the data range and the argument Y have the same value, the processing is terminated as it is.
By executing the above processing, the spreadsheet data can be converted into a text file and output. The text file that is the output result is shown in FIG.

By executing the processing as described above, it is possible to output a spreadsheet into which data related to a plurality of rows or columns is input to a text file.

Although the case where there are two content values vertically has been described above, the same processing can be executed when there is one content value, when there are two content values horizontally, and when there are a plurality of content values vertically and horizontally.

An example of a spreadsheet with one content value is shown in FIG. In this case, for example,
FIG. 34 shows setting values accepted on the setting value input screen of the pre-setting processing unit 14. Further, based on this set value, the output result of the text file obtained by executing the processing of FIG.
It is.

An example of a spreadsheet when there are two content values horizontally is shown in FIG. In this case, for example, FIG. 37 shows setting values accepted on the setting value input screen of the pre-setting processing unit 14.
Further, FIG. 38 shows the output result of the text file in which the processing of FIG. 19 is executed based on this set value.

FIG. 39 shows an example of a spreadsheet when there are a plurality of content values. In this case, for example, FIG. 40 shows a set value accepted on the set value input screen of the advance setting processing unit 14.
Further, FIG. 41 shows the output result of the text file in which the processing of FIG. 19 is executed based on this set value.

By using the data conversion system 1 of the present invention, data created in a spreadsheet can be automated. As a result, errors during data conversion can be eliminated, and the labor and time required for the work can be greatly reduced. Even if data related to a plurality of rows and columns is input, the data can be processed as one data group and converted into text file data.

1: Data conversion system 10: Capture range input reception processing unit 11: Target file input reception processing unit 12: Conversion processing unit 13: Data capture processing unit 14: Pre-setting processing unit 15: Matrix decomposition conversion processing unit 16: Data Output processing unit 70: arithmetic device 71: storage device 72: display device 73: input device 74: communication device 120: capture range data extraction processing unit 121: capture range data storage unit 122: determination target data extraction processing unit 123: Determination target data storage unit 124: Exclusion target data determination processing unit 125: Exclusion target data storage unit 126: Data generation processing unit 127: Data output processing unit

Claims (3)

A data conversion system for converting spreadsheet data into which related data is input across multiple rows and / or multiple columns into a text file,
The data conversion system includes:
Correspondence relationship between content value items including the range data to be processed, content value that sets the range of data group as a unit of data acquisition, and items and data for executing data conversion processing in the spreadsheet , And a determination processing cell that receives a setting value including a determination target cell that is a cell for determining whether or not the data is to be excluded;
The acquired data in the range to be processed in the set value from the data in a spreadsheet, for each data group to be pre-Symbol a unit, each of the determination-target cell judges matrix degradation converts the presence or absence of satisfaction of the predetermined condition for A processing unit;
A data output processing unit that outputs the data of the acquired data group to a text file in accordance with a determination result of satisfaction of the predetermined condition;
Have
Wherein the predetermined condition is, Ri conditions der to determine whether there is data in each determination target cell, or a predetermined value,
A process for determining whether or not a predetermined condition is satisfied for each determination target cell for each data group as one unit,
From the cell position set in the content value of the content value item, specify the number of columns in the minimum column, the number of rows in the minimum row, the number of columns in the maximum column, and the number of rows in the maximum row. Set the range of the data group as one unit by setting the cell in the smallest row as the smallest cell, the cell in the largest column and the largest row as the largest cell,
Further, the value obtained by subtracting the minimum column from the maximum column and adding 1 is calculated as the number of data group columns, and the value obtained by subtracting the minimum row from the maximum row and adding 1 is calculated as the number of data group rows.
The column argument X and the row argument Y are provided as counters.
Each determination target cell for each data group is a cell specified by adding X to the number of columns of the determination target cell of the setting value and adding Y to the number of rows of the determination target cell of the setting value. Yes,
As long as X is less than the number of columns in the data range to be processed, the number of data group columns is added to X each time a data group is acquired and the sufficiency is determined. Until the data is exceeded, the data is collected as a unit and the sufficiency is determined.
When the data group to be processed reaches the maximum number of columns, in order to execute the next data group row, the data group to be processed is acquired by adding the number of data group rows to Y. Change
If Y is smaller than the number of rows in the data range, perform data acquisition processing in the row direction, repeat these processing,
A data conversion system characterized in that when both X and Y exceed the number of rows in the data range and the number of columns in the data range, the processing is terminated . The correspondence between the item and data is
Related data arranged in the vertical direction in the spreadsheet, vertical items consisting of headings and content values,
Relevant data arranged in the horizontal direction in the spreadsheet, horizontal items consisting of headings and content values,
The data conversion system according to claim 1, wherein: A data conversion program for converting spreadsheet data into which related data is input across multiple rows and / or multiple columns into a text file,
The data conversion program stores a computer,
Correspondence relationship between content value items including the range data to be processed, content value that sets the range of data group as a unit of data acquisition, and items and data for executing data conversion processing in the spreadsheet , And a determination processing cell that receives an input of a setting value, including a determination target cell that is a cell for determining whether or not the data is to be excluded,
The acquired data in the range to be processed in the set value from the data in a spreadsheet, for each data group to be pre-Symbol a unit, each of the determination-target cell judges matrix degradation converts the presence or absence of satisfaction of the predetermined condition for Processing section,
A data output processing unit for outputting data of the acquired data group to a text file in accordance with a determination result of satisfaction of the predetermined condition;
Function as
Wherein the predetermined condition is, Ri conditions der to determine whether there is data in each determination target cell, or a predetermined value,
A process for determining whether or not a predetermined condition is satisfied for each determination target cell for each data group as one unit,
From the cell position set in the content value of the content value item, specify the number of columns in the minimum column, the number of rows in the minimum row, the number of columns in the maximum column, and the number of rows in the maximum row. Set the range of the data group as one unit by setting the cell in the smallest row as the smallest cell, the cell in the largest column and the largest row as the largest cell,
Further, the value obtained by subtracting the minimum column from the maximum column and adding 1 is calculated as the number of data group columns, and the value obtained by subtracting the minimum row from the maximum row and adding 1 is calculated as the number of data group rows.
The column argument X and the row argument Y are provided as counters.
Each determination target cell for each data group is a cell identified by adding X to the number of columns of the determination target cell of the set value and adding Y to the number of rows of the determination target cell of the set value. Yes,
As long as X is less than the number of columns in the data range to be processed, the number of data group columns is added to X each time a data group is acquired and the sufficiency is determined. Until the data is exceeded, the data is collected as a unit and the sufficiency is determined.
When the data group to be processed reaches the maximum number of columns, in order to execute the next data group row, the data group to be processed is acquired by adding the number of data group rows to Y. Change
If Y is smaller than the number of rows in the data range, perform data acquisition processing in the row direction, repeat these processing,
A data conversion program for ending processing when both X and Y exceed the number of rows in the data range and the number of columns in the data range .

Images