Starting import from CSV from row 2 using VBA

Question

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.

score 1 · Accepted Answer · 2020-11-22 17:10:56Z

1

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

edited Nov 22, 2020 at 17:10

answered Nov 20, 2020 at 18:05

user6585761

Sign up to request clarification or add additional context in comments.

14 Comments

jjj Over a year ago

I tried doing this but it just returns no observations.

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

user6585761 Over a year ago

See my post, which has been edited to include the full, amended macro.

jjj Over a year ago

This is strange indeed. I tried running the code and it didn't work for the csv where the headers are still there but it worked for the file where I had removed them i.e. starting a line further down. The header looks like this: begin [Europe/Berlin];end [Europe/Berlin];Deutschland [kW]. Could that mess something up?

jjj Over a year ago

Sadly not. Maybe it's just not feasible this way.

jjj Over a year ago

Is it perhaps possible for the code to delete the first row of the csv file it opens, before extracting the data?

|

Collectives™ on Stack Overflow

Starting import from CSV from row 2 using VBA

1 Answer 1

14 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

14 Comments

Your Answer

Sign up or log in

Post as a guest

Related