THE IMPORT/EXPORT FACILITY





The Payroll Data Import/Export facility imports data from a file named "PAYIN.PAY" to MASTER.PAY, exports data from MASTER.PAY to a file named "PAYOUT.PAY", and uses control files named "XINDEF.PAY" and "XOUTDEF.PAY" to govern the transfer process.



This facility has many uses, including the following:



1. Initialization of DATASMITH Payroll Employee data and/or YTD totals from files created by another system or program.



2. Extraction of selected data from DATASMITH Payroll for inclusion in reports generated by dBASE or other spreadsheets capable of reading comma delimited ASCII files.



3. Extraction of selected data from DATASMITH Payroll for inclusion in LOTUS 1-2-3 Version 1A or Version 2 or other spreadsheets capable of Importing a comma-delimited ASCII file.



4. Conveniently updating selected information on a group of employees, using a Word Processor or Text Editor to prepare the list of changes.



This capability can be useful for salary changes, W4 information updates, department code changes, etc.



You can also EXPORT Payroll data, update it with a word processor, data base, or BASIC language program, and return the modified data to Payroll with IMPORT. To help you get started using IMPORT/EXPORT, a sample data disk is included with the IMPORT/EXPORT option. The sample data includes the files used in the examples in this section.



Restrictions in using this facility:



1. The IMPORT/EXPORT Facility must be correctly configured for the Payroll file in use. The IMPORT/EXPORT Facility is configured for use with a specific Payroll Version. It MUST NOT be used with other versions, even if they bear the same serial number!



2. When Extracting data for inclusion in a LOTUS spreadsheet, the total of all field widths and their delimiting characters must not exceed 240 characters.

Employee Data Import/Export

AUTO ADD is ON TEST MODE is ON

MM Exit to Main Menu

IM IMPORT Employee data.

EX EXPORT Employee data.

ST STOP automatic employee add during Import (now ON)

TM TEST MODE OFF: Import/Export at full speed (now ON)

EF EXPORT into FIXED-length fields

Your selection, please: EX







MM Exit to Main Menu of the Payroll system.



IM IMPORT Employee data. The Employee ID contained in each import data record is used to "steer" the data into the appropriate payroll Employee Data Record. The affected fields are identified by the Import Control File.



EX EXPORT Employee data. The selected fields of the Employee Data Record for each employee are output into a comma-delimited ASCII file in the order specified by the Export Control File.



ST STOP/START automatic employee add during Import. This selection gives the operator control over whether employees can be added to the file during IMPORT. If the Import is intended only to update information on existing employees, it is recommended that the automatic add feature be turned OFF to prevent unwanted file modification resulting from incorrect or mis-read employee ID information in the imported data.



TM TEST MODE provides a way for the operator to detect and correct errors in an import or export Control File. In Test Mode, the Import/Export speed is controlled by the one-key pause/cancel feature: this allows the operator to see each transaction displayed on the screen. With Test Mode off, Import/Export typically runs too fast to follow with the naked eye.



EF EXPORT into FIXED-length fields produces a different form of output file more easily handled by certain programs. The modified Export Control file for this selection requires Field Length and Field Type parameters which govern the format of the output.

IMPORTING PAYROLL DATA





During the Import process, data from successive fields of the Import Data file is type-converted as necessary and stored in the Payroll file according to the specifications in XINDEF.PAY.



