Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

StartRow argument ignored when importing a .csv

0

Hello, I'm following the "Automated Text Import in Excel with File Selection - The Easy Way - VBA" tutorial. I've gone through and completed the example with the various components and am now trying to build it to my specific purpose. I generated a .csv in another program and would like to import this, skipping the first (header) row. The tutorial shows a way to do this which works for me with the example .txt file, but when I import my .csv file (after changing the delimiter to comma) the header row is still included (see screenshot included). Can anyone tell me where I'm going wrong?

Sub Import_File()

    Dim FileToOpen As Variant
    Dim fileFilterPattern As String
    Dim wsMaster As Worksheet
    Dim wbTextImport As Workbook
    
    Application.ScreenUpdating = False

    fileFilterPattern = ".csv Files (*.csv), *.csv"

    FileToOpen = Application.GetOpenFilename(fileFilterPattern)
   
    If FileToOpen = False Then
        MsgBox "No file selected."
    
    Else
 
        Workbooks.OpenText _
                Filename:=FileToOpen, _
                StartRow:=2, _
                DataType:=xlDelimited, _
                Comma:=True
   
        Set wbTextImport = ActiveWorkbook
        
        Set wsMaster = ThisWorkbook.Worksheets("Raw Data")
        wbTextImport.Worksheets(1).Range("A1").CurrentRegion.Copy wsMaster.Range("A3")
    
        wbTextImport.Close False
    
    End If
    
    Application.ScreenUpdating = True

End Sub
Answer
Discuss

Answers

0

Hi Jiinglelocks and welcome to the Forum.

That code works as written if you run it with a CSV and from an Excel file with a BLANK sheet named "Raw Data". If you have header titles there already (in row 1 say), they won't be erased since this line:

wbTextImport.Worksheets(1).Range("A1").CurrentRegion.Copy wsMaster.Range("A3")
 imports the CSV (less row 1) to A3 (and beyond) in the Raw Data Sheet. You could change A3 (in bold above) to another cell, say A1 to overwrite anything. 

Incidentally, you can't post a screenshot on the Forum- you can only add Excel files (via the Add Files... button in the Question page).

I've attached a version of your file (added to the question) where I've changed the delimiter to a comma (as you said you'd done), as below:

Workbooks.OpenText _
            Filename:=fileToOpen, _
            StartRow:=2, _
            DataType:=xlDelimited, _
            Comma:=True
            'Tab=True ' ### disabled this for CSV imports
    '*opens file to new workbook but specifies things about the file
then run the import with the .csv (saved from your file). You'll in the ...v0_a.xlsm file that it did NOT import the header row (Scientific_name, Common_name, X, Y, Distance  and Bearing) but adds from row 2 onwards (starting with Alcedo atthis, Kingfisher etc.).

If you erase those rows and try again (clicking Button1), it should import correctly. If not, just check that your .csv file doesn't identical rows 1 and 2!

Hope this helps.

Discuss

Discussion

Did you find it works, Jiinglelocks?
John_Ru (rep: 6102) Sep 30, '21 at 3:44 am
Unfortunately it still does not work, the code works for the example data (tab delimited) given in the tutorial but when I try to load my own CSV which is comma separated (after altering the code as such too) the data still loads the header row.
Jiinglelocks Sep 30, '21 at 1:45 pm
Oh. Not sure but can you upload your csv file (in the question) and Excel file (definitely allowed)? On my phone at present so can't check / try. 
John_Ru (rep: 6102) Sep 30, '21 at 2:58 pm
Home now and see that only Excel files can be uploaded. Please save your CSV file as an  Excel file, upload it in your question (with your Excel macro file) and I'll convert the former to .csv and try the macro/advise tomorrow hopefully.
John_Ru (rep: 6102) Sep 30, '21 at 4:39 pm
Jiinglelocks. I'm puzzled. I've even created a multi-line comma separated plain TEXT file (where the first line is "Pos.,Bib,Time,Name,Team,Gender,Gender Pos,Class,Class Pos,Chip Time,Chip Pos") and changed the macro to import that instead by changing the line setting fileFilterPattern to:
fileFilterPattern = ".txt Files (*.txt), *.txt"
That ignores that first line and imports only the data lines (after line 1, which ends in a Paragraph Mark).

If I change StartRow to 5, it skips the header and 3 data lines and only imports from line 5. If I change the paragraph marks for manual line breaks, it still works.

It seems to be working perfectly for me. Mystified!
John_Ru (rep: 6102) Oct 1, '21 at 1:51 am
Just occurred to me, you can include the .csv within an (uploadable) Excel file. In the ribbon, go to Insert and select Text (down arrow)/ Object, and then select Create from File, pick the .csv and check "display as icon" and uncheck "Link...".  
John_Ru (rep: 6102) Oct 1, '21 at 11:14 am
Hello John, thank you for the time taken to try and get to the bottom of this. I have run through the steps again and the problem persists. The example .txt you give works just fine and the startrow argument functions perfectly. When I use my .csv the header row is always imported no matter what. I have uploaded the excel file with macro and my .csv. Thanks again.
Jiinglelocks Oct 18, '21 at 6:13 pm
Jiinglelock. Please see my revised Answer and the file (with successful import (less header row)
John_Ru (rep: 6102) Oct 19, '21 at 6:58 am
Hi

I experience the same issue using StartRow, works fine with a .txt file but doesn't seem to work with .csv. Tried multiple .csv files including very basic 3 row file with 2 heading rows and 1 data row which I would like to pull through but it just keeps bringing all rows through. 
tony_richardson Oct 28, '21 at 11:38 am
Tony (/Jiinglelocks)

Sorry but I can't replicate the problem (just tried on a laptop) so can't help.

@Don - can you help? Tony's profile refers to "Exact for Office 365" (though Jiinglelocks says just "365") but I have no experience of that integration- are there any issues with VBA?
John_Ru (rep: 6102) Oct 28, '21 at 1:48 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login