Overview of Data Import Utility

Many firms enter data in files outside of Advantage and want to bring the data into Advantage for processing. For example, a firm may design and use its own timesheet data entry form, and want to bring the timesheets into Advantage for labor costing and payroll purposes.

The Data Import Utility automates this process, allowing you to take data in a text (.txt) file, and use it to create a data entry table in Advantage. You can create any of the following types of data entry tables:

•	Timesheets

•	Journal Entries

•	Miscellaneous Expenses

•	Units

•	Employee Expense Reports

•	Prints and Reproductions

•	Accounts Payable Vouchers

•	A/P Disbursements

•	Invoices

•	Cash Receipts

Once you import the data, you can check its validity, then post it as you would any other data entry batch.

•	You can run the Advantage 8.0 Import Utility in either Access 2002 or Access 2003.

•	This online help covers data entry import only. Procedures for importing data from another system into Advantage 8.0 are covered in the Advantage Import Guide.

•	For assistance with the Import Utility, please contact Deltek’s Data Migration Group.

   Related Topics


Basic Steps for Importing Data

To import a data entry file into Advantage:

1.	Prepare the file. Make sure that it is in a format that Advantage can recognize.

2.	Load the file into the Data Import utility.

3.	(Optional) Review the rows in the newly-created table.

Each row represents a single data entry line item.

4.	Validate the rows in the table.

Advantage checks the validity of project numbers, employee numbers, and other codes and amounts that appear in the table.

5.	Correct any errors that Advantage finds.

6.	Import the corrected table into Advantage for posting.

7.	Repeat steps 5 and 6 until all of the data entry files are imported.


Data Import Security

Advantage controls access to the Data Import utility, using two layers of security:

•	You must log into the utility using a valid username and password.

•	You cannot import a data type (e.g., timesheets) unless you have security access to Advantage data entry for that particular data type.

Username and Password

The Data Import utility has its own login form, where you must enter your Username and Password.

Security Access to Data Types

When you select the Data Import utility from your menu of Advantage applications, you will see the Data Import form. This form lists the types of tables you can create.

For a data type to appear on the list, you must have security access to data entry for that data type. Security access is based on the settings entered on your workgroup’s Security Maintenance form.


Accessing the Data Import Utility

To begin using the utility:

1.	Select Advantage from the Start Menu/Programs.

2.	Select Data Import from the list of Advantage applications.

3.	Select a processing period from the list of valid periods.

Advantage uses the period ending date that you choose as the end date for any data entry batches that you create during this login. The period must be prior to or the same as the current period.

When you choose a processing period, Advantage displays a blank form with menu choices at the top.

4.	Select Utilities from the menu.

5.	Select Transaction File Import.

Advantage displays the Data Import Utility form, shown below.

6.	Refer to Loading Files for further instructions.


Linking to a Different Advantage Database

If the file that you plan to import is in a different Advantage database from the one to which you are currently linked, you can link to the other database instead.

To link to a different Advantage database:

1.	Select Utilities from the menu at the top of the form.

2.	Select Database Wizard.

3.	Link to the new database.

  For instructions, please see the Database Wizard book in the Table of Contents for this Advantage online help.


Preparing Files

Text Files

To be loaded into Advantage, a file must be a text file, with the extension .txt.

Content of Files

Advantage data entry tables contain rows of data, each row containing a single line item. The rows are composed of data fields. The data fields are of a specific length and appear in a specific order. For example, an Advantage journal entry table contains rows of entries, each beginning with a 7-character reference number, an 8-character transaction date, and a 40-character master description.

You have the option of creating the .txt files:

•	using fields of the exact required length, or

•	using fields that are less than or equal to the required length, separated from each other by delimiters (for example, by tabs).

You must follow these rules when you prepare a file to be imported into Advantage:

•	The file must contain rows, each row representing a transaction.

•	Fields must be in the correct order.

•	Fields must either be of the correct fixed length or be separated by valid delimiters.

•	Text fields may begin and end with valid text qualifiers.

Table Formats

See Table Formats for a description of the fields that make up each kind of data entry table.

When You Load the File

As part of loading the file, you must tell Advantage how the file is formatted. See Loading Files for more information.


Table Formats

Refer to the table formats listed below for help in preparing files to be imported:

•	Timesheets

•	Journal Entries

•	Miscellaneous Expenses

•	Units

•	Employee Expenses

•	Prints and Reproductions

•	Accounts Payable Vouchers

•	A/P Disbursements

•	Invoices

•	Cash Receipts

  Tip

   Related Topics


Timesheet Data Entry Table Format

Refer to the table below when you prepare a text file containing timesheet transactions.

If you use delimiters, you need not enter fields using the exact start position and width shown below. Just be sure that the fields are in the correct order and that their width does not exceed the width shown below.

Required Field?	Field Name	Data Type	Start	Width
X	Employee	String	1	10
X	TransDate	Date/Time	11	8
X*	Project	String	19	12
X*	Task	String	31	5
X	LaborCode	String	36	5
must have either	RegHrs	Double	41	8
reg. or ovt. hours	OvtHrs	Double	49	8
	TransComment	String	57	255
	SpecialOvtHrs Total	Double	312	8
*Follow the standard rules for Advantage data entry in deciding what project or task to enter. Remember that you must enter a task number if the project has tasks.

   Related Topics


Journal Entry Data Entry Table Format

Refer to the table below when you prepare a text file containing journal entry transactions.

If you use delimiters, you need not enter fields using the exact start position and width shown below. Just be sure that the fields are in the correct order and that their width does not exceed the width shown below.

Required Field?	Field Name	Data Type	Start	Width
X	RefNo	String	1	7
X	TransDate	Date/Time	8	8
	MasterDescription	String	16	40
	PostType	String	56	1
X*	Project	String	57	12
X*	Task	String	69	5
X	Account	String	74	10
must have either	DebitAmount	Double	84	8
debit or credit	CreditAmounts	Double	92	8
	DetailDescription	String	100	40
	TaxCode	String	140	3
	TaxType	String	143	1
	TaxDebitAmount	Double	144	8
	TaxCreditAmount	Double	152	8
*Follow the standard rules for Advantage data entry in deciding what project or task to enter.  Remember that you must enter a task number if the project has tasks.

   Related Topics


Miscellaneous Expenses Data Entry Table Format

Refer to the table below when you prepare a text file containing miscellaneous expense transactions.

If you use delimiters, you need not enter fields using the exact start position and width shown below. Just be sure that the fields are in the correct order and that their width does not exceed the width shown below.

Required Field?	Field Name	Data Type	Start	Width
X	RefNo	String	1	7
X	TransDate	Date/Time	8	8
	MasterDescription	String	16	40
X*	Project	String	56	12
X*	Task	String	68	5
X	Account	String	73	10
X	Amount	Double	83	8
	DetailDescription	String	91	40

*Follow the standard rules for Advantage data entry in deciding what project or task to enter.  Remember that you must enter a task number if the project has tasks.

  Related Topics


Units Data Entry Table Format

Refer to the table below when you prepare a text file containing unit transactions.

If you use delimiters, you need not enter fields using the exact start position and width shown below. Just be sure that the fields are in the correct order and that their width does not exceed the width shown below.

Required Field?	Field Name	Data Type	Start	Width
X	Unit	String	1	10
X	TransDate	Date/Time	11	8
X*	Project	String	19	12
X*	Task	String	31	5
	UnitTable	String	36	12
X	Account	String	48	10
	DetailDescription	String	58	40
X	Quantity	Double	98	8
*Follow the standard rules for Advantage data entry in deciding what project or task to enter.  Remember that you must enter a task number if the project has tasks.

  Related Topics


Employee Expenses Data Entry Table Format

Refer to the table below when you prepare a text file containing employee expense transactions.

If you use delimiters, you need not enter fields using the exact start position and width shown below. Just be sure that the fields are in the correct order and that their width does not exceed the width shown below.

Required Field?	Field Name	Data Type	Start	Width
X	Employee	String	1	10
X	ReportDate	Date/Time	11	8
X	ReportName	String	19	40
X	TransDate	Date/Time	59	8
X*	Project	String	67	12
X*	Task	String	79	5
X	Account	String	84	10
X	Amount	Double	94	8
	DetailDescription	String	102	40
	SuppressBill	Yes/No	142	1

*Follow the standard rules for Advantage data entry in deciding what project or task to enter.  Remember that you must enter a task number if the project has tasks.

  Related Topics


Prints and Reproductions Data Entry Table Format

Refer to the table below when you prepare a text file containing prints and reproductions transactions.

If you use delimiters, you need not enter fields using the exact start position and width shown below. Just be sure that the fields are in the correct order and that their width does not exceed the width shown below.

Required Field?	Field Name	Data Type	Start	Width
X	RefNo	String	1	7
X	TransDate	Date/Time	8	8
X	TypeOfPrint	String	16	40
	Originals	Integer	56	2
	Copies	Integer	58	2
X*	Project	String	60	12
X*	Task	String	72	5
X	Account	String	77	10
X	Amount	Double	87	8
	DetailDescription	String	95	40
*Follow the standard rules for Advantage data entry in deciding what project or task to enter.  Remember that you must enter a task number if the project has tasks.

  Related Topics


Accounts Payable Vouchers Data Entry Table Format

Refer to the table below when you prepare a text file containing accounts payable vouchers.

If you use delimiters, you need not enter fields using the exact start position and width shown below. Just be sure that the fields are in the correct order and that their width does not exceed the width shown below.

Required Field?	Field Name	Data Type	Start	Width
	Vendor	String	1	10
	InvoiceDate	Date/Time	11	8
	Invoice	String	19	20
	TransDate	Date/Time	39	8
	LiabilityCode	String	47	10
	BankCode	String	57	10
	PayTerms	String	67	10
	PayDate	Date/Time	77	8
	AddressCode	String	85	10
*	Project	String	95	12
*	Task	String	107	5
	Account	String	112	10
	Amount	Double	122	12
	DetailDescription	String	134	40
	SuppressBill	String	170	1
	TaxCode	String	171	3
	TaxAmount	Double	174	12
*Follow the standard rules for Advantage data entry in deciding what project or task to enter.  Remember that you must enter a task number if the project has tasks.

  Related Topics


Loading Files

When you load a file, Advantage:

•	Deletes the original file.

•	Brings the file into the Import utility.

•	Validates the file, checking it for errors.

•	Displays a count of the number of rows imported (labeled Input Rows).

To load a file into the Data Import utility:

1.	Access the Data Import utility.

2.	Select a table type (e.g., timesheets) on the Data Import Utility form.

The form lists all data types to which you have security access.

3.	Click on the Load Text File button.

Advantage displays the Load Text File form, shown below. Use this form to select a file to import and to tell Advantage about the file.

4.	Enter a file name in the Text File to load field at the bottom of the form, or click on the Browse button to locate the file.

5.	Select Delimited if your file has delimiters separating data fields, or select Fixed if it has fields of a fixed length.

6.	If the file has delimiters, select the delimiter you used from the Field Delimiter drop-down list.

7.	If your file contains text qualifiers, select the qualifier you used from the Text Qualifier drop-down list.

8.	Select the appropriate date handling options.

9.	Enter the character you used to represent the decimal point (normally, a period).

10.	Click on the Load button to import the file into the Data Import utility.

When the file is loaded, Advantage redisplays the Data Import Utility form. In the Input Rows column, Advantage shows you how many rows of data were loaded.

11.	You can now correct any errors in the table, then import the corrected table into Advantage data entry.

Options are Saved

Advantage automatically saves the settings you chose on this form so they can be applied the next time you load a file of the same data type. You can, of course, change the saved settings.

Resetting Defaults

To return all settings to their default values, use the Reset Import Text File Specs utility. The defaults for all data types are: delimited using tabs; no text qualifier.


Viewing Tables

You can review and modify the contents of a table at any time after you load it and before you import it into Advantage.

To view a table:

1.	Access the Data Import Utility form.

2.	Click on the table description (e.g., Timesheet Data Entry).

3.	Click on the View Input Table(s) button.

Advantage displays the contents of the table, as shown in the example below.

4.	Click on and drag the dividers between columns to make them as wide as needed.

5.	(Optional) Enter changes directly onto the form.

6.	Click on the Close button, [image], to return to the Data Import Utility form.

Input Key

To help keep track of the rows in a table, Advantage automatically generates an identification number for each row. These numbers appear in the Input Key column of each table.

Display Tables with Rows Only

If you select Display Tables with Rows Only on the Data Import Utility form, the form lists only those data types that have tables loaded.

Refresh List button

Click on the Refresh List button at any point to have Advantage recount the number of input rows and error rows. This function does not revalidate your data.


Validating Tables

Before you import a table into Advantage, its contents must be validated. During validation, Advantage compares the project numbers, employee numbers, accounts, etc., in the table to the setup information previously entered into Advantage. Then Advantage tells you what data must be changed before a row can be imported. Advantage imports only those rows that are error-free.

When you load a file, Advantage automatically validates it. You can revalidate a file at any time, to see the results of changes you have made.

What Does Advantage Check?

Advantage checks for the same errors here as it does when you enter transactions through data entry.

Profit Center Issues

If your firm uses the Profit Center Reporting feature, you know that certain rules apply to your data entry transactions. These rules ensure that Advantage knows the profit center that is associated with each transaction. For example, you must enter a project number as part of any transaction affecting an Income Statement account. These same rules apply to files that you import through the Data Import utility.

To validate a table:

1.	Access the Data Import Utility form.

2.	Click on the table description (for example, Unit Data Entry).

3.	Click on the Validate Table(s) button.

Advantage checks the file for errors. In the Error Rows column, Advantage calculates and displays the number of rows of data that contain errors.

4.	Refer to Correcting Errors for help fixing any problems.


Correcting Errors

If you load a file that contains errors, Advantage stores a list of the invalid transactions in an error table, along with a message describing each error. Advantage displays the total number of invalid transactions in the Error Rows column on the Data Import Utility form, shown below.

To correct the errors:

•	Review the error table.

•	Make corrections on the input table.

•	Revalidate the input table.


Importing the Corrected Table

Once you have reviewed and corrected a table, you can import the table into Advantage and post it to your database.

When you import a table, Advantage revalidates your data and imports only those rows that contain no errors. Any invalid rows remain behind.

To import a corrected table:

1.	Access the Data Import Utility form.

2.	Click on the table description (e.g., Timesheet Data Entry).

3.	Click on the Import to Advantage button.

Advantage displays the Import Batch form, shown below.

4.	Review and, if necessary, change the information on the form.

By default, the batch name is the current date and time. You can enter a different batch name if you wish, provided that it does not duplicate any existing batch names.

You can also create a recurring data entry file using the transactions in the input table. For more information about recurring data entry files, see Setting Up a Recurring Data Entry File.

5.	Click on the OK button to import the table.

Advantage imports the table and deletes the original version, so that you will not import it again.

6.	The table now exists as a batch file, ready for posting. When you are ready to use it, go to the Advantage main menu and select Processing, Transaction Posting. Then select the appropriate data type.


How Tables Appear on the Transaction Posting Form

When you are ready to post an imported table, select Processing, Transaction Posting from the Advantage main menu. Then select the appropriate data type. You will find the table listed on the Transaction Posting form with any other data entry tables of that data type, as shown in the example below.

When you look for the table on the Transaction Posting form, you will find it listed with the following information:

Column	Information
File	The date and time that the table was imported into Advantage.
Creator	Import Module
End date	The ending date of the processing period that you selected when you logged into the Data Import utility.
Total(s)	The total(s) for the table, as calculated automatically by Advantage.


Exiting from the Data Import Utility

To exit from the utility:

1.	Click on the Close button on the Data Import Utility form.

2.	Click on the [image] button.


Browsing for a File

To browse for a file:

1.	Click on the Browse button.

2.	Click on the drop-down arrow in the Look in field of the Open dialog box.

3.	Navigate to the directory where the file resides.

4.	Locate and highlight the file.

5.	Click on the Open button. The path for the file appears in the Text File to load field.


Entering Date Format Information

Enter the following date format information on the Load File form:

Field	Description
Order	Specify the order in which month, day, and year appear. The drop-down list displays valid options.
Delimiter	Specify the delimiter that separates month, day, and year. You can use any character, but most people use / or -.
Leading Zeros	Select this option if one-digit months and days have leading zeros (for example, if June 1 is written as 06/04).
Four Digit Years	Select this option if years are written using four digits (for example, 2004 instead of 04).


Resetting Defaults

During setup of a data entry text file, you specify settings in the Load Text File dialog. Whenever a data entry field changes;  for example, when a date field changes to a four-digit year, you must reset the defaults to ensure that the table captures all data in the changed field.

To return the Load Text File forms for all data types to their default values:

1.	Select Utilities from the main menu.

2.	Select Reset Import Text File Specs.

The hourglass symbol appears for a few seconds as Advantage resets the options. The defaults for all data types are: delimited using tabs; no text qualifier.

Deltek also recommends resetting defaults anytime you move to a new version of Advantage, especially if the new version involves database changes.


Text Qualifiers

In text files whose layout is Delimited, text qualifiers serve to distinguish fields containing a text message, such as a comment on a timesheet, from fields containing numeric data. The Text Qualifier drop-down list appears in the Load Text File dialog.

You can choose no text qualifiers, single quotation marks, or double quotation marks. For example, all of these text fields are valid:

comment

'comment'

"comment"


Delimiters

Delimiters are characters used to separate the data fields in a row, when the text file layout is Delimited. Valid delimiters are:

tab

@	("at" sign)

#	(pound sign)

$	(dollar sign)

^	(caret)

|	(vertical line)

;	(semicolon)


