Manual Data Retrieval, Processing and Final Storage into the Nile Basin Database Entebbe October 2001 NILE BASIN INITIATIVE Initiative du Bassin du Nil Information Products for Nile Basin Water Resources Management www.fao.
The designations employed and the presentation of material throughout this book do not imply the expression of any opinion whatsoever on the part of the Food and Agriculture Organization (FAO) concerning the legal or development status of any country, territory, city, or area or of its authorities, or concerning the delimitations of its frontiers or boundaries.
Table of Contents Table of Contents 1 Introduction 9 1.1 General 9 1.2 Overview of the Contents of the Manual 9 2 Detailed Outline of the Data Trajectory 11 2.1 General 11 2.2 Hardware 11 2.3 Software 12 2.4 Comprehensive Data Trajectory 13 3 Installation of the Remaining Hardware and Software 15 3.1 Installation of SC532-A Interface 15 3.
Table of Contents 5.3.2 4 Processing Data from Met Station 5.3.2.1 General 5.3.2.2 Five-Minute Rainfall Data 5.3.2.3 Hourly Meteorological Time Series 5.3.2.4 Daily Meteorological Time Series 5.3.2.5 Meteorological Station Performance Information 5.3.2.6 Renaming Meteorological Output Files 6 Data Processing in MS Access and Final Storage into the NBD 39 6.1 General 39 6.2 MS Access Database “NBD NEW DATA” 39 6.
List of Figures List of Figures Figure 1: Figure 2: Figure 3: Figure 4: Figure 5: Hardware components involved in data acquisition process Software components and file formats involved in data acquisition and storage process Recommended directory structure for PC208W and related “work” files Required Folder (Directory) structure for all MS Access database files Final data processing steps in MS Access and subsequent removal and storage of raw files as backups 11 12 16 17 39 M
List of Tables List of Tables Table 1: Table 2: Table 3: Table 4: 6 Comprehensive Data Trajectory Various Files and their Function Used by Report in the Processing of Meteo Station Measurements Database Objects and their Function in the MS Access Database file ”NBD_Pre Processing.
Annexes Annexes Annex 1: Annex 2: Annex 3: Annex 4: Annex 5: Annex 6: Consecutive Steps in Connecting Storage Module SM4M/SM192 to PC. Consecutive Steps in Retrieving Raw Data Files form SM4M/SM192 Storage Module to PC. Consecutive Steps in Processing Raw Data Files in Report. Consecutive Steps in Pre-Processing of Report Output Files in MS Access and Appending Processed New Data Sets into NBD. General Design of the Append Queries for Pre-Processing of the Report Output Files in MS Access.
Foreword Foreword FAO regional project GCP/INT/752/ITA – Nile Basin Water Resources is committed in establishing a limited water resources monitoring network in the Nile Basin region. The adopted monitoring network consists of 21 meteorological and 14 hydrological stations to be either rehabilitated or newly implemented at key points in the Nile Basin.
Introduction Introduction 1.1 General The adopted Monitoring Network in the Nile Basin comprises of Automatic Hydrological and Meteorological Stations equipped with electronic instruments for data acquisition and storage. Measurements are initially pre-processed and stored on site in a digital storage and control module, commonly referred to as datalogger. Periodically, the accumulated raw data are transferred from datalogger to a computer at the office of the hydrometeorological service.
Introduction location of the concerned files on the PC’s hard disk drive. Chapter 5 deals in detail with retrieving the raw data from the storage module to PC, and the initial processing and storing this new information into a file in a comma separated ASCII format for each strictly defined group of data.
Detailed Outline of Data Trajectory Detailed Outline of Data Trajectory 2.1 General 2.2 Hardware This chapter gives an overview of the complete data trajectory, starting from values measured by the station’s sensor all the way till the final storage of data into the Nile Basin Database (NBD) on PC at the FPI. It presents the various hardware and software components involved in this process, together with their interconnection.
Detailed Outline of Data Trajectory thereafter to transfer/upload the raw data to the Personal Computer at the office. Information is stored in the module in a specific format, which cannot be read directly by IBM compatible PCs. The role of the SC532A or SC932 Interface is, therefore, to convert this specific storage module format into a RS232 compatible information communication standard, which is ‘understood’ by IBM compatible PCs.
Detailed Outline of Data Trajectory The datalogger works under a specific Campbell Scientific operating system and is controlled by the METSTAT datalogger program for meteorological stations. These programs are specifically created by the project GCP/INT/752/ ITA – Nile Basin Water Resources for the sensor configuration and data requirements in the network established by the project.
Detailed Outline of Data Trajectory Table 1: Comprehensive Data Trajectory Step 1 Operation ~ Periodic automatic measurement of meteorological parameters ~ pre-processing and storage in the datalogger’s RAM ~ Transfer of accumulated measurements from logger’s RAM to portable SM4M/SM192 Storage Module; each single transfer exercise results in a separate data file Hardware Involved ~ Sensors ~ Datalogger Software Involved ~ Campbell Scientific Datalogger Operating System (OS) ~ METSTAT datalogger program
Hardware and Software Installation Installation of the remaining Hardware and Software 3.1 Installation of SC532A Interface Provided a PC has already been set up successfully, only one hardware item remains to be installed: the SC532A Interface.
Recommended Directory Structure Recommended Directory Structure 4.1 General 4.2 PC208W Files For standardization and instruction purposes, this chapter proposes a Recommended Folder (Directory) Structure. The user is free to modify this if another system would better suits his or her particular computer organization. However, the instructions presented in this manual are based on the proposed structure. Modification of it will require corresponding adjustment of commands concerning file locations.
Recommended Directory Structure E xploring - Campbell View Edit File Go Favorites t Foird Back Address Up Tools y Cut iff Help Copy Paste Delete Properties CACampbell 411111.1111 Folders Floppy x A. I Name Size Type File Folder 1:11 Files File Folder P.1=0 (C:) A.crobat3 uackup ti Campbell MINN 1 Backup Iriput Output -Li' Parameter Pc21:18w Lc V udr orn 2 object(s) 4 0 bytes (D isk free space: 3.
Recommended Directory Structure 1111 Exploring Edit File HyD baso View , Back .1 G Favorie F . ocl Herp yr) . Up Cut 73 Paste Copy I 2') ' Lindo D,E4ete Properties Address Iù C:\ MjDbase Name IFolders Carnpbel II Size t TIpe LL1 N td File Fcitier rf_l NED N Dyq DATA File FcidEff jJ Pc2CIDAI Di ro rn p..111 D1.7.1 8..L1 Esri Ki-lan 1 .es) M9 Document': 1=1..) 12121212 N bi l33.-Li NED NEW DATA 12 cUrct(sil 18 1 4 I lO bytes ÍDik hot spoce...: 9.
PC208W Datalogger Support Software PC208W Datalogger Support Software 5.1 Introduction PC208W Datalogger Support Software is a Windows based software package specifically developed by Campbell Scientific for communication between datalogger and PC. It consists of a help module and 7 independent datalogger support components. Among them are also the utilities for setting up a network of PC connected stations and on-line datalogger communication.
PC208W Datalogger Support Software The following toolbar appears: Step A4: Click the StgModule tab. A window alike the one presented below shows up. The menu-bar contains five different items: File, Options, Data, Tools and Help. Just beneath this line 4 different tabs are found, named CSM1/MCR1, SM192/SM716, PCCard and SM4M/SM16M, each dealing with a different storage medium.
PC208W Datalogger Support Software SMS File Data Options Tools Help 5M192/Sty171 csk.11.4.1C:R1 SM4M1SM161.11 F'C C:ard StatusBox Module Pointers FCOM Port Setup I Free Space port CONell Baud Pate COM2 ',-52UU MI" COM3 C COM4 EVialcplox Target Address C (-6 (11 (-4 c-7 Setup!, F'rograms r5 Dat. Module Values Good FLASH blocks Error Count Programs Switch Settings (-k C2 Storage Ref Pointer Display Pointer DumpPointer r ad/MIL EraeJ L 7Status Advanced...
PC208W Datalogger Support Software SMS File Data Options Tools Help SM4M/SM16M F'C Card tyil 92/5 tyi 71 IS /tyi CFI 1 CS tyi StatusBox Module Pointers Free :pace COM Port Setup P 0 rt 0M1 Baud Pate COM2 1j Y11_11_1 COM3 2069180 Storage Ref. Pointer Display Pointer DumpPointer 27841 2 25446 Module Values Good FLASH blocks Error Count Programs COM4 64 o 2 E Via 1 f plox Target Address CA 1 2 (3 (4 r Switch Settings r 76800/9600 Baud Ring Mode Address 1 (-7 5 CO 5tatusAAd.
PC208W Datalogger Support Software rI SMS File _Options Data Tools Help .SM1S2/9ty1716 CStyl1/MCR1 File Format Status E; Module Pointers C ornma separat e d Free Space Storage Ref. Pointer Display Pointer DumpPointer r ASCII with Arra.,.. ID's r As Stored File Naming Options [Auto Name Control Auto Increment Name New Name for Each File 64 o 2 r Switch Settings 7680019600 Baud \Setup ,/,,Prograrn.
PC208W Datalogger Support Software File Options Data Tools Help Sty1192/Stvi716 SM4M/SM16M Gard iStatusBox l (Module Pointers File Format a. Comma separated CuAiS with Array ID's C As Stored Next Detaille Name Save in: File Naming Options Data031.tiat Auto [..lame Control 151 _q Input Data001.dat I] Data007.dat 141Data01 adat 2 Data002.dat Data002.dat Data0Oadat Data009.dat Dato004.dat Data1:110.dat 6" Auto Increment Name r Append ti: Current File Data005.dat Data011.
PC208W Datalogger Support Software Dump Pointer: The Dump Pointer is an internal pointer used for keeping track of the current ‘start-of-dump’ for module-to-module data dumps. It indicates the first location from where new data should be collected. It is a function of the software to move this pointer after each successful data collection.
PC208W Datalogger Support Software 5.2.6 Trouble Shooting Problem 1: SM4M does not respond by clicking the “Connect” button and is giving the message “Standard Prompt Not Detected”. Cause: 1: Cables are not properly connected 2. Power supply 3. SC532A interface is not properly connected 4. Problem with the COM Port or wrong SMS “Setup” Remedy 1.1: Check if the cables are connected firmly and if they are according to the following diagram Remedy 1.
PC208W Datalogger Support Software Due to the logger’s memory configuration, all four groups of data time series are written to the same storage area in the system’s RAM in the order of their occurrence (time of recording), and consequently downloaded from the datalogger directly via short haul modems or storage module to a PC as one single, rather unorganized raw data file.
PC208W Datalogger Support Software Matim ita Split Version 1.7 File Labels E dit Run F'rinter H elp ¡Input File(s) liutput File Input Data File- . Hie Irlo Browse ME -liti Start I-._.,:inion ,Jr _ i I Auto Detect 1 T im 11 Offsets A 1 Stop Condition- Fd I Copy Select . i 1 Step C2: Choose Open from the File menu to activate a pre-defined parameter file, as indicated in the following window. Split Version 1.
PC208W Datalogger Support Software Step C3: Navigate to the location on hard drive, which contains the parameter files. This is “C:\CAMPBELL\FILES\ PARAMETR” if the Recommended Directory Structure is used. Highlight the file “metrain.par”, as shown on the window below. Click OK. Split Version 1.7 File Labels Edit Run F'ririter Help Input File(s) Output File Input Data File-Open Browse File name: Folders: metrain. par c:\campbelltfilestparametr 'citart Condtion c:\ metday. par methour.
PC208W Datalogger Support Software WS. plit - METRAIN.PAR Labels dit Run Printer Help Input File(s) Output File At Data File 13 Select Input File Browse - art Condition File name: Folders: data031.dat cAcampbell1filesIinput OK I data031 dat data032.dat data033.dat data034.dat data035.dat data036.dat data037.dat data038.dat op Condition c:\ 116. campbell files Network... ?.j input List files of type: Drives: IData files (.DAT) .3,Date(4;3)6 ICancel c: LJ TA016.
PC208W Datalogger Support Software Step C6: In the File Name text box of the Selected Output File sub-screen, type in the name of the appropriate output file. Apply the following naming format: “metrnXX.txt” Make sure that the XX - serial number of the output file – is the same as of the respective raw data file. For instance, give the name “metrn31.txt” to the output file if it is being extracted from “data031.dat” raw data file. Click OK.
PC208W Datalogger Support Software S plit R un 1.7 Hyetograph LoggerID Year Date Time [ -] [ -] [ /MD] [H-1121] [mm] 5 9 7 2001 9 9 2001 9 9 5 2001 eading files completed. 1450 .1 5 125.5 .1 1300 .1 Lines Re 97 j Lines Written 3 Pause Rain Mk.
PC208W Datalogger Support Software c....Naschwyfic - View - CACampbelltFilestOutputtmettn31.txt File Vie.o.e Help g fF All arrays 17 7 5,2001,9 7,1450,.1 5,2001,9 9,1255,.1 5,2001,9 9,1300,.1 This concludes the user activities in Report with respect to the extraction of 5-minute rainfall time series data from the respective raw data input file downloaded from storage module. For ease of reference, the above-presented steps are listed in Annex 3. 5.3.2.
PC208W Datalogger Support Software Split - METHOUR.PAR File Edit !In Labe Printer e Output File Input File(s) Output Data Select Output File File Browse File name: methr31.txt CAM P I Folders: OK I Cancel I Network... I cAcampbellfiles\output 1.7. Screen Display c: campbell Report and Column Heac ?-A files Report Heading Column II output = 1 Elerrient/Fieldt# 2 Filename C:IATAID1 Line 1 Log_ID Line 2 [-] List files of type: lOutput files (r.
PC208W Datalogger Support Software For each full hour the Met station has been operational, the following hourly meteorological data are extracted from raw input data file: station datalogger-ID, year, date and time, average hourly air temperature, vapor pressure, vapor pressure deficit and wind speed; and total hourly solar radiation. The first four values (i.e. ID, year, date and time) guarantee unique identification of each array. The associated output file is in comma separated ASCII format.
PC208W Datalogger Support Software 5 Split - METDAY.PAR File Edit Run Labels Lifts! Printer Help Output File Input File(s) Output Data File Select Output File Browse I __1. File name: Folders: metday31.txt c:Icampbell\files\output 1.7 Screen Display Cancel A c:\ A campbell .rReport and Column H Network... A files Report Headir g Columnii OK I output 11 E len-lent/Field# Filename DAT: Line .1 Loa Line 2 [ DA List files of type: Drives: !Output files (.
PC208W Datalogger Support Software For each day the Met station has been operational, the following daily meteorological data are extracted from the respective raw input data file: station datalogger-ID, year and date, average, maximum and minimum daily air temperature, average vapor pressure and vapor pressure deficit; total daily rainfall and solar radiation; average daily wind speed and direction and standard deviation of the wind direction. The first three indices (i.e.
PC208W Datalogger Support Software There is no need to archive the output file containing the station performance information. After assessment of the station performance indicators (which will lead to a decision to either take or not take an action) this information can be printed out for records and the respective file deleted thereafter. 5.3.2.
Data Processing in MS Access and Final Storage into the NBD Final Processing in MS Access and Final Storage into the NBD 6.1 General Final destination of the meteorological data obtained from the Automatic Weather Stations in The Nile Basin Monitoring Network is the NBD: The Nile Basin Database developed in MS Access.
Data Processing in MS Access and Final Storage into the NBD This database file (NBD_Pre Processing.mdb) contains several pre-defined objects including 4 tables and 3 process-append queries. The database tables and queries names are listed in table 4 together with a brief description of their function. As indicated by their numbering, the various database objects listed in table 4 are divided into three different groups.
Data Processing in MS Access and Final Storage into the NBD the actions for importing ASCII text file “metdayXX.txt” into MS Access are equivalent to those for “methrXX.txt” and “metrnXX.txt”, only table and file names differ. The same applies for running the various queries and adding the fully processed data to the NBD. It is because of this fact, and the wish to avoid redundancy, that this manual presents the detailed user instructions for only one member of each data type.
Data Processing in MS Access and Final Storage into the NBD Meteo_Hourly : Table Datalogger-ID Time Year 200195 1500 1800 /11 Avg-VP 20.84 20.15 19.86 2.253 2.239 2.222 2.137 2.185 2.179 2.232 2.196 2.089 2.059 1 -J..36 .L.1_11 _1b 27.541 - 21101 9 !r-1 1700 25.86 25.76 2001 9 5 ?OW 9 5 1800 24.41 1900 T.,001 9 F, 2000 23.05 21.6 5 2001 9 F., 5 5 2001 9 ?OW 9 2100 2200 5! T)001 9 F, ?001 9 5 L W] Record: H Avg-T 20 .i.
Data Processing in MS Access and Final Storage into the NBD Step D5: Close Meteo_Hourly table. The auxiliary Meteo_Hourly table is now empty. No data duplication will occur during data import process unless the user imports a certain data set twice. Although this would eventually be refused by MS Access due to ‘key violation’, it is recommended not to enter this situation. Continue with step D6. Step D6: In the database window, select Get External Data from the File menu.
Data Processing in MS Access and Final Storage into the NBD Import Look in: :j ji C_Aitput - Toots metday2 metday31 Methr16 rnetprf 1 Metorf 13 , metprf 14 Metorf 16 , Metorf20 Metprf28 metrn2 metrn31 g] cf fOOrnh File name: Files of Ipport zJJ Text Files Step D8: Click Import. The following Text Import Wizard pops up. BEI Import Text Wizard 'Your data seems to be in a 'Delimited format. If it isn't, choose the format that more correctly describes your data.
Data Processing in MS Access and Final Storage into the NBD The Report output files are comma-separated and thus fall into the category ‘delimited’. This has been done on purpose since importing this format into MS Access has proved to be straight forward and flawless. Continue with step 9. Step D9: Select ‘Delimited” and click ‘Next’. The screen illustrated below appears; it shows how the imported text will be divided into various columns according to the applied delimiter.
Data Processing in MS Access and Final Storage into the NBD Import Text Wizard You can store your data in a new table or in an existing table, lyVhere would you like to store your data? C In a Nev.) Table 6. In an Existing Table: ID Converter Meteo_5rninRain 5 2001 ; 5 1500 27.54 2.253 1.429 2.782 1.388 5 2001 35 1600 25.86 2.239 1.095 1.423 1.38 5 2001 35 1700 25.76 2.222 1.094 .94 1.244 5 2001 351600 160n 24.41 2.137 .921 .294 1.04 5 2 n01 =.1 5 1q0n 23.n5 7. 1E;5 .534 5 2001 35 2000 21.
Data Processing in MS Access and Final Storage into the NBD E Meteo_Hourly : Table Datalogger-ID Year 5 5 MM-DD Time Avg-T -7)001 9 8 801D ?I:101 9 8 91DCI T.'001 9 8 1000 2001 9 8 2001 9 .11 w 1 19.13 21.83 17)00 23.47 24.85 ?5.5? 200198 !=, 2001 9 8 1400 26.91 5 2001 !=.i 8 .151:10 '7)001 9 8 buu 7)001 9 8 .171_11_1 2001 9 8 16uu T.'001 9E: 1900 '7)001 9 Ft .7.11:11:11:1 27.5 27.8 27.55 26.42, 24.63 24.
Data Processing in MS Access and Final Storage into the NBD - ITN NBD_Pre Processing : Database [1:112pien Vtg, Design Objects Nev..' X - X *-15- 4-4- Create query in C:iesign Create query by usinq rg &IMP' 13 For Meteo_5minRain AW5 AWS Meteo_Hourly Reports Pages Macros Modules Table 4 presents the input and output tables for each of the above queries. AWS stands for Automatic Weather Station, which have been established in the region within the GCP/RAF/304/JPN and GCP/INT/752/ITA Projects.
Data Processing in MS Access and Final Storage into the NBD Step D14: Click Yes. A message box similar to the one below follows. Step D15: Click Yes. This steps finalizes the data processing in MS Access. The destination NBD table is by its primary key settings protected against duplicate records. If a certain record would be added for the second time, the following message box shows up.
Data Processing in MS Access and Final Storage into the NBD Oil Daily Clim Uganda : Database 12pen W.7., Design ."'m Nleke..1 qi Create table in C:lesign view ri Rainf all Uganda r:A- Create table by using t.....lizard El Relati.....e Humidity ._.ganua EI Source Sunshine Uganda qjCree table by entering data Ffl AWS Meteo jrnin P.airl Data E1 III AWS Meteo_Daily Data IIJ'yVind Run Uganda El AWS Meteo_Hourly Data De.'.' Point Temperature ._.ganda El Dry Bulb TernperaWre ._.ganda El ._.
Data Processing in MS Access and Final Storage into the NBD 6.5 Transfer of Raw Input and Report Output Data Files to Backup Folder Once the meteorological data series are successfully processed in MS Access, the raw data files of the type “dataXXX. dat” and the respective extracted output ASCII text files, processed by using the Report utility (see chapter 5.3 of the manual), have for all purposes become obsolete.
Exchange of NBD Data with other users Exchange of NBD Data with other users 7.1 Problem Description The user may occasionally be in need to transfer some data from the main NBD database to other departments or agencies for their further use. To do so, one may require creating a smaller “Transfer” file with such data, which can either fit on a floppy disk or can be distributed by e-mail.
Exchange of NBD Data with other users au Transfer : Database 410sen esign P.Iew D Create query in Design view Objects 1;_li Create query by IJSing 5 Form Repor Page Macros Modules Groups A small window called “New Query”, will pop up on the screen with the default option “Design View” automatically highlighted. As this is exactly the option we need, proceed and click on “OK”. A second small window, called “Show Table”, will appear on the screen.
Exchange of NBD Data with other users The view shown above is called “Query 1: Select Query”. It is in this view where the user has to select/design a query which will look into the specified database table and select the data records targeted for transfer/exchange.
Exchange of NBD Data with other users Queryl Select Query IPTITr- 061 Station-ID Date 'Source AmT f...lin-T H$MKEIMMINSMINMEIMMEIMMINSMKIMMEINEMOMKORMKKIMMEIMMINSMIR Field: :ation-ID Nleteci Daike D r,ort: Show: Criteria: or: 9 :132901 Date Avc-T Source Mebao Daily Data e en # 401.
Exchange of NBD Data with other users selected data from any main database file/table into the newly created file as described in the steps E10 and E11. Data stored in the “Transfer” file can now easily be transferred to other users either by e-mail or on a floppy disk. If there is a need to transfer data from more than one station, and for different data records, the user should just repeat the steps described above but for other stations and/or different data records.
Annexes Annexes Annex 1: Annex 2: Annex 3: Annex 4: Annex 5: Annex 6: Consecutive Steps in Connecting Storage Module SM4M/SM192 to PC. Consecutive Steps in Retrieving Raw Data Files form SM4M/SM192 Storage Module to PC. Consecutive Steps in Processing Raw Data Files in Report. Consecutive Steps in Final Processing of Report Output Files in MS Access and Appending Processed New Data Sets to NBD. General Design of the Append Queries for Pre-Processing of the Report Output Files in MS Access.
Annexes Annex 1: Consecutive Steps in Connecting Storage Module SM4M/SM192 to PC. Annex 1: Consecutive Steps in Connecting Storage Module SM4M/SM192 to PC. Step A1 A2 A3 A4 A5 A6 A7 A8 A9 Action Connect the SM4M Storage Module to the SC532A Interface using the blue SC12 cable. Make sure the SC532A Interface is powered. On PC, double click the PC208W Datalogger Support Software Icon. Click the StgModule tab. Click the SM4M/SM16M tab at the top-right corner of the SMS window.
Annexes Annex 3: Consecutive Steps in Processing Raw Data Files in Report. Annex 3:Consecutive Steps in Processing Raw Data Files in Report. Step C1 C2 C3 C4 C5 C6 C7 Action Activate Report by clicking the Report tab on the PC208W toolbar. Choose Open from the File menu to activate a pre-defined parameter file. Navigate to the location on hard drive, which contains the parameter files. This is “C:\CAMPBELL\FILES\PARAMETR” if the Recommended Directory Structure is used.
Annexes Annex 5: General Design of the Append Queries for Final Processing of the Report Output Files in MS Access. The function of the pre-defined append queries is to perform the final processing steps in MS Access.
Annexes Annex 6: Design of the ID Converter Table The ID Converter table serves to transfer the datalogger IDs to official station identifiers. Due to memory allocation, the datalogger ID is a short integer between 1 and 254, which does not allow for proper station numbering in a comprehensive hydrometeorological network. The following screen presents the design of the ID Converter table.
