Hidden Characters?

Question

I've written a set of modules that parses data into .json files for upload into ArcGIS. Everything works great except that when I try to upload the .json files, it crashes and says there are unexpected characters. I don't see any characters when I open the .json in NotePad. I've tried simply re-saving the file. I've written the data to .txt files and changing the extension. I've cut the data out, saved the empty file, and pasted it back in.

The only thing that seems to work is: open the target .json file, copy the data into a new.txt file, close the target .json file, and then save the new.txt file as the target .json file. This fixes the issue, but it's very time consuming because there are 24 .json files I have to do this with.

Here's my code:

Sub WriteData()
Dim filepath As String
Dim celldata As String
Dim lastrow As String
Dim lastcol As String
Dim k As Long
Dim temp As Worksheet
Dim ouput As Worksheet
Dim fileout As Object
Dim fso As Object
'
Set temp = ActiveWorkbook.Sheets("Temp")
Set output = ActiveWorkbook.Sheets("Output Sheet")
Set fso = CreateObject("Scripting.FileSystemObject")
For i = 15 To 22
    For j = 3 To 5
        k = 3 * (i - 15) + j - 2
        filepath = Application.ActiveWorkbook.Path & "\JSON Files\" & temp.Cells(i, 1) & " " & temp.Cells(15, j) & ".json"
        Set fileout = fso.CreateTextFile(filepath, True, True)
        For l = 1 To Cells(Rows.Count, k).End(xlUp).Row + 1
            celldata = output.Cells(l, k)
            fileout.write celldata
        Next l
        fileout.Close
    Next j
Next i
Sheets("Output Sheet").Select
ActiveWorkbook.Save
End Sub

From the sample data you provided in the comment below (which you should edit into this question): That looks like XML. Whatever it is, it isn't JSON. — Dre, Jan 05 '18 at 21:21
Yes, the .jgz file is an XML file. Can I not just read that data in? I can open it in Notepad just fine and I can manipulate it like normal text in Excel. — Vance Noonan, Jan 05 '18 at 22:51
The Excel spreadsheet parses the XML data in the .jgz files to get the info I want for each geozone and puts it in .json format. I can show you the output data as well if you want, but that's pretty huge as well. — Vance Noonan, Jan 05 '18 at 23:23
My apologies, I misread "parses data into" as "parses data from" — Dre, Jan 06 '18 at 00:29

score 1 · Answer 1 · answered Jan 08 '18 at 19:39

I fixed it! There must be something going on when you use fileout.write. I changed it out to a simple Print command and it works! Here are the changes (I left the old lines in for reference):

k = 3 * (i - 15) + j - 2
filepath = Application.ActiveWorkbook.Path & "\JSON Files\" & temp.Cells(i, 1) & " " & temp.Cells(15, j) & ".json"
'Set fileout = fso.CreateTextFile(filepath, True, True)
Open filepath For Output As #1
For l = 1 To Cells(Rows.Count, k).End(xlUp).Row + 1
    celldata = output.Cells(l, k)
    'fileout.write celldata
    Print #1, celldata
Next l
'fileout.Close
Close #1

Hidden Characters?

1 Answers1