Results 1 to 8 of 8
  1. #1
    Join Date
    Nov 2012
    Location
    Canada
    Posts
    182
    Post Likes

    [N4] Multi histories export to CSV

    Hi,

    Just for help community
    This a code for export all histories to CSV files with histories exclude option
    Thanks to Digo from Niagara community for this amazing job!
    Source: https://www.tridiumuniversity.com/Co...0000000TZ4OIAW

    public void onStart() throws Exception
    {
    // start up code here
    }

    //
    // This example demonstrates executing a program object task on a separate
    // thread. Since actions are run on the system thread by default, any long-
    // running task should be completed on a separate thread in order to avoid
    // watchdog timeout problems.
    //
    // In this example, the execute method creates a new thread, and starts it.
    // Since program objects implement Runnable, you can implement the run() method
    // and allow the program object to be the target of the new thread.
    //
    // This technique is good when the program object will not be executed at a high
    // rate. Since a new thread is created for each execution, it is possible this
    // approach could consume to many system resources.
    //

    public void onExecute()
    throws Exception
    {
    if (running) return; // only allow one execution at a time

    // Create a new thread, with the starting point set as the current program
    // object.
    Thread thread = new Thread(this, getComponent().getName());


    // Start the thread
    thread.start();
    }

    public void onCancel()
    throws Exception
    {
    running = false;
    }

    public void run()
    {
    // This task will take a long time
    try
    {
    BComponent base = getComponent();
    running = true;
    BOrdList exclude = getHistoriesToExclude();
    progress("Started exporting history records");
    BHistoryService service = (BHistoryService)Sys.getService(BHistoryService.TY PE);
    BHistoryDatabase db = service.getDatabase();

    if (db == null)
    {
    progress("Failed - Could not find history database");
    return;
    }

    int count = 0;

    BHistoryDevice[] devices = db.listDevices();
    for (int i = 0; i < devices.length; i++)
    {
    if (!running)
    { // Check if canceled
    progress("Operation canceled.");
    return;
    }

    if (contains(devices[i], exclude)) continue; // skip if in excluded list

    BIHistory[] histories = db.listHistories(devices[i]);

    //add a sub folder for storing the histories if necessary
    File basePath = Sys.getStationHome();
    File ePath = null;
    File stationFolder = null;

    if(new File(basePath, "historyExports").exists() != true)
    {
    ePath = new File(basePath,"historyExports");
    ePath.mkdir();
    }
    else
    {
    ePath = new File(basePath,"historyExports");
    }

    String stationName = "";
    String historyName = "";

    for (int j = 0; j < histories.length; j++)
    {
    if (!running)
    { // Check if canceled
    progress("Operation canceled.");
    return;
    }

    if (contains(histories[j], exclude)) continue; // skip if in excluded list

    progress(" Exporting records for "+histories[j].getId());

    stationName = histories[j].getId().getDeviceName();
    historyName = histories[j].getId().getHistoryName();

    if(new File(ePath,stationName).exists() != true)
    {
    //need to make the folder
    stationFolder = new File(ePath,stationName);
    stationFolder.mkdir();
    }
    else
    {
    stationFolder = new File(ePath,stationName);
    }


    //PrintStream out = new PrintStream(new FileOutputStream(baseEPath + stationName + "" + historyName + ".csv", true));
    PrintStream out = new PrintStream(new FileOutputStream(new File(stationFolder,historyName + ".csv"), true));

    OrdTarget table = BOrd.make("history:" + histories[j].getId().toString() + "|bql:select *").resolve(base);
    out.println(exportToCsv(table));
    out.close();
    count++;
    }
    }

    progress("Completed exporting "+count+" histories ");
    }
    catch(Exception e)
    {
    progress("Error during export of histories " + e);
    }
    finally
    {
    running = false;
    }
    }

    /**
    * Run the CSV exporter against the specified table to build an
    * in memory representation of the table as a CSV file.
    */
    private String exportToCsv(OrdTarget table)
    throws Exception
    {
    // create instance of ITableToCsv exporter
    BExporter exporter = (BExporter)Sys.getType("file:ITableToCsv").getInst ance();

    // run the CSV exporter to export to memory based byte array
    ByteArrayOutputStream out = new ByteArrayOutputStream();
    ExportOp op = ExportOp.make(table, out);
    exporter.export(op);

    // return as string (this works because we String will use the default
    // encoding, which should match encoding ITableToCsv exporter used to
    // create a PrintWriter from a raw OutputStream)
    return new String(out.toByteArray());
    }



    public static boolean contains(BINavNode obj, BOrdList list)
    {
    BOrd navOrd = obj.getNavOrd().relativizeToSession();
    for (int i = 0; i < list.size(); i++)
    {
    if (navOrd.equals(list.get(i).relativizeToSession())) return true;
    }

    return false;
    }

    public void progress(String progress)
    {
    System.out.println(progress);
    setStatus(progress);
    }

    public static boolean running = false;
    //String basePath = "d:\\niagara\\Niagara-3.5.25\\stations" + (Sys.getStation().getStationName().toString()) + "";
    //String baseEPath = "d:\\niagara\\Niagara-3.5.25\\stations" + (Sys.getStation().getStationName().toString()) + "\\historyExport";


    public void onStop() throws Exception
    {
    // shutdown code here
    }
    Slot




    Imports




    Have fun!

  2. Likes orion242 liked this post
  3. #2
    Join Date
    Jan 2003
    Location
    USA
    Posts
    3,479
    Post Likes
    This just dumping them into the file system?

    You can use the report service to email them as an csv attachment as well. Dumping out a whole station of histories tvia email would be a bit of PITA if its hundreds.
    Propagating the formula. http://www.noagendashow.com/

  4. #3
    Join Date
    Nov 2012
    Location
    Canada
    Posts
    182
    Post Likes
    Thread Starter
    Quote Originally Posted by orion242 View Post
    This just dumping them into the file system?

    You can use the report service to email them as an csv attachment as well. Dumping out a whole station of histories tvia email would be a bit of PITA if its hundreds.
    Dumping under Files folder

    With report service you can't export multi trendlog because you can't use ORD source like history:/Supervisor|bql:select *

    It's better and fast compare to SerieTransform or another method

  5. #4
    Join Date
    Apr 2007
    Location
    San Diego, CA
    Posts
    1,556
    Post Likes
    This is useful for when the commissioning agent asks for individual histories as csv files.
    It dumps all histories (minus the ones you exclude) as individual csv files onto the file system. I've mostly used it on Supervisors.
    PS: I didn't write the code, it was passed along to me.

  6. #5
    Join Date
    Jan 2003
    Location
    USA
    Posts
    3,479
    Post Likes
    Quote Originally Posted by black_syphilis View Post
    With report service you can't export multi trendlog because you can't use ORD source like history:/Supervisor|bql:select *
    Normally I composite them first, then toss transform as a email attachment. But yes, if you need loads and loads of logs its tedious to setup. On the flip side, it can send them daily reports automatically without having to mess with it each day, week, etc.
    Propagating the formula. http://www.noagendashow.com/

  7. #6
    Join Date
    Jun 2011
    Location
    Ohio
    Posts
    30
    Post Likes
    Would this work for AX as well?

  8. #7
    Join Date
    Jan 2002
    Location
    Fort Worth\Dallas, Texas
    Posts
    1,817
    Post Likes
    Quote Originally Posted by DannyTanner View Post
    Would this work for AX as well?
    It will, but you will have to modify the code and change getComponent() to getProgram()
    Go Rangers!

  9. Likes orion242 liked this post
  10. #8
    Join Date
    Jan 2003
    Location
    USA
    Posts
    3,479
    Post Likes
    There's a couple typos in the code above but its just some spaces that shouldn't be. Works a treat in AX. Have to look at this and get it to weed out the crap and just give the timestamp and value. Other columns are little more than clutter.

    They all get dumped in a historyexport folder under the station running them.

    Thanks!
    Propagating the formula. http://www.noagendashow.com/

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

Related Forums

Plumbing Talks | Contractor MagazineThe place where Electrical professionals meet.