Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 3 of 3

Thread: Import a large .JSON file into Excel

  1. #1
    Join Date
    10th 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

    VB:
    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
    10th 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: 4
    Last Post: February 7th, 2012, 09:54
  2. Replies: 2
    Last Post: April 24th, 2008, 03:29
  3. Open/Import Large Comma Delimited Ascii File
    By channel7 in forum EXCEL HELP
    Replies: 6
    Last Post: April 18th, 2008, 05:42
  4. Import Large Text File To Multiple Worksheets
    By steeven in forum EXCEL HELP
    Replies: 4
    Last Post: April 11th, 2008, 18:06
  5. Import Large TPS File
    By jjob68 in forum EXCEL HELP
    Replies: 2
    Last Post: December 16th, 2006, 21: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