json - Unicode characters incorrect when parsing JSON

问题描述

This is the link of JSON sample MYSAMPLEJSON

This is sample

[{"{\"Title\":\"Close-Up\",\"Year\":\"1990\",\"Rated\":\"NOT RATED\",\"Released\":\"30 Oct 1991\",\"Runtime\":\"98 min\",\"Genre\":\"Documentary, Biography, Crime\",\"Director\":\"Abbas Kiarostami\",\"Writer\":\"Abbas Kiarostami\",\"Actors\":\"Matthew Barney, Björk, Mohsen Makhmalbaf, Abolfazl Ahankhah, Mehrdad Ahankhah\",\"Plot\":\"The true story of Hossain Sabzian that impersonated the director Mohsen Makhmalbaf to convince a family they would star in his so-called new film.\",\"Language\":\"Persian, Azerbaijani\",\"Country\":\"Iran\",\"Awards\":\"2 wins.\",\"Poster\":\"https://m.media-amazon.com/images/M/MV5BMzE4Mjc0MjI1N15BMl5BanBnXkFtZTcwNjI3MzEzMw@@._V1_SX300.jpg\",\"Ratings\":[{\"Source\":\"Internet Movie Database\",\"Value\":\"8.3/10\"},{\"Source\":\"Rotten Tomatoes\",\"Value\":\"88%\"}],\"Metascore\":\"N/A\",\"imdbRating\":\"8.3\",\"imdbVotes\":\"11,546\",\"imdbID\":\"tt0100234\",\"Type\":\"movie\",\"DVD\":\"19 Feb 2002\",\"BoxOffice\":\"N/A\",\"Production\":\"Zeitgeist Films\",\"Website\":\"http://www.zeitgeistfilm.com/current/closeup/closeup.html\",\"Response\":\"True\"}":true},{"{\"Title\":\"Life on Earth\",\"Year\":\"1998\",\"Rated\":\"N/A\",\"Released\":\"09 Jun 1999\",\"Runtime\":\"61 min\",\"Genre\":\"Comedy, Drama\",\"Director\":\"Abderrahmane Sissako\",\"Writer\":\"Abderrahmane Sissako\",\"Actors\":\"Abderrahmane Sissako, Nana Baby, Mohamed Sissako, Bourama Coulibaly\",\"Plot\":\"In the last days of 1999, after a few shots of a French supermarket, abundant in food and color, we hear Dramane compose a letter home to his father in Mali whom he then visits in the ...\",\"Language\":\"French, Bambara\",\"Country\":\"Mali, Mauritania, France\",\"Awards\":\"10 wins.\",\"Poster\":\"https://images-na.ssl-images-amazon.com/images/M/MV5BNDAxMzg2MDE1M15BMl5BanBnXkFtZTcwMzU3MDMyMQ@@._V1_SX300.jpg\",\"Ratings\":[{\"Source\":\"Internet Movie Database\",\"Value\":\"6.8/10\"}],\"Metascore\":\"N/A\",\"imdbRating\":\"6.8\",\"imdbVotes\":\"297\",\"imdbID\":\"tt0157160\",\"Type\":\"movie\",\"DVD\":\"N/A\",\"BoxOffice\":\"N/A\",\"Production\":\"N/A\",\"Website\":\"N/A\",\"Response\":\"True\"}":true},{"{\"Title\":\"Waiting for Happiness\",\"Year\":\"2002\",\"Rated\":\"N/A\",\"Released\":\"15 Jan 2003\",\"Runtime\":\"90 min\",\"Genre\":\"Drama, Music\",\"Director\":\"Abderrahmane Sissako\",\"Writer\":\"Abderrahmane Sissako\",\"Actors\":\"Khatra Ould Abder Kader, Maata Ould Mohamed Abeid, Mohamed Mahmoud Ould Mohamed, Fatimetou Mint Ahmeda\",\"Plot\":\"The story of two people who cross paths in Nouhadhibou.\",\"Language\":\"French, Hassanya, Mandarin\",\"Country\":\"France, Mauritania\",\"Awards\":\"8 wins & 3 nominations.\",\"Poster\":\"https://images-na.ssl-images-amazon.com/images/M/MV5BMTI1MzA2NDc3Nl5BMl5BanBnXkFtZTcwNjcxNzY5MQ@@._V1_SX300.jpg\",\"Ratings\":[{\"Source\":\"Internet Movie Database\",\"Value\":\"6.8/10\"},{\"Source\":\"Rotten Tomatoes\",\"Value\":\"76%\"}],\"Metascore\":\"N/A\",\"imdbRating\":\"6.8\",\"imdbVotes\":\"775\",\"imdbID\":\"tt0308363\",\"Type\":\"movie\",\"DVD\":\"11 Jun 2007\",\"BoxOffice\":\"N/A\",\"Production\":\"New Yorker Films\",\"Website\":\"N/A\",\"Response\":\"True\"}":true},{"{\"Title\":\"Delbaran\",\"Year\":\"2001\",\"Rated\":\"N/A\",\"Released\":\"27 Mar 2002\",\"Runtime\":\"96 min\",\"Genre\":\"Drama\",\"Director\":\"Abolfazl Jalili\",\"Writer\":\"Abolfazl Jalili, Reza Saberi (story)\",\"Actors\":\"Kaim Alizadeh, Rahmatollah Ebrahimi, Hossein Hashemian, Ahmad Mahdavi\",\"Plot\":\"Acclaimed director Abolfazl Jalili offers a compassionate story of the young Afghan refugee who lives illegally in Iran. 14-year-old Kaim drifts to the Delbaran crossing on the Afghan-Iran ...\",\"Language\":\"Persian\",\"Country\":\"Iran, Japan\",\"Awards\":\"5 wins & 3 nominations.\",\"Poster\":\"https://images-na.ssl-images-amazon.com/images/M/MV5BMjA4OTQyNTQzM15BMl5BanBnXkFtZTcwMTM0MDYxMg@@._V1_SX300.jpg\",\"Ratings\":[{\"Source\":\"Internet Movie Database\",\"Value\":\"6.8/10\"},{\"Source\":\"Rotten Tomatoes\",\"Value\":\"60%\"}],\"Metascore\":\"N/A\",\"imdbRating\":\"6.8\",\"imdbVotes\":\"228\",\"imdbID\":\"tt0293082\",\"Type\":\"movie\",\"DVD\":\"23 Dec 2008\",\"BoxOffice\":\"N/A\",\"Production\":\"Facets\",\"Website\":\"N/A\",\"Response\":\"True\"}":true},{"{\"Title\":\"A Horrible Way to Die\",\"Year\":\"2010\",\"Rated\":\"R\",\"Released\":\"19 Aug 2011\",\"Runtime\":\"87 min\",\"Genre\":\"Drama, Horror, Thriller\",\"Director\":\"Adam Wingard\",\"Writer\":\"Simon Barrett\",\"Actors\":\"AJ Bowen, Amy Seimetz, Joe Swanberg, Brandon Carroll\",\"Plot\":\"An escaped murderer is in pursuit of his ex-girlfriend, who has fled to start a new life in a small town.\",\"Language\":\"English\",\"Country\":\"USA\",\"Awards\":\"3 wins.\",\"Poster\":\"https://m.media-amazon.com/images/M/MV5BMTAzMjAyMzg2MzheQTJeQWpwZ15BbWU3MDg2NTc1MzY@._V1_SX300.jpg\",\"Ratings\":[{\"Source\":\"Internet Movie Database\",\"Value\":\"5.3/10\"},{\"Source\":\"Rotten Tomatoes\",\"Value\":\"67%\"},{\"Source\":\"Metacritic\",\"Value\":\"52/100\"}],\"Metascore\":\"52\",\"imdbRating\":\"5.3\",\"imdbVotes\":\"3,036\",\"imdbID\":\"tt1617620\",\"Type\":\"movie\",\"DVD\":\"06 Sep 2011\",\"BoxOffice\":\"N/A\",\"Production\":\"Anchor Bay Entertainment\",\"Website\":\"http://ahwtd.blogspot.com/\",\"Response\":\"True\"}":true},{"{\"Title\":\"Towelhead\",\"Year\":\"2007\",\"Rated\":\"R\",\"Released\":\"26 Sep 2008\",\"Runtime\":\"124 min\",\"Genre\":\"Drama\",\"Director\":\"Alan Ball\",\"Writer\":\"Alan Ball (written for the screen by), Alicia Erian (novel)\",\"Actors\":\"Summer Bishil, Chris Messina, Maria Bello, Peter Macdissi\",\"Plot\":\"A young Arab-American girl struggles with her sexual obsession, a bigoted Army reservist and her strict father during the Gulf War.\",\"Language\":\"English, French, Arabic, Spanish\",\"Country\":\"USA\",\"Awards\":\"1 win & 3 nominations.\",\"Poster\":\"https://ia.media-imdb.com/images/M/MV5BMTI3MTY3OTM5MF5BMl5BanBnXkFtZTcwOTQ4MTA3MQ@@._V1_SX300.jpg\",\"Ratings\":[{\"Source\":\"Internet Movie Database\",\"Value\":\"7.0/10\"},{\"Source\":\"Rotten Tomatoes\",\"Value\":\"49%\"},{\"Source\":\"Metacritic\",\"Value\":\"57/100\"}],\"Metascore\":\"57\",\"imdbRating\":\"7.0\",\"imdbVotes\":\"10,855\",\"imdbID\":\"tt0787523\",\"Type\":\"movie\",\"DVD\":\"30 Dec 2008\",\"BoxOffice\":\"$277,354\",\"Production\":\"Warner Independent\",\"Website\":\"http://www.towelhead-themovie.com/\",\"Response\":\"True\"}":true},{"{\"Title\":\"Moloch\",\"Year\":\"1999\",\"Rated\":\"N/A\",\"Released\":\"13 Oct 1999\",\"Runtime\":\"108 min\",\"Genre\":\"Drama\",\"Director\":\"Aleksandr Sokurov\",\"Writer\":\"Yuriy Arabov, Marina Koreneva\",\"Actors\":\"Elena Rufanova, Leonid Mozgovoy, Irina Sokolova, Yelena Spiridonova\",\"Plot\":\"In 1942, in Bavaria, Eva Braun is alone, when Adolf Hitler arrives with Dr. Josef Goebbels and his wife Magda Goebbels and Martin Bormann to spend a couple of days without talking politics.\",\"Language\":\"German\",\"Country\":\"Russia, Germany, Japan, Italy, France\",\"Awards\":\"7 wins & 8 nominations.\",\"Poster\":\"https://images-na.ssl-images-amazon.com/images/M/MV5BMTU5ODUzNTE1MV5BMl5BanBnXkFtZTcwNjQzNjgyMQ@@._V1_SX300.jpg\",\"Ratings\":[{\"Source\":\"Internet Movie Database\",\"Value\":\"6.8/10\"}],\"Metascore\":\"N/A\",\"imdbRating\":\"6.8\",\"imdbVotes\":\"1,898\",\"imdbID\":\"tt0199777\",\"Type\":\"movie\",\"DVD\":\"08 Mar 2005\",\"BoxOffice\":\"N/A\",\"Production\":\"N/A\",\"Website\":\"N/A\",\"Response\":\"True\"}":true},{"{\"Title\":\"Mother and Son\",\"Year\":\"1997\",\"Rated\":\"N/A\",\"Released\":\"06 Feb 1998\",\"Runtime\":\"73 min\",\"Genre\":\"Drama\",\"Director\":\"Aleksandr Sokurov\",\"Writer\":\"Yuriy Arabov\",\"Actors\":\"Aleksei Ananishnov, Gudrun Geyer\",\"Plot\":\"A slow and poignant story of love and patience told via a dying mother nursed by her devoted son. The simple narrative is a thread woven among the deeply spiritual images of the countryside...\",\"Language\":\"Russian\",\"Country\":\"Russia, Germany\",\"Awards\":\"4 wins & 4 nominations.\",\"Poster\":\"https://images-na.ssl-images-amazon.com/images/M/MV5BMTQwNTk4Nzk1MV5BMl5BanBnXkFtZTcwOTc0MDAyMQ@@._V1_SX300.jpg\",\"Ratings\":[{\"Source\":\"Internet Movie Database\",\"Value\":\"7.6/10\"}],\"Metascore\":\"N/A\",\"imdbRating\":\"7.6\",\"imdbVotes\":\"3,143\",\"imdbID\":\"tt0119711\",\"Type\":\"movie\",\"DVD\":\"N/A\",\"BoxOffice\":\"N/A\",\"Production\":\"N/A\",\"Website\":\"N/A\",\"Response\":\"True\"}":true},{"{\"Title\":\"Russian Ark\",\"Year\":\"2002\",\"Rated\":\"NOT RATED\",\"Released\":\"19 Apr 2003\",\"Runtime\":\"99 min\",\"Genre\":\"Drama, Fantasy, History\",\"Director\":\"Aleksandr Sokurov\",\"Writer\":\"Boris Khaimsky (dialogue), Anatoli Nikiforov, Svetlana Proskurina (dialogue), Aleksandr Sokurov (dialogue), Aleksandr Sokurov\",\"Actors\":\"Sergey Dreyden, Mariya Kuznetsova, Leonid Mozgovoy, Mikhail Piotrovsky\",\"Plot\":\"A 19th century French aristocrat, notorious for his scathing memoirs about life in Russia, travels through the Russian State Hermitage Museum and encounters historical figures from the last 200+ years.\",\"Language\":\"Russian, Persian\",\"Country\":\"Russia, Germany, Japan, Canada, Finland, Denmark\",\"Awards\":\"10 wins & 15 nominations.\",\"Poster\":\"https://m.media-amazon.com/images/M/MV5BMjEyNDEwOTU0NV5BMl5BanBnXkFtZTYwNjk0NTk5._V1_SX300.jpg\",\"Ratings\":[{\"Source\":\"Internet Movie Database\",\"Value\":\"7.4/10\"},{\"Source\":\"Rotten Tomatoes\",\"Value\":\"89%\"},{\"Source\":\"Metacritic\",\"Value\":\"86/100\"}],\"Metascore\":\"86\",\"imdbRating\":\"7.4\",\"imdbVotes\":\"16,440\",\"imdbID\":\"tt0318034\",\"Type\":\"movie\",\"DVD\":\"09 Sep 2003\",\"BoxOffice\":\"$37,439\",\"Production\":\"Wellspring Media\",\"Website\":\"http://www.russianark.spb.ru/eng/index.html\",\"Response\":\"True\"}":true},{"{\"Title\":\"The Father\",\"Year\":\"1996\",\"Rated\":\"N/A\",\"Released\":\"10 Feb 1999\",\"Runtime\":\"91 min\",\"Genre\":\"Drama\",\"Director\":\"Majid Majidi\",\"Writer\":\"Majid Majidi, Mehdi Shojai\",\"Actors\":\"Hossein Abedini, Mohammad Kasebi, Parivash Nazarieh, Hassan Sadeghi\",\"Plot\":\"Mehrollah is a 14-year-old boy who is forced to find a job to support his family after his father dies. He travels to the southern parts of Iran, looking for work. Upon his return to his hometown, he notices certain changes in his family.\",\"Language\":\"Persian\",\"Country\":\"Iran\",\"Awards\":\"9 wins & 3 nominations.\",\"Poster\":\"https://images-na.ssl-images-amazon.com/images/M/MV5BMTUxMTI1OTAyOF5BMl5BanBnXkFtZTcwOTc3OTQyMg@@._V1_SX300.jpg\",\"Ratings\":[{\"Source\":\"Internet Movie Database\",\"Value\":\"7.6/10\"}],\"Metascore\":\"N/A\",\"imdbRating\":\"7.6\",\"imdbVotes\":\"1,122\",\"imdbID\":\"tt0117315\",\"Type\":\"movie\",\"DVD\":\"27 Jan 2009\",\"BoxOffice\":\"N/A\",\"Production\":\"N/A\",\"Website\":\"N/A\",\"Response\":\"True\"}":true},{"{\"Title\":\"Inside\",\"Year\":\"2007\",\"Rated\":\"R\",\"Released\":\"13 Jun 2007\",\"Runtime\":\"82 min\",\"Genre\":\"Horror\",\"Director\":\"Alexandre Bustillo, Julien Maury\",\"Writer\":\"Julien Maury (by), Alexandre Bustillo (by), Alexandre Bustillo (scenario,  adaptation and dialogue)\",\"Actors\":\"Alysson Paradis, Jean-Baptiste Tabourin, Claude Lulé, Dominique Frot\",\"Plot\":\"Four months after the death of her husband, a woman on the brink of motherhood is tormented in her home by a strange woman who wants her unborn baby.\",\"Language\":\"French\",\"Country\":\"France\",\"Awards\":\"9 wins & 5 nominations.\",\"Poster\":\"https://m.media-amazon.com/images/M/MV5BNzA2OTM3NGMtYTVkZC00ZGM2LWEzNzYtMjk1OGEwYjFhZjA0XkEyXkFqcGdeQXVyMTQxNzMzNDI@._V1_SX300.jpg\",\"Ratings\":[{\"Source\":\"Internet Movie Database\",\"Value\":\"6.8/10\"},{\"Source\":\"Rotten Tomatoes\",\"Value\":\"83%\"}],\"Metascore\":\"N/A\",\"imdbRating\":\"6.8\",\"imdbVotes\":\"33,241\",\"imdbID\":\"tt0856288\",\"Type\":\"movie\",\"DVD\":\"15 Apr 2008\",\"BoxOffice\":\"N/A\",\"Production\":\"The Weinstein Company\",\"Website\":\"http://www.alinterieur.fr/\",\"Response\":\"True\"}":true},{"{\"Title\":\"Matthew Barney: No Restraint\",\"Year\":\"2006\",\"Rated\":\"N/A\",\"Released\":\"04 Oct 2008\",\"Runtime\":\"72 min\",\"Genre\":\"Documentary\",\"Director\":\"Alison Chernick\",\"Writer\":\"Alison Chernick\",\"Actors\":\"Matthew Barney, Björk\",\"Plot\":\"How does artist Matthew Barney use 45,000 pounds of petroleum jelly, a factory whaling vessel and traditional Japanese rituals to create his latest art project? Barney plowed the waters off...\",\"Language\":\"Japanese, English\",\"Country\":\"USA\",\"Awards\":\"1 nomination.\",\"Poster\":\"https://images-na.ssl-images-amazon.com/images/M/MV5BMTk1OTY5OTc3Nl5BMl5BanBnXkFtZTcwMDc2Mjc0MQ@@._V1_SX300.jpg\",\"Ratings\":[{\"Source\":\"Internet Movie Database\",\"Value\":\"6.0/10\"},{\"Source\":\"Rotten Tomatoes\",\"Value\":\"52%\"},{\"Source\":\"Metacritic\",\"Value\":\"59/100\"}],\"Metascore\":\"59\",\"imdbRating\":\"6.0\",\"imdbVotes\":\"108\",\"imdbID\":\"tt0481574\",\"Type\":\"movie\",\"DVD\":\"01 May 2007\",\"BoxOffice\":\"N/A\",\"Production\":\"IFC First Take\",\"Website\":\"http://www.matthewbarneynorestraint.com/\",\"Response\":\"True\"}":true},{"{\"Title\":\"Vegas: Based on a True Story\",\"Year\":\"2008\",\"Rated\":\"N/A\",\"Released\":\"24 Apr 2009\",\"Runtime\":\"102 min\",\"Genre\":\"Drama\",\"Director\":\"Amir Naderi\",\"Writer\":\"Susan Brennan (screenplay), Bliss Esposito (screenplay), Charlie Lake Keaton (screenplay), Amir Naderi (screenplay)\",\"Actors\":\"Mark Greenfield, Alexis Hart, Nancy La Scala, Zach Thomas\",\"Plot\":\"The film takes place away from the glittering strip of mega casinos, but the greed of Sin City is just as pervasive on the desert outskirts. This is where a happy family learns of a ...\",\"Language\":\"English\",\"Country\":\"USA\",\"Awards\":\"2 wins & 2 nominations.\",\"Poster\":\"https://images-na.ssl-images-amazon.com/images/M/MV5BMjE1MzYzMDIyMV5BMl5BanBnXkFtZTgwNjc2NjU5OTE@._V1_SX300.jpg\",\"Ratings\":[{\"Source\":\"Internet Movie Database\",\"Value\":\"7.0/10\"}],\"Metascore\":\"N/A\",\"imdbRating\":\"7.0\",\"imdbVotes\":\"141\",\"imdbID\":\"tt1283971\",\"Type\":\"movie\",\"DVD\":\"06 Jul 2010\",\"BoxOffice\":\"N/A\",\"Production\":\"Alphaville Films\",\"Website\":\"N/A\",\"Response\":\"True\"}":true}]

