Importing CSV Files from ADP PC/Payroll To this Application For Windows
Importing Data
The external payroll interface (EPI) for ADP PC/Payroll for Windows supports the import of specific information from the ADP database tables to CloudSuite Business in CSV (comma-separated values) format. You must use Borland ReportSmith (or a similar tool) to prepare the export file for input to CloudSuite Business via the external payroll interface. If an ADP database table field is longer than the CloudSuite Business database schema field, the ADP data will be truncated.
These are the types of records supported for users of ADP PC/Payroll for Windows:
- Primary information records
- Pay information records
- Tax information records
- Gross pay accumulators
When the primary information record is loaded into CloudSuite Business, the system checks the Employee table to see if the Employee for whom the data is to be imported already exists in the system. If the Employee is not in the table, data for the primary information record is added to CloudSuite Business.
If the employee already exists in CloudSuite Business, the records are updated with the data imported from EPI. The following chart maps the ADP PC/Payroll data base table and field names to the CloudSuite Business Employee table field names. The output file should be a CSV file named MFOUT.csv.
Primary Information Record (Record Type 1)
Field | ADP Database | ADP Field | employee.mst table column | Type | Size (format) |
---|---|---|---|---|---|
File Number | V_EMP_ALL | File | emp_num | character | 7 |
NAME | V_EMP_ALL | Name | name | character | 60 |
ADDRESS 1 | V_EMP_ALL | StreetLine1 | addr##1 | character | 50 |
ADDRESS 2 | V_EMP_ALL | StreetLine2 | addr##2 | character | 50 |
CITY | V_EMP_ALL | City | city | character | 50 |
STATE | V_EMP_ALL | State | state | character | 50 |
ZIP CODE | V_EMP_ALL | ZipCode | zip | character | 10 |
SOC. SEC. NO. | V_EMP_ALL | SocialSecurity Number | ssn | character | 11 |
HOME DEPT. | V_EMP_ALL | HomeDepartment | dept | character | 6 |
PHONE NUMBER | V_EMP_ALL | AreaCodePhoneNum | phone | character | 25 |
Pay Information Record (Record Type 2)
Field | ADP Database | ADP Field | employee.mst table column | Type | Size (format) |
---|---|---|---|---|---|
File Number | V_EMP_ALL | File | emp_num | character | 7 |
PAY FREQUENCY | V_EMP_ALL | PayFreq | pay_freq | character | 1 |
RATE 1 | V_EMP_ALL | Rate1Amt | reg_rate | decimal | 9 (-zzz,zz9.999) |
Tax Information Record (Record Type 3)
Field | ADP Database | ADP Field | employee.mst table column | Type | Size (format) |
---|---|---|---|---|---|
File Number | V_EMP_ALL | File | emp_num | character | 7 |
MARITAL STATUS | V_EMP_ALL | ActMaritalStatus | marital_stat | character | 1 |
Gross Pay Year to Date Accumulators (Record Type 7)
Field | ADP Database | ADP Field | employee.mst table column | Type | Size (format) |
---|---|---|---|---|---|
File Number | V_EMP_ALL | File | emp_num | Character | 7 |
YTD GROSS PAY | V_YTD_INFO | YTDGross$ | ytd_grs | Decimal | 8 (-zzz,zz9.99) |
YTD FEDERAL TAX | V_YTD_INFO | YTDFedTax$ | ytd_fwt | Decimal | 8 (-zzz,zz9.99) |
YTD SUI TAX | V_YTD_INFO | YTDSUISDITax$ | ytd_sui_grs | Decimal | 8 (-zzz,zz9.99) |
YTD SOC SEC TAX | V_YTD_INFO | YTDSocSecTax$ | ytd_fica | Decimal | 8 (-zzz,zz9.99) |
YTD MEDICARE TAX | V_YTD_INFO | YTDMedicare$ | ytd_med | Decimal | 8 (-zzz,zz9.99) |
YTD STATE 1 TAX | V_YTD_INFO | YTDWorkedStateTax$ | ytd_swt | Decimal | 8 (-zzz,zz9.99) |
YTD STATE 2 TAX | V_YTD_INFO | YTDLivedStateTax$ | ytd_ost | Decimal | 8 (-zzz,zz9.99) |
YTD CITY 1 TAX | V_YTD_INFO | YTDWorkedLocalTax$ | ytd_cwt | Decimal | 8 (-zzz,zz9.99) |