Leroy and Sky Dawn

PM2.5 Filter QC Checks

Enter data from PM-10 Sampler Check Forms into the Every 5 Sampling Events Maintenance Check form of the toolbox. Enter data from PM-10 Sampler 4th Maintenance Forms into the Monthly QC Check form of the toolbox. Enter flow rate data from both of these types of forms into the Flow Rate QC Check form of the toolbox (there are a total of 4 separate flow rates records to enter in the “2008-Qtr1.pdf” file that you provided-two from the PM-10 Sampler Check Forms and two from the PM-10 Sampler 4th Maintenance Forms). Here are examples of how the data should be entered (you know more about your samplers than I do so feel free to change the values, such as data that I entered as unknown values, etc., as appropriate).

Data from PM-10 Sampler Check Forms:

Data from PM-10 Sampler 4th Maintenance Forms:

1

Leroy and Sky Dawn

Flow Rate data from both types of forms:

1

Leroy and Sky Dawn

Chain of Custody Data

Your chain of custody sheets do not provide the information that is needed by the toolbox chain of custody forms. You will need to get the required data for the toolbox from other sources. This includes your “PM10 Data 2008.xls” spreadsheet and Filter PM-10 Sampler Forms (field data sheets). For example, you can get the chain of custody number, dates the filters were sent and received, and field placement values from the “Master” worksheet of the “PM10 Data 2008.xls” spreadsheet. From the Filter PM10-Sampler Forms, you can get the date the sampler was placed in the field. Make sure to include all Filter IDs in the toolbox chain of custody forms, even those for filters that will be invalidated and blanks.

Note: In the “Master” worksheet of the “PM10 Data 2008.xls” spreadsheet, the “Date received from IML” value is before the “Ship Date from IML” for some of the filters. This is not logical.

Field Data Sheet

Enter data from your Filter PM-10 Sampler Form into the Field Data Sheet form of the toolbox.

Data Import for Filter Data from Run

  1. Tip for Step A:
  2. Save the “PM10 Data 2008.xls” file as “NewImport.xls” on your hard drive. Then open the “NewImport.xls” file. Click on the Master worksheet tab if you are not already in that worksheet. Using the Edit drop-down menu, Delete option, delete the entire row of data for any Filter ID that does not have Filter Run data (such as Set Start Date, Set Start Time, etc.) or you have not yet entered chain of custody data and field data for.
  3. Use the Edit drop-down menu, Delete option to delete the following columns. Delete the “Batch #” and “Ship Date from IML” columns. Then delete the columns to the right of the “filter number” column, beginning with the “Date received from IML” column, and ending with the “press” column (there should be a total of 15 columns in this range). Delete the three columns to the right of the “note” column, the “ID1”, “ID2”, and the first unnamed column. Then delete the column to the right of Set Start Date, the second unnamed column. Then delete all of the columns after the Status column. Save the file. Your NewImport.txt file should now look similar to this (there should be a total of 25 columns, Columns A through Y):

  1. Then use the Save As option of Excel to save the file as a CSV file on your hard drive. Once you have created the NewImport.csv file on your hard drive, close the file. You might get the following two message boxes. Click OK on the first one and Yes on the second one.

Change the name of the file from NewImport.csv to NewImport.txt. You might get the following message box. Click Yes on this message box.

Delete any blank rows of data at the bottom of the NewImport.txt file. The NewImport.txt file should look similar to this:

  1. Tip for Step D: Here is the correct field mapping:

  1. Tip for Step K(1): In the data file that you provided, there are 3 sample times (Filter IDs 416575, 416582, and 416756) that are out of range (less than 24 hours). There are 3 flow rate cv percentages (Filter IDs 416200, 416390, and 416575) that are out of range (greater than 2%). There are 3 flow rates (Filter IDs 416200, 416203, and 416390) that are out of range (0 LPM). You should qualify all the records with sample times, flow cv percentages, and flow rates out of range at this step. When you get to the Final Data Validation form of the toolbox, you should also invalidate these records.
  1. Click on the “Close Form and Clear Data Import Table” button after you have clicked on the “Clear Data Verification Table” button. Move the NewImport.txt file on your hard drive to a place where you will permanently store it. In this permanent storage location, you should change the name of the file to something that describes the data in the file. Then delete the NewImport.txt file from your hard drive.

