Working with Sources (EMF Source)

<< Click to Display Table of Contents >>

Navigation:  »No topics above this level«

Working with Sources (EMF Source)

 

Item field in Source


 

Every section of the EMF has an Item field. This field requires a number value to be placed in it. This number value is assigned to the source, and this number acts as a reference throughout Enterprise.

 

In the Source section, each item number must be unique. In other words, you cannot use the same item number twice. If you try to type an item number already in use, the item number will disappear when you move to the value field.

 

 

Value field in Source


 

The value field is where the location of the source is identified.

 

If you leave the item field blank and type in the value field you will create a comment. Comments are useful for identifying the item and purpose.

 

If you hit the "F1" key in the value field a file open box will appear. By default, the open box will display the contents of the project folder. You may select source files from other locations. Click on the drop-down button in the "Look in:" box to browse to other locations.

 

Source_Browse

 

Enterprise only allows E-Tabs reports (.zte,.bte, or.ite), XML files (.mtd,.xml,.xte), Excel workbooks (.xls or.xlsx), CSV files, SPSS SAV, or E-Tabs published Excel workbooks (.exl) as source files.

 

You may select multiple source files within the same location by using the CTRL and SHIFT keys to select multiple files.

 

 

Using Source Files


 

.XLSX and.CSV as a source file

 

Excel is the preferred source file format used in Enterprise. We use an Excel file combined with Table Layouts or Crosspoints in order to use the file in an E-Tabs friendly format. One limitation in Table layouts is that we currently are unable to concatenate multiple tables with the same question into one large table, this can only be done in ZTE. Instead Table Layouts will add #1, #2, #3 etc for any tables with a duplicate name.

 

 

.ZTE as a source file

 

ZTE files are E-Tabs format of standard aggregated cross-tab files. ZTE files are created by converting the output from cross-tab packages such as Wincross, Quantum, Uncle, SPSS, and others using the E-Tabs Writer. ZTE files can also be created from text files, but there are some formatting and layout requirements in order for the conversion to be successful.

 

 

XML source file

 

When using a.MTD file from IBM Survey Reporter, XML from Wincross, or.XTE from MarketSight, you will want to use the filter RowTypes to filter out frequencies and percentages.

 

 

.EXL as a source file

 

The use of EXL files is identical to the use of XLS files when creating data statements. EXL is preferable to XLS as source because your project will run considerably faster, and you will not encounter memory resource issues. With projects using embedded Excel in the template, it is essential to use EXL as the source to avoid project crashes.

Excel files can be converted to EXL format, using EXL Publisher which is included with the installation of Enterprise and can be found in C:\E-tabs\Enterprise\EXL Publisher.

 

 

Table Layouts


 

When using Excel or EXL as a source file you can use Table layouts, providing the tables are set out in a consistent format.  Otherwise, you would use our other method: crosspoints. Table layouts will generate a TOC for the Excel file that you can import into the Tables section of the EMF.  If you use table layouts, you will still be able to use crosspoints and all other applicable filters.

 

Below is an example data table set that we would like to import into the Table Layouts Wizard.

 

TL_Data

 

To use Table Layouts wizard go to Sources tab, select the source file for which you want to create table layouts and then right click to select "create table layouts for source n" like below:

 

TL_source screen

 

Once selected, a window will pop up asking if you would like to Create a new layout file or edit an existing one.

 

TL_Create

 

In our case we want to start afresh, so we select "Create" and click on Ok button. This will then open the Table Layouts Wizard as seen below.

 

TL_Default

 

 

Wizard Mode

 

This is the default wizard the first time you open Table Layouts. The idea of this mode is to format the data tables step by step. There are 6 sections in this mode (5 sections if only a single worksheet). These sections are the tabs of the Advanced mode.

 

Wizard mode will ask a series of questions, as you answer each question the tables will be updated in the preview. There is a permanent previous and next button on the right hand side. The previous button will take the user back to the previous question and undo the last settings. If the user presses next it will skip the question. Alternatively, if this is the second time the user has been in Wizard mode, or if the user has made changes in the Advanced mode, these settings will be carried through the Wizard mode by pressing next.

 

TL_Wizard Intro

 

This is an example of the type of questions that will appear in wizard mode. There will always be buttons in the bottom left corner to proceed to the next question. Please note above the questions in bold, there is either an introduction into the section or a breakdown of previous answers to put the current question into context.

 

