Finding specific header in multiple csv files

Question

Hi,

I have 94 csv files placed in a folder. Each has it's own header, all files don't have common headers. My work is to find does all the file has one specific header or not. Is there any option available to achieve this. Since power query uses column headers appending all files doesn't give me expected output.
Just whether "Ex: ABC" header is there in each file or not. Need to check this in all 94 files.

Thanks in advance !!

Answer 1

How do you want to output the results?

The objective is to know whether that specific header is present or not for all individual files. So any method is okay with the help of excel.

Power Query \ Get Data \ From File \ From Folder

Choose your folder \ Transform Data

Add a custom column "Header" using this MCode:
=Text.Split(Text.BeforeDelimiter(Text.FromBinary([Content]),"#(cr)"),",")

Expand the Header column to new rows

This are all headings from each file, filter the column and you know the files that contains the header you are looking for.

Andreas.

Answer 2

Try running this macro:

Sub Check4Header()
    Dim wsh As Worksheet
    Dim r As Long
    Dim f As Long
    Dim strFolder As String
    Dim strHeader As String
    Dim strFile As String
    Dim strLine As String

    With Application.FileDialog(4) ' msoFileDialogFolderPicker
        If .Show Then
            strFolder = .SelectedItems(1)
        Else
            Beep
            Exit Sub
        End If
    End With
    strHeader = InputBox("Enter the header to search for")
    If strHeader = "" Then
        Beep
        Exit Sub
    End If
    Set wsh = Worksheets.Add(After:=Worksheets(Worksheets.Count))
    wsh.Range("A1:B1").Value = Array("File Name", "Header Found")
    r = 1
    If Right(strFolder, 1) <> "\" Then
        strFolder = strFolder & "\"
    End If
    strFile = Dir(strFolder & "*.csv")
    Do While strFile <> ""
        r = r + 1
        wsh.Range("A" & r).Value = strFile
        f = FreeFile
        Open strFolder & strFile For Input As #f
        Line Input #f, strLine
        Close #f
        wsh.Range("B" & r).Value = (InStr(1, strLine, strHeader, vbTextCompare) > 0)
        strFile = Dir
    Loop
End Sub

Answer 3

Yeah !! It works perfect. Thanks a lot for your help :)

Answer 4

How do you want to output the results?

The objective is to know whether that specific header is present or not for all individual files. So any method is okay with the help of excel.

Answer 5

How do you want to output the results?