Two-table Operations

Description

Two-table Operations is an online tool for processing a data table using information supplied as another table or for joint processing of two tables based on a match or difference between selected reference columns.

Data input

To input data, either paste delimiter-separated values in the Input A and Input B fields or open a plain text files (e.g. csv or tsv) containing such values. You can off-course combine the input methods. It is possible to directly copy-paste data from Microsoft Excel or other spreadsheet program (they are tab-separated when copied to clipboard). Select proper delimiters in the "Reading data" section and press the Read Data A and Read Data B buttons. In each step, check the appearance of table in the "Table Preview" field. The values should be properly distributed in table cells. If you see all values in the first cell, probably a mistaken delimiter has been set. Correct the delimiter choice and click Read Data A/B again. If unsure, visual inspection of the input data could provide a clue about the delimiter.

Extracting rows with a selection list

This operation will use a list of items provided as one-column table in Input B to extract all rows from the table in Input A in which the value in the column marked as the reference column matches any item in Input B. Choose if the order of rows in the resulting table will match that of Input A or of Input B and click the Apply button to start the operation.

Removing rows with a selection list

This operation is just an inversion of the previous operation, i.e. instead of extracting the matching rows, it will remove all such rows and output the remaining rows.

Extracting rows by reference columns match

This operation fulfills the need to extract and align corresponding data from two different tables containing also additional unwanted rows. Choose the reference column in each table and click the Apply button. Rows with matching values in reference columns will get aligned side by side, so the width of the output table will be a sum of the input tables.

Merging tables by reference columns difference

This operation is complementary to the previous operation. It will output the rows that contain non-matched values in reference columns. Unlike the previous operation, the rows from input tables will be arranged under each other so the width of the output table will match the width of the input table with more columns.

Aligning two tables

To align the two input tables the program will search for rows with matching values in reference columns and then arrange such rows side by side. Unlike the "Extracting rows by reference columns match" operation described above, this operation will not remove non-matching rows, but will place them under the matching rows, so that the output table will consist of matching rows in its top part followed by non-matching rows positioned either in left or in right part of the output table, depending on their origin. You can choose if all matching rows will be sorted to the top of the output table or if their order will follow that of the input table A and will be interleaved with non-matching rows from table A.

Data output

The program outputs processed tables into the Data Ouput field as tab-separated values. Such text can be directly copy-pasted into a spreadsheet program like MS Excel and should get properly distributed into table cells. Alternatively, the output data can be saved into a text file as delimiter-separated values. Just select the delimiter and click the Save to File button. If you are unsure whether the delimiter character is present in the data, check the "Quoted values" option. The purpose of the Table Preview panel is visual control of input and output data, but no editing or data export functions are available there.