Loading
Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports



Results 1 to 3 of 3

Thread: Import a large .JSON file into Excel

  1. #1
    Join Date
    11th September 2012
    Posts
    2

    Import a large .JSON file into Excel

    Hi

    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

    Code:
    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
        Range("C1").Select
        
         
        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

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    17th May 2012
    Posts
    406

    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.

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    11th September 2012
    Posts
    2

    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.

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Importing JSON into Excel
    By dalben in forum EXCEL HELP
    Replies: 5
    Last Post: July 19th, 2014, 18:11
  2. Replies: 2
    Last Post: April 24th, 2008, 04:29
  3. Open/Import Large Comma Delimited Ascii File
    By channel7 in forum EXCEL HELP
    Replies: 6
    Last Post: April 18th, 2008, 06:42
  4. Import Large Text File To Multiple Worksheets
    By steeven in forum EXCEL HELP
    Replies: 4
    Last Post: April 11th, 2008, 19:06
  5. Import Large TPS File
    By jjob68 in forum EXCEL HELP
    Replies: 2
    Last Post: December 16th, 2006, 22:53

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno