?
Solved

Importing over 65536 lines from CSV

Posted on 2012-03-29
5
Medium Priority
?
314 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 49

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 2000 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

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,…
This article describes a serious pitfall that can happen when deleting shapes using VBA.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

770 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