Universal Translator

Download the Translator

1 Overview

Quite often the preparation of reports depends upon taking information from one spreadsheet and copying and pasting that data into another spreadsheet. The Universal Translator was written as a tool that can do this automatically.

The Universal Translator opens an Excel Spreadsheet or CSV file and copies selected columns from the source file to a destination Excel Spreadsheet or CSV file in a specified order. Data can be copied from the source file and appended to an existing file, overwriting an existing file, or to a newly created file.

2 Using the Translator

There are two parts to the translator: the control panel and the translation tables.

2.1 Control Panel

The Control Panel sets up how the file is to be copied.

Path Names
The first two cells are used to specify the path name for both the source file and the destination file. If these fields are left blank, the current directory is assumed. Both paths must exist.

File Names
The next two cells are used to specify the source file name and the destination file name. Since both files will be open at the same time, these names cannot be the same.

The source file must exist. If the append option is specified, the destination file must already exist.

Sheet Names
The next two cells are used to specify the tab name to copy from in the source file and the tab to copy to in the destination file.

The tab name in the source file must exist. If the append option is specified, the tab name in the destination file must already exist.

Format Name
The next cell is used to specify the format of the destination file. The two choices are CSV and XLS. These types will be created regardless of extension of the destination file.

Mode Name
The next cell is used to specify the operation to perform: append or create.

If append is selected, the destination file must exist.

If create is selected and the file exists, it will be overwritten. Otherwise it will be created.

Start and End Copy
These cells specify the row to start copying from and the row to copy to.

  • Start Copy from Row is used to specify the row in the source file from which the copy will start. The ending row is the last row on the specified tab.
  • Last Row in Column Source is used to specify the column to look at to determine the last row to copy.
  • Start Paste to Row is used to specify the row to which the first row will be copied. This value is ignored if the append option is selected. If append is selected, then the copy range will be after the last row of the destination file.
  • Last Row in Column Destination is used to specify the column to look at to determine the last row in the destination file.

2.2 Translation Tables

The translation tables are used to specify which columns in the source file are copied to which columns in the destination file.

In this example:

  • Column A of the source file is copied to column A of the destination file.
  • Column B of the source file is copied to column C of the destination file.
  • Column C of the source file is copied to column B of the destination file.
  • Column D of the source file is copied to column E of the destination file.

3 Using the Translator

It is probably best to make a copy of the translator, set up all the parameters and save it with a name that indicates what it is supposed to be translating.