Text import - more than 65,000 rows

[NikNik]

Did a quick search and couldn't find anything on this...any ideas?

I have a text file which is longer than 65,000 rows and which i am importing using the standard Wizard. On import Excel picks up the fact that the file is too large and explains that this can simply be addressed by repeating the import on a second worksheet and, in the wizard, selecting the option to exclude data already imported.....but I cannot find where this setting is. Am I missing something blatantly obvious here.

I am considering just splitting the text file down but this is not ideal

 

[Iridium]

Didn't write this so no credit to me

Sub ImportLargeFileADO()
'Imports text file into Excel workbook using ADO.
'If the number of records exceeds 65536 then it splits it over more than one sheet.
'http://www.danielklann.com/Excel/importing_large_text_files_into.htm

    Dim strFilePath As String, strFilename As String, strFullPath As String
    Dim lngCounter As Long
    Dim oConn As Object, oRS As Object, oFSObj As Object

    'Get a text file name
    strFullPath = Application.GetOpenFilename("Text Files (*.txt),*.txt", , "Please select text file...")

    If strFullPath = "False" Then Exit Sub  'User pressed Cancel on the open file dialog

    'This gives us a full path name e.g. C:\temp\folder\file.txt
    'We need to split this into path and file name
    Set oFSObj = CreateObject("SCRIPTING.FILESYSTEMOBJECT")

    strFilePath = oFSObj.GetFile(strFullPath).ParentFolder.path
    strFilename = oFSObj.GetFile(strFullPath).Name

    'Open an ADO connection to the folder specified
    Set oConn = CreateObject("ADODB.CONNECTION")
    oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
               "Data Source=" & strFilePath & ";" & _
               "Extended Properties=""text;HDR=Yes;FMT=Delimited"""

    Set oRS = CreateObject("ADODB.RECORDSET")

    'Now actually open the text file and import into Excel
    oRS.Open "SELECT * FROM " & strFilename, oConn, 3, 1, 1
    While Not oRS.EOF
        Sheets.Add
        ActiveSheet.Range("A1").CopyFromRecordset oRS, 65536
    Wend

    oRS.Close
    oConn.Close

End Sub

HTH

 

[sxhall]

Great peice of code this.

I changed the '.txt' file exstension to '.csv' for my needs and it still worked like a dream.

My Monday is now looking better, finally!!!!!