×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

MS Excel 2016 VBA - Extract Specific columns from a CSV file
4

MS Excel 2016 VBA - Extract Specific columns from a CSV file

MS Excel 2016 VBA - Extract Specific columns from a CSV file

(OP)
Using the code below to extract two fields from a large csv file.

Currently receive an error - "Provider not found" and the words "xlcon.open" is highlighted.

Any insight as to what the cause of the error is and a resolution?

Thanks in advance

CODE

Sub GetMyCSVData()
Dim xlcon As ADODB.Connection
Dim xlrs As ADODB.Recordset
 
 
Set xlcon = New ADODB.Connection
Set xlrs = New ADODB.Recordset
Dim currentDataFilePath As String
Dim currentDataFileName As String
Dim nextRow As Integer
currentDataFilePath = "C:\Test\"
 
currentDataFileName = "Feb2019_Purchases"
 
xlcon.Provider = "Microsoft.Jet.OLEDB.4.0"
 
'xlcon.Provider = "Microsoft.ACE.OLEDB.12.0"   'Iter 2; did not work
 
xlcon.ConnectionString = "Data Source=" & currentDataFilePath & ";" & "Extended Properties=""text;HDR=Yes;FMT=Delimited;"""
 
xlcon.Open
 
xlrs.Open "SELECT FirstName, Age FROM [" & currentDataFileName & ".csv] WHERE Age > 30 and State = 'Florida' , xlcon"
 
xlrs.MoveFirst
 
nextRow = Worksheets("Sheet2").UsedRange.Rows.Count + 1
Worksheets("Sheet2").Cells(nextRow, 1).CopyFromRecordset xlrs
xlrs.Close
xlcon.Close
Set xlrs = Nothing
Set xlcon = Nothing
End Sub 

RE: MS Excel 2016 VBA - Extract Specific columns from a CSV file

Hi,

Do you have a SCHEMA.INI file defining your .csv text file?

https://docs.microsoft.com/en-us/sql/odbc/microsof...

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: MS Excel 2016 VBA - Extract Specific columns from a CSV file

What version of office, specifically 32bit or 64bit?

RE: MS Excel 2016 VBA - Extract Specific columns from a CSV file

Does Feb2019_Purchases have an extension?

RE: MS Excel 2016 VBA - Extract Specific columns from a CSV file

(OP)
There are over 60 columns on the csv file. Initially, I used Data/From Text on the menu to import the text file. Then, I specified to refresh the connection when the workbook is opened.

But, over the last few days, it appears that there may be a user that is either entering a comma within one or more of the 60 plus fields or entering extra characters at the end of a field within the enterprise system. The end result is that the data is now skewed when I import it (from the file on the network) into my Excel workbook - the account number for some of the records is no longer in the first column. Note, when I manually copy the entire data from the network and paste into my worksheet, all of the data is aligned properly.

So, I am thinking that since I really only need two or three columns, then I could just perform sql within vba to select the data in the two or three columns.

No, I do not have a schema.ini file.

Considering that there are over 60 columns, wouldn't a schema.ini file require the definition of all of the columns?

Is a schema.ini file required if I only want just two to three columns of data?

I am using MS Office Professional - MS Excel 2016 64-bit.

The extension of the file is ".csv"

I did append the extension within the vba but the error persists.

Continuing the review of schema.ini files...


RE: MS Excel 2016 VBA - Extract Specific columns from a CSV file

Yes, each field/column must be defined. Keep in mind, this is a one-time-effort.

I started out a couple decades ago, with that schema.ini file that expanded over those many years into dozens of file descriptions, some of which I used repeatedly. Just keep you file in a conspicuous location so you’ll be able to find it two years from now.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: MS Excel 2016 VBA - Extract Specific columns from a CSV file

>I am using MS Office Professional - MS Excel 2016 64-bit.

In which case you probably need to install the x64 Microsoft Access Database Engine 2010 Redistributable at which point your commented out provider string "Provider=Microsoft.ACE.OLEDB.12.0" should work. The provider you are trying to use is 32bit, and won't work with 64bit Office

