Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
'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
@ammine007

This comment has been minimized.

Copy link

@ammine007 ammine007 commented Jun 10, 2017

Salamu alaikum,
Thank you for this initiative, does it work with LibreOffice ?

@hackel

This comment has been minimized.

Copy link

@hackel hackel commented Jun 22, 2017

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

@davidhaynz

This comment has been minimized.

Copy link

@davidhaynz davidhaynz commented Mar 28, 2018

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
@celeduc

This comment has been minimized.

Copy link

@celeduc celeduc commented Apr 25, 2018

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).

@edips

This comment has been minimized.

Copy link

@edips edips commented Mar 12, 2019

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

@BitBuilder

This comment has been minimized.

Copy link

@BitBuilder BitBuilder commented Nov 25, 2019

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.

@aaronhoogstraten

This comment has been minimized.

Copy link
Owner Author

@aaronhoogstraten aaronhoogstraten commented Nov 25, 2019

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.

@mihai-daniel

This comment has been minimized.

Copy link

@mihai-daniel mihai-daniel commented Apr 28, 2020

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?

@ragemunene

This comment has been minimized.

Copy link

@ragemunene ragemunene commented Jul 19, 2020

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.