Updating Employee Fields from an Import File

You can use the Expanded Employee Import file to bulk load employees into PrismHR.

When you use the Expanded Employee Import method to import employee information, enter the information in the Template_for_Expanded_Employee_Import_TXT.xls file. The contents of this file are listed in the following table. The file template is also available on the Customer Resource Center.

Important Notes About the 2020 Form W-4

Beginning December 2019, this import supports the new 2020 Form W-4 filing statuses and fields. Please note the following updates:

• In column AV, you can enter the new filing statuses (SS, MJ, and H).
• If your state is using the new Form W-4 (Colorado, Delaware, Nebraska, New Mexico, North Dakota, South Carolina, and Utah), you must enter the new filing statuses in this field to avoid calculation errors.
• Federal, work state, and home state values are now supported for the new Form W-4 fields, beginning in column GX. Note that there are no validations on these fields.
• You must enter dollar amounts in the Other Income, Dependents, and Deductions fields. See the Form W-4 instructions for more information.

Information about this import file

• File Type: ASCII, Tab Delimited
• File Use: Stores employee data imported from foreign system
• Key Format: Sequential Integer

Other Notes

There are a few more things to note for certain import fields, marked with asterisks (*) in the Reqd/Optl column of the table below:

• One asterisk (*): This field is not required if ONHRP.ONBOARDED (Field 161) is set to Y.
• Two asterisks (**): This field is only optional if ONHRP.ONBOARDED (Field 161) is set to Y. Otherwise, it is required.
• Three asterisks (***): This field is required if ONHRP.ONBOARDED (Field 161) is set to Y. Otherwise, it is optional.

File specifications

Excel Column

Field Name

Field Number

Type

Length

Reqd/Optl

Description
A FIRST.NAME 1 A 15 R  
B LAST.NAME 2 A 20 R  
C MIDDLE.INIT 3 A 1 O  
D BIRTH.DATE 4 A 10 O MM-DD-CCYY format
E COMPANY.ID 5 A 7 R  
F EMPLOYER.ID 6 N 5 R  
G ETHNIC.CODE 7 A 1 O**
H MARITAL.STATUS 8 A 1 O M (Married), S (Single), D (Divorced), W (Widowed), O (Other)
I TOBACCO/SMOKER 9 A 1 O Y or N
J ADDR.ONE 10 A 30 R Resident Street Address
K ADDR.TWO 11 A 30 O  
L ZIP.CODE 12 N 5 R 99999 or NNNNN format
M CITY 13 A 20 R  
N STATE 14 A 2 R USPS State Code
O TELEPHONE 15 A 10 O  
P HANDICAPPED 16 A 1 O Y or N
Q BLIND 17 A 1 O Y or N
R VIETNAM.VET 18 A 1 O Y or N
S DISABLED.VET 19 A 1 O Y or N
T CITIZEN 20 A 20 O Country of citizenship
U GENDER 21 A 1 O** M or F
V EMERGENCY.CONTACT 22 A 25 O Emergency contact's name
W EMERGENCY.PHONE 23 A 12 O Emergency contact's phone number
X EMERGENCY.RELATION 24 A 15 O Emergency contact's relationship to employee
Y SOC.SEC.NUM 25 N 11 R Employee's Social Security or alien registration number. NNN-NN-NNNN format.
Z STATUS.CODE 26 A 1 R Employee Status Code = A (Active), T (Terminated)
AA TYPE.CODE 27 A 1 R Employee Type Code = F (Full-time), P (Part-time)
AB LOCATION.CODE 28 A 6 R  
AC JOB.CODE 29 A 8 R  
AD ORIG.HIRE 30 D 10 R Original date of hire within company. MM-DD-YYYY format.
AE LAST.HIRE 31 D 10 R Last hire date within company. MM-DD-YYYY format.
AF PEO.START.DT 32 D 10 R Date started with PEO. MM-DD-YYYY format.
AG EMPLOYEE.NO 33 N 6 O Employee number assigned by company.
AH WORK.PHONE 34 A 10 O Work telephone number
AI WORK.EXT 35 A 4 O  
AJ MAIL.ADDR.ONE 36 A 30 O First line of mailing address
AK MAIL.ADDR.TWO 37 A 30 O Second line of mailing address
AL MAIL.CITY 38 A 20 O  
AM MAIL.STATE 39 A 2 O  
AN MAIL.ZIP 40 N 5 O  

