Solved

Importing over 65536 lines from CSV

Posted on 2012-03-29
5
312 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 47

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

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

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

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

730 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