Importing the EPA Chemical Speciation Network (CSN) data

1. Follow all the general instructions on the Importing AQS Pre-Generated Data Files page, but using the following customizations:
   1. Source data: AQS Pre-Generated Data Files page --> Daily Summary Data section --> Particulates table --> PM_2.5 Speciation column --> "daily_SPEC_<YYYY>.zip"
   2. Local storage location: \\VADER\SourceData\AQS\Data\CSN\R<YYYMMDD> or \\VADER\SourceData\AQS\Data\CSN\Current
   3. Example command for transferring data from the source files:

      SELECT * INTO EPAPM25SD_2017_R20170905
      FROM OPENROWSET(
      	'MSDASQL',
      	'Driver={Microsoft Access Text Driver (*.txt, *.csv)};DEFAULTDIR=E:\SourceData\AQS\Data\CSN\R20170905\;Extensions=CSV;',
      	'SELECT * FROM daily_SPEC_2017.csv'
      ) -- (53,406 row(s) affected); took 6 seconds to complete

   4. Example command for creating the master working table:

      -- Drop any master table(s) left over from a previous import attempt
      IF OBJECT_ID('EPAPM25SD_1997_2017_R20170905') IS NOT NULL DROP TABLE EPAPM25SD_1997_2017_R20170905
      
      SELECT
      	*
      INTO EPAPM25SD_1997_2017_R20170905
      FROM ( -- Use the UNION'd SELECT commands generated by the previous script here:
      	SELECT * FROM EPAPM25SD_1990_R20170112 UNION
      	SELECT * FROM EPAPM25SD_1991_R20170112 UNION
      	SELECT * FROM EPAPM25SD_1992_R20170112 UNION
      	SELECT * FROM EPAPM25SD_1993_R20170112 UNION
      	SELECT * FROM EPAPM25SD_1994_R20170112 UNION
      	SELECT * FROM EPAPM25SD_1995_R20170112 UNION
      	SELECT * FROM EPAPM25SD_1996_R20170112 UNION
      	SELECT * FROM EPAPM25SD_1997_R20170112 UNION
      	SELECT * FROM EPAPM25SD_1998_R20170112 UNION
      	SELECT * FROM EPAPM25SD_1999_R20170112 UNION
      	SELECT * FROM EPAPM25SD_2000_R20170112 UNION
      	SELECT * FROM EPAPM25SD_2001_R20170112 UNION
      	SELECT * FROM EPAPM25SD_2002_R20170112 UNION
      	SELECT * FROM EPAPM25SD_2003_R20170112 UNION
      	SELECT * FROM EPAPM25SD_2004_R20170112 UNION
      	SELECT * FROM EPAPM25SD_2005_R20170112 UNION
      	SELECT * FROM EPAPM25SD_2006_R20170112 UNION
      	SELECT * FROM EPAPM25SD_2007_R20170112 UNION
      	SELECT * FROM EPAPM25SD_2008_R20170112 UNION
      	SELECT * FROM EPAPM25SD_2009_R20170112 UNION
      	SELECT * FROM EPAPM25SD_2010_R20170112 UNION
      	SELECT * FROM EPAPM25SD_2011_R20170112 UNION
      	SELECT * FROM EPAPM25SD_2012_R20170112 UNION
      	SELECT * FROM EPAPM25SD_2013_R20170905 UNION
      	SELECT * FROM EPAPM25SD_2014_R20170905 UNION
      	SELECT * FROM EPAPM25SD_2015_R20170905 UNION
      	SELECT * FROM EPAPM25SD_2016_R20170905 UNION
      	SELECT * FROM EPAPM25SD_2017_R20170905
      ) T1
      
      -- ^^ (41077009 row(s) affected); took 3:50 minutes
      
      -- Visually inspect the top rows of the composite table
      SELECT TOP 200 * FROM EPAPM25SD_1997_2017_R20170905

   5. Example Dataset registration command:

      USE AIRDATA_CORE
      GO
      
      EXEC bspRegisterDataSetItem
      	@DatasetItemName='EPAPM25SD_1997_2017_R20170905',
      	@DataSetID=20002,
      	@ProtocolID=2006,
      	@RevisionNumber=1,
      	@SourceName='EPAPM25SD_1997_2017_R20170905',
      	@SourceAcquisitionDate='9/5/2017',
      	@RemoteDataUri='http://aqsdr1.epa.gov/aqsweb/aqstmp/airdata/download_files.html#Daily',
      	@LocalDataUri='\\VADER\E\SourceData\AQS\Data\CSN\R20170905',
      	@ImportFormat='Text Files',
      	@RegistrationComments='Re-import of the entire AQS CSN dataset'

   6. Example Dataset import command:

      USE AIRDATA_IMPORT
      GO
      
      EXEC bspImportDatasetItem
      	@DataSetItemName='EPAPM25SD_1997_2017_R20170905',
      	@DestinationName='AirFact_EPAPM25SD_1997_2017_R20170905',
      	@ShowProgress=1,
      	@AbortOnSiteMappingError=0,
      	@AbortOnParameterMappingError=1,
      	@AbortOnMethodMappingError=1,
      	@AbortOnFlagMappingError=1,
      	@AbortOnUnitMappingError=1,
      	@KeepSource=1,
      	@KeepTempTables=1,
      	@UpdateStatistics=1,
      	@ImportedBy='SEM',
      	@ImportComments='Imported via stored procedure bspImportDatasetItem().',
      	@ArgKeys=NULL,
      	@ArgValues=NULL

2. Extract the CSN-only data from the newly-imported dataset into a separate table.
   Sometime in the recent past, the EPA stopped providing a CSN-only dataset on their Pre-Generated Data Files page and instead began combining all PM2.5 speciated data into a single Particulates --> PM2.5 Speciation dataset (see steps 4-5 above). For convenience and familiarity, we want to create a CSN-only dataset. This is done by selecting all the records from the result table that have a SiteID that exists in an auxiliary table that contains only the SiteIDs of CSN sites.

   IF OBJECT_ID('AirFact_CSN_1997_2017_R20170905') IS NOT NULL DROP TABLE AirFact_CSN_1997_2017_R20170905
   
   SELECT *
   INTO AirFact_CSN_1997_2017_R20170905
   FROM AirFact_EPAPM25SD_1997_2017_R20170905
   WHERE SiteID IN (SELECT DISTINCT SiteID FROM _CSN_Sites)
   
   -- ^^ (15,986,680 row(s) affected); took 10 seconds to complete; 2017.09.05

3. Check the results of the extraction.

   SELECT TOP 200 * FROM AirFact_CSN_1997_2017_R20170905 ORDER BY DatasetID, SiteID, FactDate, ParamID, POC
   
   SELECT MIN(FactDate) AS StartDate, MAX(FactDate) AS EndDate, COUNT(*) AS NumRecs FROM AirFact_CSN_1997_2017_R20170905
   --StartDate             EndDate              NumRecs
   --1991-09-04 00:00:00	2017-04-30 00:00:00  15986680

4. Export the updated Chemical Speciation Network (CSN) dataset to the production AirFact table using these instructions.
5. Add the Non-FRM PM_2.5 Mass (88502) parameter to the CSN dataset for years <= 2014
   1. Extract the CSN-sites-only data from the Non-FRM PM_2.5 Mass (88502) dataset into a separate table.
6. Add the FRM PM_2.5 Mass (88101) parameter to the CSN dataset for years > 2014