AO

SHIFT.CODE

41

N

3

O

The employee's work shift assignment within the company organization.

AP

-

42

-

-

-

For future use

AQ

PAY.METHOD

43

A

1

R

S (salary), H (hourly), D (driver), C (commission)

AR

EXT.PAY.RATE

44

N

12

R

999.9999 format

AS

DISCONTINUED

45

 

 

 

To pass in annual salary amounts, enter Y in the PAY.PERIOD column. See field number 87.

AT

STD.HOURS

46

N

6

R

999.99 format

AU

EIC.FILING.STATUS

47

A

1

O

S or M or null.

AV

FEDERAL.STATUS

48

A

2

R

For the 2020 Form W-4:

Enter SS (single or married filing separately), MJ (married filing jointly), or H (head of household)

 

For pre-2020 Forms W-4:

Enter S or M

 

Note: Tax withholdings will be inaccurate if the correct filing status is not entered.

AW

FEDERAL.ALLOWS

49

N

1

R

Federal Withholding Allowances

AX

EXTRA.FEDERAL

50

N

8

o

Additional FIT withholding. 999.99 format.

AY

HOME.STATE.STATUS

51

A

1

O/R*

Res. State Filing Status. May be required - varies from state to state.

AZ

HOME.STATE.ALLOWS

52

N

1

O

Res. State Withholding Allowances

BA

HOME.STATE.ADDITIONAL.AMOUNT

53

N

8

O

Additional Res. SIT Withholding. Flat Dollar Amount. 999.99 format.

BB

WORK.STATE.STATUS

54

A

1

O

Work State Filing Status

BC

WORK.STATE.ALLOWS

55

N

1

O

Work State Withholding Allowances

BD

WORK.STATE.ADDITIONAL.AMOUNT

56

N

8

O

Additional Work SIT Withholding. Flat dollar amount. 999.99 format.

BE

OFFICER

57

A

1

O

Y or N or null

BF

DEPT.CODE

58

A

6

O

 

BG

TERM.DATE

59

A

6

O

Y or N or null

BH

TERM.CODE

60

A

8

O

Term Reason Code

BI

EMAIL.ADDRESS

61

A

30

O***

Employee's email address

BJ

BENEFIT.GROUP

62

A

8

R

Benefit group to which the employee belongs

BK

USER.FIELD.1

63

A

25

O

User defined field

BL

USER.FIELD.2

64

A

25

O

User defined field

BM

USER.FIELD.3

65

A

25

O

User defined field

BN

USER.FIELD.4

66

A

25

O

User defined field

BO

USER.FIELD.5

67

A

25

O

User defined field

BP

ZIP SUFFIX

68

A

4

O

Zip code suffix

BQ

ALT.CALC.HOME.STATE.CODE

69

N

2

O

Calculate state tax if employee is living in Arizona. NOTE: This field must be validated.

BR

ALT.CALC.WORK.STATE.CODE

70

N

2

O

Calculate state tax if employee is working in Arizona. NOTE: This field must be validated.

BS

-

71

-

-

-

For future use

BT

-

72

-

-

-

For future use

BU

-

73

-

-

-

For future use

BV

1099 EMPLOYEE?

74

A

1

O

Flags 1099-only employees. Y or N or blank.

BW

-

75

-

-

-

For future use

BX

PAY.GROUP

76

A

8

R

A valid pay group code for the company.

BY

DIVISION.CODE

77

A

8

O

Division Code

BZ

PROJECT.CODE

78

A

8

O

Project Code

CA

AUTO.PAY

79

A

1

R

Flags that employee's pay hour should default to a specified value. Y for yes and N for no.

CB

AUTO.PAY.HOURS

80

N

8

O

Auto pay hours in 123.45 format. Required if AUTO.PAY.FLAG is set to Y.

CC

HOME.STATE.EXEMPT.AMOUNT

81

N

10

O

Home state tax exempt amount in 12345.78 format. Used only in specific states.

CD

HOME.STATE.SECONDARY.ALLOWS

82

N

1

O

Number of secondary allowances for the home state. Value should be an integer.

CE

HOME.STATE.SUPP.AMOUNT

83

N

10

O

Home state supplemental tax amount in 12345.78 format, used only in specific states.

CF

WORK.STATE.EXEMPT.AMOUNT

84

N

10

O