Data Import for Filter Lab Data

  1. Tip for Step A:
  2. Save the “PM10 Data 2008.xls” file as “NewImport.xls” on your hard drive. Then open the “NewImport.xls” file. Click on the Master worksheet tab if you are not already in that worksheet. Using the Edit drop-down menu, Delete option, delete the entire row of data for any Filter ID that does not have a Net Weight , does not have a 24 hr ug/m3 STD value, or you have not imported filter run data for.
  3. Add a name of FilterType to the first unnamed column (the one containing poc1 and poc2 values). Use the Edit drop-down menu, Delete option to delete most of the columns since they have already been entered or imported into the toolbox. The following is a list of the columns you should NOT delete: filter number, FilterType, Net Weight MG, 24hr ug/m3 STD, temp, press, Total Sample Time, Average Flow, and Volumn. Your NewImport.txt file should now look similar to this:
  1. You need to add a column for air concentration units. Type AirConcUnits into cell J1. Type ug/m3 in cell J2. Use the Copy and Paste options of Excel to populate this field for all of the filters.
  2. Use the Edit drop-down menu, Replace option to replace all poc1 values with RO and all poc2 values with CO. Your NewImport.xls file should now look similar to this:

Note: We are importing the PM10 concentrations at standard conditions because EPA prefers PM10 concentrations at standard conditions for AQS submittal. Also I noticed that you used default volumes in the local conditions concentration equation. You used default flow rates and sample times in the standard conditions concentration equation. Instead, I suggest you use the actual values by referencing the appropriate cells in the equation. Then, the correct concentration will be calculated if the actual values differ from the default values. For example, in the file you provided, the local and standard concentrations for Filter IDs 416582 and 416756 are miscalculated since the volume and sample times are not the default values (I think these are QA samples so it does not matter so much in this case.).

  1. The toolbox requires a tare weight and a gross weight. For some of the Filter IDs, the tare and gross weights are included in the other spreadsheet file you provided named “Gila River 04 10 07.xls”. You need to add two new columns for these weights to the NewImport.xls file. Type “TareWeight” into cell K1 and “GrossWeight” into cell L1. Then copy the Tare Wt. and Exp. Wt. values from the Gila River worksheet of the “Gila River 04 10 07.xls” spreadsheet. Paste them into the NewImport.xls spreadsheet, making sure to match the Filter IDs. You will need to find the tare and gross weights for the other Filter IDs in this file in other spreadsheets since not all of the Filter IDs in the NewImport.xls file are included in the “Gila River 04 10 07.xls” file.
  2. Then use the Save As option of Excel to save the file as a CSV file on your hard drive. Once you have created the NewImport.csv file on your hard drive, close the file. Click OK on the first message box and Yes on the second one.
  3. Then change the name of the file from NewImport.csv to NewImport.txt. Click Yes on the message box about changing the file name extension.
  4. Delete any blank rows of data at the bottom of the NewImport.txt file. The NewImport.txt file should look similar to this.

Note: The zeros in the TareWeight and GrossWeight fields should be actual values in your NewImport.txt files. I used the zeros as placeholders since I did not have the spreadsheet with the actual values.

  1. Tip for Step D: Here is the correct field mapping:
  1. Tip for Step K: You do not need to click on the “Calculate and Update Air Concentrations” button since the air concentrations were imported from the NewImport.txt file. Therefore, this step can be skipped.
  1. Click on the “Close Form and Clear Data Import Table” button after you have clicked on the “Clear Data Verification Table” button. Move the NewImport.txt file on your hard drive to a place where you will permanently store it. In this permanent storage location, you should change the name of the file to something that describes the data in the file. Then delete the NewImport.txt file from your hard drive.

1