Skip to content

Instantly share code, notes, and snippets.

@pharmankur

pharmankur/OpenOffice Export to JSON

Forked from aaronhoogstraten/OpenOffice Export to JSON
Last active April 20, 2021 09:03
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save pharmankur/773fcb940338d304b13d6e4f93f3ac3f to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save pharmankur/773fcb940338d304b13d6e4f93f3ac3f to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. pharmankur revised this gist Apr 20, 2021. 1 changed file with 35 additions and 3 deletions.
    38 changes: 35 additions & 3 deletions OpenOffice Export to JSON
    View file
    Original file line number Diff line number Diff line change
    @@ -75,15 +75,47 @@ For line = 0 to lines-1
    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)

    Rem --- identify values as null , true, false , number and string and prepares syntax accordingly --- Start ---
    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
    Rem --- identify values as null , true, false , number and string and prepares syntax accordingly --- End ---

    If i = len-1 Then
    'Don't include comma after last entry
    Print #json, keyValLastEntry
    Else
    If i = lastValidLine Then
    Print #json, keyValLastEntry
    Print #json, keyValLastEntry & ","
    Else
    Print #json, keyValString
    End If
  2. @aaronhoogstraten aaronhoogstraten revised this gist May 13, 2015. 1 changed file with 1 addition and 1 deletion.
    2 changes: 1 addition & 1 deletion OpenOffice Export to JSON
    View file
    Original file line number Diff line number Diff line change
    @@ -1,5 +1,5 @@
    'OpenOffice Basic macro for exporting a .ods spreadsheet to JSON (and CSV)
    'Project-specific formating I implemented: cull empty or "0" entries
    'Project-specific formating I implemented: cull empty or "0" entries, '^' as separator value for the csv

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

  3. @aaronhoogstraten aaronhoogstraten revised this gist May 13, 2015. 1 changed file with 3 additions and 3 deletions.
    6 changes: 3 additions & 3 deletions OpenOffice Export to JSON
    View file
    Original file line number Diff line number Diff line change
    @@ -10,7 +10,7 @@ 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 ="44,34,0,1,1"
    Propval(1).Value ="94,34,0,1,1"
    Doc = ThisComponent
    Dim FileName as String
    FileName = Doc.getURL()
    @@ -26,7 +26,7 @@ Dim len As Integer
    csv = FreeFile
    Open FileName For Input As csv
    Line Input #csv, first
    keys = split(first, ",")
    keys = split(first, "^")
    len = 0
    For Each i in keys
    len = len + 1
    @@ -60,7 +60,7 @@ Print #json, "["
    For line = 0 to lines-1
    Line Input #csv, CurrentLine
    If CurrentLine <>"" Then
    values = split(CurrentLine, ",")
    values = split(CurrentLine, "^")

    'Find the last non-empty or non-zero line for each entry
    lastValidLine = 0
  4. @aaronhoogstraten aaronhoogstraten revised this gist Dec 11, 2014. 1 changed file with 46 additions and 53 deletions.
    99 changes: 46 additions & 53 deletions OpenOffice Export to JSON
    View file
    Original file line number Diff line number Diff line change
    @@ -1,5 +1,5 @@
    'OpenOffice Basic macro for exporting a .ods spreadsheet to JSON (and CSV)
    'Project-specific formating I implemented: cull empty or "0" entries, leave "Products","" entry
    'Project-specific formating I implemented: cull empty or "0" entries

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

    @@ -29,17 +29,17 @@ Line Input #csv, first
    keys = split(first, ",")
    len = 0
    For Each i in keys
    len = len + 1
    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
    Line Input #csv, line
    If line <>"" Then
    lines = lines + 1
    End If
    Loop
    Close #csv

    @@ -58,53 +58,46 @@ 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 keys(i) = "Products" Then
    'Need to keep a blank products value because the game uses that to determine where in the list the products begin
    Print #json, keyValString
    ElseIf values(i) = "" OR values(i) = "0" Then
    'Don't include if value is blank or 0
    Else
    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
    End If
    Next i

    'Next object
    If line = lines-1 Then
    Print #json, " }"
    Else
    Print #json, " },"
    End If
    End If
    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


  5. @aaronhoogstraten aaronhoogstraten revised this gist Aug 22, 2014. 1 changed file with 52 additions and 46 deletions.
    98 changes: 52 additions & 46 deletions OpenOffice Export to JSON
    View file
    Original file line number Diff line number Diff line change
    @@ -29,17 +29,17 @@ Line Input #csv, first
    keys = split(first, ",")
    len = 0
    For Each i in keys
    len = len + 1
    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
    Line Input #csv, line
    If line <>"" Then
    lines = lines + 1
    End If
    Loop
    Close #csv

    @@ -58,47 +58,53 @@ Open fn For Output As json
    Print #json, "["

    For line = 0 to lines-1
    Line Input #csv, CurrentLine
    If CurrentLine <>"" Then
    values = split(CurrentLine, ",")
    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 keys(i) = "Products" Then
    'Need to keep a blank products value because the game uses that to determine where in the list the products begin
    Print #json, keyValString
    ElseIf values(i) = "" OR values(i) = "0" Then
    'Don't include if value is blank or 0
    Else
    If i = len-1 Then
    'Don't include comma after last entry
    Print #json, keyValLastEntry
    Else
    If i+1 >= len-1 Then
    If values(i+1) = "" OR values(i+1) = "0" Then
    'Don't include comma because this will actually be the last entry since the next one won't get written
    Print #json, keyValLastEntry
    Else
    Print #json, keyValString
    End If
    Else
    Print #json, keyValString
    End If

    End If
    End If
    Next i

    'Next object
    If line = lines-1 Then
    Print #json, " }"
    Else
    Print #json, " },"
    End If
    End If
    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 keys(i) = "Products" Then
    'Need to keep a blank products value because the game uses that to determine where in the list the products begin
    Print #json, keyValString
    ElseIf values(i) = "" OR values(i) = "0" Then
    'Don't include if value is blank or 0
    Else
    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
    End If
    Next i

    'Next object
    If line = lines-1 Then
    Print #json, " }"
    Else
    Print #json, " },"
    End If
    End If
    Next line


  6. @aaronhoogstraten aaronhoogstraten renamed this gist Aug 19, 2014. 1 changed file with 0 additions and 0 deletions.
    0 gistfile1.vb → OpenOffice Export to JSON
    View file
    File renamed without changes.
  7. @aaronhoogstraten aaronhoogstraten renamed this gist Aug 19, 2014. 1 changed file with 0 additions and 0 deletions.
    0 gistfile1.txt → gistfile1.vb
    View file
    File renamed without changes.
  8. @aaronhoogstraten aaronhoogstraten created this gist Aug 19, 2014.
    110 changes: 110 additions & 0 deletions gistfile1.txt
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,110 @@
    'OpenOffice Basic macro for exporting a .ods spreadsheet to JSON (and CSV)
    'Project-specific formating I implemented: cull empty or "0" entries, leave "Products","" entry

    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 ="44,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, ",")
    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 keys(i) = "Products" Then
    'Need to keep a blank products value because the game uses that to determine where in the list the products begin
    Print #json, keyValString
    ElseIf values(i) = "" OR values(i) = "0" Then
    'Don't include if value is blank or 0
    Else
    If i = len-1 Then
    'Don't include comma after last entry
    Print #json, keyValLastEntry
    Else
    If i+1 >= len-1 Then
    If values(i+1) = "" OR values(i+1) = "0" Then
    'Don't include comma because this will actually be the last entry since the next one won't get written
    Print #json, keyValLastEntry
    Else
    Print #json, keyValString
    End If
    Else
    Print #json, keyValString
    End If

    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