BalanceAAP > Data > Import

Import Reference Tables

Before you begin importing, please review the help page on Reference and Data Tables for information on the data requirements of each, and how to prepare your data for import. Beginning the import process before doing a preliminary review and/or cleaning of your data will result in data errors and failed imports.

The first step in the import process is to select the Type of Import. If this is your first plan, you will need to import your reference tables from an external source such as an Excel workbook or other file types. If you have created a plan in BALANCEaap before, and the reference tables will be the same (or mostly the same) in this new plan, you can import the reference tables from the older plan. You will need to select the appropriate existing plan from the existing plan drop-down box

Select which type of import (external data or existing plan) you would like to perform.

The next step is to Select Tables to import. Because information in the data tables refers back to the reference tables, the reference tables must be imported first before the data tables. Complete the import of the reference tables before importing the data tables.

On the Select Tables screen, you need to also choose which tables you want to import.

Select the table or tables to be imported by clicking and dragging the table(s) from the left box into the right box. You can also click Select All to move all tables to the right and click Unselect All to move all tables to the left. Then click Next.

If you are importing the reference tables from an existing plan, you will be brought to the Process Data screen next. If you are importing your tables from an external file, an Excel file, or other file type, your next step will be to Upload Files. On the Upload Files screen, you will select the file or file(s) from which you will be importing your data. You can import all your reference tables from one file if you have an Excel workbook, with sheets representing each of your reference tables (as seen in the Data Requirements Workbook). This is the simplest method when importing multiple tables. It is important to make sure you know which reference tables you have in your workbook and that these tables correspond to the tables you selected to import on the previous screen.

If your reference tables are saved as separate files, you will need to import these tables one at a time. Multiple table imports can be done from multiple Excel or other file types, but this process is more cumbersome. When importing one file at a time, you must maintain the sequence of file imports in order to minimize data errors, since certain reference tables refer to information in other reference tables. For example, you do not want to import the Job Code table before you have imported the Job Group table and the Grade table, since the Job Code table contains the Job Group and Grade fields. Start with the Job Group table, followed by the Grade Table (if you use salary grades in your plan), then the Job Code table. The rest of the reference tables can be imported in any order.

You may not have information for all of the reference tables. The Job Group, Job Code, Department, Applicant Disposition, and Termination Code tables are required for a compliant affirmative action plan. You will only need to import a Grade, Promotion Code, Transfer Code, or Veteran Code table if your organization uses this information. Of those, Grade and Veteran Code are more commonly used than Promotion Code or Transfer Code.

On the Upload Files screen, under Choose File Type, select the type of file you are importing. If your file does not contain column headers (most will), uncheck the First Row Headers box. The following instructions are based on importing from Excel. The process to import from an Access database is nearly identical. Instructions on importing from a text file are covered at the end of this document.

After you have selected the file type, click Browse and find the file. Highlight it in the Browse window and click Open, and the file is added to the import file list.

If your reference tables are saved as multiple files, you can add additional files by clicking the Add button, browsing, and adding them like you did for the first file. These additional slots for files can be removed by clicking the Remove File button.

After you have selected the proper file(s) to import, click Next to continue.

Now, you need to Match Tables and Fields from your Excel sheets and columns in your workbook to the corresponding tables and columns in BALANCEaap. This “matching” will be done one table at a time.

Time saving tip: If you name your Excel sheets and column names similar to the table names and column names in BALANCEaap, BALANCEaap’s built-in auto-matching function will save you time by attempting to match your sheet and column names with those of BALANCEaap names. Preparing your data using the Data Requirements Workbook template will ensure the auto-matching works for all tables and fields.

At the top of the screen, the Current Table will be listed. Select the file that contains the current table from the File drop-down box. This box will be defaulted to the first file you added to the import file list on the previous screen. If you uploaded only one table, it will already be selected for you.

Then from the Sheet Name drop-down box, select the name of the sheet in your file that contains the current table. BALANCEaap’s an auto-matching function may recognize your sheet name and select it automatically.

