CODEWITHSUNDEEP
excelcsvvba
ForJ9
ForJ9

Reputation: 745

Load CSV File faster

I load around 75 CSV files containing one line.

For dataRow = 8 To Worksheets("Liste").Range("B65535").End(xlUp).Row

        If Dir(FilePath & Worksheets("List").Cells(dataRow, 2) & ".csv") <> "" Then

            Open FilePath & Worksheets("List").Cells(dataRow, 2) & ".csv" For Input As #2
            Line Input #2, LineFromFile
            LineItems = Split(LineFromFile, ";")

            itemNumber = 0
            For i = 2 To 367
               Worksheets("Calendar").Cells(dataRow, i).value = Replace(LineItems(itemNumber), Chr(34), "")
               itemNumber = itemNumber + 1
            Next

        End If

        Close #2
    Next

The Problem is, that this code is very slow. It takes around 20 seconds to load this data. (27450 data sets, 27,3kb)

Is there a way to speed up the loading? I think my SSD is fast enough.

Upvotes: 1

Views: 1242

Answers (1)

Rachel Hettinger
Rachel Hettinger

Reputation: 8442

Here are two things you can do to speed up your code:

  1. Only do the double-quote character replacement once.
  2. Write all the data to the cells once.

The second item will probably give you the most speed up as reading/writing cells is time-consuming. Try this code:

If Dir(FilePath & Worksheets("List").Cells(datarow, 2) & ".csv") <> "" Then

    Open FilePath & Worksheets("List").Cells(datarow, 2) & ".csv" For Input As #2
    Line Input #2, LineFromFile
    LineFromFile = Replace(LineFromFile, Chr(34), "")
    lineitems = Split(LineFromFile, ";")

    With Worksheets("Calendar")
        .Range(.Cells(datarow, 2), .Cells(datarow, 367)).Value = lineitems
    End With

End If

Upvotes: 2

Related Questions