Ask Your Question

Import from REST API to Calc

asked 2016-04-27 16:49:58 +0100

larsbrandi gravatar image


I have an URL returning a JSON or a CSV file. I want to insert the received JSON data in a Calc sheet, or maybe more convenient save a CSV file to be read from Base. So I have two questions : 1. Is it possible to handle the URL returning the JSON in a macro, inserting it to a sheet ? 2. Is it possible to handle the URL returning a CSV file and save it from a macro

Best regards


edit retag flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted

answered 2016-04-28 07:54:04 +0100

Ratslinger gravatar image

Up until now I hadn't worked with this type of situation. However I do work with macros in Base and have access to JSON files from Weather Underground so I ran a few tests.

Downloading the JSON file was done in the macro with a 'Shell' statement using 'curl'. Worked without a problem. Again using the 'Shell' statement, I extracted the data using a 'batch' type file containing 'cat/jq' statements and wrote this output to another file. No problem here either. Now it's just a matter of reading that file and creating either a sheet with the data or records for a DB. That shouldn't be too difficult as I found reference to that in OOME (Open Office Macros Explained) by Andrew Pitonyak which you can download in PDF version here.

Since I'm using Linux, Windows or Mac programs and utilities used in the 'Shell' statement will obviously be different. Downloading a CSV file will be no different than the JSON file - just the processing afterwards.

I would only go this route if this process is to be done on a routine basis. If only once or rarely, then you can read a CSV file into either Calc or Base rather easily. If you have not worked with LO macros, it can be very challenging.

edit flag offensive delete link more

answered 2016-04-28 11:36:04 +0100

larsbrandi gravatar image

Hi Ratslinger

Great, I will dive into it. Andrew Pitonyak's documents is now part of my desktop env ;-) . For the csv part i found this working ( simple simple ) Just get the csv file from internet and copy it to location. Credits to :

Sub oFileCopySFA
Dim oSimpleFileAccess
Dim oFromFile
Dim oToFile
    oSimpleFileAccess = createUnoService("")
        oFromFile = ""
        oToFile = "/tmp/test_copy.csv"
        If oSimpleFileAccess.Exists(oFromFile) then
            If NOT oSimpleFileAccess.Exists(oToFile) then   
                oSimpleFileAccess.copy(oFromFile, oToFile)
                MsgBox(oToFile & " File exists, but was overwritten ", 0, "Caution !!")
                oSimpleFileAccess.copy(oFromFile, oToFile)
                Exit Sub
            End If
            MsgBox( oFromFile & " Input file missing ", 0, "Caution !!")
            Exit Sub
        End If

End Sub

edit flag offensive delete link more
Login/Signup to Answer

Question Tools



Asked: 2016-04-27 16:49:58 +0100

Seen: 9,036 times

Last updated: Apr 28 '16