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.
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