Import text file to excel that contains about a million rows

[premvinoth]

Hi,

I have a text file that contains about a million rows with space delimited.

Out of these million records, the data that suits my criteria would be only around 3 thousand. The criteria is the "Date" column as in the below example how my text file looks.

Model Invoice Date Description

ANCD INV2XE 041816 30 days credit

I)There are multiple spaces between columns
II)There are blank rows beetween rows
III)The date is in MMDDYY format
IV)Description Column has spaces in between, but that needs to be considered as a single column

Now I need to import rows only which the "Date" column is <=today().

I feel it to be quite challenging and would appreciate your help.

Thankyou in advance.

Vinoth

 

[alansidman]

My approach to this would be to import the text file into Access. Run a query in Access to filter out any records having dates not meet your "Date" criteria. Then import the results of that query into Excel. This is a relatively simple and quick process if you MS Access available to you. If you don't have Access as an option, then use MS Query which is part of Excel. Here is a link on how to achieve that.

Use MS Query with Text Files for Dynamic Excel Reporting


Alan

 

[Eric W]

You could use a VBA macro.

1) Open a new workbook
2) Press Alt-F11 to open the VBA editor
3) From the menu, select Insert > Module
4) On the page that opens, paste this code:

Sub GetItems()
Dim MyLine As String, FileNum As Long, file1, MyMax As Double, Items, OutLine As Long
    
    file1 = Application.GetOpenFilename("txt Files,*.txt", _
            1, "Select source file:", , False)
    If file1 = False Then
        MsgBox "No file selected - exiting"
        Exit Sub
    End If
    
    'Get an unused file number
    FileNum = FreeFile
    
    Open file1 For Input As #FileNum
    If Err.Number <> 0 Then
        MsgBox "File open error #" & Err.Number & "!", vbCritical, "Error!"
        Exit Sub
    End If
    
    OutLine = 0
    Line Input #FileNum, MyLine
    While Not EOF(FileNum)
        Items = Split(Trim(MyLine), " ")
        If UBound(Items) > 2 Then
            If DateValue(Left(Items(2), 2) & "/" & Mid(Items(2), 3, 2) & "/" & Right(Items(2), 2)) <= Date Then
                OutLine = OutLine + 1
                Cells(OutLine, 1) = Items(0)
                Cells(OutLine, 2) = Items(1)
                Cells(OutLine, 3) = Items(2)
                Items(0) = ""
                Items(1) = ""
                Items(2) = ""
                Cells(OutLine, 4) = Trim(Join(Items, " "))
            End If
        End If
        Line Input #FileNum, MyLine
    Wend
    
    Close #FileNum


End Sub

5) Press Alt-Q to close the VBA editor
6) Press Alt-F8 to open the macro selector
7) Select GetItems and click Run

That should do it. Let me know how it works.

 

[premvinoth]

Thankyou Allan for your suggestion...

Thankyou Eric for your help... I will try this and let you know

Vinoth

 

[premvinoth]

Hi Eric,

Good day to you...

I tried your code, it ran without error, but I don't get any results.

I tried running the macro line by line to check what is going wrong by hitting F8 key.

What I found is the value of item(2) shows as " " (blank space). I guess the lines are not trimmed for multiple spaces.

Please help me fixing it.

Thankyou

 

[Eric W]

I assume that the macro asked for a file, then ran and produced no output in the worksheet? Items(2) being a space is odd, since the Split function uses a space as a delimiter.

Try this code. It's the same as the last, except it just reads the first 10 records, prints them to the Immediate window, then quits. Then show me what the printed records look like. It may be that they use a different delimiter.

Sub GetItems()
Dim MyLine As String, FileNum As Long, file1, MyMax As Double, Items, OutLine As Long
    
    file1 = Application.GetOpenFilename("txt Files,*.txt", _
            1, "Select source file:", , False)
    If file1 = False Then
        MsgBox "No file selected - exiting"
        Exit Sub
    End If
    
    'Get an unused file number
    FileNum = FreeFile
    
    Open file1 For Input As #FileNum
    If Err.Number <> 0 Then
        MsgBox "File open error #" & Err.Number & "!", vbCritical, "Error!"
        Exit Sub
    End If
    
    OutLine = 0
    ctr = 0
    Line Input #FileNum, MyLine
    While Not EOF(FileNum)
        Debug.Print MyLine
        Items = Split(Trim(MyLine), " ")
        If UBound(Items) > 2 Then
            If DateValue(Left(Items(2), 2) & "/" & Mid(Items(2), 3, 2) & "/" & Right(Items(2), 2)) <= Date Then
                OutLine = OutLine + 1
                Cells(OutLine, 1) = Items(0)
                Cells(OutLine, 2) = Items(1)
                Cells(OutLine, 3) = Items(2)
                Items(0) = ""
                Items(1) = ""
                Items(2) = ""
                Cells(OutLine, 4) = Trim(Join(Items, " "))
            End If
        End If
        Line Input #FileNum, MyLine
        ctr = ctr + 1
        If ctr > 10 Then GoTo ExitLoop:
    Wend
    
ExitLoop:
    Close #FileNum

End Sub

 

[V_Malkoti]

Eric I believe that the problem is multiple spaces as stated in his original post. Split on a string with multiple spaces in between will give you an array with blank elements.

Changing that line as following line should fix the problem:

Items = Split(Trim(Application.WorksheetFunction.Trim(MyLine)), " ")

 

[Eric W]

Nice catch!

premvinoth, if you haven't already, give V_Malkoti's suggestion a try.