Solved

Importing over 65536 lines from CSV

Posted on 2012-03-29
5
313 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 48

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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
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.

717 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