TL_Wizard Select

 

After answering some questions, you may be required to select a cell/s from the preview below. Here is an example above, where we are selecting the question text position.

 

 

Advanced Mode

 

This mode allows you to format your tables in any order. There are also options in this mode that are not possible in Wizard mode.

 

TL_Advanced

 

Ignore Sheets In the wizard using the first menu option you can ignore sheets for ex if there is a TOC sheet you could ignore.

 

Apply layouts to You can also specify the sheets to which you want to apply Table lay outs.

 

Find Start of Tables . This tab defines the start point of the tables. The start of new tables will need to be identified by some consistent text or pattern. In this example each table starts with a “Q” followed by the question number or type followed by a “.”. The pattern is “Q[some text].” You can type the unique character in the text field like in our example Q then ? and a full stop. * is for any number of characters, # is for any number of numbers and ? is for one character. Now click Apply and see that the Table of contents updated in the left panel as shown below.

 

TL_StartOfTables

 

TOC Entry Location(s) The TOC entry is the text that appears in the table of contents, which then you add in to your Tables emf screen. Click on TOC Entry Location(s) and now click on the question text in the data area > Right click > TOC Entry Part 1 like below.

 

TL_TOC Right Click

 

Click Apply and you will see that the TOC is updated with the Question texts as shown below.

 

TL_TOC entry

 

In this case there are repeat question rows, so Table Layouts automatically puts #n after each replicated TOC entry. We then have 3 options:

1) Use the #n as a reference.

2) Add additional TOC Entries, these TOC entry cells can be found in different columns.

3) Use a range of cells that are all found in the same column. Until check box

 

In this case row 4 contains the additional information, so in the example below option 3 is used.

 

TL_TOC Entry Range

 

Banner Details Set the Banner details. This step is only needed if the banner has more than one line. In our example file, the banner has no extra lines.  But if you have two lines in your banner you can set your banner to two rows like this, alternatively you can select pattern and customize the banner. Please refer to the orange help button for more information. TL_Help

 

TL_Banner details

 

Stub Details Set the Stub details. This step is only needed if your stub has multiple columns. If you need your stub to be two columns or more to make them unique from each other then set the stub to two or more columns under the simple option. If you would like to customize your stub, you can do this under the pattern option. Please refer to the orange help button for more information. TL_Help

 

TL_Stub details

 

Table corner Table corner is the intersection of banner and stub. You can set the Table corner by right clicking on the cell which you think is the Table corner and clicking on Set Table corner.

 

TL_Table corner

 

In the above example, we have used set Table corner by position.  You can also set by Text like below.

 

TL_Table corner by text

 

Lastly, there is the option for Table Layouts to guess the Table Corner. This can be activated on the far right. Notice that you cannot use the other Table Corner options when guessing. This feature is especially useful if you have summary tables that have a slightly different format.

 

TL_table Corner Guess

 

Once the design has been chosen. You can select save. This will generate a text file TableLayouts.proj.txt and it will be placed inside the project folder. This save is know as save by source number. This Table Layout will always be applied to the file linked to that certain source item number.

 

If this type of save is not ideal for your situation. There are some other options. If you select Save as.

 

TL_Save As

 

...source number n this as explained above, is the default save and the Table Layout file will be assigned to the source item number. The file produced will be called TableLayouts.proj.txt and will be placed in the project folder.

 

...source file <Filename.xlsx> this will save the layout file to a specific file. In this case the file is called Data.xlsx. So the Layout folder will be placed in the same folder as the data and called Data.xlsx.layouts.txt.

 

...all source files in this folder this will create one layout file, and it will be applied to every data file of the same format (e.g. xlsx) in the folder. An example of use may be in a multi-country project and you have a number of country data files that all share the same layout, rather than creating a layout for each. When a layout file is produced a file called TableLayouts.xlsx.txt will be placed in the folder.

 

When a layout file has been applied to a data file, it can be seen within the Source tab. Notice your source file now reads that it (Uses table layouts) as shown below.

 

TL_Source

 

The final step is to add your newly created Tables to the Tables EMF screen. You would add by typing in an item number in Item column of the Tables screen and then in the Value column press F1 which opens up the window for you to select the source file and then check All TOC sections to add all four tables and then click ok.  This will add all the four tables to your Tables screen.

 

TL_Add Table

 

By adding all TOCs it would result in the below:

 

TL_Table Screen