Time Card Import from Excel

The AccuBuild Excel Time Card Template is used for entering time card data into a spreadsheet that contains company data lookup fields. The spreadsheet can be copied and e-mailed to job sites or to any person responsible for tracking payroll time cards. There is NO need to be logged into AccuBuild in order to make entries into the spreadsheet. You may use the spreadsheet over and over again without the need to log into AccuBuild. The completed spreadsheet is then emailed back to the payroll department for importing into a time card batch for final processing.

	Excel Time Card Import - Set Up
	Excel Time Card Import - Data Entry

•Download and install the 8.1 ODBC Driver from the advantage website using the following link: http://devzone.advantagedatabase.com/dz/content.aspx?Key=20. Do not install any other driver other than the 8.1 version even though the website says it is unsupported.

4.	Create a new folder under your company folder called TCImport. AccuBuild will use this folder to store the Ascii files that are created during the import process.

•Ascii File - Enter the full file path of the Ascii file to be imported from the Excel spreadsheet. Note: DO NOT include the name of the Ascii file in the file path.

Example: \\servername\sharedfolder:\AccuBld8\constructioncompanyfolder\tcimport\

Note: In a network environment, the Ascii File Path MUST be set up with a UNC path and NOT a drive letter path. The processing functions for Excel Time card imports use a stored procedure on the Advantage Database Server to copy all ASCII files to the \TCImport\ProcessedFiles\ folder after they have been imported, and the copy routine MUST use a UNC Drive when working in a network environment. During the time card import routine, if the copy process fails, a message will be added to the process dialog to indicate the copy failure with the recommended UNC Path to be used.

•Ascii Spec File - Enter the full path and file name of the Ascii Spec File to be used during the import process. This spec file defines the layout of the Ascii import file so that the fields in the Ascii file are mapped into the proper fields of the AccuBuild Time Card file.

Configure the Excel Time Card Input.xls spreadsheet for the current company settings. Open the desired spreadsheet: TimeCard_Master_Rev2 will display the 'lookup' of employee information in employee number order; TimeCard_Master_Rev3 will display in order by last name, first name. Click on the Setting Tab at the bottom of the spreadsheet. Change the three path settings to match your company paths as follows:

•Text File Path - Copy the path from the Ascii File setting in the previous step (AccuBuild Payroll Properties). Note: Only include the file path with trailing backslash in this setting and NOT the file name. The Export Button on the Excel spreadsheet names each file automatically during the export process based on the Windows User Name and the Data and Time of the export in order to insure that each export file has a unique name.

•Settings File Path - Copy the file name and path from the Ascii File Spec setting in the previous step (AccuBuild Payroll Properties).

•DSN File Path - Enter the company path where the AccuBuildODBCTimeCard.dsn resides (see above).

•Clear After Export? - If you would like to clear the cells in the spreadsheet after you have exported the entries then set to Yes, otherwise set to No to leave the entries remaining in the spreadsheet.

•Export to Spreadsheet? - If you want to export the entries to another spreadsheet instead of an Ascii File, then set to Yes (be sure to use uppercase 'Y' and lowercase 'es' for 'Yes' response),otherwise set to No. If you do set this to yes then be sure that the target spreadsheet exists and that the full path and spreadsheet name are entered in the entry below:

•Export Spreadsheet Path - If you are exporting to another spreadsheet, then enter the name of the target spreadsheet here including the full file path. Important Note: The target spreadsheet must exist in order for the export to spreadsheet option to work. Be sure to put a copy of the AccuBuild Time Card Spreadsheet into the target directory. This target spreadsheet will need to have the settings changed to exporting to Ascii.

After the settings are established, test the settings to make sure the spreadsheet connects to the AccuBuild company database files. Click on the Time Card tab at the bottom of the spreadsheet and test the settings as follows:

•Click the Update Lookup Lists button at the top of the spreadsheet and wait until the update process is completed. This process should connect to the AccuBuild Company Database and load up all of the lookup tables required for entering timecard data. Note: Macros must be in enabled in Excel which is the default setting otherwise the update option will not work.

•Once the update process is complete, click on each of the cells in the spreadsheet and make sure that the company data is listed in each drop down field.

•Save the spreadsheet at this point. You may also wish to make a copy of the spreadsheet for backup purposes in case the original spreadsheet settings are lost. The spreadsheet is now ready for use in entering time card data.

Once the time card import has been set up, the spreadsheet is ready for entering time card data. The spreadsheet can be sent to a job site or to any person who will be tracking time card hours. The user simply opens the spreadsheet and enters the data into the fields provided. Data such as the employee name, job number and phase, can be selected from the drop down list on each column. Be sure to select the proper pay type (regular pay vs. overtime) and enter the hours for each job/phase combination. Hours for employees working on more than one job/phase combination for the same week will need to be entered on separate lines of the spreadsheet. If the hours are to be included on the certified payroll report, then be sure to check the box.

•Refreshing Company Data - Before using the spreadsheet for new entries, you may need to refresh the lookup data for the spreadsheet if any new employees, jobs, costs phases etc. have been entered into your company. Use the Update Lookup Lists in the spreadsheet to refresh the lookup lists in the spreadsheet. Note: If the spreadsheet is sent to a job site or to any person who is not directly connected to the server, then the company data must be refreshed BEFORE the spreadsheet is sent to the job site.

•Exporting Time Card Entries - Once all of the time card entries have been entered into the spreadsheet, use Export to AccuBuild button to export the time card data into an Ascii file. Note: Make sure you are on a workstation that has access to the path as defined above under the path settings for the Text File Path: in the Settings Tab of the spreadsheet. The Ascii file will be created and placed in the designated folder.

•Importing Time Card Entries - Log into the AccuBuild Program and follow the directions for importing time card entries into the current user's time card batch. Refer to the Enter / Update Time Cards menu option for further information. The Time Card Import feature for Excel Spreadsheets processes multiple Ascii files at one time. This makes it much easier to import data from several job site locations using separate time card spreadsheets for each job site. The import process for handling Ascii files from Excel spreadsheets is as follows:

•The source folder (where the Ascii file(s) to be imported are located) is scanned to build a list of all Ascii files based on the file extension of '.TXT'.

•Each Ascii file is copied to a ProcessedFolder located under the source folder and the original file is deleted from the source folder once it has been imported into the system. If you ever need to re-process an Ascii file, simply copy it back from the ProcessedFolder to the original source folder and run the import process again.