The following details the process to parse an Excel spreadsheet file or database source.
Parsing a database format requires that you create a connection file. This connection file is used as the source file you insert in Catalyst.
As part of creating the connection file, you will define an SQL query which CATALYST uses to parse the database source (parsing both source and target strings).

Once translated, extracting from CATALYST will update the database source overwriting the translation column.

 

Creating a Datasource connection file

1. Create a Datasource Connection file which used to parse your database source

Catalyst 11 and later:

On the HOME Tab > Datasource > Define

 

Catalyst 10 and older

Choose Tools menu > Datasource > Define

 

2. Select Connection String radio button and click on the Build button.

 

 

3(a). Office 97 to 2003 Excel spreadsheet

3(a)1. From the Provider tab select Microsoft Jet 4.0 OLE DB Provider.

3(a)2. Click on the Connection tab. You must manually paste in the full path to the file in the Data Source text box. Make sure there are no quotes in your path as it will result in a connection error.

3(a)3. On the All tab: Type Excel 8.0 in Extended Properties value and click OK.

 

 

3(b). Office 2007 Excel spreadsheet

3(b)1. From the Provider tab select Microsoft Office 12.0 Access Database Engine OLE DB Provider.

3(b)2. Click on the Connection tab. You must manually paste in the full path to the file in the Data Source text box. Make sure there are no quotes in your path as it will result in a connection error.

3(b)3. On the All tab: Type "Excel 12.0 XML" or "Excel 12.0" in Extended Properties value and click OK. See notes below to determine the correct value for your file extension.

 

3(c). Office 2016 Excel spreadsheet

If you have Microsoft Office 2016 32bit, download the 32bit Microsoft Office 12.0 Access Database Engine OLE DB Provider.
Only Catalyst 32bit version will be able to connect to a spreadsheet.

If you have Microsoft Office 2016 64bit download the 32bit Microsoft Office 12.0 Access Database Engine OLE DB Provider.
Only Catalyst 64bit version will be able to connect to a spreadsheet.

 

On the Provider tab select Microsoft Office 12.0 Access Database Engine OLE DB Provider.

 

Click on the Connection tab. You must manually paste in the full path to the file in the Data Source text box.

Make sure there are no quotes in your path as it will result in a connection error.

 

If you click on Test Connection at this stage, it is possible that you will receive an error message.

This error can be ignored, click on OK.

 

On the All tab, type "Excel 12.0 XML" or "Excel 12.0" in Extended Properties value and click OK.

See notes below to determine the correct value for your file extension.

 

 

How to determine the Extended Properties Property value

Enter "Excel 12.0 XML" for the default file format that uses the .xslx file name extension or Enter "Excel 12.0" for the non-default binary file format that uses the .xslb file name extension.

Note: When you import from Excel 2007, you can specify either Excel 12.0 or Excel 12.0 XML because the driver detects the correct format from the input file. However, when you export to Excel 2007, you have to specify the Excel file format that corresponds to the file name extension for the output file.

 

4. To confirm your connection string is correct click back on the Connection tab and click on the Test Connection button. You should get the message Test connection succeeded.

 

5. Click OK to the Datasource Connection dialog and the Database Rules dialog will appear.

 

 

Setting up the Database Rules

Once the you have created a successful connection to your Excel file, you will need to setup the Database Rules in order to create a .ddf file which will be inserted in your Alchemy Catalyst project. This .ddf file contains in the information to connect to your Excel file and what columns and rows to parse as localisable.

In the example below, a successful connection was made to an excel file which contains multiple tabs, each with 3 columns, String ID (String), source string (English) and target string (Spanish).

Once the connection is made, you get to the Database Rules dialog. The contents of the excel file is listed in the Database structure pane, listed by tabs:

 

Enter an SQL query (see 1 below) in the Result Set pane. In this example we select everything in the About tab of the excel file. The tab name must be typed in square brackets as per screenshot below.
Enter a name for this query in the Name field  (see 2 below).
Click the GO button  (see 3 below). This will result in displaying the contents of the About tab in the ResultSet Structure pane:

 

Now that the contents of the first tab are listed in the ResultSet Structure, we need to identify what is localisable. Defining the String ID and Source and Target strings.
Click the + sign left of the query name ("About tab" in this example) to expand the contents.
Click + hold on the String column and drag to Localisable Objects in Project file pane. Then select New ID in the context menu that opens. This define it as string ID:

 

Click + hold on the English column and drag to Source in Project file pane.  This define it as the source string for translation.

 

Click + hold on the Spanish column and drag to Source in Project file pane. Then select New translation in the context menu that opens. This define it as the corresponding translated string for the Source string previously defined:

 

At this point, if you are working on an Excel file which has 1 tab, your Database Rules would be complete.

However, if you have more than one tab in your Excel file, you will need to repeat the above steps for each tab.

Enter a new Query to select the next tab. In this example we are selecting the AC tab.
Enter a different name for this query in the Name field.
Click the GO button. This will result in displaying the contents of the AC tab in the ResultSet Structure pane, below any Tab already setup:

Follow the same steps to define the String ID, Source string and Target string.
You can always monitor your Project File selection in the Preview pane at the bottom of the dialog.

 

Once all your tabs have been successfully setup in your Database Rules, you can Save.

This will prompt you to save as a DDF file. This DDF file contains all the necessary information for Catalyst to parse your Excel file as a database and will allow to extract back to the excel file (overwriting it's contents).

Once your DDF file is saved, insert it in your Alchemy Catalyst project file like you would any other localisable file. Once translated, Extract like you would with any other localisable file and it will warn you that the excel file will be permanently updated.

 

IMPORTANT: Duplicate IDs in your excel file will cause the same translation to be repeated to all the strings with the duplicate ID. Make sure to correct any Duplicate IDs in your Excel file prior to inserting in Catalyst.

 

Products or Versions Affected

  • Alchemy CATALYST 8.0 and higher