- Krupp Consulting, Inc.
EHS Consulting - Information Technology - Management Systems
SIM for ADL/Track - lnstructions Introduction
This document describes Spreadsheet Import Module (SIM) for ADL/Track. With SIM, a spreadsheet is used as a surrogate data entry tool for auditors who do not have the ADL/Track software. For those auditors, audit findings and recommendations will be entered into the spreadsheet then the entire spreadsheet will be imported into ADL/Track.
The SIM spreadsheet is not designed to serve as an ongoing device for tracking the completion and determining the status of corrective actions. Instead, ADL/Track and its various task reports will be used to track this information.
This document contains the following major parts:
- Part I describes how the spreadsheet is formatted.
- Part II contains instructions on how to import the spreadsheet into ADL/Track.
- Additional Comments contains tips for using SIM
- Also, you can view/download the SIM sample spreadsheet.
To download the SIM sample spreadsheet (which is in Excel v7 for Windows 95 format), click here. Part I: The Audit Spreadsheet Format
Spreadsheet Software. Any spreadsheet software can be used (e.g., Excel or Lotus), as long as it (1) is capable of saving the spreadsheet as a tab-delimited text file, and (2) allows the spreadsheet to be formatted according the Critical Formatting rules (below).
Spreadsheet Format. The SIM audit spreadsheet contains all of the findings for a single audit (one row per finding).
Critical Formatting Rules. The following aspects of the spreadsheet format are critical. Refer to the sample spreadsheet (see link above); it obeys all of the critical formatting rules.
- One Row Per Finding: Each row of the spreadsheet (other than the special ‘heading’ rows) will contain the information for a single audit finding.
- Don’t Mix Audits in a File: Findings from different audits must not be included in the same spreadsheet file (e.g., for Excel, in the same .XLS file). All of the findings in a file must pertain to the same audit.
- Follow the Rules in Table 1: For each finding, the columns of the spreadsheet must be filled in with the information described in Table 1.
- Wrap Text in Cells: All cells should be set up to wrap the text within the cell boundaries and text should be typed such that it wraps naturally when it reaches the end of the cell. If you use the template spreadsheet, the cells are already set up to wrap. If not, the specific steps to do this will depend on your spreadsheet software. In Excel 5.0, for example, this is done by blocking out the cells, then selecting Format: Cells: Alignment and checking the Wrap Text box.
- Group Findings by Category and Include Special ‘Header’
Rows: Spreadsheet rows (i.e., findings) must be grouped by
category and subcategory, with the category and subcategory listed
in a special ‘heading row’ prior to the findings. For
example, the Water Pollution Control findings (category = Environmental,
subcategory = Water Pollution Control) will be grouped together,
and the row preceding the first Water Pollution Control finding
will contain the following text in column A:
Environmental: Water Pollution Control
The text for such ‘heading’ rows must follow these rules: (a) The text must be put in column A; (b) the category and subcategory must be separated by a colon (:); and (c) all of the other columns in that row (columns B through G) must be blank. In the sample spreadsheet, there are three such ‘heading’ rows. - Continue Text in a Blank Row Below: If you need to write more text than can fit in a cell, add a blank row below the finding and continue the text in the cell immediately below. You can create as many of these ‘continued’ rows as needed. (When the spreadsheet is imported, text from ‘continued’ rows will be combined with that from the main finding row.) Note: In the ‘continued’ row, fill in only those columns that need to be continued. Do not repeat text from the above row (e.g., don’t retype the Title or the Type) or put in any other text (e.g., "continued..."). If you do, the import will treat the two rows as two different findings. (An example of a properly formatted ‘continuation’ row is shown in the sample spreadsheet, finding #4.)
Non-Critical Formatting. Other than the formatting items discussed above (Critical Formatting), most other aspects of the spreadsheet format are ignored when the spreadsheet is imported into ADL/Track. Therefore, these formatting aspects can be modified, if desired, without adversely affecting the import. For example:
- page headers, page footers, and page numbers
- the text of column titles
- column width and row height
- shading, fonts, and lines/borders
- the numbering scheme used in column A
- page breaks
- paper size and orientation (i.e., landscape vs. portrait)
- adding columns beyond those listed in Table 1
Part II: Importing the Spreadsheet Into ADL/Track
Required Files. To import the audit spreadsheet into ADL/Track, you must:
(1) Be running version 970425 of ADL/Track (or greater). (The version number is shown above the ADL/Track menu and on the startup screen.); and
(2) have the file s_import.exe (SIM version 1) or c_sim2.exe (SIM version 2) located in the same folder where ADL/Track is installed.
Import Procedure. To import the spreadsheet into ADL/Track, first use the spreadsheet program, then use ADL/Track, as described by the following steps:
1. Run your spreadsheet software (e.g., Excel or Lotus).
2. Open the spreadsheet file.
3. Check the spreadsheet to ensure that it conforms to the formatting requirements described above. If it does not, adjust the format before proceeding.
4. Save the spreadsheet file in Tab Delimited Text format using a filename ending in .TXT. Exactly how you do this depends on the spreadsheet software you use. In Excel 5.0, for example, this is accomplished by selecting "Save As..." from the File menu, entering a filename ending in txt (e.g., Newaudit.txt), selecting "Text (Tab delimited)" in the Type box, then clicking OK to save.
5. Run ADL/Track.
6. Select "Spreadsheet Import Module" from the Misc menu. The Spreadsheet Import screen will appear.
7. Specify the audit number of the audit you want to import into (step 1 on the screen). If you can't remember the audit number, click [Lookup]. (Remember, all findings from the spreadsheet will be added as findings for this audit.)
8. Specify the .TXT spreadsheet file that you wish to import (step 2 on the screen).
9. Click [Import].
10. ADL/Track will analyze the rows of the spreadsheet, then ask you to confirm that you really want to import the findings. If you say Yes, the import will proceed. When the import is over, a summary screen will be displayed showing the number of findings and tasks added. ADL/Track imports the spreadsheet into that audit, making one new finding and one new task for each finding row in the spreadsheet. As it does the import, cells from the spreadsheet are placed in their proper places within ADL/Track, as explained in Table 1.- If the spreadsheet import option does not appear on the Misc menu in ADL/Track, then you may not be running the correct version of ADL/Track or you may not have the required SIM files.
- Since the spreadsheet file is not modified by the import, the same file can be imported by different ADL/Track systems (if necessary).
- During the import, the number of rows analyzed will almost always be greater than the number of findings, so don’t worry if the two numbers don’t match. (The number of rows analyzed includes header and footer rows, blank rows, and ‘continued’ rows, which are not findings.)
- In most cases, all of the findings for a single audit will be consolidated into a single spreadsheet file. However, it is possible to import multiple spreadsheets into the same ADL/Track audit.
- If the audit you import into already contains findings/tasks (e.g., findings/tasks entered directly into ADL/Track), those findings/tasks will be retained as is; they will not be modified.
- If you import the same spreadsheet more than once into the same audit, you will get multiple copies of the findings and tasks in ADL/Track. The spreadsheet import feature does not overwrite the existing data.
- Don’t get confused: The Reports menu in ADL/Track includes options with the names "Audit Spreadsheet," "Finding Spreadsheet," and "Task Spreadsheet." These reports simply print ADL/Track data in spreadsheet-style, row-and-column formats. These reports do not print out the audit spreadsheet discussed here, nor are these reports in any way related to actual spreadsheet files.
- The SIM version number is located in the corner of the SIM
screen.