QCEW Open Data Access: Sample Code and Examples

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.


Getting Data with Excel & VBA

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)


Getting Data with SAS

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


Getting Data with R

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)


Getting Data with Perl

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)


Getting Data with Python

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


Getting Data with PHP & JavaScript

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


Getting Data with C#

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


Getting Data with Java

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



 

Last Modified Date: December 2, 2014