Customer Engagement & Dynamics CRM Forum

Expand all | Collapse all

webapi to retrive data or reports in CSV format

  • 1.  webapi to retrive data or reports in CSV format

    Posted May 22, 2020 01:35 PM
    I'm in the processes of setting up an application to application integration with my on premise version of dynamics 365 .  I need to produce a audit listing of activities that happen in the application .   ideally I would simply create a webservice that they can inquire and retrieve the listing in Json of XML format but they can only consume CSV at this time.  I know that the interface lets you export a report in CSV format  but what i'm looking at is a way to automated the retrieval from a WEBAPI end point (ODATA2 or ODATA4) . this WebAPI would ether retrieve the data from a get . or trigger the retrieval of a report in CSV format .

    my question is as anyone done of seen this done . note I'm on premise and cannot  use flow



    ------------------------------
    Martin Carpenter
    Senior It Specialist
    Canada Revenue Agency
    Ottawa
    ------------------------------
    Academy - Online Interactive Learning from Experts


  • 2.  RE: webapi to retrive data or reports in CSV format

    TOP CONTRIBUTOR
    Posted May 25, 2020 06:47 AM
    There's no built-in support for getting data out in CSV format. You'll need to build your own service that extracts the data from the Web API and translates it to CSV for your other application to consume.

    ------------------------------
    Mark Carrington
    Chief Technologist
    Data8
    Chester
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 3.  RE: webapi to retrive data or reports in CSV format

    D365UG/CRMUG ALL STAR
    Posted May 25, 2020 09:15 AM
    If you are on-premise, and assuming that you need to just "read" the data from Dynamics 365, you could setup a SQL Server Integration Services scheduled job to query the the SQL database directly (there are a series of filtered views so you don't even need to hit the tables directly) and then dump to CSV in a folder somewhere.

    I know you know this, but I need to say it, *writing* data back is NOT supported via SQL.  Reading data is OK (this is how Dynamics 365 T-SQL SSRS reports work)

    Another approach is to use something like Kingswaysoft.  This again is SSIS, but can connect to Dynamics 365 via the WebAPI in case your database admin is nervous about giving you direct access to the SQL server.

    Just a couple of ideas that might be faster than writing code.

    Cheers
    Nick

    ------------------------------
    Nick Doelman
    Microsoft MVP
    Power Platform/Dynamics 365 Specialist
    Nick Doelman Co.
    Ottawa ON
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 4.  RE: webapi to retrive data or reports in CSV format

    Posted May 25, 2020 09:35 AM
    thanks that would work, (I should have specified)  unfortunately although we are on premise the environment is being treated as "online" and we dont have SQL access to the database. and at this point we dont have kingway or any other ETL  (corporate architecture is looking into it) .  I've been mandated to do everything though APIs for application to application integration. as is our corporate direction.  our other application that are doing this have no issue creating an download endpoint that downloads the file.  I'm trying to see if I can do the same with dynamic.

    so far I have the following options :
    1. custom action that build the CVS and outputs it as a string (was able to do this with an XML, have not tried CVS yet)
    2. custom action that returns a annotation with the csv file (not my favorite as this exposes the file to unauthorized access if not cleaned up properly )

    ------------------------------
    Martin Carpenter
    Senior It Specialist
    Canada Revenue Agency
    Ottawa
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 5.  RE: webapi to retrive data or reports in CSV format

    GOLD CONTRIBUTOR
    Posted Jun 01, 2020 02:07 PM
    So at the risk of scaring you off, I had a requirement a few years back to generate a CSV from CRM regularly, and I found and used the MSCRMToolkit (no not the XrmToolbox).  I don't know that it's "supported" anymore, but it works and has worked for us for 2-3 years now.  It has the ability to define a query (in fetchxml) and define some parameters (eg delimiter) and the connection information, then you can schedule the .cmd program (this is on Windows) to be executed as you like.
    It's hard to find, so here's the link to the github repository:
    https://github.com/zoranivanov/MSCRMToolKittest

    I downloaded the zip from somewhere else I think, I coulda swore it was here:
    https://archive.codeplex.com/?p=mscrmtoolkit

    I'm pretty sure I had to hunt around the discussion threads to find it last time.  I couldn't find it just now though.  Either way, hopefully the repository above can help you.

    ------------------------------
    Mike Power
    Senior CRM Solutions Developer
    American University
    Washington DC
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 6.  RE: webapi to retrive data or reports in CSV format

    Posted Jun 08, 2020 02:34 PM
    thanks Mike I will have a look at it

    ------------------------------
    Martin Carpenter
    Senior It Specialist
    Canada Revenue Agency
    Ottawa
    ------------------------------

    Academy - Online Interactive Learning from Experts


If you've found this thread useful, dive deeper into User Group community content by role