Kelly

QC Sheets

Enter any QC check data into the toolbox.

Data Import

  1. Tip for Step A: There are many things you will need to do to the BAMdataJulyAugSept.txt file in order to import it into the toolbox. I have listed the steps as follows. However, it might be possible to export the data from the BAM in a different format that is easier to use with the toolbox. You might want to consult the users manual of your BAM to determine if there are other type of file formats to export your data in or consider using a datalogger to generate files that are easier to use with the toolbox.
  2. First, import the data into a blank Microsoft Excel spreadsheet. Go to the Data drop-down menu of Excel, choose Import External Data, and then choose Import Data. Navigate to the location of the BAMdataJulyAugSept.txt file on your computer. Double-click on the file. In the box that comes up, choose the Delimited option, as shown. Then click on the Next button.

  1. In the next box that comes up, uncheck the Tab option and check the Space option in the Delimiters area, as shown. Then check the Finish button.

  1. Click OK on the next box that comes up asking you where you want to put the data. Your screen should now look like this.

  1. Save the file as NewImport.xls. Then make sure the cursor is in cell A1 and insert a new column. In cell A8, enter the word Date. In cells A11 through A34, enter the following formula: =E5. You need to manually enter this formula into each cell. Now copy cells A11 through A34. Put your cursor in cell A49 and paste the cells you just copied.
  1. Copy cells A11 through A72. Put your cursor in cell A87 and paste the cells you just copied. Repeat this process until you have dates in column A for all of your data. Note that the range of cells that you copy is always going to start with cell A11 and end with the last date in column A. Where you put the cursor to paste the cells needs to always be the first cell in column A adjacent to the 0:00 that does not have a date in it. This might seem tedious at first, but it took me less than 20 minutes to finish this step for all of the data in the spreadsheet. Delete any extraneous dates without corresponding times at the end of the file. Save the file.
  1. Put the cursor in cell A1 and insert a new column. Copy the entire column B. Put your cursor in cell A1. In the Edit drop-down menu, choose the Paste Special option. In the box that comes up, choose the “Values and number formats” option in the Paste section, as shown.

Then click the OK button of the Paste Special box. Then use the Edit drop-down menu, Delete option to delete column B. Save the file.

  1. In cell B8, change the word Sensor to Time. In cell D8, change Qtot to Concentration. In cell G8, change No to WindSpeed. Note: Something odd is going on with the wind speeds in the BAMdataJulyAugSept.txt file. If you open up that original file, and scroll down to the data for 7/27/07, you will notice that the header for wind speed moves from column 02 to column 05 between 7/27/07 and 7/28/07. The location of this header is inconsistent for several weeks. On some days, there are two columns with the wind speed header. I suggest that you investigate what is going on. If you are planning on importing your meteorological data into the toolbox, you should determine what column is the “real” wind speed before you delete columns in the NewImport.xls file. To demonstrate how things will go when you have determined this, I am going to pretend that this inconsistency does not exist and that all wind speeds are in column 02 of the BAMdataJulyAugSept.txt file, however, you should NOT make this assumption.

In cell K8, enter AmbientTemp. (Note that the headers were shifted to the left when we imported the data into Excel due to the column containing the ------not having a field name in the BAMdataJulyAugSept.txt file, however, we have corrected for this by renaming the columns that we are going to import into the toolbox.) Save the file. Your screen should now look like this.

  1. Use the Edit drop-down menu, Delete option to delete columns C, E, F, H, I, and J. Use the Edit drop-down menu, Delete option to delete rows 1 through 7, 9, and 10 (Click and drag to select rows 1 through 7. Then press the Ctrl key while using the mouse to select rows 9 and 10. This should select rows 1through 7, 9, and 10.) Your screen should now look like this.

  1. Click on the grey corner to the left of the letter A and above the number 1. This will select (highlight) the entire worksheet. Then go to the Data drop-down menu and choose the Sort option. In the box that comes up, choose the “Header row” option in the “My data range has” area. In the “Sort by” area, choose Date in the drop-down box. Then choose the Ascending option in this area. It is very important that you make sure that the worksheet is selected (highlighted), except for the Header row, before continuing. Your screen should look like this (note that the worksheet, except for the header row, is highlighted).

Then click on the OK button of the Sort box.

  1. Then, use the Edit drop-down, Delete option to delete any rows that contain dates without corresponding times. Then scroll to the last date in the worksheet. Use the Edit drop-down, Delete option to delete any rows that do not contain dates, but contain extraneous text and data in other columns. In the following example, the rows at the bottom of the spreadsheet that you should delete are highlighted.
  1. Save the file. Scroll to the top of the worksheet. Your screen should now look like this.
  1. 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.
  1. 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.
  1. Open the NewImport.txt file. Delete any blank rows of data at the end of the file. Your file should look like this.
  1. Tip for Step E: Begin by transferring concentration data from the Data Import to the Data Verification table. Here is the correct field mapping (note that we are leaving the Append To row for the meteorological data fields blank for now since we need to enter those separately).
  1. Tip for Steps J and K: Neither of the Date and Time corrections need to be run.
  1. Tip for Step L: Choose Conc. Units of mg/m3.
  1. Click on the “Enter Met Data or Other Pollutant Concentration Data from the Same Text File” button after you have clicked on the “Clear Data Verification Table” button.
  1. This will take you to the form for moving another pollutant or met data from the Data Import table to the Data Verification table (you do not need to complete Steps A through D of the help file since your file has already been imported into the Data Import table and contains meteorological data). Next, transfer meteorological data from the Data Import to the Data Verification table.
  1. Tip for Step E: Delete all of the fields from the Query Design Grid. Select all fields in the Field selector box and drag them into the Query Design Grid. Map the fields. Here is the correct field mapping (note that we are leaving the Append To row for the Concentration field blank since we already entered that data).
  1. Tip for Steps J and K: Neither of the Date and Time corrections need to be run.
  1. Tip for Step M1: The ambient temperature value for 6/28/07 at 2:00 PM should be qualified in this step since it is a null value. When you get to the Final Data Validation form of the toolbox, you should also invalidate this record.
  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