Work state tax exempt amount in 12345.78 format. Used only in specific states.

CG

WORK.STATE.SECONDARY.ALLOWS

85

N

1

O

Number of secondary allowances for the work state. Value should be an integer.

CH

WS.SUPP.AMOUNT

86

N

10

O

Work state supplemental tax amount in 12345.78 format. Used only in specific states.

CI

PAY.PERIOD

87

A

1

R

Time period that the pay rate applies to. H = hourly, W = weekly, B = biweekly, S = semimonthly, M = monthly, Y = annual.

CJ

VETERAN

88

A

1

O

Y for yes, N for no.

CK

NEWLY.SEPARATED.VET

89

A

1

O

Y for yes, N for no.

CL

SERVICE.MEDAL.VET

90

A

1

O

Y for yes, N for no.

CM

OTHER.PROTECTED.VET

91

A

1

O

Y for yes, N for no.

CN

I9.DOCUMENT.TITLE.A

92

A

60

O

The title of the first document used for Form I-9 identification.

CO

I9.DOCUMENT.NUMBER.A

93

A

20

O

The ID number of the first document used for Form I-9 identification.

CP

I9.ISSUING.AUTHORITY.A

94

A

20

O

The authority issuing the first document used in Form I-9 identification

CQ

I9.EXPIRATION.DATE.A

95

D

20

O

The expiration date of the first document used for Form I-9 identification.

CR

I9.DOCUMENT.TITLE.B

96

A

20

O

The title of the second document used for Form I-9 eligibility.

CS

I9.DOCUMENT.NUMBER.B

97

A

20

O

The ID number of the second document used for Form I-9 eligibility.

CT

I9.ISSUING.AUTHORITY.B

98

A

20

O

The authority issuing the second document used for Form I-9 eligibility.

CU

I9.EXPIRATION.DATE.B

99

D

20

O

The expiration date of the second document used for Form I-9 eligibility.

CV

ALIEN.REG.NO

100

A

10

O

Alien registration number

CW

FICA.EXEMPT

101

A

1

O

Y for yes or N for No.

CX

UNION.CODE

102

A

8

O

Union ID Code.

CY

SUPERVISOR.ID

103

A

6

O

PTO approver's employee ID.

CZ

BENE.THRU.DATE

104

D

10

O

Benefits through-date in MMDDYYYY format.

DA

SCHOOL.DISTRICT

105

A

6

O

School district code.

DB

AGRICULTURAL

106

A

6

O

Agricultural worker: Y for yes or N for no.

DC

HOME.PHONE.2

107

A

6

O

Alternate home phone number.

DD

ALT.PAY.RATE.1

108

N

10

O

Alternate pay rate #1 in 123.4567 format.

DE

ALT.PAY.RATE.2

109

N

10

O

Alternate pay rate #2 in 123.4567 format.

DF

ALT.PAY.RATE.3

110

N

10

O

Alternate pay rate #3 in 123.4567 format.

DG

ALT.PAY.RATE.4

111

N

10

O

Alternate pay rate #4 in 123.4567 format.

DH

ALT.PAY.RATE.5

112

N

10

O

Alternate pay rate #5 in 123.4567 format.

DI

ALT.PAY.RATE.6

113

N

10

O

Alternate pay rate #6 in 123.4567 format.

DJ

ALT.PAY.RATE.7

114

N

10

O

Alternate pay rate #7 in 123.4567 format.

DK

ALT.PAY.RATE.8

115

N

10

O

Alternate pay rate #8 in 123.4567 format.

DL

ALT.PAY.RATE.9

116

N

10

O

Alternate pay rate #9 in 123.4567 format.

DM

ALT.PAY.RATE.10

117

N

10

O

Alternate pay rate #10 in 123.4567 format.

DN

NEW.HIRE.REPORT.DATE

118

D

10

O

Used to populate the Expanded report date to prevent reporting the employee again on the next Expanded download.

DO

MAIL.CHECK.HOME

119

A

1

O

Y for yes or N for no.

DP

W2_ADDRESS_ONE

120

A

30

O

First line of Form W-2 address.

DQ

W2_ADDRESS_TWO

121

A

30

O

Second line of Form W-2 address.

DR

W2.CITY

122

A

40

O

Form W-2 City

DS

W2.STATE

123

A

3

O

Form W-2 State

DT

W2.ZIPCODE

124

A

