• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to import TXT file with more than 1048576 rows of data in Excel

Not able to understand, why macro gives error when we try other method.
Yes 'cause obviously this is not the same method …​
It is not allowed hence I would have uploaded at the first post itself.
I understood but you can upload one original on a files host website password protected​
and give me the link and the password to access the file under a private conversation​
so this is not shared on the net on a public forum and once I uploaded it you can delete it from the website …​
If you can't you can read the file with a specific text editor (some have the option to read on hexa format)​
in order to see the specifics or you can try to read the file in binary format under VBA and find out how to parse it …​
 
ThrottleWorks. An alternative to consider. PQ can handle many more lines than Native Excel.

Attached is a file with both of your samples loaded and parsed based upon the "|" delimiter. This was done with Power Query. You can review the Mcode and detailed steps by clicking on Data-->Queries and Connections. A window will open to the right. Right Click on the Query and then click on Edit to see all the detail and steps.

Alan
 

Attachments

  • PQThrottle.xlsx
    38.1 KB · Views: 7
Hi @AlanSidman sir, PowerQuery is not allowed that is why not able to use your solution.
Hence posting below reply however thanks a lot for the help.

Hi @Marc L sir, if I could disturb you again on this.
Am using code copied from below URL. It is working good. Importing data and splitting in worksheets.
However this code takes time to run if file size is heavy, for example, if row size is 1,20,000 macro is taking around 20 minutes to complete.
I am using a different code to delimit the data. Will post below this code.
If possible can you please help me to make this code faster.

The solutions you have provided are great but somehow not working with my data due to some format issue.
Hence am trying to use below code.


Code:
'https://answers.microsoft.com/en-us/msoffice/forum/all/excel-vba-macro-to-import-large-txt-files-into/87150313-ea59-449a-8015-1a2e4a2bdf27
Option Explicit '<<< FIRST LINE
'Const Nmax As Long = 1048576 '<< max number of entries per sheet
Const Nmax As Long = 50000 '<< max number of entries per sheet

'Const N1 As Long = 900000 '<< split txt file every 900000 entries, change as needed / Nmax>=N1
Const N1 As Long = 10000 '<< split txt file every 900000 entries, change as needed / Nmax>=N1

Dim obj As Object
Dim oFl As Object
Dim sh As Worksheet, newSh As Worksheet
Dim t As Long, N As Long, x As Long, tt As Long, c As Long
Dim sFile As String
Dim sLine As String
Dim v As Variant, vv As Variant, v1 As Variant
Dim t1 As Double, t2 As Double
Sub Split_Large_TXT()
    Dim MacroBook As Workbook
    Dim MapSht As Worksheet
    Dim TempSht As Worksheet
    Dim MacroSht As Worksheet
   
    Set MacroBook = ThisWorkbook
    Set MapSht = MacroBook.Worksheets("Mapping")
    Set MacroSht = MacroBook.Worksheets("Macro")
   
    'Sep 11, 2016
    If N1 > Nmax Then Exit Sub
        t1 = Now
       
        sFile = MapSht.Cells(TRng.Row, 4) & "\" & MapSht.Cells(TRng.Row, 5)
       
        With Application
            .Calculation = xlManual
        End With
   
        For Each sh In Sheets
            If sh.Name Like "*Temp*" Then sh.Delete
        Next
       
        ReDim v(1 To N1, 1 To 1)
        ReDim vv(1 To N1, 1 To 1)
        t = 1
       
        Set newSh = Sheets.Add(after:=ActiveSheet)
        ActiveSheet.Name = "Temp" & t
        Set obj = CreateObject("Scripting.FileSystemObject")
        Set oFl = obj.OpenTextFile(sFile, 1)
        N = 0
   
        Do Until oFl.AtEndOfStream
            sLine = oFl.ReadLine
            N = N + 1
            v(N, 1) = sLine
            If N = N1 Then
            Cells(1, 1).Resize(N1) = v
            c = UBound(Split(Cells(1, 1).Value, vbTab))
            SplitData c, v, vv, N1
            ReDim v(1 To N1, 1 To 1)
            ReDim vv(1 To N1, 1 To 1)
            t = t + 1
            Set newSh = Sheets.Add(after:=ActiveSheet)
            ActiveSheet.Name = "Temp" & t
            N = 0
            End If
        Loop
        oFl.Close
   
        Cells(1, 1).Resize(N1) = v
        SplitData c, v, vv, N1
        With Application
        .Calculation = xlAutomatic
    End With
   
    t2 = Now
    'MsgBox "time hh.mm.ss = " & Format(t2 - t1, "hh.mm.ss") & " to process " & MapSht.Cells(TRng.Row, 5)
    End Sub

Sub SplitData(c As Long, v As Variant, vv As Variant, N1 As Long)
'''''    On Error Resume Next
'''''    For x = 0 To c
'''''        tt = 1
'''''        For Each v1 In v
'''''            vv(tt, 1) = Split(v1, vbTab)(x)
'''''            tt = tt + 1
'''''        Next v1
'''''        Cells(1, 1 + x).Resize(N1) = vv
'''''    Next x
End Sub

One data is imported and split in different worksheets I use below code to delimit the data.
 
Code:
Sub SplitName()
    Dim TempLr_1 As Long
    Dim MacroBook As Workbook
    Dim MapSht As Worksheet
    Dim TempSht As Worksheet
    Dim TempLr As Long
        
    Dim MyArray() As String, MyString As String, i As Variant, N As Integer
    Dim TempRng As Range
    Dim TRng As Range
    Dim ws As Worksheet
    
    For Each ws In ThisWorkbook.Worksheets
        ThisWorkbook.Activate
        ws.Select
        If ws.Name Like "*Temp*" Then
            Set TempSht = ws
            TempLr = TempSht.Cells(Rows.Count, 1).End(xlUp).Row - 1 'is the -1 necessary?
            If TempLr > 1 Then
                If ws.Name = "Temp1" Then
                    TempLr = TempSht.Cells(Rows.Count, 1).End(xlUp).Row
                    TempSht.Range("A2:A" & TempLr).TextToColumns DataType:=xlDelimited, Other:=True, OtherChar:="|"
                End If
            End If
            If ws.Name <> "Temp1" Then
                TempLr_1 = TempSht.Cells(Rows.Count, 2).End(xlUp).Row - 1
                TempLr = TempSht.Cells(Rows.Count, 1).End(xlUp).Row - 1 'is the -1 necessary?
                If TempLr = TempLr_1 Then
                        TempLr = TempLr_1
                    Else
                        TempLr = TempLr + 1
                End If
                
                TempSht.Range("A1:A" & TempLr).TextToColumns DataType:=xlDelimited, Other:=True, OtherChar:="|"
            End If
        End If
    Next
End Sub
 
As my fast procedure can't work with your weird files so I can't do nothing without the necessary material as I yet wrote in posts #9, 11 & 23 …​
 
Back
Top