Timestamps in Filenames when Using DOS Batch

To get a nicely formatted timestamp in a filename e.g. ftpTransfer_20111214@0134.txt when using DOS batch scripts to control execution you can use the following.




@ECHO OFF
::GetDate
for /f "tokens=1-3 delims=/" %%a in ('DATE/T') do (
set dd=%%a
set mm=%%b
set yyyy=%%c
)


Call :TRIMYEAR %yyyy%
SET odate=%yyyy%_%mm%_%dd%
::Get Time
SET WFLWDATE=%odate%


for /f "tokens=1-3 delims=:" %%a in ('time/t') do (
  set hh=%%a
  set mm=%%b
  set ss=%%c
)


SET WFLWTIME=%hh%%mm%


::Create TimeStamp
SET TMSTMP=%TMSTMP%@%hh%%mm%


::Do your stuff here

:TRIMYEAR
set yyyy=%*
GOTO :EOF

Excel Output Libraries

In previous posts I've discuss using the Carlos AG Excel Writer library which outputs in XML format and have found it to be very usable. I have been finding a few issues which were bugging me and that was largely the difficulty in working with large datasets due to the file size and the fact that the library looks like it is no longer under active development.

I then looked at ExcelLibrary which had some recommendations but has almost no documentation and the defects page gives the indication that it is no longer being actively developed. This isn't as much of a problem as it was with the Carlos AG library but it was worth looking further afield.

A post on StackOverflow linked to ClosedXML which had plenty of documentation and the source was available for modification, even better it appeared to be under active development.

To do a basic test of the library I wrote a small program to do something I've been meaning to do for a while.

Given a Datalink file and a file containing a list of SQL Statements generate a Excel file with a worksheet for each statement. I also used this as an opportunity to improve how I handle command line arguments by using the CommandLineParser utility for XSD2DB.

The full code of the program I wrote to test this can be found on GitHub and a zip of the binaries can be downloaded.

At this point in time I've gone for the very simplest model and just created a new Worksheet using a DataTable as a source and left the formatting as a manual operation.
As you can see it's incredibly simple

   1:      class DataTableToExcel

   2:      {

   3:   

   4:          private DataTable dt;

   5:          private XLWorkbook wb;

   6:          private string filename;

   7:          

   8:   

   9:          public DataTableToExcel(DataTable dt, string filename)

  10:          {

  11:              this.dt = dt;

  12:              this.filename = filename;

  13:              if (File.Exists(filename))

  14:              {

  15:                  wb = new XLWorkbook(filename);

  16:              }

  17:              else

  18:              {

  19:                  wb = new XLWorkbook();

  20:              }

  21:          }

  22:   

  23:          public void Process()

  24:          {

  25:              var ws = wb.Worksheets.Add(dt);

  26:              ws.Name = "Sheet " + wb.Worksheets.Count;

  27:              wb.SaveAs(filename);

  28:          }

  29:      }

Planned Updates
In time I want to be able to add the following features by having a more complex configuration file.

• Associating a name with a query so that sheets are correctly named
• Running the queries in parallel and passing the datatables to the generator when necessary
• Better error handling of bad statements
• Support for auto inserting date and timestamp in the output file name

Saving Flash Video & Audio Using JCopia

Background
One of the most common ways to demonstrate software is to record the screen and capture it into a Flash Video. This techinque is used both in the commercial and educational sphere but does result in the problem that you may come across the following situations.

* The video is no longer being hosted when you return to it at a later date.
* The video is inaccessible from your classroom or when giving a presentation.

Over the years I've tried various browser based extensions which haven't always worked or the user interface has been so poor that I could not recommend it to non-technical friends.

While searching for a more mature solution I came across JCopia by Jiteco which is competitively priced application which can save both flash video files and MP3s from websites.

JCopia
The application install is a standard MSI and as expected there were no problems installing it. Upon launch you are presented with an intutive interface which differentiates between music and video and also allows you to track completed and pending downloads.

Using the application is incredibly simple as it starts off in monitoring mode, as soon as I opened by browser and went to YouTube and selected a video it immediately started downloading it. The browser can be closed at this point and the download will continue and can be paused and restarted which is very handy for large videos.

More advanced users of the application can add their own mime types and extensions in which means that it's a moderately simple task to be able to download new formats as they are added and adjust to other websites. The other advantage of this method is that unlike many browser based solutions this software is not site independent will allow you to save flash files from many sources.

JCopia is made by Jiteco and can be found here.