Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Importing over 65536 lines from CSV

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

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

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

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

609 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