6

O

Form W-2 Zip Code

DU

LICENSE_NUMBER

125

A

16

O

Driver's license number

DV

LICENSE_EXPIRE_DATE

126

D

10

O

Driver's license expiration date.

DW

LICENSE_STATE

127

A

2

O

Driver's license state

DX

S_CORP_PRINCIPAL

128

A

1

O

Y for yes or N for no.

DY

ELECT_PAY_STUB

129

A

1

O

Y for yes or N for no.

DZ

ELEC_W2_FORM

130

A

1

O

Y for yes or N for no.

EA

ALLOC_LOCATION.1

131

A

10

O

Valid location for company

EB

ALLOC_LOCATION.2

132

A

10

O

Valid location for company

EC

ALLOC_LOCATION.3

133

A

10

O

Valid location for company

ED

ALLOC_LOCATION.4

134

A

10

O

Valid location for company

EE

ALLOC_LOCATION.5

135

A

10

O

Valid location for company

EF

ALLOC_DIVISION.1

136

A

10

O

Valid division for company

EG

ALLOC_DIVISION.2

137

A

10

O

Valid division for company

EH

ALLOC_DIVISION.3

138

A

10

O

Valid division for company

EI

ALLOC_DIVISION.4

139

A

10

O

Valid division for company

EJ

ALLOC_DIVISION.5

140

A

10

O

Valid division for company

EK

ALLOC_DEPARTMENT.1

141

A

8

O

Valid department for company

EL

ALLOC_DEPARTMENT.2

142

A

8

O

Valid department for company

EM

ALLOC_DEPARTMENT.3

143

A

8

O

Valid department for company

EN

ALLOC_DEPARTMENT.4

144

A

8

O

Valid department for company

EO

ALLOC_DEPARTMENT.5

145

A

8

O

Valid department for company

EP

ALLOC_PROJECT.1

146

A

8

O

Valid project for company

EQ

ALLOC_PROJECT.2

147

A

8

O

Valid project for company

ER

ALLOC_PROJECT.3

148

A

8

O

Valid project for company

ES

ALLOC_PROJECT.4

149

A

8

O

Valid project for company

ET

ALLOC_PROJECT.5

150

A

8

O

Valid project for company

EU

ALLOC_JOBS.1

151

A

10

O

Valid job for company

EV

ALLOC_JOBS.2

152

A

10

O

Valid job for company

EW

ALLOC_JOBS.3

153

A

10

O

Valid job for company

EX

ALLOC_JOBS.4

154

A

10

O

Valid job for company

EY

ALLOC_JOBS.5

155

A

10

O

Valid job for company

EZ

ALLOC_PCNTS.1

156

N

6

O

123.45 format. Must total 100.00.

FA

ALLOC_PCNTS.2

157

N

6

O

123.45 format. Must total 100.00.

FB

ALLOC_PCNTS.3

158

N

6

O

123.45 format. Must total 100.00.

FC

ALLOC_PCNTS.4

159

N

6

O

123.45 format. Must total 100.00.

FD

ALLOC_PCNTS.5

160

N

6

O

123.45 format. Must total 100.00.

FE

ONHRP.ONBOARDED

161

A

1

O

For PrismHR and OnHRP, Y for yes or N for no. Note: Use this column only when PrismHR Onboarding or OnHRP is enabled.

FF

HANDBK.RCD

162

D

10

O

MM-DD-YYYY format

FG

LAST.REVIEW

163

D

10

O

MM-DD-YYYY format

FH

NEXT.REVIEW

164

D

10

O

MM-DD-YYYY format

FI

NICKNAME

165

D

10

O

 

FJ

WORK.EMAIL

166

A

25

O

Work E-mail Address

FK

BEN.EMAIL.OPT.IN.DATE

167

D

10

O

Benefits E-mail Opt In Date

FL

BENEFITS.EMAIL.ADDRESS

168

A

30

O

Benefits E-mail Address

FM

USE.ADDED.I9.VALUES

169

A

1

O

Use the additional I-9 information (Y to comply with new Federal regulations).

FN

FED.W4.FILE

170

A

1

O

Federal W-4 Filing

FO

FED.W4.YEAR

171

N

4

O

Federal W-4 Year

FP

FED.W5.FILE

172

A

1

O

Federal W-5 Filing

FQ

FED.W5.YEAR

173

N

4

O

