OpenOffice Export to JSON

'OpenOffice Basic macro for exporting a .ods spreadsheet to JSON (and CSV)
'Project-specific formating I implemented: cull empty or "0" entries, '^' as separator value for the csv

REM  *****  BASIC  *****

Sub Main

'First export to CSV which we will later use as the source for the export to json
Dim Propval(1) as New com.sun.star.beans.PropertyValue
Propval(0).Name = "FilterName"
Propval(0).Value = "Text - txt - csv (StarCalc)"
Propval(1).Name = "FilterOptions"
Propval(1).Value ="94,34,0,1,1"
Doc = ThisComponent
Dim FileName as String
FileName = Doc.getURL()
splitName = split(FileName, ".")
FileName = splitName(0) + ".csv"
Doc.StoreToURL(FileName, Propval())

'Export to JSON

'Get the number of keys in the header of the csv
Dim csv As Integer
Dim len As Integer
csv = FreeFile
Open FileName For Input As csv
Line Input #csv, first
keys = split(first, "^")
len = 0
For Each i in keys
	len = len + 1
Next i

'Need to count the number of lines in the csv (after header line)
Dim lines as Integer
lines = 0
Do While not eof(csv)
	Line Input #csv, line   
	If line <>"" Then
		lines = lines + 1
	End If
Loop
Close #csv

'Need to re-open the csv again for a fresh read from just after the header
Open FileName For Input As csv
Line Input #csv, line

'Open the JSON file for writing
Dim json As Integer
Dim CurrentLine As String
Dim fn As String
fn = splitName(0) + ".json" 
json = Freefile

Open fn For Output As json
Print #json, "["

For line = 0 to lines-1
	Line Input #csv, CurrentLine   
	If CurrentLine <>"" Then
    	values = split(CurrentLine, "^")
    	
    	'Find the last non-empty or non-zero line for each entry
    	lastValidLine = 0
    	For j = 0 to len-1
    		If values(j) = "" OR values(j) = "0" Then
    			'NOT
    		Else
    			lastValidLine = j
    		End If
    	Next j
    		
    	Print #json, "    {"
    	
    	For i = 0 To len-1
    		keyValString = "        " & CHR(34) & LCase(keys(i)) & CHR(34) & ": " & CHR(34) & values(i) & CHR(34) & ","
    		keyValLastEntry = "        " & CHR(34) & LCase(keys(i)) & CHR(34) & ": " & CHR(34) & values(i) & CHR(34)
    		    		
	    	If i = len-1 Then
	    		'Don't include comma after last entry
	    		Print #json, keyValLastEntry
	    	Else
	    		If i = lastValidLine Then
	    			Print #json, keyValLastEntry
	    		Else
	    			Print #json, keyValString
	    		End If
	    		
	    	End If
    	Next i
    	
    	'Next object
    	If line = lines-1 Then
    		Print #json, "    }"
    	Else
    		Print #json, "    },"
    	End If 	
	End If
Next line
 
 
' Close file 
Print #json, "]"
Close #json
Close #csv          

End Sub

Unfortunately running it just gives:
Inadmissible value or data type. Index out of defined range.

Inadmissable value or type can be triggered if you have a big file, because the integer variable in Basic ranges only from -32768 to 32767.
Look for the following integer variable types and change them from Integer to Long

Dim csv As Integer
Dim len As Integer
Dim lines as Integer
Dim json As Integer

Although I appreciate the effort, I couldn't get it to work. My solution was to export to CSV, then convert CSV to JSON (for which there are many tools available).

Thank you very much. It works for Libre Office Calc. You should save the file as ods before running the script.

Works for me in LibreOffice, changed Integer Dims as davidhaynz suggested, and after running the macro got [FILENAME].json in same directory with original file. I was working off .xlsx file not .ods, so as long as a file location is present this should work.

For some reason I had not been receiving notifications for comments here until BitBuilder's, but I'm glad some folks have found some use for this small bit of code I had to whip up for a one-off project. I haven't touched OpenOffice since I posted this gist (or any flavor of BASIC for that matter) so for anyone stumbling upon this in the future, apologies if I can't respond to any questions.

