Below you will find answers to frequently asked questions about VETS-4212 batch file configuration and uploading.
There are two types of batch files used during uploading the VETS-4212 data. The primary file is for the actual data of the reports. This is the most complex file and requires a little special attention when making sure it has the correct format and number of columns. The second file is ONLY used when you have Multiple State Consolidated (MSC) reports contained in your primary data file. This second file contains only the locations for those MSC reports.
No, you only use the hiring location file (the second file) when the primary file contains Multiple State Consolidated Reports (MSC).
The format of these batch files is a comma separated values (CSV) file which is an ASCII plain-text file containing each field separated from the next by a comma. An ASCII text file is a small, simple document that can be created by a text editor such as Microsoft Notepad or exported from a spreadsheet application such as Microsoft Excel.
NOTE: ASCII (American Standard Code for Information Interchange) refers to "plain-text" which includes the 128 characters (numbers, upper and lower-case letters, and symbols) that are most likely found on your computer keyboard.
NOTE: Do not leave empty cells in the employee/new hires count section from the worksheet (columns Z to BS, if no employees/new hires for a job category, please enter zeros)
In general a CSV file does not require any padding the way a fixed length fields file does, however there are seven (7) fields of the VETS-4212 primary file and three (3) fields in the secondary file that does have a length/format requirement.
The seven fields in the primary file that require special formatting are the ending period, contact phone, zip codes, NAICS, EIN, and DUNS as shown in the spreadsheet image columns E, L, N, V, W, X, and Y.
To format the column in Excel you will first need to select the column you want to format in this case the ending period column E.
Next, right-click on the column and select "Format Cells..." from the menu.
This will open the "Format Cells" dialog box. You will need to select the custom category option at the left and for the type enter mm/dd/yyyy.
You will do the same for each of the other columns except you will enter the following type values for each column indicated.
- Columns L and V (Zip code): 00000
- Column N (Contact phone): 0000000000
- Column W (NAICS): 000000
- Column X (DUNS): 000000000
- Column Y (EIN): 000000000
The three (3) fields in the secondary file that require formatting is the zip code, DUNS, and NAICS which correspond to columns G, H, and I. Use the above type information to correctly format these columns.
Make sure you save these files as regular Excel files since they contain formatting information and will allow you to make any adjustments needed without redoing the same work.
Now that we have our data correctly formatted in Excel what do we need to do to correctly save the data as a comma separated values (CSV) file.
From the file menu select "Save As", this will open the save dialog.
Make sure to give your file a good name such as the company number and type of file. In the "Save as Type" make sure to select "CSV (Comma Delimited) (*.csv)" and click on the "Save" button.
This will open a second dialog box confirming that you want to continue to use the CSV format. You will need to click on the "Yes" button.
You can now close the Excel document but you need to make sure and select "NOT to Save" when prompted.
Now that we have tried to upload our batch files and received an ambiguous error page how do we figure out our file issue(s).
NOTE: All upper case is NOT required.
Here are the most common issues found with the formatting of batch files.
- Make sure the parent company and contact information exactly matches your system record.
- Make sure there is only alpha-numeric characters and only the following special characters
- Company Name: dash (-), period (.), ampersand (&), apostrophe (')
- Street Address: apostrophe ('), dash (-), period (.)
- Hiring location is required for Multiple Establishment Hiring Location (MHL) and Multiple Establishment State Consolidated (MSC) reports.
- Number of MSC Locations is required and can not be blank for Multiple Establishment State Consolidated (MSC) reports.
- Invalid number of column, either too few or extra commas at the end of the line making too many columns.
- Invalid values for contractor type should be S, P, or B but NOT spelled out.
- Blank lines with spaces or commas at the end of the file.
- Empty values in employment data (please make sure to enter zeros).
- DUNS, NAICS, FEIN, Zip Codes, make sure they have the proper lengths and if needed prefixed with zeros to give valid length.
- Contractor type should be S, P, or B; NOT spelled out.