Solved

Importing over 65536 lines from CSV

Posted on 2012-03-29
5
309 Views
Last Modified: 2012-03-29
Hi,

I am trying to import large log files into excel and some of them exceed the 65536 line limit on excel.

To get around this I am importing them into seperate sheets so the 1st load of lines into one sheet and then continue on the next sheet and append the sheets name.  This is done by using the code below.  It all works great apart from the second sheet only has one line but should have more.  It starts from the correct next line in the csv (65537) but only that line.

Cheers

filea = Dir(strdocpath & "\")

Do While filea <> ""
If Right(filea, 7) = "HK1.csv" Then
logcomplete = False
inc = 0
Do While logcomplete = False
ActiveWorkbook.Worksheets.Add After:=Sheets(Worksheets.Count)
    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;" & strdocpath & "\" & filea _
        , Destination:=Range("$A$1"))
        .Name = Left(filea, Len(filea) - 4)
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 850
        .TextFileStartRow = (inc * 65536) + 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileOtherDelimiter = "`"
        .TextFileColumnDataTypes = Array(1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
    If inc = 0 Then
    ActiveSheet.Name = Left(filea, 10)
    Else
    ActiveSheet.Name = Left(filea, 10) & " (" & inc & ")"
    End If
    
    If ActiveSheet.UsedRange.Rows.Count = 65536 Then
    logcomplete = False
    inc = inc + 1
    Else
    logcomplete = True
    End If
    Loop
     End If
    filea = Dir
Loop

Open in new window

0
Comment
Question by:simonwait
  • 2
  • 2
5 Comments
 
LVL 46

Expert Comment

by:Martin Liss
ID: 37782897
What happens if you remove the following?


            Else
                logcomplete = True
0
 
LVL 1

Author Comment

by:simonwait
ID: 37783255
It then just keeps looping on the 1st file (but errors out when trying to give the new sheet the same name as the previous)
0
 
LVL 23

Expert Comment

by:wdosanjos
ID: 37783329
What's your Excel version?
0
 
LVL 1

Author Comment

by:simonwait
ID: 37783388
Mine is 2010 but needs to work on 2003 upwards
0
 
LVL 23

Accepted Solution

by:
wdosanjos earned 500 total points
ID: 37783502
I found a reference indicating that TextFileStartRow only accepts numbers between 1 and 32767.  That's probably what's causing the behavior you described.
BTW, the following MS KB Article includes a macro to accomplish what you want and you should be able to use as a reference.

Text files that are larger than 65,536 rows cannot be imported to Excel 97, Excel 2000, Excel 2002 and Excel 2003
http://support.microsoft.com/kb/120596
I hope this helps.
0

Featured Post

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

785 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question