QCEW Flat File Helper Applications

Flat text files containing all data by geographic groupings are available via the special request FTP service available at ftp://ftp.bls.gov/pub/special.requests/cew/. In addition to annual data, these files have monthly employment and quarterly establishment and wage data.

These files include all published QCEW observations, including many which are not in the standard BLS database. We recognize that they are not easy to use. The QCEW program has developed several helper applications that can be used to import the flat files into common data manipulation applications such as Excel, Access, and SAS.

Helpers for:

Instructions on how to open ENB files into Microsoft Excel

  1. Unzip the excel file (makexls.xls) and open in Microsoft Excel
  2. Select Tools from the menu bar at top.
  3. Select Macro from menu, then select Macros
  4. Select MAKEXLS and then hit the “Run” button
  5. The macro will ask you click “OK” a couple of times
  6. Then it will allow you to select the ENB file you want to open.
  7. Another “OK” will appear-click it.

This will import the ENB data into excel. Be warned, though, that Excel can only open 65,536 lines. Files longer than this should be opened using our MS Access database tool, or your favorite database.

Instructions on how to open ENC files into Microsoft Excel

  1. Unzip the excel file (makexls.xls) and open in Microsoft Excel
  2. Select Tools from the menu bar at top.
  3. Select Macro from menu, then select Macros
  4. Select MakeENCXLS and then hit the “Run” button
  5. The macro will ask you click “OK” a couple of times
  6. Then it will allow you to select the ENC file you want to open.
  7. Another “OK” will appear-click it.

This will import the ENC data into excel. Be warned, though, that Excel can only open 65,536 lines. Files longer than this should be opened using our MS Access database tool, or your favorite database.

Instructions on how to open END files into Microsoft Excel

  1. Unzip the excel file (makexls.xls) and open in Microsoft Excel
  2. Select Tools from the menu bar at top.
  3. Select Macro from menu, then select Macros
  4. Select MakeENDXLS and then hit the “Run” button
  5. The macro will ask you click “OK” a couple of times
  6. Then it will allow you to select the ENC file you want to open.
  7. Another “OK” will appear-click it.

This will import the ENC data into excel. Be warned, though, that Excel can only open 65,536 lines. Files longer than this should be opened using our MS Access database tool, or your favorite database.

Back to top

Instructions on how to import EN* files into Microsoft Access 97

  1. Unzip the database (mkaccess97.mdb), then open it in Access.
  2. Click on the “New” button on the right of the box on-screen.
  3. Select import table and click “OK”.
  4. Set file type to text files, and enter under file name *.en*
  5. Select the data file you wish to import, then click “Import”. This will start the import text wizard.
  6. Click on “Advanced” at the bottom of the window
  7. Click “Specs” on the right side.
  8. Select the appropriate import specs (for example, “ENB Import Specification” for ENB files), click “Open”, then click “OK”.
  9. Click “Finish” in the bottom of the window and the data will appear as a new table.

Instructions on how to import EN* files into Microsoft Access 2000/Access XP:

  1. Unzip data file, change its file extension from ENB/ENC/END to TXT.
  2. Unzip the database (mkaccessXP.mdb), then open it in Access.
  3. Click on the “New” icon on the top line of the box on-screen.
  4. Select import table and click “OK”.
  5. Set file type to text files.
  6. Select the file you wish to import, then click “Import”. This will start the import text wizard.
  7. Click on “Advanced” at the bottom of the window
  8. Click “Spec” on the right side.
  9. Select the appropriate import specification. For example, for ENB files, select “ENB Import Specification”, click “Open”, then click “OK”. 10. Click “Finish” in the bottom of the window and the data will appear as a new table.

Back to top

Instructions on how to import EN* files into SAS:

  1. Unzip data file and save to a directory of your choice.
  2. Unzip the SAS code file (makesas.zip).
  3. Open the SAS input snippet (either cewenb.sas or cewend.sas depending on your choice of data file).
  4. Change the infile statement to point at the location and name of the file on your system.
  5. Save the snippet.
  6. Paste the snippet into a SAS program of your choice.
  7. Run as needed.

Back to top