Automic REST API output exported to CSV file format

[Genarro Montano]

Hello!

I have a desire to take my REST api output from a RA REST job and export that into a csv table or something that looks a little cleaner. Has anyone currently done this? I have looked into Parsing the response, but that doesn't let me specify a file format, specifically if I wanted that in excel I would still need to convert through some form of translation into the various columns. I have also looked into using the script function PREP_PROCESS_REPORT but I am still having difficulty writing to the csv format that I want. the method doesn't directly have to apply to REST either, at the root level I am attempting to take a report of a jobs output and convert that into a csv format.

Any help or input is appreciated!

------------------------------
[JobTitle]
[CompanyName]
[State]
------------------------------

[Joel Wiesmann]

Help without knowing how your code looks like so far is pretty hard to give ;-). Basically PREP_PROCESS_REPORT is not a json parser, so the solution is gonna be a hack (you possibly will need to decode escaped characters etc.).

------------------------------
☎️ Swisscom Automation Engineer & 🧙 PE Membership Creator

Automic Kurse, Tutorials, Tools und mehr auf:
https://membership.philippelmer.com/
Zwei Wochen kostenlos testen!
------------------------------

[Thierry Tarrason]

Hi Genarro,
I don't know how to produce a CSV file but i import the data through Rest API directly in Excel. I do it to check Forecasts as it's much faster than using AWI.

Here's an example on how to get a list of available forecasts:

Sub ForecastList()
Dim ws As Worksheet

AutomicEnv = InputBox("Automic Environement ( Server ur4rdev/uc4hprod)", "Automic Server", Default:="uc4rdev")
AutomicClient = InputBox("Client Number ( without leading 0's )", "Client number", Default:="50")

Worksheets("ForecastList").Activate
Set aw = ActiveWorkbook

    OrigFormula = "let Source = Json.Document(Web.Contents(""http://#envir#:8088/ae/api/v1/#client#/forecasts""))," _
     & Chr(10) & "data = Source[data]," _
     & Chr(10) & "#""Converted to Table"" = Table.FromList(data, Splitter.SplitByNothing(), null, null, ExtraValues.Error)," _
     & Chr(10) & "#""Expanded Column1"" = Table.ExpandRecordColumn(#""Converted to Table"", ""Column1"", {""id"", ""title"", ""end_time"", ""estimated_start"", ""estimated_end"", ""status"", ""status_text"", ""user"", ""type""}, {""id"", ""title"", ""end_time"", ""estimated_start"", ""estimated_end"", ""status"", ""status_text"", ""user"", ""type""})" _
     & Chr(10) & "in" _
     & Chr(10) & "#""Expanded Column1"""

Set Query = aw.Queries("ForecastList")
NewFormula = Replace(OrigFormula, "#envir#", AutomicEnv)
NewFormula = Replace(NewFormula, "#client#", AutomicClient)

Query.Formula = NewFormula

aw.Connections("Query - ForecastList").Refresh

End Sub​

Hope this helps.

------------------------------
Best regards,
Thierry

Banque de Luxembourg
------------------------------

[Genarro Montano]

Hi Thierry,

Thank you for the response, that is extremely helpful. I assume with this VBA macro you are also calling sql query and I should be able to run this on a windows host provided I use my relevant information. This is a great starting point for me! Do you initiate this from Automic or just run through Excel when you need to?

------------------------------
[JobTitle]
[CompanyName]
[State]
------------------------------