How To Capture Water Quality Results Electronically on WMS - Contents Page

How to Capture Water Quality Results Electronically on WMS ? Chapter 2

INTRODUCTION

Chapter 2: Preparing data for import

This chapter will tell you how to export data in a .CSV format and check the files to ensure the correct data formats are used.

Next: Chapter 3: Loading data

Check spreadsheet for required information

Before you save your spreadsheet in a .CSV format, ensure that the following are correct:

Heading Record

If you used the ?fixed? electronic import template provided to you and used the heading record (copied and pasted) this information into your specific data capture templates then it just a matter of checking if all the fields are present and in the correct order.

If the heading is not correct you will not be able to import the file. If incorrect information is entered in a column then the data will be imported incorrectly.

Date and Time formats

If you use date and time formats different to the WMS standard then your date and time will be incorrect after the import has completed.

The following Date format is correct i.e. ?yyyy-mm-dd?.

The following Time format is correct i.e. ?HH:MM:ss?

Tip: You can ensure that MSExcel keeps your dates in the correct format if you change your settings to the right format in your Windows ?Regional and Language Options? in the ?Control Panel?. If you do not know how to do this, then log a call to your local IT service provider.

You can also use the MSExcel ?Format?? ?Cells? function to change the column format to the right date or time format.

All the date and time fields must in the correct format. If you apply the date format and it does not change to required format you have to retype it.

Minimum required fields

The following fields must have entries:

Monitoring point: SamplePointCode or SamplePointDesc or WMS_pointID

Depths: StartDepth; EndDepth

Sampling Date & Time: SamplingDate; SamplingTime

Monitoring Variable: Constituent

Numeric Value: Result

Enter all the fields if possible, because you will have fewer Sample and Results Administration Problems to resolve afterwards.

Redundant empty records

It is possible to create empty records and fields in MSExcel that are not visible to the eye. These records and fields will be included when you export data in the .CSV format. Empty records prevent completion of the WMS import process.

To remove such records, click on the record below the last entry so that the whole record is highlighted. Hold the left mouse button in and drag down until a number of empty records are highlighted. Right click on the mouse, select the delete option and enter.

Do the same on the right of last entered column. Delete a number of columns.

Converting your spreadsheet to .CSV format

Under the ?file? menu select the ?save as? option. Give a proper name and open the ?save as type? dropdown. Choose the ?CSV (MS-DOS) *.csv? option. Press the save button.

A warning comes up that says that all functions in MSExcel will not be saved in .CSV format. Click the Yes button.

Close the form and answer No to the Save the file prompt.

Checking your .CSV file format

As a last step re-check the above checks by using Notepad. Do the following.

• Open the folder containing the .CSV file
• Click once on the file
• Right click and select the open with option
• When the file opens in notepad you will see your data demarcated with commas (,) and text enclosed in quotes (??).
• Do the checks.
• If incorrect, redo the steps described above in your MSExcel spreadsheet.

When this process is completed satisfactorily and the record in the data provider file is updated the datasheet the file is ready to be imported as described in Chapter 3: Loading data