vbacsvimport-from-csv

Starting import from CSV from row 2 using VBA


I'm new to VBA and therefore need some help. I'm trying to import data from a CSV file using a method where I can chose the csv of a list using the following piece of code:

Private Sub commandbuttonimport_click()



Dim fd              As Office.FileDialog
Set fd = Application.FileDialog(msoFileDialogFilePicker)
With fd
    .Filters.Clear
    .Title = "Select a CSV File"
    .Filters.Add "CSV", "*.csv", 1
    .AllowMultiSelect = False
    
    Dim sfile       As String
    If .Show = True Then
        sfile = .SelectedItems(1)
    End If
    
End With

'import csv from filedialog
If sfile <> "" Then
    Open sfile For Input As #1
    row_number = 1
    Do Until EOF(1)
        Line Input #1, LineFromFile
        LineItems = Split(LineFromFile, ";")
        Application.Range("FC_Range_Final").Cells(row_number, 1).Value = LineItems(0)
        Application.Range("FC_Range_Final").Cells(row_number, 2).Value = LineItems(1)
        Application.Range("FC_Range_Final").Cells(row_number, 3).Value = LineItems(2)
        row_number = row_number + 1
    Loop
    Close #1
    End If
    
End Sub

But the thing is, I would only like to import the data from the chosen csv file starting from row 2 and skip the first one. If I manually delete the first row from the CSV file (which contains the headers) and then use the code above everything works, but if the headers remain no luck.

Help would be appreciated as I'm kinda stuck right now.


Solution

  • Try the following...

    'import csv from filedialog
    Dim LineItems() As String
    Dim LineFromFile As String
    Dim line_number As Long
    Dim row_number As Long
    
    line_number = 1
    row_number = 1
    Open sfile For Input As #1
        Do Until EOF(1)
            Line Input #1, LineFromFile
            If line_number > 1 Then
                LineItems = Split(LineFromFile, ";")
                Application.Range("FC_Range_Final").Cells(row_number, 1).Value = LineItems(0)
                Application.Range("FC_Range_Final").Cells(row_number, 2).Value = LineItems(1)
                Application.Range("FC_Range_Final").Cells(row_number, 3).Value = LineItems(2)
                row_number = row_number + 1
            End If
            line_number = line_number + 1
        Loop
    Close #1
    

    EDIT

    Based on your comments, it looks like a line feed (vbLf) is used as the end of line marker. Assuming that this is the case, you'll need to change your code to the following...

    'import csv from filedialog
    Dim DataFromFile As String
    Dim LinesFromData() As String
    Dim LineItems() As String
    Dim row_number As Long
    Open sfile For Input As #1
        DataFromFile = Input(LOF(1), 1) ' get contents from entire file
        LinesFromData = Split(DataFromFile, vbLf) ' split data into separate lines assuming line feed as end of file marker
        For row_number = LBound(LinesFromData) + 1 To UBound(LinesFromData) ' +1 to start with second line of data
            LineItems = Split(LinesFromData(row_number), ";") ' split the line into separate items
            If UBound(LineItems) <> -1 Then
                Application.Range("FC_Range_Final").Cells(row_number, 1).Resize(, 3).Value = LineItems
            End If
        Next row_number
    Close #1