Configuring Data Translation

Configuring Data Translation

Preface

SaveToDB allows translating database objects to a business language within Microsoft Excel, without changing the underlying database objects.

For example, users can see a user-friendly name "Budget Form" instead of a database name like xls41.uspBudgetForm, "Manager Name" instead of "MGR_NAME", etc.

SaveToDB supports translation and annotation for the following database objects and queries defined using the Query List configuration and the event handler configuration:

  • Database tables, views, stored procedures, and functions.
  • SQL codes, HTTP-queries, and text file queries.
  • Macros, Windows Shell and CMD commands.
  • Fields of tables and views.
  • Parameters of stored procedures and other objects.

The translation is performed within Microsoft Excel after loading data.

Users can change the actual data translation language using the Options dialog box.

Object Translation and Annotation

SaveToDB reads the translation configuration from tables* and views that contain the following fields:

  1. TABLE_SCHEMA
  2. TABLE_NAME
  3. LANGUAGE_NAME
  4. TRANSLATED_NAME
  5. TRANSLATED_DESC
  6. TRANSLATED_COMMENT

* Tables are used starting SaveToDB 7 and in previous versions for Microsoft SQL Server Compact.

The tables and views can contain an additional primary key column.

Configuration data formats:

TABLE_SCHEMATABLE_NAMELANGUAGE_NAMETRANSLATED_NAMETRANSLATED_DESCTRANSLATED_COMMENT
<Object schema>
or <Object type>
<Object name><Language code>[<Translated name>]
[<Description>][<Comment>]

TABLE_SCHEMA and TABLE_NAME define database objects or queries defined using the Query List configuration or the event handler configuration:

TABLE_SCHEMATABLE_NAME
<Table schema><Table name>
<View schema><View name>
<Procedure schema><Procedure name>
<Function schema><Function name>
CODE<SQL code name>
HTTP<HTTP-query name>
TEXT<text-query name>
MACRO<Menu item name>
CMD<Menu item name>
REFRESH<Menu item name>

TABLE_SCHEMA is ignored on Microsoft SQL Server Compact and SQLite. Only TABLE_NAME is used.

The LANGUAGE_NAME field must contain the first two characters of a language code.

The TRANSLATED_NAME field is used as a name of Query List, context menu and Actions menu items.

You may also use translated values on Excel sheets using the following named cells:

FieldNamed Cell
TRANSLATED_NAMESaveToDB_Name
TRANSLATED_DESCSaveToDB_Desc
TRANSLATED_COMMENTSaveToDB_Comment

To insert a named cell, use the Formulas, Define Name Excel menu.

See Configuring names cells about using annotation fields on sheets.

If the translation data have been changed in a database, or a user has changed the actual data translation language, then the translation data should be reloaded.

SaveToDB reloads configuration data in the following actions:

There is an example of the configuration view:

Translation and Annotation of Fields and Parameters

SaveToDB reads the translation configuration from tables* and views that contains the following fields:

  1. TABLE_SCHEMA
  2. TABLE_NAME
  3. COLUMN_NAME
  4. LANGUAGE_NAME
  5. TRANSLATED_NAME
  6. TRANSLATED_DESC

* Tables are used starting SaveToDB 7 and in previous versions for Microsoft SQL Server Compact.

The tables and views can contain an additional primary key column.

Configuration data formats:

TABLE_SCHEMATABLE_NAMECOLUMN_NAMELANGUAGE_NAMETRANSLATED_NAMETRANSLATED_DESC
[<Object schema>
or <Object type>]
[<Object name>]<Column name> or
<Parameter name>
<Language code>[<Translated name>]
[<Description>]

TABLE_SCHEMA and TABLE_NAME define database objects or queries as described above.
However, columns can have NULL values to define translation for groups of columns or parameters:

TABLE_SCHEMATABLE_NAMEDescription
[<Object schema>
or <Object type>]
<Object name>The translation is used for the specified object.
[<Object schema>
or <Object type>]
NULLThe translation is used for all schema objects by default.
NULLNULLThe translation is used for all objects by default.

TABLE_SCHEMA is ignored on Microsoft SQL Server Compact and SQLite. Only TABLE_NAME is used.

The mandatory COLUMN_NAME field defines a column or a parameter that is being translated.

The LANGUAGE_NAME field must contain the first two characters of a language code.

TRANSLATED_NAME field values are used to translate Excel table column headers and interface elements like ribbon parameters.

TRANSLATED_DESC field values are used as column header comments and interface element ScreenTips.

If the translation data have been changed in a database, or a user has changed the actual data translation language, then the translation data should be reloaded.

SaveToDB reloads configuration data in the following actions:

There is an example of the configuration view:

In the example, the translation data are used for all the fields and parameters of objects of the xls40 schema as the TABLE_NAME values are not specified.

Creating Database Configuration Objects

SaveToDB Framework allows developers to add this feature quickly to any database on all supported platforms:
Microsoft Azure SQL Database, Microsoft SQL Server, Microsoft SQL Server Compact, Oracle Database, IBM DB2, MySQL, MariaDB, NuoDB, PostgreSQL, and SQLite.