«NILE BASIN INITIATIVE Initiative du Bassin du Nil Information Products for Nile Basin Water Resources Management The ...»
Last but not least, if the user decides to suitably rename the output ASCII text files – as described in chapter 5 then the original names of all the respective ASCII output text files given in table 4 above will be different; in this case, each file name will have a three-letter prefix and underscore added to indicate the station name. To illustrate this by using the same examples as in chapter 5, the name of the file “metday31.txt” would change into “KAD_metday31.
txt” and so on.
6.3 Importing Output ASCII Text Files Generated by Report into MS Access This paragraph describes procedures for importing output ASCII text file “methrXX.txt” into MS Access file “NBD_Pre Processing.mdb”. The same or similar commands are applied for importing “metdayXX.txt” and “metrnXX.txt”.
Step D1: Open the MS Access file “NBD_Pre Processing.mdb”; consult paragraph 6.2 above to locate the appropriate folder/directory path of the file, which is country-dependent. In table view, the following database window appear The above view shows the 4 pre-defined tables as specified in table 4.
Step D2: Highlight the auxiliary table “Meteo_Hourly” and click “Open”. This results is illustrated in the following
In the above screen, the Meteo_Hourly table still contains data from a previous transfer exercise. Although primary key setting in the final NBD tables do not allow for data duplication, it is good policy to delete all records from the auxiliary tables, in this case Meteo_Hourly, prior to a processing new data set. To this end, continue with step 3.
Step D3: Select all records by simultaneously keying CTRL and A, or clicking Select All Records in the Edit menu.
The complete record set is now highlighted.
Step D4: Delete all records by choosing Delete Records from the Edit menu, or by pressing the ‘Delete’ key as
shown on the following screen:
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. Choose the sub command Import, as presented below.
Step D7: In the subsequent window, navigate to the folder containing the Report output files and select the concerned “methrXX.txt” file. Use the appropriate Windows Explorer operations. If the recommended directory structure is used, “methrXX.txt” is stored in folder C:\CAMPBELL\FILES\OUTPUT. Make sure the ‘Files of type:’ box below-left
is set to Text Files. The resulting screen is depicted below:
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.
Step D10: Select ‘comma’ and click ‘Next’.
The subsequent window (shown below) is used to define the output location for the imported data. In this particular case, the new information is to be imported into an existing pre-defined auxiliary table Meteo_Hourly.
Step D11: Check “In an Existing Table:” and navigate in the related list box to the Meteo_Hourly table. Click ‘Next’.
This constitutes the final step in importing the ‘methrXX.txt’ ASCII file into the appropriate pre-defined MS Access table. If no importing errors are encountered, the Text Import Wizard finishes the process by giving the following message.
The text file is now imported into the desired auxiliary MS Access table Meteo_Hourly. View the results by opening the Meteo_Hourly table in the database window. Check on the integrity of data, in particular, the datalogger ID values
and make corrections if necessary. The result is presented on the screen given below:
This step concludes description of procedures for transfer of the contents of the Report Output file “methrXX.txt” into the appropriate location in MS Access in preparation for final processing of data.
As already stated at the beginning of this paragraph, the import procedures for “metdayXX.txt” and “metrnXX.txt” are analog to the procedures presented above for the file “methrXX.txt”. To import these ASCII files into “NBD_Pre Processing.mdb”, the user is requested to change file and table names where and when necessary.
For easy reference, all steps discussed in this paragraph are again presented, in proper sequence, in Annex 4. This time however without accompanying comments and illustrations.
6.4 Processing of Imported Data in MS Access and Final Transfer into the NBD After being imported into an auxiliary table in MS Access, the new data sets are subject to the following processing
and final storage of processed data records into NBD:
• two separate text fields containing day, month and year respectively, have to be converted into a single date field;
• short datalogger ID (due to the logger’s memory configuration limited to a number below 255) has to be converted into an official numerical station identifier, for example in case of Hydromet-IDs consisting of 8 digits;
• automatic quality control: checking if the data values are within an expected range of variation;
• adding of ‘source’ value: indicating the origin of the data set;
• re-arranging column structure in accordance with the NBD database format.
• final automatic storage of the processed new data records into the appropriate NBD file and table.
For each data type, all these actions are automatically performed in one go with a pre-defined query. When the
Queries tab is activated in the database view, 3 pre-defined queries appear, as shown in the window below:
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.
For reference purposes, the general query design is presented in Annex 5. The function and design of the ID-Converter table, included in the various queries, is discussed in Annex 6.
Continue the data processing exercise by performing step 13.
Step D13: Finalize data processing by activating the appropriate query. For example, highlight the “Meteo_Hourly AWS” append query to process the newly imported data set from the “Meteo_Hourly” auxiliary MS Access table to the final “AWS Meteo_Hourly Data”. Click Open. By this operation data records in “Meteo_Hourly” table will be processed as appropriate and final results will be automatically stored/appended into the final NBD destination table “AWS Meteo_Hourly Data” table.
The following message box pops up.
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. It indicates that the processing operation is carried out but that any double rows will not be added due to ‘key violations’. Click Yes to perform the query to process the genuinely new data.
In order to visualize the final results stored/appended in the NBD destination table “AWS Meteo_Hourly Data”, locate this table in the appropriate NBD folder and file (consult chapter 6.2 for this purpose) and open the table in the database view.
Step D16: Highlight the “AWS Meteo_Hourly Data” table from the NBD file “Daily Clim Uganda.mdb”.
The following screen presents the fully processed data set in the NBD format. Note the new Station-ID, combined Dates, Time and Source fields uniquely identifying each record.
The above stepD16 concludes description of the procedures for data processing in MS Access and final storage of processed data records into the appropriate NBD destination table. Other meteorological data files can be processed by following the same procedures.
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. The processed files with file extensions “dat” and “txt” are no more needed to be stored in the sub-folders C:\Campbell\Files\Input and C:\Campbell\Files\Output, respectively, as the user can only confuse the processed files with new files which are yet to be processed in MS Access.
For this reason, all the processed files should be removed from the above folders immediately upon completion of their final processing in MS Access. For security reasons, however, these files are recommended not to be deleted but rather temporarily stored in the sub folder C:\Campbell\Files\Backup; once the user is sure that these files are no longer needed, they may eventually be deleted from hard disk or stored on other computer media if felt of interest.
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. For instance, this type of data transfer is foreseen to take place, on regular basis, between the regional offices in Bukoba and Mwanza; the Regional Office in Mwanza and Directorate of Water Resources in Dar-es-Salaam; the PWO in Kisumu and Water Development Department of the Ministry of Water Resources in Nairobi; as well as among the countries of the Nile Basin Region. A need for similar data transfers between the FPIs and other agencies may also occasionally arise.
The text below describes step-by-step procedures for preparation of these so-called transfer files as and when necessary for data exchange.
7.2 Preparation of Transfer Files Step E1: By running the appropriate append query in MS Access new meteorological data records will be processed and automatically added into the respective tables in the NBD (see chapter 6.4). Once this has been completed, open in the NBD folder the database file and the table which contains the data records targeted for transfer/exchange, and write down the NBD Station ID, the begin and end date of data records planned for transfer to other users. Use
a small table as shown below to write this information on a piece of paper:
Close the database table and file.
Step E2: Open a new database by checking the box “Blank Access database” by running the MS Access Programme. A new small window will appear on the screen, called “File New Database”. A default database file name “db1.mdb” is displayed in the “File Name” field of the window and a list of folders and/or files in the main window area. Proceed and select a folder in which the database file is preferred to be created; once the folder is selected, continue and change the default file name “db1.mdb” into a preferred file name which would subsequently be used for transferring selected data records to another user (either by e-mail or on a floppy disk). Also note that the extension “mdb” is a default extension for database files created in MS Access and should not be changed. Once the name of the new database file is typed in the file name field, complete the operation by clicking “Create” button and then by closing the new database file window; note that the created file is still empty. Remember, or better, write down the name of newly created database file and its location by using the Windows/DOS convention for path description.
As an example, let’s assume a user has opted to create the new MS Access database file named “Transfer.mdb” in
the folder “MyDbase”; the path of the file in this case should read:
Step E3: Open the appropriate main database file (for example the file “Daily Clim Uganda.mdb”) in the NBD folder where the historical and new data have already been processed and stored. From displayed list of available database tables in the selected file, select the appropriate target table (say the table “AWS Meteo_Daily Data” as an example);
check once again if the data records that are planned to be transferred to other users (as per the station ID, begin and end date of data records written down in Step E1) are indeed properly stored in the selected table. Once this has been verified, close the table (but don’t close the database file), and proceed with Step E4.
Step E4: Click on the Queries Tab in MS Access and then press “NEW” as shown on the screen below:
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”.