list specific .csv files rather than all the .csv files (in a folder)

[jxb]

To all,

I have a macro that list all the .csv files in a specified folder.
I would like to add an option so that only the .csv files corresponding to an (user) specified option are listed

Let's say that I have 2 options 'A' & 'B'
.csv file for option 'A' will always have the keyword "AAA" in row 1, col 2
.csv file for option 'B' will always have the keyword "BBB" in row 1, col 2

Is is possible to check "on-the-fly" i.e. when the macro create the list if a file is type 'A' or 'B' without
reading the 1st line
spline the line at the ","
check field 1 for "AAA" or "BBB" against specified option
add file name to ListToDisplay depending on previous check

In other word: is there a "smarter" way od doing it?

Thanks
Regards

 

[Kenneth Hobson]

ADO might be faster. This does a bit more than you need. Obviously, it needs some work to just find the 2nd fieldname.

Sub pull_data()
  Dim s1 As Worksheet, ML_Dir As String
  ML_Dir = ThisWorkbook.Path
  'Open_Sort_CSV ML_Dir, "ImportCSV.csv", ActiveSheet.Name, True, "Data Used"
  Open_Sort_CSV ML_Dir, "ImportCSV.csv", ActiveSheet.Name, False, "F1"
End Sub

'Add Tools > References... > Microsoft ActiveX Data Objects 2.8 Library
Sub Open_Sort_CSV(CSV_Dir, CSV_name, Data_Sheet, Optional Header As Boolean = True, _
  Optional SortField As String = "", Optional SortASC As Boolean = True)

  Dim connectionString As String
  'Late binding:
  'Dim objConnection As Object, objRecordset As Object
  ' Early Binding:
  Dim objConnection As Connection, objrecordset As Recordset
  Dim A As Integer
  Dim Location As Range, Rw As Long, col As Integer, c As Integer, MyField As Variant

  'set record set variables
  Const adOpenStatic = 3
  Const adLockOptimistic = 3
  Const adCmdText = 1 '&H1
  
  'set connection and recordset
  Set objConnection = CreateObject("ADODB.Connection")
  Set objrecordset = CreateObject("ADODB.Recordset")
  
  'oopen connection (headers,Delimited style,mixed data taken as text(not sure imex works))
  connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=" & CSV_Dir & ";" & _
    "Extended Properties=""text;HDR=" & Header & ";FMT=Delimited;IMEX=3"""
  objConnection.Open connectionString
  
  'get data from csv
  Select Case True
    Case SortField = ""
      objrecordset.Open "SELECT * FROM " & CSV_name, _
      objConnection, adOpenStatic, adLockOptimistic, adCmdText
    Case SortField <> "" And SortASC
      objrecordset.Open "SELECT * FROM " & CSV_name & " Order By `" & SortField & "` ASC", _
        objConnection, adOpenStatic, adLockOptimistic, adCmdText
    Case SortField <> "" And SortASC = False
      objrecordset.Open "SELECT * FROM " & CSV_name & " Order By `" & SortField & "` DESC", _
        objConnection, adOpenStatic, adLockOptimistic, adCmdText
  End Select
  
  'Loop across the fields
  If Header Then
      With objrecordset
           For A = 0 To .Fields.Count - 1
           ' Add field names to data sheet
            If Right(.Fields(A).Name, 7) <> ".NoName" Then _
              ThisWorkbook.Worksheets(Data_Sheet).Cells(1, 1).Offset(0, A).Value = .Fields(A).Name
           Next A
      End With
      'copy data into worksheet under headers
      ThisWorkbook.Worksheets(Data_Sheet).Cells(2, 1).CopyFromRecordset objrecordset
   'Write RecordSet to results area
    Set Location = ThisWorkbook.Worksheets(Data_Sheet).Range("A2")
    Rw = Location.Row
    col = Location.Column
    c = col
    With objrecordset
      Do Until .EOF
          For Each MyField In .Fields
            Cells(Rw, c) = MyField
            c = c + 1
          Next MyField
          .MoveNext
          Rw = Rw + 1
          c = col
      Loop
    End With

  
    Else
      'copy data into worksheet
      ThisWorkbook.Worksheets(Data_Sheet).Cells(1, 1).CopyFromRecordset objrecordset
  End If

    'end connection and recordset
    Set objConnection = Nothing
    Set objrecordset = Nothing
    
End Sub

Another option might be to look in the contents when you build the array using a shell command like Find/Search. That could be faster.

I guess the only way to know for sure would be to do some speed tests on the 3 methods.

 

[jxb]

Thanks. A bit above my programming skills. Was hoping that there is a option in the IO method to check for a string in the file. just realised that it does not matter where AAA or BBB. are located as long as the "peek" return true
Maybe I could loop through the list of files then add the valid file to a final list

For each file in ListofFile
if option=A and peek("Aaa")= true then ListofFileFinal.add(filenane)

somethimg along these line!

 

[Kenneth Hobson]

Takes me back to commodore64 peek and poke days. No, there is no VMA Peek().

I can look at the file find/search contents method to build a file list if you like.

 

[Kenneth Hobson]

FindStr is the command. Ctrl+R and type CMD and press Enter key to enter the command shell manually. Type, Help FindStr, and press Enter key to get help or see: Findstr - Search for strings | Windows CMD | SS64.com

I can show you how to return the command shell stream in VBA if this method interests you. This could be sort of a Peek().

e.g.
FindStr ken c:\csv\*.csv