Displaying the Error Table

If table validation results indicate errors in a data entry import table, you can display the error table to locate them.

To display the error table:

1.	Access the Data Import Utility form.

2.	Click on the table’s description (for example, Timesheet Data Entry).

3.	Click on the View Error Table(s) button to see all of the rows that contain errors.

In the Error Message column, Advantage provides a brief explanation of the error in that row. If more than one error exists per column, Advantage provides an explanation for only the first error that it encounters. When you correct that error and validate the table again, Advantage displays an explanation for the next error.

You cannot correct errors in the error table. You must display the input table and make corrections there.

4.	Click on the Close button, [image], to return to the Data Import Utility form.


Correcting the input table

To correct the input table:

1.	Access the Data Import Utility form.

2.	Click on the table description (for example, Timesheet Data Entry).

3.	Make corrections directly into the rows that contain errors.

4.	Click on the Close button, [image], to return to the Data Import Utility form.


A/P Disbursements Data Entry Table Format

Refer to the table below when you prepare a text file containing accounts payable disbursements.

If you use delimiters, you need not enter fields using the exact start position and width shown below. Just be sure that the fields are in the correct order and that their width does not exceed the width shown below

Required Field?	Field Name	Data Type	Start 	Width

Vendor	String	1	10

InvoiceDate	Date/Time	11	8

Invoice	String	19	20

TransDate	Date/Time	39	8

CheckNo	String	47	7

BankCode	String	54	10

PayTerms	String	64	10

PayDate	Date/Time	74	8

AddressCode	String	82	10

*	Project	String	92	12

*	Task	String	104	5

Account	String	109	10

Amount	Double	119	8

DetailDescription	String	127	40

SuppressBill	String	167	1

NetAmount	Double	168	8

TaxCode	String	176	3

TaxAmount	Double	179	8

ProjType	String	187	1

TaxType	String	188	1

*Note that you do not need to enter Project Type or Task Type when importing Cash Disbursements. Advantage automatically inserts these fields based on the fields in the PR table.

  Related Topics


Invoice Data Entry Table Format

Refer to the table below when you prepare a text file containing invoices.

If you use delimiters, you need not enter fields using the exact start position and width shown below. Just be sure that the fields are in the correct order and that their width does not exceed the width shown below.

Required Field?	Field Name	Data Type	Start 	Width

X	Invoice	String	1	7

X	TransDate	Date/Time	8	8

X*	Project	String	16	12

X*	Task	String	28	5

X	TaskType	String	33	1

TransComment	String	34	40

InvoiceSection	String	74	1

X	Account	String	75	10

AccountName	String	85	40

Amount	Double	135	8

RetAmount	Double	143	8

TaxCode	String	151	3

TaxBasis	Double	154	8

Retainer	Yes/No	162	1

*Follow the standard rules for Advantage data entry in deciding what project or task to enter.  Remember that you must enter a task number if the project has tasks.

  Related Topics


Cash Receipt Data Entry Table Format

Refer to the table below when you prepare a text file containing cash receipts.

If you use delimiters, you need not enter fields using the exact start position and width shown below. Just be sure that the fields are in the correct order and that their width does not exceed the width shown below

Required Field?	Field Name	Data Type	Start 	Width

X	RefNo	String	1	7

X	TransDate	Date/Time	8	8

X	BankCode	String	16	10

TransComment	String	26	40

Description	String	66	40

X*	Project	String	106	12

X*	Task	String	118	5

X	Invoice	String	123	7

X	Account	String	130	10

Amount	Double	140	8

Interest	Double	148	8

TaxCode	String	156	3

TaxBasis	Double	159	8

Retainer	Yes/No	167	1

*Follow the standard rules for Advantage data entry in deciding what project or task to enter.  Remember that you must enter a task number if the project has tasks.

  Related Topics


Overview of the Interface to Microsoft Project 98

The Interface to Microsoft Project lets you create labor budgets in Microsoft Project 98, modify them, and then import the data to the Project Budget Worksheet in Advantage.

The Interface to Microsoft Project offers you an easy way to collect labor budget data from your project managers and import it into Advantage for processing.

You can run this data import utility in either Access 2002 or Access 2003.

For assistance with this utility, please contact Deltek’s Advantage Data Migration Group.

  Notes

  Related Topics


System Requirements for the Interface to Microsoft Project 98

Please review these system requirements before using the Interface to Microsoft Project utility:

•	Microsoft Project 98 must be installed. The utility does not work with any other version of Microsoft Project.

•	Advantage 8.0 must be installed.

•	You must be running Advantage on a Jet or SQL Server database.

  Related Topics


Basic Steps for Importing Data from Microsoft Project 98

To import data from Microsoft Project 98:

1.	In Microsoft Project, save your Microsoft Project file as a Microsoft Access 2002 Database file (.mdb).

2.	Exit Microsoft Project 98.

3.	From the Start menu, select Programs, Deltek Advantage 8.0, Import Utility, Advantage opens the Import Utility.

4.	Select Utilities, Microsoft Project Interface from the main menu. Advantage displays the Microsoft Project Interface form.

5.	Click on the Browse button and locate the Microsoft Project file that you want to import into Advantage. Select the Microsoft Project file that you saved as a Microsoft Access Database file (*.mdb).

6.	Map your project, task (if any), and labor code from Microsoft Project to Advantage by entering the Outline Level and appropriate Text Field for each field.

7.	Map any additional fields from Microsoft Project to Advantage:

Pct compl.	Start date
Rate	End date
Budgeted hours	ETC hours
Budgeted amount	ETC amount
Budgeted billing amount
8.	Click on the Preview button to display the data that will be imported from Microsoft Project to Advantage.

9.	Click on the Validate button to ensure that the data that will be imported to Advantage is in the appropriate format.

10.	Click on the Import button to move the data from the Microsoft Project database file (*.mdb) to your Advantage database.

11.	Click on the Close button to exit the Microsoft Project Interface form.

12.	Select File, Exit to close the Import Utility.

13.	Open Advantage and view your imported data.

  Related Topics


Security Considerations

If both these conditions are met, the user has full access to the Interface to Microsoft Project utility:

•	Advantage 8.0 must be installed on the user’s workstation.

•	The user’s security workgroup must have Budgets or Full access to the Project Budget Worksheet (as defined on the Privileges tab of Security Setup Maintenance).

  Related Topics


Accessing the Interface to Microsoft Project

To access the Interface to Microsoft Project Utility:

1.	From the Start menu, select Programs, Deltek Advantage 8.0, Import Utility. Advantage opens the Data Import Utility.

2.	Select Utilities, Microsoft Project Interface from the main menu. Advantage displays the Microsoft Project Interface form.


Required Format for the Microsoft Project File

To import Microsoft Project data into Advantage, you must be sure that the Microsoft Project file meets these requirements:

•	You must have a project, task (if your database is configured to use tasks), and labor code level in your Microsoft Project file. Without these components, data cannot be imported into Advantage.

•	You can have any desired name for your projects, tasks (if any), and labor codes in Microsoft Project. You map Microsoft Project Field Names, not Titles, to Advantage.

•	Although you can use any desired names for your projects, tasks (if any), and labor codes, each must be associated with a text field that contains the actual project, task (if any), or labor code value that matches data in your Advantage database.

•	Microsoft Project project number lengths must match the Advantage project number length. Enter leading/trailing zeros in the Microsoft Project file as needed. Do not use delimiters.

•	Projects, task (if any), and labor code levels must be placed together in the Microsoft Project file.

•	Project, task (if any), and labor code outline levels must be either equal or sequential values.

•	You can have outline levels defined above and below your project, task (if any), and labor code levels.

