asked on

Excel VBA: Import Data From text/.csv

What is the best code used to import data from a text/.csv file in Excel file without using: "Workbooks.Open" or "Query Tables". Also the number of rows in the text/.csv files varies largely and changes each day. Therefore, if you are using ADO or some other method, will you please include in your code a way to "Select * From" or "Copy All" without looping through each record?

Background/Reason
I am working in a very large environment where "Query Tables" and "Workbooks.open" code works on some machines and not others. (They are all using Excel 2010 and/or 2016 and claim to all have the same setup with Windows 7 and/or Windows 10.) I will take code that works on one machine and it won't work on another. The code is correct, the full path is defined with and/or without the extension; depending. (i.e., some machines require the extension in the path others will error 1004 if it is there and vice versa. There are also other random problems and the users won't send me a screenshot when it errors nor will they allow me to see/debug the errors on their machine.) Therefore, I would like to look at some solid alternatives as per above.

Ejgil Hedegaard

Make a text import on a new sheet using the text import wizard.
Then start the macro recorder, and edit the just imported query.
Just accept.
Stop the recorder.
The recorded code should look something like this.

    With Selection.QueryTables(1)
        .Connection = "TEXT;" & CsvFileName
        .TextFilePlatform = 1252
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = False
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = True
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 3, 3)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With

Open in new window

Change Selection to the sheet specification, so you don't have to select the sheet to update.
The sheet can also be hidden.
With a variable for the sheet like wsData, where wsData is assigned to the sheet with Set wsData= Worksheets("Data"), so the first line will be
With wsData.QueryTables(1)

The Connection in the recording will include the csv file name.
Using a variable CsvFileName having the complete path to the csv file, the file name can be changed by the code as shown.

Change the settings to not ask for filename on update.

Then you can update the query by running the code.

Formulas referring to the query will automatically adjust to the number of rows in the import.

ouestque

ASKER

Normally that would be great!!!

Nonetheless, I am automating a process where a new .csv file is downloaded from a specified location each time someone presses a button then imports the data from the newly downloaded .csv. (i.e., the location is specified by the user and can change every hour.)

Do you have any other ideas? that would work with my scenario?

Ejgil Hedegaard

I don't understand how you will import the file if the location and file name is not known.
You need to have some way to specify the file, either by selecting, or searching with some known identifiers for the file.
Code can be made for both scenarios, and when the file name and path is stored in the variable CsvFileName, above import will work.

ouestque

ASKER

Hey Ejgil. The VBA code works in this way:

1) The location and .csv/text file name is specified by the user
2) The user presses a button
3) The .csv/text file is downloaded
4) Data is downloaded from the .csv/text file

Using a macro recorder to create a 'Query Table' is nice, but (As per my original post) I am looking for a way to do this without 'Query Tables'. Reason being, I am looking for a good full proof way to pull data from a .csv/text file that won't fail. In my environment I have seen them work on some machines and when I bring the exact same code to another machine they won't work.

Do you have any full proof alternatives?

ASKER CERTIFIED SOLUTION

Ejgil Hedegaard

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ouestque

ASKER

Very Interesting! Thanks Ejgil!