The Import Data file PAYIN.PAY (or a filename of your choice) must be a Delimited format ASCII text file. This means that all field data is to be separated by commas, all text field data is surrounded by double quotes ("), and each record ends with a carriage return and line feed. This file can be created from dBASE 3+ using the command:

COPY TO PAYIN.PAY TYPE DELIMITED



The first field of each PAYIN.PAY record must be the Employee ID. If the Employee ID previously exists in the file MASTER.PAY, the appropriate record will be retrieved, updated, and replaced in the file. The update process is controlled by the Operation field of each Field Specification in XINDEF.PAY: the new data will either replace the old or (if numeric) optionally be added to the old data according to the Operation Specification for each Field.

If the Employee ID in PAYIN.PAY does not previously exist in MASTER.PAY, the data in the current PAYIN.PAY record is assumed to define a New Employee, and is added to MASTER.PAY. Whenever employees are added in this manner, the operator should use the INitialize function to Rebuild the Master Keys (and Auxiliary Keys, if used) before proceeding with other operations.




WARNING


Because of the possibility of errors in the imported information, it is highly recommended that you make a backup copy of your original MASTER.PAY file BEFORE importing any data into it. Because Imported data has not been checked by Payroll, there may be both missing data (such as no Filing Status code) and inconsistent data (such as YTD gross pay and YTD deductions which do not match YTD net pay, non-existing Department codes, etc.). After you import data, rebuild the Master Keyfile and check the results before proceeding. You may want to run a sample payroll as a final check before proceeding. Bad input data files or incorrect control files can result in new employee records with missing or defective employee ID's! If this happens, restore your old MASTER.PAY file, correct the error which caused the problem, and re-run IMPORT with the corrected data and/or control file(s).


The Import Control file (named XINDEF.PAY by default) consists of one line of text for each field to be read from the Import Data File (PAYIN.PAY by default) in the following format:

[Employee Record], [Field #], [Subfield #], [Operation] [Comment]



The Employee Record is a number identifying which Employee Record contains the Field of interest. Employee Records are numbered sequentially, starting with 0 (the Base Record).



The Field # is a number identifying the specific Field of interest within the selected Employee Record. Fields are numbered sequentially, starting with 1 (the first Field in a Record).



The Subfield # is used only with Bit-encoded or Byte-encoded Fields. These Fields may be updated all at once (Subfield # = 0), or each Bit or Byte may be updated separately by specifying a nonzero Subfield to identify the specific information being changed.



An Integer field with a nonzero Subfield number is treated as a Bit-Encoded Field. Bits are numbered from 1 to 16, where Bit #1 is the low-order bit.



A String field with a nonzero Subfield number is treated as a Byte-Encoded Field. Bytes are numbered from 1 to [string field length], where Byte #1 is the leftmost byte of the string.



The Operation code specifies whether the subject information in MASTER.PAY is to be replaced with the new information from PAYIN.PAY (Operation = 0), added to existing information in MASTER.PAY (Operation = 1), or multiplied by existing information (Operation = 2). The Operation code is used only if the destination data item is Numeric: otherwise it is ignored and the non-numeric subject item will be replaced.



Valid Operation Codes are:

For Numeric Information:



0 - Replace Existing Information

1 - Add New Information to Existing Value

2 - Multiply New Information by Existing Value



For Bit-Encoded values:



0 - Replace the Bit with New Information.

1 - Replace the Bit with the logical OR

of the New and Existing Information:



Existing Info New Info Result

0 0 0

0 1 1

1 0 1

1 1 1

2 - Replace the Bit with the logical AND

of the New and Existing Information:

Existing Info New Info Result

0 0 0

0 1 0

1 0 0

1 1 1



NOTE: When transferring Bit-Encoded values, the New Information contained in the Imported Data File (PAYIN.PAY) must be either 1 or 0: use of other numeric values may cause erroneous or undesirable results.



The Comment is an optional item which may be added after the Operation code on any specification line. If present, it is separated from the Operation code by a space or a comma. The Comment item is used only to provide documentation of the Import Control file (XINDEF.PAY): it is ignored by the program during the Import process. Comment fields must not include commas, unless the entire comment field is inside quotation marks ("Like this comment, for example").



EXAMPLE #I-1: Using the Import facility to add new Employees.



XINDEF.PAY file contents:



0,2,0,0 Employee ID * ***** EXAMPLE I-1 *****

0,4,0,0 First Name *

0,3,0,0 Last Name * Add a new employee

0,5,0,0 Address

0,6,0,0 City

0,7,0,0 State

0,8,0,0 Zipcode

0,9,0,0 Social Security Number

1,1,0,0 Employment Status

1,3,0,0 Department Number *

1,4,0,0 Applicable State for Tax

1,5,0,0 Applicable Local Code for Tax

1,6,0,0 Filing Status *

1,7,0,0 # Federal Exemptions

1,18,0,0 # State Exemptions

1,8,0,0 Unit of Pay (Hourly or Salaried) *

1,22,0,0 Hourly Wage or Periodic Salary

1,25,0,0 Date Hired

1,11,0,0 Federal Tax Control

1,12,0,0 State Tax Control

1,14,0,0 Salary Category *

5,2,0,0 YTD hours

5,3,0,0 YTD Gross

5,4,0,0 YTD Fed Tax Withheld

5,5,0,0 YTD State Tax Withheld

5,6,0,0 YTD FICA Tax Withheld

5,7,0,0 YTD Local Tax Withheld

5,16,0,0 YTD Net Pay

10,9,0,0 YTD Fed Tax Basis

10,10,0,0 YTD FICA Tax Basis

10,11,0,0 YTD State Tax Basis

10,12,0,0 YTD Local Tax Basis

10,13,0,0 YTD Non-Adj Gross for Tax





To try this yourself, copy the sample control file I1DEF.PAY into XINDEF.PAY. Using The MAster List function, generate a list of the existing employees.



Run IMPORT, specifying the sample data file I1DATA.PAY as the Import data file. Use the KF selection of INitialize to Rebuild the Master Key file (to add the key for the new employee). Then regenerate the MAster list, noting that a new employee has been added.



EXAMPLE #I-2:



Using the Import facility to add money to the salary of selected employees:



XINDEF.PAY file contents:

0,2,0,0 Employee ID ***** EXAMPLE I-2 *****

1,22,0,1 Add to hourly wage/salary







I2DATA.PAY file contents:



"BAC",10

"MJW",1.25





To try this yourself, copy the sample control file I2DEF.PAY into XINDEF.PAY. Using Employee data input & changes, Check Bruce Clinton's salary (BAC) and Mary Jane Weaver's (MJW) rate of pay. The sample input data file calls for an addition of $10 to Bruce Clinton's salary, and for an addition of $1.25 to Mary Jane Weaver's hourly pay.



Run IMPORT, specifying the sample data file I2DATA.PAY as the Import data file. Then recheck the Employee files, noting how the rates of pay were changed.





PREPARATION OF NEW EMPLOYEE DATA USING dBASE 3+



We recommend that you familiarize yourself with the basic capabilities of the IMPORT/EXPORT facility, including use of the sample files to run Example I1, before trying the following:



To demonstrate off-line preparation of new employee data entry using dBASE, we have included the following files on Disk #3:



I1.DBF An empty dBASE file named "I1"

I1.FMT A sample employee data entry screen



You can use these files with your dBASE 3+ to prepare data records which can be Imported into Payroll using the I1DEF.PAY control file. To do this, first activate your dBASE and use the commands:

USE I1

SET FORMAT TO I1

APPEND

( after you exit APPEND )

COPY TO PAYIN.PAY TYPE DELIMITED

QUIT

Then copy I1DEF.PAY into XINDEF.PAY and activate the IMPORT facility of Payroll: specify PAYIN.PAY as the input file. The data records you added with dBASE should then appear in the MAster employee list.



LPI/DATASMITH can provide custom data entry tools written in dBASE, DATAFLEX, or other popular data base languages to fill your specialized needs.





EXPORTING PAYROLL DATA



When you use the Export function, selected data from each Employee record in MASTER.PAY is converted to ASCII and stored in successive records of PAYOUT.PAY (or a filename of your choice) according to the specifications in XOUTDEF.PAY.



The Export Data file PAYOUT.PAY is also a Delimited format ASCII text file. This file can be added to an existing dBASE file using the dBASE command:



APPEND FROM PAYOUT.PAY TYPE DELIMITED







You can also Import this data into a LOTUS 1-2-3 spreadsheet by naming your Export file PAYOUT.PRN and retrieving it with the LOTUS command:



/FINPAYOUT







The Output Control file (XOUTDEF.PAY) consists of one line of text for each field to be exported to the Output Data File (PAYOUT.PAY) in the following format:

[Employee Record], [Field #], [Subfield #], [Comment]





The Employee Record is a number identifying which Employee Record contains the Field of interest. Employee Records are numbered sequentially, starting with 0 (the Base Record).



The Field # is a number identifying the specific Field of interest within the selected Employee Record. Fields are numbered sequentially, starting with 1 (the first Field in a Record).



The Subfield # is used only with Bit-encoded or Byte-encoded Fields. These Fields may be exported in Encoded form (Subfield # = 0), or each Bit or Byte may be exported separately by specifying a nonzero Subfield to identify the specific information desired.



The Comment is an optional item which may be added after the Subfield # on any specification line. It is separated from the Subfield # by a space or a comma. The Comment item, which must not contain additional commas, is provided to facilitate documentation of the XOUTDEF.PAY control file.





EXAMPLE #E-1: Using the EXPORT facility to get a list of employees and some of their basic employment information:



XOUTDEF.PAY contents:



0,2,0, Employee ID ** Example A: To LOTUS **

0,4,0, First Name

0,3,0, Last Name

1,3,0, Department #

1,6,0, Federal exemptions

1,18,0, State exemptions

0,7,0, State

1,22,0, Wage or salary





Use DATASMITH Payroll's EXPORT facility, naming the output file PAYOUT.PRN. Activate LOTUS and IMPORT the file with the commands:

/FINPAYOUT



Your payroll data will appear in the spreadsheet. You will need to adjust the column widths to appropriate values.



EXAMPLE #E-2: Using the EXPORT facility to get a mailing list usable by dBASE from Payroll:



XOUTDEF.PAY contents:

0,4,0, First Name *** MAILING LIST TO DBASE ***

0,3,0, Last Name

1,3,0, Address

1,6,0, City

1,18,0, State

0,7,0, Zipcode





To try this out with your dBASE 3 or dBASE 3+, copy the sample control file named E2DEF.PAY into XOUTDEF.PAY. Use EXPORT to create the output file named PAYOUT.PAY. Then transfer this file and the sample dBASE data file E2.DBF to your dBASE directory. Activate dBASE and type the dot commands:

USE E2

APPEND FROM PAYOUT.PAY TYPE DELIMITED

BROWSE





EXPORTING PAYROLL DATA INTO FIXED-FIELDLENGTH FILES



The operation of this selection is very nearly the same as the normal EXPORT. The main difference in using this option is the format of the Export Control file.



The Export Control file for Fixed-Fieldlength Output files (default name: "XFDEF.PAY") consists of one line of text for each field to be exported to the Output Data File in the following format:

[Employee Record], [Field #], [Subfield #], [Comment], [Length], [Type]





The Employee Record is a number identifying which Employee Record contains the Field of interest. Employee Records are numbered sequentially, starting with 0 (the Base Record).



The Field # is a number identifying the specific Field of interest within the selected Employee Record. Fields are numbered sequentially, starting with 1 (the first Field in a Record).



The Subfield # is used only with Bit-encoded or Byte-encoded Fields. These Fields may be exported in Encoded form (Subfield # = 0), or each Bit or Byte may be exported separately by specifying a nonzero Subfield to identify the specific information desired.



The Comment is a required item in Fixed-Fieldlength Export Control Files. It is separated from the Subfield # by a comma. The Comment item, which must not contain additional commas (unless the entire comment is within quotation marks), is provided for documentation of the Fixed-Fieldlength Export Control File. The Comment will appear on the screen as the item is written to the Output Data File.



The Length is an integer value specifying the total number of characters in the fixed-length output field. No delimiters appear in the output file. Each output record is terminated by a CR-LF.



The Type specifier determines the nominal output data format:



Valid Type codes are:



A - ASCII. Valid for string source data fields.

M - Money field, two decimals, no decimal point. Valid for single-precistion and double-precision source fields.

P - Percent field, three decimals, no decimal point. Valid for single-precision and double-precision source fields.

D - Date field, MMDDYY, leading zero-filled. Valid for single-precision and double-precision source data fields.

I - Integer field. Valid for integer, single-precision, and double-precision source data fields.

L - Logical: Valid when printing integer source data fields and specifying a nonzero subfield #. Value is printed as "T" or "F". Field length should be set to 1.