Importing a CSV file into Excel

I need help making this code run faster. I am using it to import a CSV file into Excel. It works, but it is very slow. The file is almost 20MB.

```
{Sub OpenTextFile()

Dim FilePath As String
Dim linitem As Variant
FilePath = "filepath.txt"
Open FilePath For Input As #1
row_number = 0
Do
Line Input #1, LineFromFile
LineItems = Split(LineFromFile, "|")

ActiveCell.Offset(row_number, 30).Value = LineItems(0)
ActiveCell.Offset(row_number, 29).Value = LineItems(1)
ActiveCell.Offset(row_number, 28).Value = LineItems(2)
ActiveCell.Offset(row_number, 27).Value = LineItems(3)
ActiveCell.Offset(row_number, 26).Value = LineItems(4)
ActiveCell.Offset(row_number, 25).Value = LineItems(5)
ActiveCell.Offset(row_number, 24).Value = LineItems(6)
ActiveCell.Offset(row_number, 23).Value = LineItems(7)
ActiveCell.Offset(row_number, 22).Value = LineItems(8)
ActiveCell.Offset(row_number, 21).Value = LineItems(9)
ActiveCell.Offset(row_number, 20).Value = LineItems(10)
ActiveCell.Offset(row_number, 19).Value = LineItems(11)
ActiveCell.Offset(row_number, 18).Value = LineItems(12)
ActiveCell.Offset(row_number, 17).Value = LineItems(13)
ActiveCell.Offset(row_number, 16).Value = LineItems(14)
ActiveCell.Offset(row_number, 15).Value = LineItems(15)
ActiveCell.Offset(row_number, 14).Value = LineItems(16)
ActiveCell.Offset(row_number, 13).Value = LineItems(17)
ActiveCell.Offset(row_number, 12).Value = LineItems(18)
ActiveCell.Offset(row_number, 11).Value = LineItems(19)
ActiveCell.Offset(row_number, 10).Value = LineItems(20)
ActiveCell.Offset(row_number, 9).Value = LineItems(21)
ActiveCell.Offset(row_number, 8).Value = LineItems(22)
ActiveCell.Offset(row_number, 7).Value = LineItems(23)
ActiveCell.Offset(row_number, 6).Value = LineItems(24)
ActiveCell.Offset(row_number, 5).Value = LineItems(25)
ActiveCell.Offset(row_number, 4).Value = LineItems(26)
ActiveCell.Offset(row_number, 3).Value = LineItems(27)
ActiveCell.Offset(row_number, 2).Value = LineItems(28)
ActiveCell.Offset(row_number, 1).Valu

If I were to rewrite your code, I'd do it like this:

Option Explicit

Step one, always be explicit. Nobody likes seeing a variable being used that they never saw declared anywhere.

Public Sub ImportTextFile()

Step two, name the procedure after what it's doing, and make it do that.

Dim filePath As String
    filePath = "filepath.txt"

    Dim fileNumber As Long
    fileNumber = FreeFile

FreeFile will give you a file number that's ok to use. Hard-coding a file number is never a good idea, even if you're only using one file.

Dim inputData As String
    Dim lineCount As Long

    On Error Goto ErrHandler
    ToggleWaitMode True, "Importing text file..."

On Error Goto ErrHandler means if anything wrong happens (file not found, more data in the file than can fit on the worksheet, or whatever), we'll skip straight to an error-handling subroutine where we'll take good care of closing our file and toggle "wait mode" back off.

Open filePath For Input As #fileNumber

        Do Until EOF(fileNumber)

            Line Input #fileNumber, inputData
            lineCount = lineCount + 1

            WriteLineContentToActiveSheet inputData, lineCount

        Loop

    Close #fileNumber

So the ImportTextFile procedure does nothing but reading the file's content. WriteLineContentToActiveSheet will do the worksheet-writing part.

ErrHandler:
    ToggleWaitMode

    If Err.Number <> 0 Then
        Close 'closes any file left open
        MsgBox Err.Description
        Err.Clear
    End If

End sub

The code under this label will execute even if no error occurred, this is why we're checking for Err.Number being other than 0. But regardless of whether there's an error or not, we always want to ToggleWaitMode.

Private Sub ToggleWaitMode(Optional ByVal wait As Boolean = False, Optional ByVal statusBarMessage As String = vbNullString)

    Application.ScreenUpdating = True 'ensure status message gets displayed
    Application.StatusBar = statusBarMessage
    Application.Cursor = IIf(wait, xlWait, xlDefault)
    Application.ScreenUpdating = Not wait
    Application.EnableEvents = Not Wait

End Sub

This small procedure takes two optional parameters. If none are specified, "wait mode" is toggled off - this means the status bar message is reset, the mouse cursor goes back to default, Excel resumes redrawing the screen and fires events whenever something happens. "Wait mode" is just all that reversed :)

Private Sub WriteLineContentToActiveSheet(ByVal inputData As String, ByVal targetRow As Long)
    Dim lineFields() As String
    lineFields = Split(inputData, "|")

    Dim fieldCount As Integer
    fieldCount = UBound(lineFields)

    Dim currentField As Integer
    Dim targetColumn As Integer
    Dim fieldValue As String

    With ActiveSheet
        For currentField = 0 To fieldCount

            fieldValue = lineFields(currentField)
            targetColumn = fieldCount - currentField + 1

            .Cells(targetRow, targetColumn).Value = fieldValue

        Next
    End With

End Sub

This small procedure takes a single line of data, splits it into as many fields as it has, and writes each field value into a cell of the active sheet. Using UBound we're no longer tied to a specific number of fields - if the file format changes, the procedure should still work. And if it doesn't, the procedure that called it will cleanly handle the error for us.

Local variables currentField, targetColumn and fieldValue are not necessary - they could all be inlined, but having them makes code easier to follow and allows you to place breakpoints to validate their values as you run the code line-by-line with F8 when you're debugging.

Couple observations:

• You need to indent your code. Indentation makes it much easier to read, for yourself and anyone looking at it. Give that Tab button some lovin'!

• Procedures define a scope, anything under it should be indented.

• Within a scope, code blocks should also be indented - this includes If blocks, With blocks and any code that runs inside a loop.

• OpenTextFile is a bad name for a procedure that actually imports a text file.

• Be consistent with your naming: chose a convention, and stick to it.

• You're reading a file, that's an I/O operation and by Murphy's Law this is going to fail one day or another. Make sure your code properly handles any error that could happen while processing the file, you don't want to leave a file handle opened by mistake.

• Importing a 20mb text file will take a while, even with screen updating turned off. Tell your user you're working, give'em a hourglass cursor, and you can even update the statusbar message every couple thousand lines read/written (just call ToggleWaitMode True with a new status message) so they know it's not frozen.

StackExchange Code Review Q#33728, answer score: 7