Get matching values using ADO from closed CSV file

Originally Posted by romperstomper

I don't see where your code makes any attempt to read from the CSV file?

Hi romperstomper,

I put the code almost as a reference of what I think could be the way, using ADO, but I'm a beginner in that area.

I was hoping to understand how that example works and then try to change to a csv, but even that example not seem to show the output as they say.
In my case I think the middle part of the code would be something like:

objConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=C:\MyFiles\;" & _
        "Extended Properties = ""text; HDR=No ""," 

objRecordset.Open "Select * FROM Origin.csv Where X = Y", _
    objConnection, adOpenStatic, adLockOptimistic, adCmdText

The part in red X=Y should be values in column A of Results.xlsx that are equal to values in A of Origing.csv. I don't know how would be the syntax here and how to retrieve values in other columns for matching rows.

Like a start I tried following exactly with file in example and didn't work for me, actually my computer hanged when the "Do until EOF loop" was executed.

Maybe you can help me to get the desired result.

Many thanks in advance.

Thanks in trying to help me.

The value to lookup should be all values in column A of Results.xlsx file. I don't know if a for loop as below or how to lookup for each value within the closed csv file(Origing.csv).

objConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=C:\MyFiles\;" & _
        "Extended Properties = ""text; HDR=No "","
        
For i=1 to LastRow 'I'm not sure if for loop
objRecordset.Open "Select * FROM [Origin.csv] Where [F1] = Cells(i,1), _
    objConnection, adOpenStatic, adLockOptimistic, adCmdText
    
Next

and once a value is match, how to pull values from other columns in the same row of Origing.csv into corresponding row of the value matched in in desired column in Results.xlsx?

Many thanks for help so far.

Regards.

I've run the macro as follow and doesn't seem to do anything

Sub LookUp_With_ADO()
On Error Resume Next

Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adCmdText = &H1

Set objConnection = CreateObject("ADODB.Connection")
Set objrecordset = CreateObject("ADODB.Recordset")

objConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=D:\MyFiles\;" & _
        "Extended Properties = ""text; HDR=No "","
        
With ActiveWorkbook
    For i = 2 To 11 'LastRow 'I'm not sure if for loop
        objrecordset.Open "Select [F2], [F3], [F4] FROM [Origin.csv] Where [F1] = '" & .Sheets(1).Cells(i, 1).Value & "'", _
        objConnection, adOpenStatic, adLockOptimistic, adCmdText
        If Not objrecordset.EOF Then
            .Sheets(1).Cells(i, 2).CopyFromRecordset objrecordset
            objrecordset.Close
        End If
    Next i
End With
End Sub

What could be the problem?

Thanks for help so far.

Regards

The same result. where is supposed to appear the pulled values? in active workbook right?

If you see the attached files it must be 10 matches between result.xlsx and Origin.cvs.

I'm confused why is not showing anything.

I added that and the same result, nothing appears in my active workbook.

and if I remove the error hanling line at the beginning, I receive error in this line.

objrecordset.Open "Select [F2], [F3], [F4] FROM [Origin.csv] Where [F1] = '" & .Sheets(1).Cells(i, 1).Value & "'", _
        objConnection, adOpenStatic, adLockOptimistic, adCmdText

Ok, that is a good sign.

Do I need to have enabled some library or something?

It´s working now!!!. The problem was I had the Origin.csv file open.

Now maybe you could help me with these last 2 questions.

1-) If I want to copy values from columns into different columns
example: F2 of Origing to be copied in Col5, F3 of Origing to be copied in Col7 etc. How could I do this?

2-) After the found values have been copied in order desired, how to delete in Origing.csv those rows that are not present in Result.xlsx?

Many thanks again a lot Romperstomper.

It works, it works, it works!!!!

Only the deletion part is missing. The problem is I don't want to open the file
because it has more than 50MB in size. Is not possible to delete the rows without open the file with ADO or other procedure?

I'm satisfied with the help received, the code works with ADO works and I'll mark as solved, but I'm not sure if I need to open a new question regarding the issue I describe below.

The only thing is that when I tried to run the macro and the closed file is like 4 MB in size (10,000 lines) execution time is 50 seconds, but when closed file is larger as 50 MB in size, my computer hangs.

So, is there another option using VBA within Excel, to get the same result in a faster way, without affecting too much computer resources?


Thanks again.

Hi romperstomper,

Thanks for your help again.

I've tried the new version and in this line:

objRecordset.Filter = "[F1] = '" & .Sheets(1).Cells(i, 1).Value & "'"

I get the following error:
-------------------------------------------------------------------------
Runtime error "3265"

Item cannot be found in the collection corresponding to the requested name or ordinal.
-------------------------------------------------------------------------
and when I add a "On error Resume Next" as follow

    On Error Resume Next
        objRecordset.Filter = "[F1] = '" & .Cells(i, 1).Value & "'"

Only copies the value from first row within "big data.csv" even when there isn't a match. I mean, the value copied from
recordset is no present in colA in result.xlsx. It seems, the error force the recordset to store only first row even when is not a match.

Hope I get some help in how to solve this issue.

Many thanks so far.

Regards.

Hi romperstomper,

Yes, I've used entirely your new code.

I use this:

  objConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                      "Data Source=" & cstrFILE_PATH & ";" & _
                      "Extended Properties = ""text;HDR=No;FMT=Delimited"""
   objRecordset.Open "Select [F1], [F2], [F3], [F4] FROM [Big Data.csv]", _
                     objConnection, adOpenStatic, adLockOptimistic, adCmdText

in combination of this:

         objRecordset.Filter = "[F1] = '" & .Sheets(1).Cells(i, 1).Value & "'"

I'm not sure, but it seems when a value in cells(i,1) is not in [F1] the problem appear. I don't know because happens with the first value. After that I can't continue.

Maybe you know what could be the problem.

Thanks a lot for your help.

Regards

No, it has a different name, but I changed it properly. Even I tried to use a string variable instead of the
[Bid Data.csv] but I got an error, then I only changed to [Source.csv]. I think it could be other problem.

Sorry I looked in depth and I realized that I replaced everything except one thing,

The correct code you provided contains [F1] in select line and I had missing
didnt have [F1] in my code, I had this:

objRecordset.Open "Select [F2], [F3], [F4] FROM [Big Data.csv]", _ 
                     objConnection, adOpenStatic, adLockOptimistic, adCmdText

when the correct is:

objRecordset.Open "Select [F1], [F2], [F3], [F4] FROM [Big Data.csv]", _ 
                     objConnection, adOpenStatic, adLockOptimistic, adCmdText

Well, knowing that issue I could test the macro but I found that the execution is very slow. When the line "objRecordset.Filter=..." is executed
it took like 10 seconds to pass to the next line of code. It seems that needs much time when filters a value within the Recordset.

After 5 minutes only 10 found lines were printed. I had to stop Excel because almost hangs.

I'll change to solved because is like that, the code works, the speed issue is beyond the goal of my question, because with
smaller files should work better.

I've learned many things from this problem, I know a little bit of how to use ADO to get info from closed wrokbook. I would
like to know more about this manipulations, I wasn't able to get good examples nor much info about this.

Do you know a link or reference where to learn more of this particular subject?

Many thanks romperstomper for all your great and kindly help. You know a lot.

PS: If I'm not wrong, the guy the image is Socrates in Azteca stadium, World Cup 1986, right? you are a fan of him I guess .

Best regards,

César.

Great!!! Many thanks for all man!!!

Best regards