Thank you for the macro. However, it has a problem: it replaces the diacritic letters with question marks. Could it be altered to keep the UTF-8 encoding of the .ods document?

Thanks, this macro is great. Works for me very nicely, from LibreOffice Calc.

In order to get correct identification of values as null , true, false , number and string and prepares syntax accordingly,

You can replace :-

  	keyValString = "        " & CHR(34) & LCase(keys(i)) & CHR(34) & ": " & CHR(34) & values(i) & CHR(34) & ","
  	keyValLastEntry = "        " & CHR(34) & LCase(keys(i)) & CHR(34) & ": " & CHR(34) & values(i) & CHR(34)

by :-

		If values(i) = "" OR values(i) = "0" Then ' Checks if a value is null
  		keyValString = "        " & CHR(34) & LCase(keys(i)) & CHR(34) & ": " & "null" & ","
  		keyValLastEntry = "        " & CHR(34) & LCase(keys(i)) & CHR(34) & ": " & "null"
  	
  		Else  				
  			dim ChkIfTrueFalse as string
  			ChkIfTrueFalse = LCase(values(i))

  			If ChkIfTrueFalse = "true" OR ChkIfTrueFalse = "false" Then ' Checks if a value is boolean true or false
  			    				    
  			    keyValString = "        " & CHR(34) & LCase(keys(i)) & CHR(34) & ": " & LCase(values(i)) & ","
  				keyValLastEntry = "        " & CHR(34) & LCase(keys(i)) & CHR(34) & ": " & LCase(values(i))
  			    				
  				Else
  					dim ChkIfNo as variant ' Checks if a value is a number
  					ChkIfNo = values(i) ' Checks if a value is a number

  					If IsNumeric(ChkIfNo) = True Then ' Checks if a value is a number
  						
  						keyValString = "        " & CHR(34) & LCase(keys(i)) & CHR(34) & ": " & values(i) & ","
  						keyValLastEntry = "        " & CHR(34) & LCase(keys(i)) & CHR(34) & ": " & values(i)
  		   			
  						Else
  							keyValString = "        " & CHR(34) & LCase(keys(i)) & CHR(34) & ": " & CHR(34) & values(i) & CHR(34) & ","
  							keyValLastEntry = "        " & CHR(34) & LCase(keys(i)) & CHR(34) & ": " & CHR(34) & values(i) & CHR(34)
  				
  					End If
  				
  			End If
  				
  	End If

Also to make second last BLANK entry end with " , " UPDATE following

    	If i = len-1 Then
    		'Don't include comma after last entry
    		Print #json, keyValLastEntry
    	Else
    		If i = lastValidLine Then
    			Print #json, keyValLastEntry & ","     '''' ------ UPDATED LINE ------- 
    		Else
    			Print #json, keyValString
    		End If
    	End If

---

Hello,
Thank you for this macro, how to adapt it to export a particular sheet in JSON ?
regards

This macro exports the "Active" sheet as JSON.
So to export any particular sheet, set that sheet as active sheet just after declaring Sub

e.g. [Rename "Sheet1" with your required sheet Name]

Sub export_as_json REM ------ Set Sheet1 as active sheet ------------- ThisComponent.getcurrentController.setActiveSheet(ThisComponent.Sheets.getByName("Sheet1"))

Otherwise manually go to the sheet of your choice and run the macro from there. It will export only that sheet.

I've made a version for LibreOffice (I think this also works for OpenOffice?) that doesn't rely on a CSV export if anyone's interested.
This can also export all sheets in the file.
https://gist.github.com/ceruleancerise/de1f6a7a9bf285e033e9135c5049216b

@ceruleancerise
Good Work ! especially for getting rid of CSV !
Thanks

Just a few suggestions ... [ As Mentioned in https://gist.github.com/pharmankur/773fcb940338d304b13d6e4f93f3ac3f ]

As Many JSON specifications requires to be in that way , and JSON has specific for parameters, kindly consider :-

1. Let Boolean get expressed as TRUE / FALSE , your output represents as 1 / 0 ... [ I know , You have clarified it specifically ]
2. Let blank cell get expressed as null , your output represents it as "" [ as you know in JSON, "" <> null ]