RE: MS Excel 2016 VBA - Extract Specific columns from a CSV file

(OP)
Bingo! strongm,

Stepping through the vba, I made it past the line "xlcon.Open."

However, the error that I now receive is:

CODE

Run-time error '3709'

The connection cannot be used to perform this operation.  It is either closed or invalid in this context. 

Upon clicking "Debug", the following line is highlighted

CODE

xlrs.Open "SELECT FirstName, Age FROM [" & currentDataFileName & ".csv] WHERE Age > 30 and State = 'Florida' , xlcon" 


Still creating the schemi.ini file and exploring this option as well.


RE: MS Excel 2016 VBA - Extract Specific columns from a CSV file

(OP)
I do not have the rights to install the Microsoft Access Database Engine 2010 Redistributable.

RE: MS Excel 2016 VBA - Extract Specific columns from a CSV file

Would that be:

xlrs.Open "SELECT FirstName, Age FROM [" & currentDataFileName & ".csv] WHERE Age > 30 and State = 'Florida'", xlcon


---- Andy

There is a great need for a sarcasm font.

RE: MS Excel 2016 VBA - Extract Specific columns from a CSV file

(OP)
Good catch Andrzejek,

Did make the change and also eliminated the ".csv" from the file name.

Code executes but no data is displayed on Sheet2.

Interesting!

Will need to step through and debug. Maybe test the actual SQL statement.

Any initial thoughts as to why no data would display?

RE: MS Excel 2016 VBA - Extract Specific columns from a CSV file

I know "There are over 60 columns on the csv file", the file is located in C:\Test\ and it is named Feb2019_Purchases.csv
You are interested in fields: FirstName, Age, and State.

I would just open this file like any other text file with:

CODE

Dim  strTextLine As String

Open "C:\Test\Feb2019_Purchases.csv" For Input As #1
Do While Not EOF(1)           
   Line Input #1, strTextLine   
   'Do stuff with this line of text
Loop
Close #1 

By-pass first record (with headers, I assume), Split() every line by comma, pick whatever I am interested in, evaluate, and write what I need to my Excel sheet.

If the 3 fields are NOT in predetermine place, you can evaluate the very first row (with headers) to find out where your FirstName, Age, and State. fields are.

No ADODB needed.


---- Andy

There is a great need for a sarcasm font.

RE: MS Excel 2016 VBA - Extract Specific columns from a CSV file

(OP)
Andy,

Appreciate the insight.

However, opening the entire text file when I only need just two to three fields...

Have utilized sql within vba in the past and it has never failed to extract specific data from a text file or another worksheet.

Will ponder this a little more before attempting your suggestion.

RE: MS Excel 2016 VBA - Extract Specific columns from a CSV file

(OP)
Additional Context:

Basically, I am importing the data from the CSV file to perform a vlookup.

So, thinking about this from a different angle - I believe that it is possible to perform a vlookup against a closed csv file.

The csv file resides on the network and is refreshed everyday. Same file name. Same structure.

RE: MS Excel 2016 VBA - Extract Specific columns from a CSV file

>xlrs.Open "SELECT FirstName, Age FROM [" & currentDataFileName & ".csv] WHERE Age > 30 and State = 'Florida' , xlcon"

xlrs.Open "SELECT FirstName, Age FROM [" & currentDataFileName & ".csv] WHERE Age > 30 and State = 'Florida'" , xlcon

RE: MS Excel 2016 VBA - Extract Specific columns from a CSV file

(OP)
Tried the various suggestions and it appears that without the rights to install the x64 Microsoft Access Database Engine 2010 Redistributable, I will not be able to use the
provider "Provider=Microsoft.ACE.OLEDB.12.0."

Upon running the code, there appear to be no syntax errors but there is no data output to Sheet2.

Any insight as to the use of a array to store the csv file and then selecting a few columns of the csv file from the array? Advantages/Disadvantages of this approach?

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close