Import from REST API to Calc

[larsbrandi]

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

[Ratslinger]

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.

[larsbrandi]

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