Take the 2-minute tour ×

I want to import a CSV file that looks like this (the comma is the seperator):


Here, x represents a user ID, and y the value I want to extract.

Secondly I have an Excel file that has similar but way fewer user IDs in its first column. I want to import the y-value of only those users that are included in the Excel file.

Does anyone know how to do that?

share|improve this question

3 Answers 3

You can use ADO. Roughly:

Dim cn As Object
Dim rs As Object
Dim strFile As String
Dim strCon As String
Dim strSQL As String
Dim TextInput As String

''This is not the best way to refer to the workbook
''you want, but it is very convenient for notes
''It is probably best to use the name of the workbook.

strFile = ActiveWorkbook.FullName

''Note that if HDR=No, F1,F2 etc are used for column names,
''if HDR=Yes, the names in the first row of the range
''can be used.
''This is the ACE connection string, you can get more
''here : http://www.connectionstrings.com/excel

strCon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strFile _
& ";Extended Properties=""Excel 12.0 Xml;HDR=YES"";"

TextInput = "[Text;FMT=Delimited;HDR=Yes;IMEX=2;DATABASE=Z:\docs]"

''Late binding, so no reference is needed
Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")

cn.Open strCon

strSQL = "SELECT a.ID,a.Data " _
       & "FROM " & TextInput & ".[TestIn.csv] a " _
       & "INNER JOIN [Sheet1$] b ON a.ID=b.ID" _

rs.Open strSQL, cn, 3, 3

''Pick a suitable empty worksheet for the results

Worksheets("Sheet3").Cells(2, 1).CopyFromRecordset rs

''Tidy up
Set rs = Nothing
Set cn = Nothing
share|improve this answer
Nice. I'll have to favorite this one just from your answer. –  Jon49 Dec 16 '11 at 0:06

Assuming you have unique user IDs in your data (both the Excel file and the CSV), I would simply bring the CSV into Excel on a separate tab, then do a simple VLOOKUP() with the subset of IDs you need (in the Excel file) to get those specific y values.

Note: I know this isn't really filtering anything before bringing in the CSV, but it gets the job done (pulling out y values) fairly easily. If you're looking to automate this task, then hopefully someone has a more programmatic answer :)

share|improve this answer
I should probably have been more clear: the CSV file is very large; it contains over 650,000 rows. Excel will only import 65k rows--from the top of my head. I'm far from an export, but a VLOOKUP would probably not look into the rows that were not imported? –  daniel_ev Dec 15 '11 at 22:02

I would do something like this where you check yourself for each user ID. Change it to make it work for you. It should go pretty fast.

Note: I have a reference to Microsoft Scripting Runtime which enables the Dictionary, FileSystemObject, File, and TextStream objects.

Sub test()

    Dim i As Long
    Dim dicItems As Dictionary
    Dim fso As FileSystemObject
    Dim oFile As File
    Dim saItems() As String, saReturn() As String
    Dim oStream As TextStream
    Dim vUserID As Variant

    'Get stream of file
    Set fso = New FileSystemObject
    Set oFile = fso.OpenTextFile("YourFile.csv")
    Set oStream = oFile.OpenAsTextStream(ForReading)

    Set dicItems = New Dictionary
    'loop through items that you want extracted and put in dictionary
    vUserID = Range("A1", Range("A" & Rows.Count).End(xlUp)).Value2
    ReDim saReturn(1 To UBound(vUserID))
    For i = 1 To UBound(vUserID)
        dicItems.Add vUserID(i, 1), i
    Next i

    'Loop through stream lines
    Do While Not oStream.AtEndOfStream
        saItems = Split(oStream.ReadLine, ",")
        If dicItems.Exists(saItems(0)) Then
            saReturn(dicItems(saItems(0))) = saItems(1)
        End If

    'Return information to your spreadsheet
    Range("B1", Range("B" & UBound(saReturn))) = Application.Transpose(saReturn)

End Sub
share|improve this answer
Could take a while with 650,000 rows. –  Fionnuala Dec 15 '11 at 23:38

Your Answer


