Selectively Import Rows into Excel from a CSV file based on criteria

[brawnystaff]

I am looking to import certain information from a CSV file that is 4GB in size (first row is the header row). Rather than import the entire CSV file, I only want to import certain rows that meet a certain criteria. In this case, all rows that contain the value "AZ" in a column labeled "State Name". I am working in Excel 2010. Any ideas how to create a macro to do it? Thanks.

 

[diddi]

open the file for input and read every line, but only save the ones you want.

    Open MyDataFileName for input as #1
    while not eof(1)
        input #1, my list of fields, etc,...   , MyState, etc
        if MyState = "AZ" then
            CurrentDataRow= CurrentDataRow + 1
            'save the relevant data to CurrentDataRow
        end if
    Wend
    close 1

 

[brawnystaff]

Could give more detail on the macro?

I am looking to import all fields (columns) in the rows that contain the text I want.

 

[diddi]

i need to see a sample of the textfile. at least the header row and first row of data before i can give any further advice.

 

[brawnystaff]

Trying to import selectively import values from a CSV file. A smaller version of the CSV can be found here:

http://nppes.viva-it.com/NPPES_Data_Dissemination_100812_101412_Weekly.zip


I am been working on this for a while. I have some code below that works. The only problem is that the first column (column A) has the values in quotes. Any ideas why? Thanks.

Sub csvimport()
Dim FileName1 As Variant
Dim MyState As String
Dim J As Integer
Dim X As Integer
Dim arow As Integer
Dim r As Integer
Dim arr As Variant
Dim S As String
Dim State As String
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
    FileName1 = Application.GetOpenFilename("<wbr>Csv Files (*.csv), *.csv")
    MyState = Application.InputBox("Enter Keywords")
    On Error GoTo err:
    X = 1
    arow = 1
     Open FileName1 For Input As #1
    Do While Not EOF(1)
      r = 0
      S = ""
      For J = 1 To 329
         Input #1, State
         S = S & State & "|"
         If State = MyState Then r = J
      Next
      If r > 0 Or arow = 1 Then
        arr = Split(S, "|")
        Range(Cells(X, 1), Cells(X, 330)).Value = arr
        X = X + 1
      End If
      arow = arow + 1
    Loop
 
err:
    Close 1
End Sub