QCEW Open Data Access: Sample Code
QCEW provides a collection of CSV files representing logical slices of data. Data are sliced by industry, by area,
and by establishment size-class. The QCEW data slices can be directly accessed from various programming languages.
QCEW has made an effort to provide simple examples in several different languages. These examples are provided as is
and may be outdated. Some modification may be necessary to successfully retrieve data. These examples
are intended to be stepping stones for advanced data users and software developers: They are not
necessarily the optimal solution of their respective environments.
The QCEW open data files can be directly imported into an Excel Spreadsheet using VBA.
An example of this VBA code can be downloaded from the link below. The example workbook
is saved in the ".xlsb" format. You'll need to enable VBA Macros for this example to function
properly. For convenience, we have also provided a legacy version compatible with the Excel 97-2003 format.
Excel/VBA Example (Excel 2007-2013 .xlsb)
Excel/VBA Example (Excel 97-2003 .xls)
The QCEW open data files can be imported into SAS using the SAS macros defined in
this example. Each macro imports data as a SAS data set. This example can be downloaded
from the link below. The version below was tested with SAS 9.3 and may require modification
for different versions.
SAS Example
The QCEW open data files can be imported using the R programming language.
This example provides three functions that will import QCEW CSV files
that are stored on the BLS Web Site. Data are converted from CSV files to data frames.
A link to this example is provided below. This R Script was tested on R
version 3.1.0 and may require modifications for different versions.
R Example (RScript - R Language 3.1.0)
The QCEW open data files can be accessed from within Perl using a simple get request.
This example provides three subroutines that get CSV data from the BLS Web Site.
Each subroutine builds and returns a 2-dimensional array representing the rows and columns
of data in underlying CSV files. The header row is included as row zero. This
code targets Perl 5.20.0 and may require modifications for different versions.
Perl Example (Perl 5.20.0)
The QCEW open data files can be accessed from within Python using the "urlopen" command.
There are two links below. The first link provides an example
for Python 2.x and the second link provides an example for Python 3.x. Both versions
provide the same 3 functions. These functions get CSV data from the BLS Web Site and
convert it into a 2-dimensional array representing the rows and columns of the CSV
file. Headers are included in row zero. These two versions were tested specifically
with Python 2.7 and Python 3.3 and may require modification for other versions.
Python 2.x Example
Python 3.x Example
The QCEW open data files can be accessed from PHP. They can also be accessed from the
browser using a combination of PHP and JavaScript There are two examples below.
The first example provides three simple PHP functions that return data by area, industry,
and establishment-size class. Data are returned in 2-dimensional arrays representing the
rows and columns of the csv file.
The second example is paired with a JavaScript library to provide data access via
JavaScript in the browser. The jsdata.php file returns the content of the CSV file wrapped
in an object defined within the Qcew namespace. The qcew.js functions parse out the CSV blob,
create a 2-dimensional array representation of the CSV file, and pass the result to the
provided callback function. There is also a simple example.html file for reference.
Pay attention to the script 'src' attribute in the example.html file. It should be pointing to
your hosted version of qcew.js. Also, notice the "urlRoot" variable in qcew.js itself. This variable
should also be pointing to your hosted version of the jsdata.php file. All three files, qcew.js,
jsdata.php, and example.html are provided within the second example linked to below.
PHP Example
PHP & JavaScript Example
The QCEW open data files can be accessed from C#. There are two different links below.
The first link provides just the Qcew.cs file. This file defines a static class that exposes
three static methods. These methods get CSV data from the BLS Web Site and return populated
DataTable objects.
The second link provides a zipped console project. This project includes the QCEW.cs and a
few example calls within the Program.cs file. This project was created in Microsoft Visual
Studio 2010 and targets the .NET 2.0 Framework. This example should work with later versions
of the .NET Framework. However, some modification may be necessary.
Qcew.cs (Library Only)
C# Example Project
The QCEW open data files can be accessed using Java. This example provides a single class
with three static methods. These methods get CSV data from the BLS Web Site and
convert it into an ArrayList of String Arrays where each ArrayList item represents a row
and the elements of the String Array represent the values in each column of the CSV
file. Headers are included in ArrayList item zero. This example was tested with Java 1.7
and may require modification for other versions.
Java Example
The QCEW open data files can be accessed using ElectronJS. This example provides several options
to fetch and view QCEW data. It is intended as a roadmap for developers who are
building their own custom desktop solutions. This example uses the http and https modules
provided by the underlying NodeJS instance to fetch QCEW open data files. This example is currently
available for the Windows environment. However, future releases may include support for Linux
and MacOS. This example targets Electron version 3.1.1 and was tested with Windows 10.
It may require modification for other versions and operating systems.
ElectronJS Example
Last Modified Date: March 6, 2019
United States Department of Labor
