Using an excel (.xlsx) file for weather (met) data.
Traditionally the met file format is a ‘.csv’ file, in the format shown below:
It is possible to store met data in an excel ‘.xlsx’ file. The format of the worksheet is represented below.
Each excel file can contain multiple worksheets, and they don’t all need to contain met data. This allows for weather data to be combined with other relevant data, into the one file.
Converting met data to the excel format:
1. Copy and Paste the met data into excel. If your file doesn’t look like (ie, it is automatically split into columns), this then you may need to close excel and start again.
Starting with the weather data:
2. Click on the cell that contains the ‘year’ (A8), hold down the Shift, Control and the Down Arrow to highlight all of the weather data.
3. Click ‘DATA’ on the ribbon menu (at top), and select ‘Text to Columns’. Select ‘Delimited’, click Next, change delimiters to be ‘Space’, check that the Data preview looks correct, and then click Finish. The data should now look like the following:
4. Measurements should be displayed after the heading which they apply to. The following shows how the data titles and measurements should look after they have been converted.
Converting the Header information:
1. Highlight from A1 to A6, click ‘Text to Clolumns’. Select ‘Delimited’, click Next, change delimiters to be ‘Other’, and type an equal (‘=’) in the space provided, and click Finish:
2. Edit the ‘latitude’ and ‘longitude’ rows (3 and 4) so that the ‘(DECIMAL DEGREES)’ is moved to be after the title, and the values are on their own in column 2:
3. Edit the ‘tav’ and ‘amp’ rows so that the measurement is displayed with the title (name), and that the commenting, including the exclamation (!) is in column 3. Column 2 should only contain the values.
The worksheet should now be complete:
Unlike the ‘.csv’ files, the column alignment and spacing is not relevant. The columns have been widened only to show all of the data in them.
Blank rows with be ignored when reading the data. Any Rows with nothing in Column A will be treated as a blank row.
For Constants and other header details, the Name (or Title) must be in Column A, following by the measurement in brackets, if measurements are wanted. Column B is for the values, and Column C is for any Comments, these must be prefixed by an exclamation mark (‘!’). There should not be any data in any other columns for this part of the file.
Another example of converted file: