logo
down
shadow

Parsing JSON data into Excel sheet


Parsing JSON data into Excel sheet

Content Index :

Parsing JSON data into Excel sheet
Tag : json , By : codelurker
Date : December 05 2020, 12:18 PM

help you fix your problem First of all you need to examine the structure of the JSON response, using any online JSON viewer (e. g. http://jsonviewer.stack.hu/), where you can see that your JSON object contains data array, and several properties with scalar values:
Option Explicit

Sub Test()

    Dim sJSONString As String
    Dim vJSON
    Dim sState As String
    Dim aData()
    Dim aHeader()
    Dim vResult

    ' Retrieve JSON content
    With CreateObject("MSXML2.XMLHTTP")
        .Open "GET", "https://www.nseindia.com/live_market/dynaContent/live_watch/stock_watch/foSecStockWatch.json", True
        .send
        Do Until .readyState = 4: DoEvents: Loop
        sJSONString = .responseText
    End With
    ' Parse JSON sample
    JSON.Parse sJSONString, vJSON, sState
    If sState = "Error" Then MsgBox "Invalid JSON": End
    ' Convert raw JSON to 2d array and output to worksheet #1
    JSON.ToArray vJSON("data"), aData, aHeader
    With ThisWorkbook.Sheets(1)
        .Cells.Delete
        .Cells.WrapText = False
        OutputArray .Cells(1, 1), aHeader
        Output2DArray .Cells(2, 1), aData
        .Columns.AutoFit
    End With
    MsgBox "Completed"

End Sub

Sub OutputArray(oDstRng As Range, aCells As Variant)

    With oDstRng
        .Parent.Select
        With .Resize(1, UBound(aCells) - LBound(aCells) + 1)
            .NumberFormat = "@"
            .Value = aCells
        End With
    End With

End Sub

Sub Output2DArray(oDstRng As Range, aCells As Variant)

    With oDstRng
        .Parent.Select
        With .Resize( _
                UBound(aCells, 1) - LBound(aCells, 1) + 1, _
                UBound(aCells, 2) - LBound(aCells, 2) + 1)
            .NumberFormat = "@"
            .Value = aCells
        End With
    End With

End Sub

Comments
No Comments Right Now !

Boards Message :
You Must Login Or Sign Up to Add Your Comments .

Share : facebook icon twitter icon

Copy rows from Sheet(Data) to Sheet(Result) based on values from Sheet(Items)(Excel 2016)


Tag : excel , By : user183289
Date : March 29 2020, 07:55 AM
it helps some times An AutoFilter method can help you select the rows you are looking for and deliver them in one piece to the results worksheet.
Sub TestCopy_jpd()
    Dim v As Long, vITMs() As Variant, rng As Range
    With Worksheets("Items")
        With .Range(.Cells(1, 1), .Cells(Rows.Count, 1).End(xlUp))
            For Each rng In .Cells
                ReDim Preserve vITMs(v)
                vITMs(v) = rng.Value2
                v = v + 1
            Next rng
        End With
    End With
    With Worksheets("Data")
        If .AutoFilterMode Then .AutoFilterMode = False
        With .Cells(1, 1).CurrentRegion
            .AutoFilter field:=3, Criteria1:=vITMs, Operator:=xlFilterValues
            'step down one row off the header
            With .Resize(.Rows.Count - 1, .Columns.Count).Offset(1, 0)
                'are there rows to copy?
                If CBool(Application.Subtotal(103, .Cells)) Then
                    'there are visiblke rows - copy and paste them
                    .Cells.Copy _
                        Destination:=Worksheets("Result").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
                End If
            End With
        End With
        If .AutoFilterMode Then .AutoFilterMode = False
    End With
End Sub
With Worksheets("Data")
    For i = 1 To LastRow
        If Not IsError(Application.Match(.Cells(i, 3).Value, ThisWorkbook.Sheets("Items").Columns(1), 0)) Then
            .Rows(i).Copy Destination:=Worksheets("Result").Range("A" & j)
            j = j + 1
        End If
    Next i
End With

Copy and Pasting excel data from unopened excel sheet to destination excel sheet to the last row


Tag : excel-vba , By : Daniel Halsey
Date : March 29 2020, 07:55 AM
fixed the issue. Will look into that further I don't see you finding the last row in WB_1 nor can I see you using that?. My suggestion: Work with objects. It becomes easier to understand what you are doing and you have a better control over the code.
Is this what you are trying? (Untested)
Sub CopyToExcel()
    Dim wbDest As Workbook, wbSource As Workbook
    Dim wsDest As Worksheet, wsSource As Worksheet
    Dim lRowDest As Long, lRowSource As Long

    '~~> This is the workbook where you want to copy
    Set wbDest = ThisWorkbook
    '~~> This is the worksheet where you want to copy
    Set wsDest = wbDest.Sheets("DB2")
    '~~> This is the last row where the data will be copied
    lRowDest = wsDest.Range("A" & wsDest.Rows.Count).End(xlUp).Row + 1

    '~~> This is the workbook FROM where you want to copy
    Set wbSource = Workbooks.Open("C:\Analysis.xls")
    '~~> This is the worksheet FROM where you want to copy
    Set wsSource = wbSource.Sheets("Page 1")

    With wsSource
        '~~> This is the last row till where the data will be copied from
        lRowSource = .Range("A" & .Rows.Count).End(xlUp).Row

        '~~> Do the final Copy
        .Range("A1:R" & lRowSource).Copy wsDest.Range("C" & lRowDest)
    End With

    wbSource.Close (False)
End Sub

Parsing Excel sheet in Hebrew (.xlsx) to JSON produces question marks


Tag : javascript , By : BooTeK
Date : March 29 2020, 07:55 AM
With these it helps I believe it's only your console that's showing invalid characters. Try dumping the excel file contents to file like so:
"use strict";
const excelToJson = require("convert-excel-to-json");

// -> Read Excel File to Json Data

const excelData = excelToJson({
sourceFile: "customers.xlsx",
sheets: [
    {
    // Excel Sheet Name
    name: "Customers",

    header: {
        rows: 1
    }
    }
]
});

const fs = require("fs");
fs.writeFileSync("customers.json", JSON.stringify(excelData));
{"Customers":[{"A":"לקוח 1"},{"A":"לקוח 2"}]}

Parsing data from excel sheet


Tag : ruby-on-rails , By : John Studdert
Date : March 29 2020, 07:55 AM

Excel Macro to give Input Box, Create New Sheet, Copy Data from Original Sheet into New Sheet


Tag : excel , By : KingGuppy
Date : March 29 2020, 07:55 AM
Related Posts Related QUESTIONS :
  • How to read a file line by line and then output all lines compounded with sth in jq?
  • java.lang.RuntimeException: Failed : HTTP error code : 400 : Bad Request
  • How to check jq result is null or not?
  • CloudFormation - How to use Sub intrinsic function?
  • VBA: Nested JSON Object Structure for UK Bank Holidays - Run Time Errors 13, 438 and 449
  • JasperReports. Using value from parent json in child table
  • Output key value pairs as JSON from T-SQL query
  • Getting key value pairs with linux server
  • Modifying settings.json in vscode to add shell escape flag to pdflatex in latex workshop
  • Unable to render data into grid column using JSON results
  • JSON decode failed
  • How to remove brakets on Json respone
  • How to parse JSON-input
  • JSON syntax for getting personal query ID
  • detecting 400 response without JSON response
  • Newtonsoft deserializing Json incorrectly
  • Getting _jsonparsefailure with valid json in logstash
  • How to send Map as String in POST request in flutter?
  • Difficulty fetching json data from API using Angular
  • Reading JSON data for my select query in SQL Server
  • AWS CloudFormation Template (JSON) to Create EC2 - Unexpected Error
  • How to concat multiple fields to same line with jq
  • Issue sending POST request from Swift
  • Storing JSON in BigQuery
  • How to get the value of a key in a JSON?
  • How to validate JSON input in Go
  • Extracting specific and latest data from http webserver using golang
  • Incorrect type. Expected "object"
  • json_to_record with embedded encoded json in PostgreSQL >= 10
  • Nested json extract from powershell
  • Parsing Json using Golang
  • Type 'number' is not assignable to type 'string'. How to cast number to string
  • kubectl - format the resource quota values in json format
  • Initializing and inserting nested JSON data in Golang?
  • Open JSON files in PhpStorm built-in web server
  • Extract Badge ID from JSON in .gitlab-ci.yml
  • Sharing Json Schema files among projects with versioning
  • Export Json data to an excel file using Angular 4
  • Return an empty array instead of null with golang for json return with gin
  • can Kafka connect value conveter (JSONConverter) can be used to convert GPB?
  • read json and access multiple keys
  • Spark How to get number of Keys changed in two JSONS in Scala?
  • Break JSON in pager "less"
  • How to filter Map in Flutter?
  • Convert hashmap to simple object in Groovy
  • Not extracting json properly using json4s
  • How do I display JSON data in my tableView when objects have the same value?
  • Where I can parse IoT data in Azure so I can afterwards save it to SQL DB
  • How to unmarshal this json string
  • What's wrong with my JSON?
  • JSON parsing problem in BlackBerry
  • GWT: How can I use JsonpRequestBuilder to handle a Json response of a list
  • JSON feed to Java Object
  • json character encoding problem
  • JSON Loading Speed Optimization: Use online API vs. create my own API based on that?
  • How can I access some JSON documents from my AWS lambda function?
  • How to extract multiple correlating variables from a JSon
  • Schema/Resolve for nested objects graphql/mongoose
  • Dynamics 365 opportunity EntityType processid and stagename error
  • How to convert dataframe output to json format and then Normalize the data?
  • shadow
    Privacy Policy - Terms - Contact Us © scrbit.com