If you see “Sheet 1, Sheet 2, and Sheet 3,” your sheets were not named in the Excel file. If you see duplicate sheet names, with one of the duplicates having a $ at the end of the name, select the one with the $.

Once the sheet is selected, you then will need to match the columns in your file to the columns in BALANCEaap. Use the drop-down boxes to select your column names for each of the BALANCEaap columns listed. Columns marked with an asterisk are required. The auto-matching function will work here as well, and if your column names are recognized by BALANCEaap, the selections will already be made for you.

Click Next, and you will be taken to the next table to import, unless you are only importing one table from a single file, at which point you will be taken to the Process Data step. Repeat the steps to match the file, sheet, and columns, and continue through each reference table.

BALANCEaap will alert you if you attempt to import a data column in a table when the required corresponding reference table is missing. For example, if you try to import the Grade field in the Job Code table, but you have not imported a Grade table, BALANCEaap will notify you. If this occurs, a page will appear with options on how to handle the missing field. It may include some or all of the following options: •Allow the records to go into errors. The table will be imported with the missing field, with the understanding that the missing field will cause all records imported in the table to be invalid, and be moved to the Errors table. •Do not import the invalid field. As mentioned above, you will only need to import a Grade, Promotion Code, Transfer Code, or Veteran Code table if your organization uses these tables. But if you include a Promotion Code table in your plan, the Promotion Code field in the Promotions table becomes required. Use this option to not import the invalid field if you have mistakenly chosen to import a field for which you have not, and will not, define the corresponding reference table. •Create the missing table from the invalid field. This option will allow BALANCEaap to create the missing table by simply adding the values from the invalid field. For example, if you included promotion codes in your Promotion Table import, BALANCEaap will create the Promotion Code table from the promotion codes being imported. If you select this option, be sure to review the table created for accuracy. •Go back. Select this option to return to the beginning of the import process. From here you can correct mistakes in your table and field selections.

When the tables have been mapped, and you are ready to import, you will be given three options on the Process Data screen, the final step. •Overwrite - This option will delete all records (if any) in the tables you are importing and replace them with the records being imported. Use this option when importing a table for the first time, or if you are completely replacing a table. •Overwrite Matching Records – This option will overwrite any record in your table in which the primary key data matches a record being imported. The primary key of any table is the field that must be unique for that table. For example, the Job Code table’s primary key is the Job Code field. The Roster table’s primary key is the Employee Number. Use this option if you have a partial list of records that need to be imported into an existing table, but those records may already be in the table. •Append – This option will add the imported records to the records already in the table. Use this option if you have records that need to be imported into an existing table, and you are sure those records don’t already exist in the table.

Import Drilldown Reference Tables

If you have created Drilldown Report Levels on the Plan -> Reporting Levels screen and want to use these Drilldown levels when running your reports, you will need to import the Reference table of codes that relates to your drilldown level. Once a Drilldown Report Level is created, when selecting the Reference tables to import, you will see the Drilldown table name listed under the Tables Available. You can select your Drilldown Reference table and then follow the same steps for importing a Reference table.

If you have zip code information related to your Drilldown field and wish to use this information to build your recruitment areas you should include the zip code for each Drilldown code you are importing.

When you create a Drilldown Report Level the Drilldown field then becomes a required field for all Data tables. So you must make sure you include this Drilldown field on all your Data tables prior to importing them.

For example, if you create Branch Location as a Drilldown Report Level on the Reporting Levels screen, then on the Import screen under Reference tables to import, Branch Location will be listed as a Reference table. You would then select to import this table, and you must have a file that contains the list of codes for all your branch locations. The Branch Location code now becomes a required field on all your Data tables. So if you were to now import your Roster, on the Match Tables step of the Import process, under the balanceAAP fields, you will see Branch Location listed with an asterisk, indicating that this field is now required.

Import Data Tables

Once the reference tables are imported, you are ready to import your data tables. Return to the first screen of the import process and select to import Data Tables, and repeat the above steps. Data tables can be imported in any order.

Quick Tip: The Data Import process may take some time to run after [Import] is clicked; select [Run in Background] to continue working in other areas of the software. A notification panel at the top of the page will provide a message when the process is complete.