Every section of the EMF has an Item field. Only a numeric value can be entered into this field (no text). This number is then assigned to an external data file. This number acts as a reference throughout Enterprise.
In the Source tab, each item number must be unique. In other words, the user cannot have duplicate item numbers. If a user tries to type an item number already in use, then the word "Source" will not appear in the grey left column. Only when "Source" appears is the item number valid.
If a user tries to duplicate or paste an item number that already exists, then the following message will appear.
By clicking OK, the user will be brought back to the Item Number Paste Tool. The user can then select a different item number not in use. By clicking Cancel, this will close the message box and the items will not be pasted.
The value field is where the location of the external data file is identified.
If the user leaves the item field blank and types into the value field, this will create a comment. Comments are useful for identifying the items and purpose.
For example, in the image below. A user can identify quickly which file is the current and previous wave.
If the user presses the "F1" key in the value field a Windows Explorer "Open file" box will pop up (below image).
By default, the "Open file" box will display the contents of the project folder. From here the user may select data files from other locations.
Only certain data files qualify as an Enterprise Source. These are the following:
|1)||E-Tabs generated formats:|
2) XML/SPSS files:
3) Microsoft Excel:
|c)||.xls (we recommend updating this file to .xlsx or .exl)|
The user may select multiple data files within the same location by holding down the Ctrl or Shift keys.
Microsoft Excel as a source file
Microsoft Excel is the preferred source file format used in E-Tabs Enterprise. Once an Excel source file has been added, the next stage is to format the file in an Enterprise friendly format. If the tables are in a consistent format, it is recommend to use Table Layouts (Click here for information on Table Layouts). The Table Layout process, will generate a table of contents, so that your tables can be located by a reference (Normally question text). Within each table a user can then reference a cell by Banner and Stub text. Referencing data by text allows much more flexibility, especially if changes are needed to the data tables. Table Layouts is built to work with aggregated data generated from Wincross, Quantum, Uncle, SPSS, Q as well as many more.
When data tables are less consistent, Enterprise has a Constant known as Crosspoints. Crosspoints allows the user to set a table corner in an ad hoc way. The user has full flexibility in that they can reference by text or they can reference an exact cell (e.g. A1 - top left cell). Click here for information on Crosspoints.
.ZTE as a source file
ZTE files are files generated by E-Tabs Writer. E-Tabs Writer will put aggregated cross-tabs into a E-tabs Enterprise friendly format. 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. Table Layouts has a lot more functionality than the original E-Tabs Writer, so we recommend users opt for Table Layouts.
Table software source file
When using a .mtd file from IBM Survey Reporter, .xml from Wincross, .xte from MarketSight or .sav from SPSS. The user will want to use the Constant RowTypes to filter out frequencies and percentages. The benefit of using these files, is that the format is already in a E-Tabs Enterprise friendly format.
.EXL as a source file
The use of .exl files is identical to the use of Excel files when creating data statements. .exl is preferable to .xls as a source, because the project will run considerably faster, and there is significantly less chance of encountering 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:\Program Files (x86)\E-Tabs\Enterprise\EXL Publisher.exe.
If a user has Excel or .exl source data files and they are set out in a consistent format, it is a perfect candidate for using Table Layouts. If the data files are less consistent, then it is recommend the user creates Constants using crosspoints. Table Layouts will generate a Table of Contents for the data file that is imported. This Table of Contents can be used in the Table Tab of Enterprise. Please note if there are a few tables that have a slightly different format such as summary tables, it is possible to apply a Crosspoint to a source that has a Table Layout, in this case the table corner can be adjusted.
Example of use - Please see video here
In the below image, we have an example aggregated dataset that we would like to import into the Table Layouts Wizard.
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:
Once selected, a window will pop up asking if you would like to Create a new layout file or edit an existing one.
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.
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.
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.
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.
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.
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 here the user can specify the sheets to which the Table Layouts will be applied.
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.
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.
Click Apply and you will see that the TOC is updated with the Question texts as shown below.
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.
Banner Details user will 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.
Stub Details user will 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.
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.
In the above example, we have used set Table corner by position. You can also set by Text like below.
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.
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.
...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.
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.
By adding all TOCs it would result in the below: