No announcement yet.

Import a large .JSON file into Excel

  • Filter
  • Time
  • Show
Clear All
new posts

  • Import a large .JSON file into Excel


    I have a large file which I import into excel and run a macro to copy the data into columns. This moves down until the file is complete (approx 1mil rows in excel). My problem is the .JSON file is a lot bigger than the row limits on excel.

    Does anyone know how I can import the data into the columns instead of the rows ? Or have a another solution which will allow excel to read and analyse the whole file? Notepad is useless for me as I need to be able to sort/filter and manipulate data.

    Code is below. It is rather slow ! Thanks Jason

    Option Explicit
    Sub Jason()
    Application.ScreenUpdating = False
    Application.Calculation = xlManual
    Dim counter As Long
    Sheets("Raw Data").Select
        Dim lrow As Long, nrow As Long, i As Long
        lrow = 0
        nrow = 0
        lrow = Cells(Rows.Count, 2).End(xlUp).Row
        nrow = Cells(Rows.Count, 4).End(xlUp).Row
        lrow = lrow + 14
        For i = 1 To lrow Step 20
            counter = counter + 19
            Range(Cells(i + 1, 3), Cells(i + 19, 3)).Copy
            Range("C" & nrow).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
            False, Transpose:=True
            nrow = nrow + 1
                If nrow = 52429 Then
                MsgBox i
                Cells(i, 1) = "end macro"
                Exit Sub
                End If
        Next i
        Application.ScreenUpdating = True
        Application.Calculation = xlCalculationAutomatic
    End Sub

  • #2
    Re: Import a large .JSON file into Excel

    Use Access, or any other database you have access to. If you can't do the manipulation you need in the database directly, you ought to at least be able to spit it out in manageable chunks which you can move into excel.


    • #3
      Re: Import a large .JSON file into Excel

      Thanks, I'll give that a go. I hope I would be able to get away from using a database but I guess not.