Ask Your Question
1

Import from REST API to Calc

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

larsbrandi gravatar image

Hi

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

larsbrandi

edit retag flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted
0

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

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
0

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

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 http://openoffice3.web.fc2.com/OOoBasic_General.html#OOoGB2A :

Sub oFileCopySFA
Dim oSimpleFileAccess
Dim oFromFile
Dim oToFile
    oSimpleFileAccess = createUnoService("com.sun.star.ucb.SimpleFileAccess")
        oFromFile = "https://api.vaultoro.com/latest"
        oToFile = "/tmp/test_copy.csv"
        If oSimpleFileAccess.Exists(oFromFile) then
            If NOT oSimpleFileAccess.Exists(oToFile) then   
                oSimpleFileAccess.copy(oFromFile, oToFile)
            else
                MsgBox(oToFile & " File exists, but was overwritten ", 0, "Caution !!")
                oSimpleFileAccess.copy(oFromFile, oToFile)
                Exit Sub
            End If
        else
            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

2 followers

Stats

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

Seen: 6,495 times

Last updated: Apr 28 '16