This tutorial is for the conversion of .XLS format weather data that some users might have available to them via 'tas'.

Files Required for this Tutorial:

• tasCSVtoWEA.zip
• ...plus your 'tas' .XLS weather data file

1. Open XLS File

To begin, download and open the tasCSVtoWEA.zip file. Contained in this zip archive are two files; an .XLS file containing a macro, and a .CCF file which we will use late for import of our data into the Weather Tool.

Once opened, the tasCSVtoWEA.xls file should look similar to the image below.

view of tasCSVtoWEA.xls file once opened in Microsoft Excel.

2. Open your 'tas' .XLS File

Next you need to open your 'tas' .XLS file containing the data you want to convert. An example version of the kind of file you will get with 'tas' is shown below.

Example 'tas' .XLS weather data file.

3. Load the formatting Macro

Once you have opened your 'tas' .XLS weather data, go to Tools » Macro » Macros. This will open the Macro dialog box, as shown below.

Macro dialog box in Microsoft Excel.

At this point select the tasCSVtoWEA.xls!tasXLStoCSZ Macro from the list, then click the Run button.

Note: If you can't see this Macro name in the list, be sure to choose All Open Workbooks from the Macros in: section at the bottom of the of the dialog box.

For those who are interested, the macro contents is as follows:

Sub tasXLStoCSV()
' tasXLStoCSV Macro
' Macro authored 25/10/2007
    Dim wbname As Variant
    Dim iDotPosition As Integer
    MsgBox "This macro will convert the active workbook" & _
           "to a CSV file and insert" & Chr(13) & _
           "date/time columns so that it can be imported" & _
           "into the Weather Tool." & Chr(13) & Chr(13) & _
           "When prompted, click YES to save a CSV" & _
           "version of your weather data file.", _
           vbInformation, "tas XLS to CSV"
    wbname = ActiveWorkbook.Name
    iDotPosition = InStrRev(wbname, ".")
    If iDotPosition > 0 Then
        wbname = Left(wbname, iDotPosition - 1) & ".csv"
    End If
    ActiveWorkbook.SaveAs Filename:=wbname, _
        FileFormat:=xlCSV, CreateBackup:=False
    ActiveWindow.Close
    Workbooks.Open Filename:=wbname
    Rows("1:11").Select
    Selection.Delete Shift:=xlUp
    Columns("H:J").Select
    Selection.Delete Shift:=xlToLeft
    Windows("tasCSVtoWEA.xls").Activate
    Columns("A:C").Select
    Selection.Copy
    Windows(wbname).Activate
    Range("A1").Select
    Selection.Insert Shift:=xlToRight
    Application.CutCopyMode = False
    ActiveWorkbook.Save
End Sub

4. Running the formatting Macro

Once you have clicked Run the following dialog box will appear with further instructions. Make sure you read the dialog box in full and then click OK to continue with running the Macro.

Dialog displayed at start of Macro.

Then the following warning dialog will appear asking if you want to save changes to the .CSV file. Make sure you click Yes.

Dialog displayed in middle of Macro asking you confirm Saving a .CSV file. You need to click Yes.

Once the Macro is complete the re-formatted data should look similar to the following image.

view of re-formatted file once Macro has completed.

5. Save newly formatted weather data file and open the Weather Tool

Once the Macro has finished, go to File » Save to make sure that the newly formatted file that you will use for import into the Weather Tool is saved to disk. You will likely be prompted by another warning dialog box, as below, again make sure you choose Yes.

CSV format warning dialog box.

After saving the CSV file, you can now close Microsoft Excel and open the Weather Tool.

Note: If you don't have the Weather Tool but have a licenced version of ECOTECT, you can instead use the Weather Manager for the rest of this tutorial. You can access this via Tools » Convert Weather Data from within ECOTECT.

6. Import our new weather data file

Once you have the Weather Tool open, go to File » Open and in the dialog box choose Files of type » Separated Value Files.

Choose correct Files of type from the Open dialog box.

Note: The reason this file type is called "Seperated Value Files" is because you can use the option o import files with many different sorts of separators, such as tabs, full-colons, semi-colons etc. as well as commas.

Next locate the file that you saved after re-formatting in steps 1 through 4 at the start of this tutorial, then click Open.

The following Import dialog box will then appear. In it you will be able to view a few rows of sample data from the file you are importing at the top, and controls for how the data is to be imported at the bottom of the dialog box.

Seperated values Import dialog box.

Next we need to load the custom import format file, tasCSVtoWEA.ccf. This file can be found in the zip file downloaded at the start of this tutorial. Click the Load button at the bottom left corner of the Import dialog box, locate the tasCSVtoWEA.ccf file, then click Open.

Once you have loaded the Custom Column Format file (.CCF) you should notice that the section on the right of the Import dialog box is filled with column definitions and their appropriate units, similar to the image to the right.

At this point it is important to double-check that the column definitions, their units and column order is correct for your original 'tas' .XLS weather data. In some cases they may vary slightly depending on the original source of the weather data, in which case you would need to make the relevant adjustments to the import format at this stage.

To alter any of the import definitions; use the Add and Delete buttons to add units from the choice on the left side, or delete units from the right; use the Up and Down arrow buttons at the bottom of the dialog box to change the order of the columns on the right hand side; and lastly you can left click on the Units option next to each column definition to choose from a selection of common units, or specify your own custom formula for unit conversion.

Once you are happy that the column definitions are appropriate for your original 'tas' .XLS weather data file, click the Import button.

...and finally, once the file has been imported, all that remains is to fill in the appropriate Location Data in the roll-out panel in the top right corner of the Weather Tool.

Most importantly, make sure you have provided the correct Latitude, Longitude and Time-zone as these values will have significant impact on the Solar Poisition and Optimum Orientation features.

7. Save the file as .WEA format

The last step before you can use the file in ECOTECT, is to save it as a .WEA weather data file. Go to File » Save As making sure to name your file appropriatly and to a location where you typically store your other weather data files. The default location for use in a typical ECOTECT installation is C:\Program Files\Square One\Weather Data.