Extract field headers and values between quotes

Similar to BillPrew's code, but using regex to remove what we don't want from each line

Option Explicit

Const ForReading = 1
Const ForWriting = 2
Const ForAppending = 8
Const TristateTrue = -1
Const TristateFalse = 0
Const TristateUseDefault = -2

Dim strLine, strInFile, strOutFile, objFSO, objInFile, objOutFile, objRegex

strInFile = "B:\ee\EE28990550\in.txt"
strOutFile = "B:\ee\EE28990550\out.txt"

Set objFSO = CreateObject("Scripting.FileSystemObject")

Set objInFile = objFSO.OpenTextFile(strInFile, ForReading, False, TriStateUseDefault)
Set objOutFile = objFSO.OpenTextFile(strOutFile, ForWriting, True, TriStateUseDefault)

Set objRegex = New RegExp
objRegex.Global = True
objRegex.Pattern = "(^[^""]+="")|(""[^""]+$)|(""[^""]+="")"

Do Until objInFile.AtEndOfStream
    strLine = objInFile.ReadLine
    strLine = objRegex.Replace(strLine, ",")
    strLine = Mid(strLine, 2)    'remove initial comma
    strLine = Left(strLine, Len(strLine) - 1)    'remove trailing comma
    objOutFile.WriteLine strLine
Loop

objInFile.Close
objOutFile.Close

Select allOpen in new window

Note: It might even be possible to do something like this for the entire file, but my initial regex tests weren't promising.

Just to clarify, the code I posted earlier produced an output file exactly matching the sample desired output that was posted by NVIT.

~bp

@Aikimark and Bill: Not sure how I should grade this one because I did say I wanted the header, which Bill's kind of provides but Aiki doesn't. I was hoping for a cleaner header line, i.e. just the field names and commas. I can live with that. On the other hand, although I didn't post a sample, Bill's missed the top record, which Aiki's provides. So, I'll divide it equally. To show, here's the top 2 lines:

Bill's:
" SZ=, LB=, AT=, CT=, MT=, ST=, AR=, XA=, IN=, OST=, IIMG=
Public,813139942623,66,1461377486,1217736680,1461377486,10,0,8192,0,0,1

Aiki's:
,813139942623,64,0,0,0,10,16,8192,0,0,0
Public,813139942623,66,1461377486,1217736680,1461377486,10,0,8192,0,0,1

@JesterToo: Yours was close but the 2nd-to last field missed the comma. Thus making the last field 07 instead of 0,7

NVIT,

Curios, where did you request a header record, I don't see it which was why I didn't plan on one.

~bp

In your question text, you wrote:

I'd like to extract the fields and their values

As Bill Prew noted in his most recent comment, you neither asked for a header nor showed any header in your output example.

Since this question is now closed, I recommend that you open a new question and ask about a header line.  It would be helpful to those experts to have a link to this question thread.

Did the input text you posted accurately represent the actual input data?

I was not clear. My apologies...
Is field and header the same? I stated in the title but not in the body.

The fields / headers would be on the left of the values. e.g.

FP="Public\ACAD\PROJECTS\1400\In\112316A" SZ="11366959" LB=

Select allOpen in new window

i.e.

FP, SZ, LB, ...

Select allOpen in new window

Give this  try:

Const ForReading = 1
Const ForWriting = 2
Const ForAppending = 8
Const TristateTrue = -1
Const TristateFalse = 0
Const TristateUseDefault = -2

strInFile = "B:\ee\EE28990550\in.txt"
strOutFile = "B:\ee\EE28990550\out.txt"

Set objFSO = CreateObject("Scripting.FileSystemObject")

Set objInFile = objFSO.OpenTextFile(strInFile, ForReading, False, TriStateUseDefault)
Set objOutFile = objFSO.OpenTextFile(strOutFile, ForWriting, True, TriStateUseDefault)

Set objRegex = New RegExp
objRegex.Global = True

blnHeader=True
Do Until objInFile.AtEndOfStream
    strLine = objInFile.ReadLine
    If blnHeader = True Then
        blnHeader = False
        strTemp = ""
        objRegex.Pattern = " ([a-zA-Z0-9]+?)="
        Set objMatches = objRegex.Execute(strLine)
        For Each objMatch In objMatches
            If strTemp = "" Then
                strTemp = objMatch.SubMatches(0)
            Else
                strTemp = strTemp & "," & objMatch.SubMatches(0)
            End If
        Next
        objOutFile.WriteLine strTemp
    End If

    strTemp = ""
    objRegex.Pattern = "\""(.+?)\"""
    Set objMatches = objRegex.Execute(strLine)
    For Each objMatch In objMatches
        If strTemp = "" Then
            strTemp = objMatch.SubMatches(0)
        Else
            strTemp = strTemp & "," & objMatch.SubMatches(0)
        End If
    Next
    objOutFile.WriteLine strTemp
Loop

objInFile.Close
objOutFile.Close

Select allOpen in new window

~bp