ReadCSV.g

Code

The code for this and other example scripts can be found in the DataHub distribution archive, typically at one of these locations: C:\Program Files\Cogent\OPC DataHub\scripts\ C:\Program Files\Cogent\Cascade DataHub\scripts\ Please refer to Section 3.1, “How to Run a Script” for more information on using scripts.

/*
 * This script reads a CSV file and writes the values found there
 * into a set of data points in the DataHub.  The format of the
 * file is:
 *
 * row 1:  name1, name2, name3, ...
 * row 2:  value1, value2, value3, ...
 * row 3:  value1, value2, value3, ...
 *  ...
 * row N:  value1, value2, value3, ...
 *
 * The script will read all rows in the file, but ignore all but
 * the first and last.  The first row contains the point names and
 * the last contains the most recent data.
 * If a name is left blank then that column is ignored.
 * If a point name does not contain a domain name, then the domain
 * set in the "domain" member of the application is used.
 *
 * e.g.,
 *    default:point1, default:point2, default:point3
 *    1, 2, 3
 *    4, 5, 6
 *
 * will result in:
 *    default:point1 = 4
 *    default:point2 = 5
 *    default:point3 = 6
 *
 * Strings containing ',' characters must be quoted within double quotes,
 * like this:
 *    "hello, friend"
 *
 * Double-quotes within strings must be escaped, like this:
 *    "He said, \"hello\"."
 *
 * This script will guess whether a value is a number or a string.  If the
 * value can be parsed to a number, it is treated as a number.  Otherwise it
 * is a string.
 *
 * This script looks for new data at a set time interval.
 *
 * This script will operate in one of two modes:
 *    In "reload" mode, the file is re-read from the beginning on each
 *    timer tick.
 *    In "append" mode, the file is kept open, and the file is read
 *    from the last read position on each timer tick.  This mode will
 *    not work if the writing application does not open the file
 *    as "shared".
 *
 * This script adds a menu item to the OPC DataHub system tray icon that
 * allows the user to re-load the file, change reade mode, and toggle logging
 * to the Script Log window.
 */

require ("Application");

class ReadCSV Application
{
    mode = #reload;		// set to #reload or #append
    domain = "default";
    filename = "c:/tmp/data.csv";
    verbose = t;
    update_secs = 5;
    separators = ",";	// e.g, use " " for space separated, or "\t" for tab-separated
    
    /* --- No need to change these --- */
    columns;
    fptr;
    modemenu;
    verbosemenu;
}

/* Logging function that prepends the time to the output. */
method ReadCSV.Log (args...)
{
    if (.verbose)
    {
        funcall (princ, cons (date(), cons(": ", args)));
        princ("\n");
    }
}

/* Open the given file, if possible. */
method ReadCSV.OpenFile (filename)
{
    .fptr = open(filename, "r");
    if (!.fptr)
    {
        .Log ("Could not open file: ", filename);
    }
    else
    {
        .filename = filename;
        .Log ("File: ", filename, " opened");
        .ReadColumns();
    }
    .fptr;
}

method ReadCSV.CloseFile ()
{
    if (.fptr)
    {
        close(.fptr);
        .Log ("File: ", .filename, " closed");
        .fptr = nil;
    }
}

method ReadCSV.Trim(str)
{
    local		l = strlen(str), start, end;
    for (start=0; start<l && strchr(" \t",str[start]) != -1;)
        start++;
    for (end=l-1; end >= start && strchr(" \t",str[end]) != -1;)
        end--;
    if (start != 0 || end != l-1)
        substr(str,start,end-start+1);
    else
        str;
}

