[gradsusr] Writing Station Data File Using Excel VBA

[Stephen McMillan]

The GrADS Users Guide, in the "Creating a Station Data File" section, shows
a short sample dataset as follows:

Year   Month  Stid    Lat    Lon    Rainfall
1980     1     QQQ    34.3  -85.5    123.3
1980     1     RRR    44.2  -84.5     87.1
1980     1     SSS    22.4  -83.5    412.8
1980     1     TTT    33.4  -82.5     23.3
1980     2     QQQ    34.3  -85.5    145.1
1980     2     RRR    44.2  -84.5    871.4
1980     2     SSS    22.4  -83.5    223.1
1980     2     TTT    33.4  -82.5     45.5

I output the attached (test.sta.dat) binary file using an Excel VBA macro,
shown here:

Private Type stadathdr         'data type "station data header" (len=24)
    stid As String * 8      'station ID (e.g., "QQQ")   len=8
    lat As Single           'station latitude  (world   len=4
    lon As Single           'station longitude  coords) len=4
    tim As Single           'time (grid-relative units) len=4
    nlev As Integer         '# data groups after header len=2
    nflg As Integer         'level indep var set flag   len=2
End Type
Sub WriteStaDat()
    Close       'close any open files
    Dim hdr As stadathdr
    Dim yr, mo, flg As Integer      'year (yyyy), month (m), flag
    Dim rf As Single        'rainfall (mm)
    Dim oldyr, oldmo As Integer     'old year, old month
    stadatfile = "c:\projectmgr\stationdata\out\test.sta.dat"
    rnum = 8   '# of data reports to read/write
    flg = 1     'initialize flag
    Range("a1").Select
    Open stadatfile For Binary Access Write As #1
    For r = 1 To rnum     'loop through data rows, read in data
        yr = ActiveCell.Offset(r, 0)
        mo = ActiveCell.Offset(r, 1)
        hdr.stid = ActiveCell.Offset(r, 2)
        hdr.lat = ActiveCell.Offset(r, 3)
        hdr.lon = ActiveCell.Offset(r, 4)
        rf = ActiveCell.Offset(r, 5)
        If flg = 1 Then
            oldyr = yr: oldmo = mo: flg = 0
        End If
        If oldyr <> yr Or oldmo <> mo Then  'if new time group
            hdr.nlev = 0
            Put #1, , hdr     'write station header/time group terminator
(no variable)
            'rec = rec + 1
        End If
        oldyr = yr: oldmo = mo
        hdr.tim = 0#: hdr.nlev = 1: hdr.nflg = 1
        Put #1, , hdr    'write station header
        Put #1, , rf
    Next r
    hdr.nlev = 0
    Put #1, , hdr     'write last time group terminator
    Close #1
End Sub

When I ran the stnmap utility, here were my results:
>stnmap -i c:/projectmgr/stationdata/out/test.sta.ctl
  Name of binary data set: c:\projectmgr\stationdata\out\test.sta.dat
  Number of times in the data set: 2
  Number of surface variables: 1
  Number of level dependent variables: 0

Starting scan of station data binary file.
Binary data file open: c:\projectmgr\stationdata\out\test.sta.dat

Processing time step 1
  Invalid station hdr found in station binary file
  Possible causes:  Invalid level count in hdr
                    Descriptor file mismatch
                    File not station data
                    Invalid relative time
                    Invalid sequential format
    levs = 1123457434  flag = 542265938  time = 9.18369e-41

It appears the station data file was not properly formatted.  Perhaps
someone can understand why I'm getting this result, and how to resolve.

Stephen Mc

On Thu, Apr 29, 2010 at 5:15 PM, Stephen McMillan <smcmillan at planalytics.com
> wrote:

> Does anyone have a "simple" example of an Excel VBA (macro) for creating a
> station data file for use in GrADS?  I have searched the User Archives but
> came up empty.  I am familiar with the format instructions and the C and
> Fortran examples in the GrAD's Users Guide, but wish to use Excel to create
> the file.
>
> I could probably eventually translate from the Fortran example to VBA, but
> it would help if someone has already done that!
>
> Stephen M
>
>
>

***************************************************
The information contained in this e-mail message 
is intended only for the use of the recipient(s) 
named above and may contain information that is 
privileged, confidential, and/or proprietary. 
If you are not the intended recipient, you may not
review, copy or distribute this message. If you have
received this communication in error, please notify 
the sender immediately by e-mail, and delete the original message.
***************************************************
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://gradsusr.org/pipermail/gradsusr/attachments/20100504/ac8c1b86/attachment-0003.html 
-------------- next part --------------
A non-text attachment was scrubbed...
Name: test.sta.ctl
Type: application/octet-stream
Size: 246 bytes
Desc: not available
Url : http://gradsusr.org/pipermail/gradsusr/attachments/20100504/ac8c1b86/attachment-0006.obj 
-------------- next part --------------
A non-text attachment was scrubbed...
Name: test.sta.dat
Type: application/octet-stream
Size: 272 bytes
Desc: not available
Url : http://gradsusr.org/pipermail/gradsusr/attachments/20100504/ac8c1b86/attachment-0007.obj