Federal W-5 Year

FR

FORM.I9

174

A

1

O

Form I-9 Filed

FS

I9.FROM.OB

175

A

1

R

I-9 Originally from Onboarding (Y if from EE Onboarded)

FT

I9.FROM.OB

176

D

10

O

I-9 Renew Date

FU

I9.START.DATE

177

D

10

O

I-9 Start date

FV

I9.LAST.NAME

178

D

20

O

I-9 Last Name

FW

I9.FIRST.NAME

179

A

20

O

I-9 First Name

FX

I9.MIDDLE.NAME

180

A

15

O

I-9 Middle Name

FY

I9.OTHER.NAME.USED

181

A

15

O

I-9 Other Name Used

FZ

I9.SSN

182

A

11

O

I-9 SSN

GA

I9.DOB

183

D

10

O

I-9 Date of Birth

GB

I9.ELIG2.DOCMT

184

A

60

O

I-9 Eligibility 2 Document

GC

I9.ELIG.AUTH2.NAME

185

A

60

O

I-9 Eligibility 2 Authorization Name

GD

I9.ELIG.DOCNO

186

A

15

O

I-9 Eligibility 2 Document Number

GE

I9.ELIG2.DOC.EXP

187

D

10

O

I-9 Eligibility 2 Document Expiration

GF

I9.ELIG3.DOCMT

188

A

60

O

I-9 Eligibility 3 Document

GG

I9.ELIG3.AUTH.NAME

189

A

60

O

I-9 Eligibility 3 Authorization Name

GH

I9.ELIG3.DOCNO

190

A

15

O

I-9 Eligibility 3 Document Number

GI

I9.ELIG3.DOC.EXP

191

D

10

O

I-9 Eligibility 3 Document Expiration

GJ

CITIZEN.STATUS

192

A

40

O

Citizenship Status

GK

FOREIGN.PASSPORT.NO

193

A

10

O

Foreign Passport Number

GL

FOREIGN.PASSPORT.COUNTRY

194

A

10

O

Foreign Passport Country

GM

I94.ADMISSION.NO

195

A

10

O

I-94 Admissions number

GN

ALIEN.REG.EXP

196

D

10

O

Alien Registration Expiration

GO

NONRES.ALIEN

197

A

1

O

Non-Resident Alien

GP

HNDBK.MAILED

198

D

10

O

Handbook Mailed On

GQ

BACKGRND.TEST.DATE

199

D

10

O

Background Test Date

GR

BCKGRND.TEST.DONE

200

A

1

O

Background Test Done

GS

FORM.C112.SIGNED

201

A

1

O

Ohio Form C-112 Signed

GT

PSD.CODE

202

N

7

O

Override code for PA taxes

GU

-

203

-

-

-

For future use.

GV

CROSS.HIRE

204

-

6

R/O (Only required if the employee is a cross hire)

The employee ID of the existing employee in PrismHR. If the Social Security number (SSN) of the imported employee matches the SSN of the existing employee, the system prevents the import.

GW

-

205

-

-

-

For future use

GX

MULTIPLE.JOBS.FED

206

A

 

O

Y for Yes, N for No (2020 Form W-4)

GY

CLAIM.DEPENDENTS.FED

207

N

 

O

2020 Form W-4

GZ

OTHER.INCOME.FED

208

N

 

O

2020 Form W-4

HA

DEDUCTIONS.FED

209

N

 

O

2020 Form W-4

HB

WS.MULTIPLE.JOBS

210

A

 

O

Y for Yes, N for No (2020 work state Form W-4)

HC

WS.CLAIM.DEPENDENTS

211

N

 

O

Work state Form W-4

HD

WS.OTHER.INCOME

212

N

 

O

Work state Form W-4

HE

WS.DEDUCTIONS

213

N

 

O

Work state Form W-4

HF

HS.MULTIPLE.JOBS

214

A

 

O

Y for Yes, N for No (2020 home state Form W-4)

HG

HS.CLAIM.DEPENDENTS

215

N

 

O

Home state Form W-4

HH

HS.OTHER.INCOME

216

N

 

O

Home state Form W-4

HI

HS.DEDUCTIONS

217

N

 

O

Home state Form W-4

IT

SENIORITY.DATE

254

D

10

O

Calculates employee's benefits and can be adjusted to provide more seniority to the employee than the last hire date, which can affect benefits and paid time accrual