And I have used this code to parse json

Sub Test()
Dim ky1         As Collection
Dim ky2         As Collection
Dim ky3         As Dictionary
Dim fso         As Object
Dim jsonFile    As Object
Dim Json        As Object
Dim arr()       As String
Dim jsonText    As String
Dim i           As Long
Dim j           As Long

Set fso = CreateObject("Scripting.FileSystemObject")
Set jsonFile = fso.OpenTextFile(ThisWorkbook.Path & "\Sample.json")
jsonText = jsonFile.ReadAll
Set Json = JsonConverter.ParseJson(jsonText)

Set ky1 = Json("results")
Set ky2 = ky1(1)("allTests")
ReDim arr(1 To ky2.Count)

For i = 1 To ky2.Count
    Set ky3 = ky2(i)
    If InStr(ky3.keys(0), "Movie not found") = 0 Then
        j = j + 1
        arr(j) = ky3.keys(0)
    End If
Next i

Application.ScreenUpdating = False
    Sheets("Sheet1").Range("A1").Resize(UBound(arr)) = Application.Transpose(arr)
Application.ScreenUpdating = True
End Sub

And it is working well and as expected. The only problem is that I found out incorrect in some of the results that have unicode characters for example debug the macro and find arr(12) >> it is supposed that there is the value "Matthew Barney, Björk\" but it appears on worksheet incorrectly

As for the simple sample try arr(1)

Thanks advanced for help

标签： jsonexcelvbaparsing

json - Unicode characters incorrect when parsing JSON

问题描述

解决方案

推荐阅读