Дневник megavtogal.com

МЕНЮ
  • Контакты
  • Статьи

Как распарсить json в excel

Обновлено: 07.01.2025

I have the same issue as in Excel VBA: Parsed JSON Object Loop but cannot find any solution. My JSON has nested objects so suggested solution like VBJSON and vba-json do not work for me. I also fixed one of them to work properly but the result was a call stack overflow because of to many recursion of the doProcess function.

The best solution appears to be the jsonDecode function seen in the original post. It is very fast and highly effective; my object structure is all there in a generic VBA Object of type JScriptTypeInfo.

The issue at this point is that I cannot determine what will be the structure of the objects, therefore, I do not know beforehand the keys that will reside in each generic objects. I need to loop through the generic VBA Object to acquire the keys/properties.

If my parsing javascript function could trigger a VBA function or sub, that would be excellent.


949 1 1 gold badge 7 7 silver badges 10 10 bronze badges More easy to follow if the structures are similar but the "keys" are different. Out of interest where is the data coming from?

11 Answers 11

If you want to build on top of ScriptControl , you can add a few helper method to get at the required information. The JScriptTypeInfo object is a bit unfortunate: it contains all the relevant information (as you can see in the Watch window) but it seems impossible to get at it with VBA. However, the Javascript engine can help us:

  • If the JScriptTypeInfo instance refers to a Javascript object, For Each . Next won't work. However, it does work if it refers to a Javascript array (see GetKeys function).
  • The access properties whose name is only known at run-time, use the functions GetProperty and GetObjectProperty .
  • The Javascript array provides the properties length , 0 , Item 0 , 1 , Item 1 etc. With the VBA dot notation ( jsonObject.property ), only the length property is accessible and only if you declare a variable called length with all lowercase letters. Otherwise the case doesn't match and it won't find it. The other properties are not valid in VBA. So better use the GetProperty function.
  • The code uses early binding. So you have to add a reference to "Microsoft Script Control 1.0".
  • You have to call InitScriptEngine once before using the other functions to do some basic initialization.
67.1k 16 16 gold badges 149 149 silver badges 186 186 bronze badges The best ever answer . I have just completed a POC on how to call an JSON Restful service , parse the recieved json based on your answer and then displayed it in Excel . This was very well recieved by our clients. Thank you very much . +1 for this .. I got your solution to work for VBScript by stripping out the types and initializing by using the following: Set se = CreateObject("MSScriptControl.ScriptControl") . +1 Thanks!

UPDATE 3 (Sep 24 '17)

Check VBA-JSON-parser on GitHub for the latest version and examples. Import JSON.bas module into the VBA project for JSON processing.

UPDATE 2 (Oct 1 '16)

However if you do want to parse JSON on 64-bit Office with ScriptControl , then this answer may help you to get ScriptControl to work on 64-bit.

UPDATE (Oct 26 '15)

Trying to avoid that, I've created JSON parser based on RegEx's. Objects <> are represented by dictionaries, that makes possible to use dictionary's properties and methods: .Count , .Exists() , .Item() , .Items , .Keys . Arrays [] are the conventional zero-based VB arrays, so UBound() shows the number of elements. Here is the code with some usage examples:

One more opportunity of this JSON RegEx parser is that it works on 64-bit Office, where ScriptControl isn't available.

INITIAL (May 27 '15)

Here is one more method to parse JSON in VBA, based on ScriptControl ActiveX, without external libraries:

I'm trying to extract JSON data into Excel sheet as table by using the following code.

However, I'm getting the below error!

enter image description here

enter image description here

Why I'm getting this error? Kindly advise



935 1 1 gold badge 12 12 silver badges 31 31 bronze badges Do you have the JsonConverter module imported to the VBA project? I highly recommend to activate Option Explicit : In the VBA editor go to Tools › Options › Require Variable Declaration.

2 Answers 2

JSON

Going further there are objects within data array, each of them contains some properties that can be populated in rows on the worksheet:

data array

Here is VBA example showing how that values could be retrieved. Import JSON.bas module into the VBA project for JSON processing.

The output for data array for me is as follows:

output

BTW, the similar approach applied in other answers.


11.8k 3 3 gold badges 36 36 silver badges 81 81 bronze badges

I pasted your code into a test module and then imported the JsonConverter as an additional module in my empty workbook. The error you're getting is likely because you need to add the "Microsoft Scripting Runtime" library to your workbook. In the VBE go to the Tools-->References. menu and then scroll down and put a check mark next to the library. After doing this, your code parsed the JSON without issue.

However it did fail in your loop.

I highly recommend that you use Option Explicit at the top of your module. The variable types you think you're using (because I see you're attempting to use Hungarian notation) are not the types of the actual data necessarily. My suggestion is to use descriptive names for the variables to avoid confusion. Additionally, you should be looping on the oJSON("data") structure (which is a Collection by the way). Here is my suggestions put into practice:

Всем доброго времени суток. Хочу предложить метод парсинга JSON-строки c помощью RegEx для Excel VBA. В отличие от достаточно известного способа преобразования JSON-строки в объект с помощью ScriptControl:

данный метод не создает уязвимостей системы. Объекты <> представлены Scripting.Dictionary, что позволяет обращаться к их свойствам и методам: .Count, .Items, .Keys, .Exists(), .Item(). Массивы [] являются обычными VB-массивами с индексацией с нуля, поэтому количество элементов можно определить с помощью UBound(). Ниже привожу код с некоторыми примерами использования:

2 Ответ от omegastripes 2017-10-25 18:58:57

  • omegastripes
  • Разработчик
  • Неактивен

3 Ответ от Xameleon 2017-10-26 13:05:33

  • Xameleon
  • Разработчик
  • Неактивен











omegastripes, приетствую. А такой вариант Вам не подошёл ?

Передумал переделывать мир. Пашет и так, ну и ладно. Сделаю лучше свой !

4 Ответ от omegastripes 2017-10-26 13:45:09

  • omegastripes
  • Разработчик
  • Неактивен

Xameleon, из очевидных недостатков - ScriptControl не доступен в 64-битных версиях офиса, только через костыль с созданием ScriptControl в 32-битном процессе и передачей в VBA. По быстродействию и удобству использования элементов объекта в циклах - не готов сказать, нужно тестировать.

5 Ответ от Xameleon 2017-10-26 13:54:58

  • Xameleon
  • Разработчик
  • Неактивен











omegastripes, понял. Тогда альтернатива:

Передумал переделывать мир. Пашет и так, ну и ладно. Сделаю лучше свой !

6 Ответ от Xameleon 2017-10-26 14:38:41

  • Xameleon
  • Разработчик
  • Неактивен











Для оценки скорости работы можно потестить на таком варианте - загрузка 10 000 записей и их парсинг.
В код пришлось добавить метод в прототип объектов для чтения атрибутов объекта, так как злостный Word исправлял имена свойств, меняя первую букву на заглавную.

Передумал переделывать мир. Пашет и так, ну и ладно. Сделаю лучше свой !

7 Ответ от omegastripes 2017-10-28 19:24:36

  • omegastripes
  • Разработчик
  • Неактивен

Сравнил быстродействие парсинга и сериализации с помощью кода из второго и шестого постов, по среднему за 5 прогонов, в качестве сэмпла взял предложенные 10000 записей. В первом случае потребовалось 23,3 с на парсинг и 8,01 с на сериализацию, соответственно. Во втором случае - 0,468 с и 0,485 с, что в общем-то свойственно нативному eval(). В принципе, на этих результатах уже можно и заканчивать сравнение. Добавлю лишь немного.

Проверил скорость доступа к элементам в цикле.
Для второго поста For Each длится 0,0195 с, For Next - 0,0234 с:

Для шестого поста For Each выдает ошибку, For Next - 0,227 с:

Как реализовать доступ к элементам в цикле For Each?

Ну и для доведения кода из 6 поста до завершенного вида, на мой взгляд, все-таки нужно отвязать его от внешего источника, "инкапсулировав" JS код внутрь VBA (в свое время делал подобное, сохраняя JS код в виде base64-кодированных строк в VBA функции).

8 Ответ от Xameleon 2017-10-29 13:13:44

  • Xameleon
  • Разработчик
  • Неактивен











omegastripes, обычный For Each не получится, так как для этого объект должен поддерживать интерфейс IUnknown, а модель JavaScript массивов иначе устроена. Там возврат подразумевается через new Enumerator по виду близкому к Recordset. Подробнее вот тут: тут.

Так что либо делать "костыли", либо использовать счетчик. ) Но конечно остаётся ещё вариант с for i in array, но он тоже не очень красив.

Читайте также:

      
  • Как сделать маршрутную карту в ворде
    •   
  • Как работать в браузере opera
    •   
  • Mcafee как отключить в браузере
    •   
  • В чем отличие листа от страницы в excel
    •   
  • Как сделать наклонный текст в ворде
  • Контакты
  • Политика конфиденциальности