Importing Excel Timecard Sets
Excel Spreadsheets saved in .csv format can be used to upload timecard sets. These sets are uploaded directly into Payroll Timecards using PYTCIFXLRG. It is recommended that .csv files be named the same as the timecard set name and saved by Payroll as the source document for payment.
Creating the .CSV File in Excel
All .csv files used for timecard entry must include a header row. The header row tells the timecard import program which column in the table your data should populate. The first row in the spreadsheet must be the column headings. Additional titles or blank rows will cause an error with the upload file. After creating the spreadsheet and entering employee data, make sure to save the file as a .csv (comma delimited) file.
File Naming Convention
It is recommended that import files and Timecard Sets be named in the following format: Type of Set (Max 5 Characters) + Pay Period Number (ex: PRIOR1601030). This will allow easy identification of each type timecard set. Users can upload their own timecard sets and resolve their own import issues. The indicator of A or B at the end of the type of set (Ex: PROFA1301030) may be used to identify week 1 or week 2 timecard sets.
Importing the Timecard Upload File in Dashboard (PYTCIFXLRG)
Timecards are uploaded through the Timecard Import mask PYTCIFXLRG. This utility can be run in test mode to validate your file before upload into the system. The import will produce an import report, error listing (if applicable) and tail sheet.
Go to PYTCIFXLRG:
- Enter the Pay Period Number for your timecard set (ex: 1601030)
- Enter the name of the Timecard Set (ex: PRIOR1601030)
- Enter the name of the source file or use the Lookup function to locate the file.
- If this Set already exists, let the system replace the existing set (field set to "RE").
- Uncheck the trial run box to run in LIVE mode. Users can test this import before running in live mode by leaving this box checked.
Click the Submit button to run the import. After the import job has completed, verify there are no errors with the import. If there are errors, there will be an additional job report called ERRORS & MESSAGES. Resolve any errors listed and re-import the file. Make sure the job produces an Interface Report and a tail sheet. If there is a tail sheet only, the .csv file may be invalid. Check the tail sheet to review.
CSV Interface File Mapping
The table below shows the file layout used to interface with the PY Timecards program. The fields with asterisks " * " are required fields. Items that are not being populated should be filled with blanks.
Field Type / Size | CSV Header Row Value | Timecards Online Import Column | Description | Comments |
|---|---|---|---|---|
PIC X(12) | EMP-ID* | ifpy_emp_id | Employee ID |
|
PIC X(16) | BATCH-NAME | ifpy_batch_id | Set Name |
|
PIC X(9) | BATCH-REC-NO |
| Record Number | Sequential # |
PIC X(8) | PAY-PERIOD* | ifpy_per_cc | Pay Period | YYccIII |
PIC X(8) | DATE* | ifpy_trn_dt | Date | CCYYMMDD format |
PIC X(9) | HRS* | ifpy_hrs | Number of Hours |
|
PIC X(4) | HRS-NO* | ifpy_cdh | CDH Value | Requires leading digit of 3 |
PIC X(8) | HRS-CODE | n/a | Hours Code | Text describing CDH |
PIC X(1) | HRS-OVR | ifpy_ovr_hrs | Hours Override | Y or blank |
PIC X(9) | RT | ifpy_rate | Rate | up to 5 dec |
PIC X(1) | RT-OVR | ifpy_ovr_rt | Rate Override | Y or blank |
PIC X(9) | RT-ADJ | n/a | Rage Adjustment |
|
PIC X(9) | PAY-AMT | ifpy_amt | Payment |
|
PIC X(1) | PAY-OVR | ifpy_pay_ovr | Pay Override | Y or blank |
PIC X(2) | REC-TYPE | ifpy_rec_type | Pay Assignment Type |
|
PIC X(1) | NUM-OVR | ifpy_ovr_num | Num Code Override | Y or blank |
PIC X(9) | NUM-CD | ifpy_num_cd | Num Code |
|
PIC X(4) | PAY-CLASS | ifpy_pay_class | Pay Class |
|
PIC X(10) | POS | ifpy_pos | Position |
|
PIC X(4) | STEP | ifpy_step | Step |
|
PIC X(2) | GL-LEDGER | ifpy_gl_gr | GL Ledger |
|
PIC X(40) | GLKEY | ifpy_gl_key | GL Key | FQA supported depending on PYUPGN |
PIC X(8) | GLOBJ | ifpy_gl_obj | GL Object |
|
PIC X(2) | JL-LEDGER | ifpy_jl_gr | JL Ledger |
|
PIC X(40) | JLKEY | ifpy_jl_key | JL Key | FQA supported depending on PYUPGN |
PIC X(8) | JLOBJ | ifpy_jl_obj | JL Object |
|
PIC X(10) | WO-NUMBER | ifpy_wo | Work Order |
|
PIC X(12) | SPREAD-INFO | n/a | not used |
|
PIC X(12) | MISC | n/a | not used |
|
PIC X(8) | AUDIT-DATE | n/a | not used |
|
PIC X(8) | AUDIT-TIME | n/a | not used |
|
PIC X(8) | AUDIT-USER | n/a | not used |
|
PIC X(20) | USER-PART10 | ifpy_user1 |
|
|
PIC X(20) | USER-PART11 | ifpy_user2 |
|
|
PIC X(20) | USER-PART12 | ifpy_user3 |
|
|
PIC X(20) | USER-PART13 | ifpy_user4 |
|
|
PIC X(20) | USER-PART14 | ifpy_user5 |
|
|
PIC X(20) | USER-PART15 | ifpy_user6 |
|
|
PIC X(20) | USER-PART16 | ifpy_user7 |
|
|
PIC X(20) | USER-PART17 | ifpy_user8 |
|
|
PIC X(20) | USER-PART18 | ifpy_user9 |
|
|
PIC X(12) | SERV-DT |
|
|
|
PIC X(12) | APRV-CD |
|
|
|
PIC X(12) | GROUP |
|
|
|
PIC X(12) | PROC-FLAG |
|
|
|
PIC X(12) | MISC1 |
|
|
|
PIC X(12) | MISC2 |
|
|
|
PIC X(12) | HR-ID |
|
|
|
PIC X(12) | REASON-CD |
|
|
|
PIC X(12) | APRV-CD1 |
|
|
|
PIC X(12) | APRV-CD2 |
|
|
|
PIC X(12) | APRV-CD3 |
|
|
|
PIC X(12) | APRV-CD4 |
|
|
|
PIC X(12) | APRV-CD5 |
|
|
|
PIC X(12) | MISC3 |
|
|
|
PIC X(12) | MISC4 |
|
|
|
PIC X(12) | MISC5 |
|
|
|
PIC X(12) | RATE-CD1 |
|
|
|
PIC X(12) | RATE-CD2 |
|
|
|
PIC X(12) | RATE-CD3 |
|
|
|
PIC X(12) | RATE-CD4 |
|
|
|
PIC X(12) | RATE-CD5 |
|
|
|
PIC X(12) | MISC-DESC |
|
|
|
PIC X(12) | WF-CODE |
|
|
|
Additional Comments
- If the Num Code and all of the Pay Assignment information (the fields from the Pay Class to the Work Order) are empty, then the employee's active PM pay assignment will be used. If the Pay Assignment information is provided then the process will use and create this assignment.
- Override flags: if the value that is being provided is to be used regardless of system settings, place a "Y" in the OVR- flags. For example, the employee's regular hourly rate is $10.00, but the user wants the record to be paid at $20.00. After populating the Rate field, put a "Y" in Rate Override to force this rate. If this is not done, the system will derive the employee's current rate during calculation time.
- For the flat file/csv imports, all fields are characters; dates are in CCYYMMDD format and numeric values should have an explicit decimal point as necessary.
- The file name must be eight characters or less and start with a letter.
- The first record of a delimited file must be a header record that specifies the timecard fields in the order they appear in the file from record 2 onwards. The available field names are shown in the table above under the column CSV Header Row Value.
- The first field in the first record must be EMP-ID, the other fields may be in any order. The delimiter following EMP-ID will be used to parse the remaining records; i.e., the file may be delimited by a character other than a comma by indicating the expected delimiter on the header record. Only one delimiter character is supported. An example can be seen in the specification: EMP-ID,DATE,HRS,HRS-NO,HRS-CODE,RT,REC-TYPE.