method ReadCSV.ReadColumns ()
{
    local		line = read_line (.fptr);
    local		i;
    
    if (line != _eof_)
    {
        line = list_to_array(string_split(line,.separators,0,t,"\"\"",t,"\\",nil));
        for (i=0; i<length(line); i++)
        {
            if (.Trim(line[i]) == "")
            {
                line[i] = nil;
            }
            else
            {
                if (strchr(line[i],':') == -1)
                    line[i] = string(.domain,":",line[i]);
                line[i] = symbol(line[i]);
                datahub_command(format("(create %s 1)", stringc(line[i])),1);
            }
        }
        .columns = line;
        .Log("Set columns to ", .columns);
    }
}

method ReadCSV.GuessTypeValue (str)
{
    local		value;

    try
    {
        value = parse_string(str,nil);
        if (!number_p(value))
            value = str;
    }
    catch
    {
        value = str;
    }
    value;
}

method ReadCSV.ApplyLine (line)
{
    local		i, value;
    
    .Log ("Applying line: ", line);
    if (line)
    {
        line = list_to_array(string_split(line,.separators,0,t,"\"\"",nil,"\\",nil));
        for (i=0; i<length(.columns); i++)
        {
            if (.columns[i])
            {
                value = .GuessTypeValue(line[i]);
                //.Log ("Set: ", .columns[i], " to ", stringc(value));
                if (value)
                    set(.columns[i], value);
            }
        }
    }
}

method ReadCSV.ReadLines ()
{
    local		line, input;

    .Log ("Looking for new data...");
    while ((input = read_line(.fptr)) != _eof_)
    {
        if (.Trim(input) != "")
            line = input;
    }
    if (line)
    {
        .ApplyLine(line);
    }
}

method ReadCSV.ReadFile (filename)
{
    if (!.fptr)
        .OpenFile(filename);
    if (.fptr)
    {
        .ReadLines();
        if (.mode == #reload)
            .CloseFile();
    }
}

method ReadCSV.SetMode (mode)
{
    .mode = mode;
    .Log("Set read mode to ", mode);
    .ChangeMenuItemLabel(.modemenu,
                         string("Set ", (mode == #append) ? "Reload" : "Append",
                                " Mode"));
    if (.filename)
        .Reload(.filename);
}

method ReadCSV.ToggleMode ()
{
    .SetMode((.mode == #append) ? (#reload) : (#append));
}

method ReadCSV.ToggleVerbose ()
{
    .SetVerbose(!.verbose);
}

method ReadCSV.SetVerbose (mode)
{
    .verbose = t;
    .Log("Set verbosity to ", (mode ? "verbose" : "quiet"));
    .verbose = mode;
    .ChangeMenuItemLabel(.verbosemenu,
                         string(mode ? "Quiet" : "Verbose", " Mode"));
}

method ReadCSV.Reload (filename)
{
    .CloseFile();
    .ReadFile(filename);
}

/* Write the 'main line' of the program here. */
method ReadCSV.constructor ()
{
    .TimerEvery(.update_secs, `(@self).ReadFile((@self).filename));
    .AddCustomSubMenu("CSV File Reader");
    .AddCustomMenuItem("Reload CSV File", `(@self).Reload((@self).filename));
    .modemenu = .AddCustomMenuItem("Set Append Mode", `(@self).ToggleMode());
    .verbosemenu = .AddCustomMenuItem("Verbose", `(@self).ToggleVerbose());
    .SetVerbose(.verbose);
    .SetMode(.mode);
}

method ReadCSV.ChangeMenuItemLabel (menuitemid, label)
{
    local	parent = .CreateSystemMenu();
    local	info = new MENUITEMINFO();

    if (cons_p(menuitemid))
        menuitemid = car(menuitemid);
    info.cbSize = 48;
    info.fMask = MIIM_STRING | MIIM_ID;
    info.fMask |= (WINVER < 0x0500 ? MIIM_TYPE : MIIM_FTYPE);
    info.fType = MFT_STRING;
    info.wID = menuitemid;
    info.dwTypeData = label;
    SetMenuItemInfo (parent, menuitemid, 0, info);
}

method ReadCSV.destructor ()
{
    .CloseFile();
}

ApplicationSingleton (ReadCSV);