•	Your project, task (if any), and labor code outline levels must be consistent throughout the Microsoft Project file (i.e., you cannot define the project, task  (if any), and labor code on one line for one project and on separate outline levels for a different project.

•	Your project, task (if any), and labor code levels can be in any one of four arrangements. Click here for examples of each arrangement.


Microsoft Project File Format Examples

These examples illustrate the four permissible arrangements of project, task (if any), and labor code fields in the Microsoft Project file. Although outline levels 1, 2, and 3 are used in these examples, your levels can be at any outline level, as long as they are sequential (e.g. 6, 7, and 8) in the Microsoft Project file.

Format 1 – Project, Task, and Labor Code on different outline levels.

In this example the Project, Task, and Labor Code fields are each on different outline levels. This is a step-down approach. This method distinguishes between each level and lets you have summary level information in your Microsoft Project file.

Format 2 – Project, Task, and Labor Code on the same outline level.

In this example the Project, Task, and Labor Code fields are all on the same outline level. This method allows full detail for each project, task, and labor code combination in your Microsoft Project file.

Format 3 – Project on one outline level, Task and Labor Code on a different outline level.

In this example the Project field is on one outline level while the Task and Labor Code fields are on a different outline level. This is a variety of the step-down approach in Format 1. This method distinguishes between the project and task/labor code detail and allows for summary level information in your Microsoft Project file.

Format 4 – Project and Task on one outline level, Labor Code on a different outline level.

In this example the Project and Task fields are on one outline level while the Labor Code field is all on a different outline level. This is a variation of Format 1. This method distinguishes between the project/task and labor code detail and allows for summary level information in your Microsoft Project file.


Data that can be Imported into Advantage

The following table lists the types of data that you can import from Microsoft Project to Advantage, along with a brief description of the field. Use this list to determine whether the data you have in Microsoft Project 98 can be imported into Advantage.

These fields are listed on the Microsoft Project Interface form and map directly to each labor line in the Project Budget Worksheet. For more information, see the topic titled Preparing to Import Data.

Field	Description
Project	The project number. To import data from Microsoft Project to Advantage, you must have a Project field mapped.
Task	The task numbers (if any). If your project has tasks in Advantage, you must have a Task field in Microsoft Project.
Labor Code	The labor code. To import data from Microsoft Project to Advantage, you must have a Labor Code field mapped.
Pct Compl.	The reported labor percent complete for this project/task/labor code combination.
Rate	The budgeted hourly cost rate for this project/task/labor code combination.
Budgeted hours	The budgeted hours for this project/task/labor code combination.
Budgeted amount	The budgeted cost amount for this project/task/labor code combination.
Budgeted billing amount	The budgeted billing amount for this project/task/labor code combination.
Start date	The start date for the labor budget.
End date	The end date for the labor budget.
ETC hours	The estimated number of hours required to complete this portion of the project.
ETC amount	The estimated number of dollars required to complete this portion of the project.


Exporting Data from Microsoft Project

The export process simply converts the format of your Microsoft Project 98 file to a Microsoft Access 2002/2003 database file format. This is an important step because the import utility can only read data from a Microsoft Access Database file. Once your Microsoft Project file is in the proper format, it can be imported into Advantage for processing.

 Warning

Prior to exporting data from Microsoft Project you should save your Microsoft Project file (*.mpp) as a *.mpp file and as a *.mdb file.

If you do not save your Microsoft Project file as a *.mpp file, your changes will be reflected only in the Microsoft Access Database file. You will not be able to return to your updated Microsoft Project file.

To export data from Microsoft Project 98:

1.	Open Microsoft Project 98.

2.	Access the Microsoft Project file that contains the data to export.

3.	Select File, Save As from the Microsoft Project main menu. The system displays the File Save form.

4.	Click on the drop down arrow in the Save as type field.

5.	Select the Microsoft Access 2002/2003 Database (*.mdb) option. When you do this, the system changes the file extension in the File name field to *.mdb.

6.	Click on the Save button.

If this is the first time you are exporting data for this Microsoft Project file, Advantage displays the Export Format dialog box. You should skip the next step and proceed to step 8.

If you have saved this file before under the same name, Advantage displays a message indicating that this file already exists.

7.	Click on the Append button to add the project data to the existing file (replacing any project data that exists in both files). Otherwise click on the Overwrite button to delete the information in the existing file and replace it with the information in the new file.

8.	Accept the default options by clicking on the Save button on the Export Format form. Advantage exports the data to a Microsoft Access Database file.

9.	Select File, Exit from the main menu to exit the Microsoft Project application.


Considerations for Exporting Files from Microsoft Project

•	Your Microsoft Project file is exported into a Microsoft Access 2002/2003 database (*.mdb extension).

•	You can append or overwrite any existing database.

If you append data, Advantage overwrites with the new data any project data that exists in both files. If there is a new project in the file you are exporting, that project is added to the information in the file. All other projects are left as is.

If you override data, the system deletes all project data in the old database file and replaces it with the data from the new file.

•	Your firm must choose a method for gathering files and importing them into Advantage (e.g., one pool of all budgets, one pool per period, etc.).


Preparing to Import Data into Advantage

Before importing data from Microsoft Project to Advantage, you must perform the following steps:

1.	Access the Microsoft Project Interface form.

2.	Locate the Microsoft Project database file.

3.	Enter the required and optional information on the Microsoft Project Interface form.

4.	Preview the data to import into Advantage.

5.	Validate the data.

6.	Correct the data as needed.

Once these steps have been completed, you can import the data from the Microsoft Project database file into Advantage. For complete instructions, see the topic titled Importing Data into Advantage.


Required Fields - Interface to Microsoft Project

The following table lists the required fields for the Interface to Microsoft Project. Data must be entered in these fields before Microsoft Project data can be exported to Advantage.

Field	Description
Project Outline Level	The outline level, as specified in Microsoft Project, on which your project number has been defined.
Project Text Field	The name of the text field in Microsoft Project that contains the project number. The project number in Microsoft Project must be between three and 12 characters and match the project number format specified in Advantage.
Task Outline Level	The outline level, as specified in Microsoft Project, on which your task number has been defined. If your project has no tasks, you can select No tasks used on this form, to avoid having to enter a blank task mapping column in Microsoft Project.
Task Text Field	The name of the text field in Microsoft Project that contains the task number. The task number in Microsoft Project must one to five characters long and must match the task number format specified in Advantage. If your project has no tasks, you can select No tasks used on this form, to avoid having to enter a blank task mapping column in Microsoft Project.
Labor Code Outline Level	The outline level, as specified in Microsoft Project, on which your labor code has been defined.
Labor Code Text Field	The name of the text field in Microsoft Project that contains the labor code. The labor code in Microsoft Project must be between one and five characters and match the labor code format specified in Advantage.


Optional Fields - Interface to Microsoft Project

These optional fields are displayed in the Modify Labor Budgets window of the Project Budget Worksheet in Advantage. Although they need not be included in your Microsoft Project Budget, including them and exporting them to Advantage makes budgeting in Advantage much easier.

Pct Compl.

Rate

Budgeted Hours

Budgeted Amount

Budgeted Billing Amount

Start Date

End Date

ETC Hours

ETC Amount


Previewing Data in the Microsoft Access Database File

To preview Microsoft Project data exported to the Microsoft Access database file:

1.	Ensure that you are Prepared to Import Data.

2.	Click on the Preview button to view the data to be imported into Advantage. Advantage opens a Microsoft Access Datasheet form, displaying the data to be imported into Advantage.

3.	Close the Microsoft Access Datasheet form when you are through previewing data.

  Related Topics


Validating Data and Viewing Errors Generated by the Import Utility

To validate the data from Microsoft Project and view any errors generated:

1.	Ensure that you are Prepared to Import Data.

2.	Click on the Preview button to view the data to be imported into Advantage.

3.	Click on the Validate button to generate a list of errors contained in the Microsoft Project file, if any.

There are several possible reasons for errors generated by the utility:

•	The project number in Microsoft Project does not match a project number in Advantage.

•	The task number in Microsoft Project does not match a task number in Advantage.

•	The labor code in Microsoft Project does not match a labor code in Advantage.

•	Data entered in one of the fields is not in the correct format.

  Notes

  Related Topics


Correcting Errors

You must correct any errors generated during the validation step prior to importing data. All corrections must be made in the Microsoft Project file.

To correct errors:

1.	Ensure that you are Prepared to Import Data

2.	Click on the Validate button to retrieve a list of errors generated by the validation process. The error list appears in a Microsoft Access datasheet. The datasheet lists the project, task, and labor code line that contains the error.

3.	Open Microsoft Project and access the Microsoft Project file containing the error(s).

4.	In the file, review the line containing the error. Ensure that the project, task, and labor code match an Advantage project, task, and labor code. Also ensure that numeric fields contain numeric values and that text fields contain the appropriate text values.

5.	Export the data from Microsoft Project again.

6.	Click on the Validate button again and ensure that no errors remain uncorrected.

  Related Topics


Importing Data into Advantage

To import data from Microsoft Project 98 into Advantage:

1.	From the Start menu, select Programs, Advantage 8.0, Import Utility. Advantage opens the Import Utility.

2.	Select Utilities, Microsoft Project Interface from the main menu. The system displays the Microsoft Project Interface form.

3.	Enter the file from which to import data in the Microsoft Project Database File field.

4.	Enter the Outline Levels and Text Fields for your Project, Task (if any), and Labor Code fields from Microsoft Project. These are required fields and must be filled in prior to exporting data.

5.	Enter any additional optional fields to export from Microsoft Project.

6.	Click on the Preview button. The system opens a Microsoft Access datasheet in read-only format.

7.	Review the information that has been imported from Microsoft Project, ensuring that all data that should be imported is appearing in the file.

8.	Click on the Validate button. The system opens a Microsoft Access datasheet in read-only format.

9.	Fix any errors identified by the import program.

10.	Click on the Validate button to ensure that all errors are fixed.

11.	Click on the Import button. The system imports all valid data into the Project Budget Worksheet.

  Notes

  Related Topics


Exiting the Microsoft Project Interface

To exit from the Microsoft Project Interface:

1.	Click on the Close button at the bottom of the Microsoft Project Interface form.

2.	Select File, Exit from the Advantage main menu. Advantage closes the Import Utility.

  Related Topics


Microsoft Project Database File

Enter the name and path of the Microsoft Project database file. Advantage displays the last file specified in this application.

To browse for the file:

1.	Click on the Browse button from the main Microsoft Project Interface form. Advantage displays the Open dialog.

2.	Using the Look in field, locate the directory where you saved the Microsoft Project database file.

3.	Select the Microsoft Project database file.

4.	Click on the Open button. Advantage displays the name and path of the file in the Microsoft Project Database File field.


Outline Levels

The outline level in Microsoft Project is the number of levels a component is indented from the top level of the outline.

You must provide the export program with the outline levels for your project, task (if any), and labor code columns.

To determine the outline level:

1.	Open Microsoft Project 98.

2.	Access the Microsoft Project file that you are exporting.

3.	Select Insert, Column from the Microsoft Project main menu. The system displays the Column Definition form.

4.	Click on the drop-down arrow in the Field name field.

5.	Select the Outline Level option.

6.	Click OK. Advantage inserts a new column displaying the outline level for each Microsoft Project line.

7.	Use this number to enter the Outline Levels for your project, task (if any), and labor code on the Microsoft Project Interface form.


Notes on the Interface to MS Project 98

•	The Microsoft Project Interface utility imports only rows that are valid. You must correct any errors prior to importing data to Advantage.

•	Data matching the Project, Task (if any), and Labor Code combination are replaced, not appended, in the Project Budget Worksheet.

•	The interface imports only labor budgets.

•	You cannot import data from Advantage to Microsoft Project 98. Budget data must come from Microsoft Project.

•	Once the data has been imported into Advantage, you can access it as data entered through Advantage. There are no restrictions on imported data.


Notes on Validating Data and Viewing Errors

•	The validation process does not validate optional fields such as Pct Compl and Rate. If you map one of these fields to a field with a different data type; for example, if you map the Pct Compl field to the Expected Start Date field, the field is imported as zero or blank.

•	The Microsoft Project Interface utility imports only valid rows. You should correct any errors prior to continuing. Otherwise, rows in error are not imported.


Overview of the Interface to ET/Deltek Time Collection

The interface to Deltek Time Collection, formerly called ET, allows you to share information between Advantage and Time Collection:

•	You can export information about projects, labor codes, employees, and charge groups from Advantage to Time Collection.

•	You can import information about labor hours from Time Collection to Advantage.

   Notes

What do you want to do?

Activate the feature.

Set up file and server locations on the General Tab

Map fields on the Employee Tab

Map fields on the Leave Plans Tab

Create charge folders on the Charge Folders Tab

Segment labor codes on the Labor Codes Tab

Export data to ET/Time Collection.

Import timesheet information to Advantage.


Activating the Feature

You must have a SQL Server database to use this interface.

To activate the feature:

SQL Server Logins (Perform in SQL Server Enterprise Manager)

1.	Create an Advantage Login ID with db_owner privilege to the Advantage and ET Databases. If the Advantage login already exists, ensure that it has db_owner privilege to both databases.  (Another login name will also work, but requires db_owner privilege in both databases).

2.	Create a DeltekET Login ID with db_owner privilege to the Advantage and ET Databases. If the DeltekET login already exists, ensure that it has db_owner privilege to both databases. (Another login name will also work, but requires db_owner privilege in both databases)

Configure the ET and Advantage SQL Server Databases for the Import

These scripts can be found in the Advantage Install folder (by default, installed at c:\Program Files\Deltek\Advantage\8.0), or downloaded from the Deltek website and run in Query Analyzer against the respective database. (For Version 3.0, the script names end in 30.sql.)

Advantage Database - Run the following queries in the order in which they appear.

1.	ETInsert.SQL - (Population scripts for ET Interface tables with default values.

If the Import has ever worked in the past, DO NOT RUN this script, but run the remaining scripts and see Exporting Data From Advantage.

2.	s_ETADVExchange31.SQL - (Stored Procedures for Advantage to ET Export)

3.	s_ETADVLaborImport31.sql - (Stored Procedures for ET Import into Advantage)

4.	s_FormatLCString31.sql (Stored Procedures for ET Export; called by ETADVExchange31 stored Procedure)

ET Database - Run the following queries in the order in which they appear:

1.	ETADVPKCreate.SQL - (Stored Procedures for ET ADV Interface; generates primary keys for import tables if they do not exist)

2.	ETViewsCreate.sql (Creating scripts for ET interface Views)

3.	Confirm that the required staging tables exist in the ET database.  These tables are created in the ET build and patch scripts.  They are:

X_IMP_ET_UDT01

X_IMP_ET_UDT02

X_IMP_ET_UDT03

X_IMP_ET_UDT04

X_IMP_ET_UDT05

X_IMP_ET_UDT06

X_IMP_ET_UDT07

X_IMP_ET_UDT08

X_IMP_ET_UDT09

X_IMP_ET_UDT10

X_IMP_ET_EMPLL

X_IMP_ET_EMPLH

X_IMP_ET_EMPL

X_EXP_ET_LD_ADV

1.	From the Advantage Main Menu, select Utilities, Setup Table Maintenance, General System Setup.

2.	Select the Enable Deltek Time Collection interface field.


Exporting Data from Advantage

When you run the export procedure, Advantage exports project numbers, labor codes, employee numbers, and charge groups so that they can be used by ET/Deltek Time Collection. The export procedure varies slightly, depending on whether you use ET/Deltek Time Collection Version 3.0, 3.1, or 4.0.

The amount of project data exported depends on the Last Export Date. The export procedure exports only those project numbers that have been created since the Last Export Date.

Note that you can schedule exports to occur on a regular basis by entering the stored procedure ETADVExchange31 to run as a task in SQL Server Enterprise Manager. (For Version 3.0, the stored procedure is ETADVExchange30.)

To export data from Advantage to ET/Deltek Time Collection:

1.	Select Programs, Advantage, Data Import Utility from the Start menu.

2.	Select Utilities, ET 3.0/3.1/4.0 Export from the main menu. Advantage displays the ET Advantage Data Export Utility form.

3.	Perform steps 4-7 if you are using the interface for the first time, or need to change interface settings. Otherwise, skip to step 8.

4.	To link the interface to a storage directory and destination database in ET/Deltek Time Collection, select the General tab.

If you are using Version 3.0 of ET/Deltek Time Collection, use the Browse button to locate the directory to which you plan to export files.

5.	To associate Advantage and ET/Deltek Time Collection fields, select the Employee tab. Associated fields must have the same data type.

6.	To customize charge groups based on the project data, select the Charge Folders tab.

7.	To determine how you want to split a labor code and to which Electronic Timekeeper tables you want to export each of the segments, select the Labor Codes tab.

8.	To move the data from the Advantage database to files for import into your ET/Deltek Time Collection database, click on the Export to ET button.

9.	To exit the ET Advantage Data Export Utility, click on the Close button.


Setting Up File and Server Locations on the General Tab

Before you can export data, you must complete the General tab of the Data Export Utility form to link the interface to a storage directory and to the destination database in ET/Deltek Time Collection.

To complete the General tab:

1.	Select Utilities, ET 3.03.1/4.0 Export from the Data Import Utility menu. Advantage displays the ET Advantage Data Export Utility form.

2.	Complete the General tab, referring to context-sensitive help.


Mapping Fields on the Employee Tab

Use the Employee tab to map Advantage fields to ET/Deltek Time Collection tables and fields. You do this by selecting a field in the ET Column and entering the appropriate Advantage field in the Advantage Column on the same row. Note that these associated fields must have the same data type.

Required Mapping

You must map the following fields:

ET Column:	Usually Mapped to the Following Advantage Column:
Effective_dt	HireDate
TS_Schedule_cd	EmSortCode
Login_empl_id	Employee
Class_cd	This is configured in Advantage as a sort/select option in Employee Table Maintenance.

To complete the Employee tab:

1.	Select Utilities, ET 3.0/3.1/4.0 Export from the Data Import Utility menu. Advantage displays the ET Advantage Data Export Utility form.

2.	Complete the Employee tab, referring to context-sensitive help.


Mapping Fields on the Leave Plans Tab

Use the Leave Plans tab to enter sort codes from your employee tables. On this form, you determine which sort code data the system should export from Advantage to ET/Deltek Time Collection for each employee.

To complete the Leave Plans tab:

1.	Select Utilities, ET 3,0/3.1/4.0 Export from the Data Import Utility menu. Advantage displays the ET Advantage Data Export Utility form.

2.	Complete the Leave Plans tab, referring to context-sensitive help.


Creating Charge Folders on the Charge Folders Tab

Use the Charge Folders tab to customize charge groups. This customization allows you to select the project data to export to ET/Deltek Time Collection. This form includes as many columns as there are profit center levels.

To complete the Charge Folders tab:

1.	Select Utilities, ET 3.0/3.1/4.0 Export from the Data Import Utility menu. Advantage displays the ET Advantage Data Export Utility form.

2.	Complete the Charge Folders tab, referring to context-sensitive help.

  Notes


Segmenting Labor Codes on the Labor Codes Tab

In Advantage, all labor costing and budgets are based on labor codes. These labor codes classify work for a project/task in categories (levels) that you define, such as department, phase (level of progress), service, or staff level. On timesheets, labor codes are used to identify each hour of labor.

The Labor Codes tab allows you to split a labor code into its constituent segments and to export each to a different table in ET/Deltek Time Collection. This form can include up to five windows (which accommodate the maximum size of an Advantage labor code), allowing you to designate how a labor code should be split and to which ET/Deltek Time Collection table each segment should be exported.

To complete the Labor Codes tab:

1.	Select Utilities, ET 3.0/3.1/4.0 Export from the Data Import Utility menu. Advantage displays the ET Advantage Data Export Utility form.

2.	Complete the Labor Codes tab, referring to context-sensitive help.


Importing Timesheet Information into Advantage

The Interface to ET/Deltek Time Collection lets you import labor charges from the Time Collection system into Advantage. The steps you follow are different depending upon whether you are importing data from Version 3.0, 3.1, or 4.0 of ET/Deltek Time Collection:

Importing steps for Version 3.0.

Importing steps for Versions 3.1 and 4.0


Importing Timesheet Information from ET/Deltek Time Collection 3.0

To import labor charges:

1.	Select Utilities, ET 3.0 Labor Import from the Data Import Utility menu. Advantage displays the ET Advantage Labor Import Utility form.

2.	Complete the fields on this form, referring to context-sensitive help.

3.	If you are ready to import data from ET, click on the Process Labor button. This step brings all valid timesheet lines into the data entry file that you named in Step 2.

4.	If you receive an error message, some of the timesheet lines that you attempted to import are invalid. Click on the View Errors button to see the invalid lines, then correct your errors in either Advantage or ET.

5.	To exit the Data Export utility, click on the Close button.

 Notes


Importing Timesheet Information from ET/Deltek Time Collection 3.1/4.0

To import labor charges:

1.	Select Utilities, ET 3.1 Labor Import from the Data Import Utility menu. Advantage displays the ET Advantage Labor Import Utility form.

2.	Complete the fields on this form, referring to context-sensitive help.

3.	If you are ready to import data from ET, click on the Load Labor button. This step brings the timesheet information into a staging table, where you can validate its accuracy.

4.	Click on the Validate button The number of valid timesheet lines appears in the Validated field. The number of timesheet lines with errors appears in the Errors field.

5.	If you receive an error message, click on the View Errors button, then correct your errors in Advantage, ET, or from within the import utility.

6.	Click on the Validate button again to revalidate the corrected timesheet lines.

7.	Click on the Import to Advantage button when you are ready to create a data entry file that contains the imported timesheet lines.

8.	To exit the Data Export Utility, click on the Close button.

  Notes


Using Context-Sensitive Help

Display the form as you would normally, then choose the appropriate level of help:

Help for the entire form

Click on the [image] key, then click anywhere on the background of the form.

Help for a single field

Click on the [image] key, then click in the field. Or, click in the field, then press the F1 key.

To print the help text

Click on the Print button, if one is available. Or right-click and select the Print Topic option from the menu.


Path for Export Files

Enter the location of the folder in which you want to store the data text files and BCP scripts. From this location you will export the files to ET/Deltek Time Collection. If you are unsure of the path to the desired folder, click on the Browse button to find the exact location.


Download Employee Social Security Numbers option

A social security number is required in ET/Deltek Time Collection. If you do not want to export employee social security numbers, select this option and the interface will substitute all zeroes for employee social security numbers during the export.


Advantage Column

Each row in the Mapping Advantage Fields to ET section includes fields for an ET table, ET column, data type, and Advantage Column. The purpose of this form is to associate Advantage fields with ET fields so that exported data is processed accurately.

Required Mapping

You must map the following fields:

ET Column:	Usually Mapped to the Following Advantage Column:
Effective_dt	HireDate
TS_Schedule_cd	EmSortCode
Login_empl_id	Employee
Class_CD	ETClass


Track Employee Leave Option

Select this option if you want the interface to track the leave time that corresponds to the selected sort codes for each employee. You create these sort codes in Sort/Select Codes Setup Maintenance in Advantage.

Advantage will export leave time data to ET/Deltek Time Collection.


Charge Folder Column 1

For each item listed among the selections for this column, the system stores all charges that fall into a specific category in a single folder. If, for example, you include Client Number as a selection, the system stores all projects for the same client in the same folder.

You can include as many items as you wish. However, be aware that certain charges could very well appear in more than one folder. If, for example, Client Number, Overhead Project, and Regular Project appear as selections on your Charge Folders list, overhead and regular projects appear in more than one folder because they also fall into the category of client number charges. This repetition does not cause a problem when you export your data, but the system does take longer to complete this function.


Subsequent Columns

Subsequent columns on the Charge Folders form indicate how your firm's profit centers are divided. In each field, you can associate the charge information contained in the specific folder with the appropriate section of the profit center. You can, for example, link the Office section of the profit center for Column 2 with the Overhead Projects charge folder.

Note that you can leave these fields blank.


ET on Different Server

Select this option if you are running ET/Deltek Time Collection on a different server than Advantage.


ET Server

Enter the name of the destination server for ET/Deltek Time Collection.


Name of ET Database

Enter the name of the ET/Deltek Time Collection database to which you want to export the Advantage information.


Owner of ET Database

Enter the login that was created for the ET/Deltek Time Collection database. See Activating the Feature.


Leave Plan Name

Enter a descriptive name that indicates the type of leave time recorded. This field is optional.


Project Column

Select the name of a text sort field in Advantage. The drop-down menu includes all text sort fields in use on the Employee table. The sort fields that you enter in this column should contain a preset number with delimiters, a space, and a task number (when using a task).


Effective Date Column

The interface records the balance of hours remaining for the employee for the specific leave plan. This balance accrues from the date indicated by the field entered in this column. A warning message appears in ET when you exceed the balance, based on your entry in this field.


Hours Balance Column

This field indicates the balance left for the specific leave plan as of the effective date listed in the preceding column.


Segment

In the first field of each column, you indicate into which ET/Deltek Time Collection table the segments of the Advantage labor code should be placed.

The actual segments of the labor code appear in the second field of each column. If you combine segments for export to the same ET/Deltek Time Collection table, one or more of these fields could be blank. Use the arrow buttons that appear between columns to combine or separate segments of the labor code. The interface does not allow you to combine segments that are not in sequence. You cannot, for example, combine Segments 1 and 3 without including Segment 2.


Location of ET Labor Import File

Enter the location of the ET/Deltek Time Collection folder in which the required timesheet data is stored. From this location you will import the files to Advantage. If you are unsure of the path to the desired folder, click on the Browse button to find the exact location.


File Name

Enter a name for the batched timesheet information that you want to import from ET into Advantage.


Start Date

Enter a start date for the timesheet processing period.


End Date

Enter the closing date for the timesheet processing period.


Last Export Date

The export procedure decides what project data to export based on the Last Export Date—the last time the export procedure was performed. The export procedure exports data for only those projects whose data has changed since the Last Export Date. You can, however, enter your own date in the Last Export Date field to, for example, re-export data that was somehow lost.

To support the Last Export Date feature, Advantage stores a corresponding date in Project Table Maintenance. When you begin using the Interface to ET/Deltek Time Collection, Advantage devotes the Last Modified field on the Project Table Maintenance Sort/Select tab to tracking the last date that a project’s data was changed.

To have this tracking occur:

1.	In Advantage, select Utilities, Setup Table Maintenance, Sort/Select Setup.

2.	In one of the date fields, enter <DATELASTMOD> as the Label.


ET Version

Select the Version of ET that you are currently using, either 3.0 or 3.1.


Loaded

The total number of timesheet lines imported from ET/Deltek Time collection. This includes both valid and invaild lines.


Validated

The number of imported timesheet lines that are error-free.


Errors

The number of imported timesheet lines that contain errors.


Notes about Charge Folders

•	You can customize the Charge Folder Column 1 menu to include any number of charge groups. You would generally include two or three criteria.

•	The system will not create a charge folder for any fields that are Null.


Correcting Version 3.0 Labor Import Files

You have two options for importing incorrect timesheet lines:

•	Correct timesheets in ET/Deltek Time Collection and re-export the timesheet lines.

•	Correct the timesheet lines in the Advantage data entry file created by the Import utility.


Correcting Version 3.1 Labor Import Files

You have three options for importing incorrect timesheet lines:

•	Correct timesheets in ET/Deltek Time Collection and re-export the timesheet lines.

•	Correct the timesheet lines in the Advantage data entry file created by the Import utility.

•	Correct the timesheet lines on the error table generated by the Import utility.


Notes about the Labor Import Utility

The entries in the SQL Server Connection fields default from the Export file. You can modify these entries.


Notes about the Interface to ET/Deltek Time Collection

•	This online help area describes the steps you must follow from within Advantage. To import or export data, you must also perform a separate set of steps from within ET/Deltek Time Collection.

•	You must have a SQL Server database to use this interface.

•	Before you use the interface for the first time, your IT person or database administrator must create (or modify) SQL Server logins for each database and then run a set of stored procedures. These procedures are included in the conversion directory on the Advantage CD. For more information about this setup, see Activating the Feature.


Advantage Data Dictionary Contents

The Advantage Data Dictionary lists all the tables in the Advantage database. Column names, descriptions, data type, data length, and valid options are provided for each table.

Advantage Data Dictionary

Note:

If you receive an "Action Canceled – Internet Explorer was unable to link to the Web page you requested" or "Page cannot be displayed" error when you click the above Advantage Data Dictionary link, it may be due to a Microsoft® security issue which results when running a .chm file over a network, an internal Web site, or an external Web site.

The Advantage data dictionary help is contained in the DeltekAdvantage.chm file. This file is stored in the Advantage program folder, which is normally installed on your workstation. When you open Advantage from your workstation and it accesses Advantage program files on a network or Web site rather than on your workstation, you receive an error.

To resolve this, copy the DeltekAdvantage.chm file from the Advantage program folder in the network or Web location to any location on your workstation. Then double-click the file to open the topic.

Another scenario that causes this error is when you download the DeltekAdvantage.chm file over the Internet and then try to open it. To avoid this, zip the .chm file first, download it, unzip it, and then double-click it to open it.


Overview of the Best Data Import Utility

The Best Data Import Utility automates the process of collecting data from your FAS Asset Inventory software and bringing it into Advantage for processing.

This utility allows you to take data that is in a text (.sdf) file, and use it to create a new data entry table. Once you import the data, you can check its validity and then post it as you would any other data entry batch.

You can run the Advantage 8.0 Best Data Import Utility in either Access 2002 or Access 2003.

For assistance with the Best Data Import Utility, please contact Deltek’s Data Migration Group.

What do you want to do?

Access the Best Import Data Utility

Import Your Data

Prepare Files

Format Tables

Load Files

Validate Tables

Correct File Errors

Import Tables

Exit the Best Data Import Utility

 Note


Basic Steps for Importing Data

To import a file into Advantage:

1.	Prepare the file. Make sure that it is in a format that Advantage can recognize.

2.	Load the file into the Best Data Import utility.

3.	Optional. Review the rows in the newly-created table. Each row represents a single data entry line item.

4.	Validate the rows in the table.

Advantage checks the validity of employee numbers and other codes and amounts that appear in the table.

5.	Correct any errors that Advantage finds.

6.	Import the corrected table into Advantage for posting.

7.	Repeat steps 5 and 6 until all of the data is corrected and imported.


Best Data Import Security

Advantage closely controls who can use the Best Data Import utility. Two layers of security protect the utility:

•	You must log into the utility using a valid username and password.

•	You cannot import a data type (for example, timesheets) unless you have security access to Advantage data entry for that particular data type.

Username and Password

The Best Data Import utility has its own login form, where you must enter your Username and Password.

Security Access to Data Types

When you select the Best Data Import utility from your menu of Deltek Advantage applications, you will see the Data Import form. This form lists the types of tables you can create.

For a data type to appear on the list, you must have security access to data entry for that data type. Security access is based on the settings entered on your workgroup’s Security Maintenance form.


Accessing the Data Import Utility
Accessing the Best Data Import Utility

To begin using the utility:

1.	From the Start Menu, select Programs, Deltek Advantage 8.0, Import Utility.

2.	Select a processing period from the list of valid periods.

Advantage uses the period ending date that you choose as the end date for any data entry batches that you create during this login. The period must be prior to, or the same as, the current period.

When you choose a processing period, Advantage displays a blank form with menu choices at the top.

3.	From the menu, select Utilities, Best Import Utilities, FAS Import Utility.

4.	Advantage displays the Best Data Import Utility form.

If the file that you plan to import is in a different database from the one to which you are currently linked, you can change databases by linking to a different database.


Linking to a Different Advantage Database

To change databases:

1.	Select Utilities from the menu at the top of the form.

2.	Select Database Wizard.

3.	Link to the new database.

For instructions, please see the Database Wizard book in the Table of Contents for this Advantage online help system.


Preparing Files

Text Files

To be loaded into Advantage, a file must be a text file, with the extension .sdf.

Content of Files

Advantage data entry tables contain rows of data. Each row contains a single line item. The rows are composed of data fields. The data fields are of a specific length and appear in a specific order. For example, an Advantage journal entry table contains rows of entries, each beginning with a 7-character reference number, an 8-character transaction date, and a 40-character master description.

You have the option of creating the .sdf files:

•	using fields of the exact required length, or

•	using fields that are less than or equal to the required length, separated from each other by delimiters (for example, by tabs).

You must follow these rules when you prepare a file to be imported into Advantage:

•	The file must contain rows, each row representing a transaction.

•	Fields must be in the correct order.

•	Fields must either be of the correct fixed length or be separated by valid delimiters.

•	Text fields may begin and end with valid text qualifiers.

Table Formats

See Table Formats for a description of the fields that make up the data entry table.

When You Load the File

As part of loading the file, you must tell Advantage how the file is formatted.


Loading Files

When you load a file, Advantage:

•	Deletes the original file.

•	Brings the file into the Best Import utility.

•	Validates the file, checking it for errors.

•	Displays a count of the number of rows imported (labeled Input Rows).

To load a file into the Best Data Import utility:

1.	Access the Best Data Import utility.

2.	Select a table type (for example, FAS Depreciation) on the Best Data Import Utility form. The form lists all data types to which you have security access.

3.	Click on the Load Text File button. Advantage displays the Load Text File form.

4.	Enter a file name in the Text File to load field at the bottom of the form, or click on the Browse button to locate the file.

5.	Select Delimited if your file has delimiters separating data fields, or select Fixed if it has fields of a fixed length.

6.	If the file has delimiters, select the delimiter you used from the Field Delimiter drop-down list.

7.	If your file contains text qualifiers, select the qualifier you used from the Text Qualifier drop-down list.

8.	Select the appropriate date handling options.

9.	Enter the character you used to represent the decimal point (normally, a period).

10.	Click on the Load button to import the file into the Best Data Import utility.

When the file is loaded, Advantage redisplays the Best Data Import Utility form. In the Input Rows column, Advantage shows you how many rows of data were loaded.

What’s Next

Correct any errors in the table.

Import the corrected table into Advantage data entry.

Options are Saved

Advantage automatically saves the settings you chose on this form so that they can be applied the next time you load a file of the same data type. You can change the saved settings.

Resetting Defaults

To return all settings to their default values, use the Reset Import Text File Specs utility. The defaults for all data types are: delimited using tabs; no text qualifier.


Table Formats

Refer to the following table when you prepare a text file containing FAS depreciation transactions.

If you use delimiters, you need not enter fields using the exact start position and width shown here. Just be sure that the fields are in the correct order and that their width does not exceed the width show below:

Required?	Field Name	Data Type	Start	Width
X	Account	String	1	50
X	TransDate	Date/Time	51	8
	MasterDescription	String	59	40
X	FASPeriod	Date/Time	99	8
X	RefNo	String	107	7
	DetailDescription	String	114	40
X	LinkFile	String	154	40
X	Amount	Double	194	12
X	EntryType	String	206	3
X*	Project	String	209	12
X*	Task	String	221	5
	PostType	String	226	50
	TaxCode	String	276	1
	TaxType	String	277	50
	TaxDebitAmount	Double	327	12
	TaxCreditAmount	Double	339	12

*Follow the standard rules for Advantage data entry in deciding what project or task to enter. Remember that you must enter a task number if the project has tasks.


Viewing Tables

You can review and modify the contents of a table at any time after you load it, but before you import it into Advantage.

To view a table:

1.	Access the Best Data Import Utility.

2.	Click on the table description (for example, FAS Depreciation Entry).

3.	Click on the View Input Table(s) button. Advantage displays the contents of the table.

4.	Click on and drag the dividers between columns to make them as wide as needed.

5.	Optional. Enter changes directly onto the form.

6.	Click on the Close button to return to the Best Data Import Utility form.

Input Key

To help keep track of the rows in a table, Advantage automatically generates an identification number for each row. These numbers appear in the Input Key column of each table.

Display Tables with Rows Only

If you select Display Tables with Rows Only option on the Best Data Import Utility form, the form lists only those data types that have tables loaded.

Refresh List button

Click on the Refresh List button at any point to have Advantage recount the number of input rows and error rows. This function does not revalidate your data.


Validating Tables

Before you import a table into Advantage, its contents must be validated. During validation, Advantage compares the project numbers, employee numbers, accounts, and so on in the table to the setup information previously entered into Advantage. Then Advantage tells you what data must be changed before a row can be imported. Advantage imports only error-free rows.

When you load a file, Advantage automatically validates it. You can revalidate a file at any time, to see the results of changes you have made.

What Does Advantage Check?

Advantage checks for the same errors here as it does when you enter transactions through data entry.

To validate a table:

1.	Access the Best Data Import Utility.

2.	Click on the table description (for example, FAS Depreciation Entry).

3.	Click on the Validate Table(s) button.

Advantage checks the file for errors. In the Error Rows column, Advantage calculates and displays the number of rows of data that contain errors.

What’s Next

Correct errors as needed.


Correcting Errors

If you load a file that contains errors, Advantage stores a list of the invalid transactions in an error table, along with a message describing each error. Advantage displays the total number of invalid transactions in the Error Rows column on the Best Data Import Utility form.

To correct the errors:

•	Review the error table.

•	Make corrections on the input table.

•	Revalidate the input table.


How Tables Appear on the Transaction Posting Form

When you are ready to post an imported table, select Processing, Transaction Posting from the Advantage main menu. Then select the appropriate data type. You will find the table listed on the Transaction Posting form with any other data entry tables of that data type,

When you look for the table on the Transaction Posting form, you will find it listed with the following information:

Column	Information
File	The date and time that the table was imported into Deltek Advantage.
Creator	Import application.
End date	The ending date of the processing period that you selected when you logged into the Best Data Import utility.
Total(s)	The total(s) for the table, as calculated automatically by Deltek Advantage.


Importing the Corrected Table

Once you have reviewed and corrected a table, you can import the table into Advantage and post it to your database.

When you import a table, Advantage revalidates your data and imports only those rows that contain no errors. Any invalid rows remain behind.

To import a corrected table:

1.	Access the Best Data Import Utility.

2.	Click on the table description (for example, FAS Depreciation Entry).

3.	Click on the Import to Advantage button. Advantage displays the Import Batch form.

4.	Review and, if necessary, change the information on the form.

By default, the batch name is the current date and time. You can enter a different batch name if you wish, provided that it does not duplicate any existing batch names.

5.	Click OK to import the table. Advantage imports the table and deletes the original version, so that you will not import it again.

6.	The table now exists as a batch file, ready for posting. When you are ready to use it, go to the Advantage main menu and select Processing, Transaction Posting. Then select the appropriate data type.


Exiting from the Best Import Utility

To exit from the utility:

1.	Click on the Close button on the Best Data Import Utility form.

2.	Click on the Exit button.


Browsing for a File

To browse for a file:

1.	Click on the Browse button.

2.	Click on the drop-down arrow in the Look in field of the Open dialog box.

3.	Navigate to the directory where the file resides.

4.	Locate and highlight the file.

5.	Click on the Open button. The path for the file appears in the Text File to load field.


Entering Date Format Information

Enter the following date format information on the Load File form.

Field	Description
Order	Specify the order in which month, day, and year appear. The drop-down list displays valid options.
Delimiter	Specify the delimiter that separates month, day, and year. You can use any character, but most people use / or -.
Leading Zeros	Select this option if one-digit months and days have leading zeros (for example, if June 1 is written as 06/04).
Four Digit Years	Select this option if years are written using four digits (for example, 2004 instead of 04).


Resetting Defaults

To return the Load Text File forms for all data types to their default values:

1.	Select Utilities from the main menu.

2.	Select Reset Import Text File Specs.

The hourglass symbol appears for a few seconds as Advantage resets the options. The defaults for all data types are: delimited using tabs; no text qualifier.


Text Qualifiers

In text files whose layout is Delimited, text qualifiers serve to distinguish fields containing a text message, such as a comment on a timesheet, from fields containing numeric data.

You can use no text qualifiers, single quotation marks, or double quotation marks. For example, all of these text fields are valid:

comment

'comment'

"comment"


Delimiters

Delimiters are characters used to separate the data fields in a row, when the text file layout is Delimited. Valid delimiters are:

tab

@	("at" sign)

#	(pound sign)

$	(dollar sign)

^	(caret)

|	(vertical line)

;	(semicolon)


 Displaying the error table

If table validation results indicate errors in a data entry import table, you can display the error table to locate them.

To display the error table:

1.	Access the Best Data Import utility.

2.	Click on the table description (for example, FAS Depreciation Entry).

3.	Click on the View Error Table(s) button to see all of the rows that contain errors.

In the Error Message column, Advantage provides a brief explanation of the error in that row. If more than one error exists per column, Advantage provides an explanation for only the first error that it encounters. When you correct that error and validate the table again,  Advantage displays an explanation for the next error.

You cannot correct errors in the error table. You must display the input table and make corrections there.

4.	Click on the Close button to return to the Best Data Import Utility form.


Correcting the input table

To correct the input table:

1.	Access the Best Data Import utility.

2.	Click on the table description (for example, FAS Depreciation Entry).

3.	Make corrections directly into the rows that contain errors.

4.	Click on the Close button to return to the Best Data Import Utility form.


Note about Best Import Utility

If your firm uses Profit Center Reporting and allows for separate balance sheets, you must export your information from FAS by Profit Center. You will then be prompted for a Project number and Task number (if required) so Advantage will be able to determine which Profit Center should be affected.


Tip on Table Formats

As you work with the data import utility, you may wish to refer to these table formats without opening online help. If so, select the Fixed fields option on the Data Import form. Advantage displays the table format for the data type on which you are working.


Text Fields

The Text1-30 fields in Microsoft Project store custom text information you want to enter. You must define your project, task (if any), and labor code fields as Text fields in Microsoft Project. Otherwise, they will not be validated by the import process.

To determine the name of the text field for your project, task (if any), and labor code:

1.	Open Microsoft Project 98.

2.	Access the Microsoft Project file that you are importing.

3.	Double-click on the Column Heading of the component for which you want to determine the Text field name. The system displays the Column Definition form as follows:

The value displayed in the Field name field is the value you should enter in the Project, Task (if any), or Labor Code Text Field on the Microsoft Project Interface form.


Pct Compl.

The percent complete amount for this labor code. This field is updated each period, based on the progress of the project.

•	Advantage Format: Three numeric characters, (e.g. 100).

•	Recommended Microsoft Project Format: Use the % Complete field provided by Microsoft Project.


Rate

The job-to-date budgeted rate for work to be performed under the labor code for the project.

If you do not have a rate in your Microsoft Project file, Advantage calculates this field automatically if the hours and amount are entered for the labor code.

•	Advantage Format: Amount to two decimal places (e.g. 172.43).

•	Recommended Microsoft Project Format: Use one of the Cost1-10 fields provided by Microsoft Project.


Budgeted Hours

The job-to-date budgeted number of hours for work to be performed under the labor code for the project.

•	Advantage Format: Numeric field (for example, 362).

•	Recommended Microsoft Project Format: Use one of the Number1-20 fields provided by Microsoft Project, or use one of the Work fields (e.g., Regular Work or Work).


Budgeted Amount

The job-to-date budgeted cost amount for the labor to be charged to the labor code for the project. Advantage automatically calculates this amount based on the hours and rate.

When you post actual labor transactions in Advantage, affecting the project and labor code combination, the amounts are updated in the Job-to-Date Hours column on the Project Budget Worksheet form.

•	Advantage Format: Amount to two decimal places (for example, 172.43).

•	Recommended Microsoft Project Format: Use one of the Cost1-10 fields provided by Microsoft Project.


Budgeted Billing Amount

The job-to-date budgeted cost amount for the labor to be charged to the labor code for the project. Advantage automatically calculates this amount based on the hours and rate.

When you post actual labor transactions in Advantage, affecting the project and labor code combination, the amounts are updated in the Job-to-Date Hours column on the Project Budget Worksheet form.

•	Advantage Format: Amount to two decimal places (for example, 172.43).

•	Recommended Microsoft Project Format: Use one of the Cost1-10 fields provided by Microsoft Project.


Start Date

The anticipated start date of hours charged to the labor code.

•	Advantage Format: Date field (for example, 01/01/04).

•	Recommended Microsoft Project Format: Use the Start field provided by Microsoft Project.


End Date

The anticipated end date of hours charged to the labor code.

•	Advantage Format: Date field (for example, 01/01/04).

•	Recommended Microsoft Project Format: Use the Finish field provided by Microsoft Project.


ETC Hours

The estimate to complete hours for this labor code.

•	Advantage Format: Numeric field (for example, 362).

•	Recommended Microsoft Project Format: Use one of the Number1-20 fields provided by Microsoft Project.


ETC Amount

The estimate to complete dollar amount for this labor code.

•	Advantage Format: Amount to two decimal places (for example, 172.43).

•	Recommended Microsoft Project Format: Use one of the Cost1-10 fields provided by Microsoft Project.