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

[BxWill]

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

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

 

[SkipVought]

Hi,

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

https://docs.microsoft.com/en-us/sq...ni-file-text-file-driver?view=sql-server-2017

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[strongm]

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

 

[mintjulep]

Does Feb2019_Purchases have an extension?

 

[BxWill]

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

 

[SkipVought]

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,

_{Just traded in my OLD subtlety...
for a NUance!}
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[strongm]

>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

 

[BxWill]

Bingo! strongm,

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

However, the error that I now receive is:

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

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.

 

[BxWill]

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

 

[Andrzejek]

Would that be:
[tt]
xlrs.Open "SELECT FirstName, Age FROM [" & currentDataFileName & ".csv] WHERE Age > 30 and State = 'Florida'[highlight #FCE94F]"[/highlight], xlcon[/tt]



---- Andy

There is a great need for a sarcasm font.

 

[BxWill]

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?

 

[Andrzejek]

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

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

Dim  strTextLine As String

Open "C:\Test\Feb2019_Purchases.csv" For Input As #1
Do While Not EOF(1)           
   Line Input #1, strTextLine   [green]
   'Do stuff with this line of text[/green]
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 [tt]FirstName, Age[/tt], and [tt]State.[/tt] fields are.

No ADODB needed.


---- Andy

There is a great need for a sarcasm font.

 

[BxWill]

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.

 

[BxWill]

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.

 

[strongm]

>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

 

[BxWill]

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?