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

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 ]