/* BeejBløg */

Sep 28, 2010 - 3 minute read - Comments -

Handy ETL JScript

import.cmd: @cscript /b import.js %*

import.js
<div style="background: #ddd; max-height: 300px; overflow: auto">
  <ol style="background: #ffffff; margin: 0 0 0 2.5em; padding: 0 0 0 5px;">
    <li>
      //v1.0
    </li>
    <li class="even">
      &nbsp;
    </li>
    <li>
      function cleanarg(i)
    </li>
    <li class="even">
      {
    </li>
    <li>
        //WScript.stdout.WriteLine("i: " + i);
    </li>
    <li class="even">
        return(WScript.Arguments(i+1).replace("\"", ""));
    </li>
    <li>
      }
    </li>
    <li class="even">
      &nbsp;
    </li>
    <li>
      //WScript.stdout.WriteLine("arg count: "+WScript.Arguments.length);
    </li>
    <li class="even">
      &nbsp;
    </li>
    <li>
      if (WScript.Arguments.length == 0)
    </li>
    <li class="even">
      {
    </li>
    <li>
        WScript.stderr.WriteLine("Usage:");
    </li>
    <li class="even">
        WScript.stderr.WriteLine("  -i \"input file\"");
    </li>
    <li>
        WScript.stderr.WriteLine("  -o \"output file\" (blank = screen output)");
    </li>
    <li class="even">
        WScript.stderr.WriteLine("  -r \"record separator\"");
    </li>
    <li>
        WScript.Quit();
    </li>
    <li class="even">
      }
    </li>
    <li>
      &nbsp;
    </li>
    <li class="even">
      var inputfile;
    </li>
    <li>
      var outputfile;
    </li>
    <li class="even">
      var record;
    </li>
    <li>
      &nbsp;
    </li>
    <li class="even">
      var localpath = WScript.ScriptFullName.replace(WScript.ScriptName, "");
    </li>
    <li>
      &nbsp;
    </li>
    <li class="even">
      for (var i = 0; i < WScript.Arguments.length; i++)
    </li>
    <li>
      {
    </li>
    <li class="even">
        //WScript.stdout.WriteLine("arg["+i+"]: "+WScript.Arguments(i));
    </li>
    <li>
        switch (WScript.Arguments(i))
    </li>
    <li class="even">
        {
    </li>
    <li>
          case "-i": inputfile = cleanarg(i); break;
    </li>
    <li class="even">
          case "-o": outputfile = cleanarg(i); break;
    </li>
    <li>
          case "-r": record = cleanarg(i); break;
    </li>
    <li class="even">
        }
    </li>
    <li>
      }
    </li>
    <li class="even">
      &nbsp;
    </li>
    <li>
      var fso = new ActiveXObject("Scripting.FileSystemObject");
    </li>
    <li class="even">
      &nbsp;
    </li>
    <li>
      //WScript.stderr.WriteLine("  inputfile: " + inputfile + ", record: " + record + ", exension: " + fso.GetExtensionName(inputfile) + ", WScript.ScriptFullName: " + WScript.ScriptFullName.replace(WScript.ScriptName, ""));
    </li>
    <li class="even">
      //WScript.Quit();
    </li>
    <li>
      &nbsp;
    </li>
    <li class="even">
      var ForReading = 1, ForWriting = 2;
    </li>
    <li>
      &nbsp;
    </li>
    <li class="even">
      var f = fso.OpenTextFile(inputfile, ForReading);
    </li>
    <li>
      &nbsp;
    </li>
    <li class="even">
      var out = WScript.stdout;
    </li>
    <li>
      if (outputfile != undefined) out = fso.OpenTextFile(outputfile, ForWriting, true);
    </li>
    <li class="even">
      &nbsp;
    </li>
    <li>
      var line="";
    </li>
    <li class="even">
      while ( f.AtEndOfStream != true )
    </li>
    <li>
      {
    </li>
    <li class="even">
        var str = f.Readline();
    </li>
    <li>
        if (str == record)
    </li>
    <li class="even">
        {
    </li>
    <li>
          out.WriteLine(line.slice(1));
    </li>
    <li class="even">
          line = "";
    </li>
    <li>
        }
    </li>
    <li class="even">
        else line += "," + str;
    </li>
    <li>
      }
    </li>
    <li class="even">
      out.Close();
    </li>
    <li>
      &nbsp;
    </li>
    <li class="even">
      if (fso.GetExtensionName(outputfile) == "csv")
    </li>
    <li>
      {
    </li>
    <li class="even">
        var WshShell = WScript.CreateObject("WScript.Shell");
    </li>
    <li>
        //WScript.stderr.WriteLine(localpath + outputfile);
    </li>
    <li class="even">
        WshShell.Run(localpath + outputfile);
    </li>
    <li>
        WshShell.Run(localpath + outputfile);
    </li>
    <li class="even">
        WshShell.Run(localpath + outputfile);
    </li>
    <li>
      }
    </li>
  </ol>
</div></p>