Splitting text file into CSV with multiple delimiters in bash?

Question

Trying to parse a text file into CSV. Catch is that I have multiple delimiters currently, that I'd ideally like to use as column headers but can strip out of the csv results. Ideally would rather use bash, but whatever works... running this on a Mac OS system.

Sample text (DISA STIG)


 ----------
Group ID (Vulid): V-81749
Group Title: SRG-OS-000067-GPOS-00035
Rule ID: SV-96463r1_rule
Severity: CAT II
Rule Version (STIG-ID): AOSX-13-067035
Rule Title: The macOS system must enable certificate for smartcards.
_
_

 Vulnerability Discussion: To prevent untrusted certificates the certificates on a smartcard card must be valid in these ways: its issuer is system-trusted, the certificate is not expired, its "valid-after" date is in the past, and it passes CRL and OCSP checking.
Check Content:
To view the setting for the smartcard certification configuration, run the following command:
sudo /usr/sbin/system_profiler SPConfigurationProfileDataType | /usr/bin/grep checkCertificateTrust
If the output is null or not "checkCertificateTrust = 1;" this is a finding.
Fix Text: This setting is enforced using the "Smartcard" configuration profile.
CCI: CCI-000186 ___________________________________________________
<Break>

----------

Basically, I want to break the CSV for the following columns:

Group ID (Vulid)
Group Title:
Rule ID:
Severity:
Rule Version (STIG-ID):
Rule Title:
Vulnerability Discussion:
Check Content:
Fix Text:
CCI:

The <Break> delimiter will go to the next row.

I'd like to end up with something like this for my columns:

    Group ID (Vulid)    Group Title:    Rule ID:    Severity:   Rule Version (STIG-ID)  Rule Title: Vulnerability Discussion    Check Content   Fix Text:   CCI:    CCI:

Would the best approach be to strip each of these headers out, replacing with an arbitrary delimiter, and then use awk to split there? Never tried splitting with multiple criteria like this, so just a little stumped on how to best approach it.

Answer 1

Answer

First, you need to clean the files and make it look like uniform, like the postscripted 505942.txt. I leave this job for you since you only know the original files and their intricacies and you can easily do it googling for simple sed commands. Notice that you might have to write specific sed commands for some lines which deviate from the norm or do the changes manually if they aren't too much trouble (e.g. I wouldn't write a 5-6 lines script for a simple character deletion).

When working with strings, you need to separate the job to simple tasks. I give an example of how to transform your given file into a comma separated value file (CSV). The final file is 505942.csv which I also postscript.

sed -i 's/^.*\(: \)/\1/g' 505942.txt # Use '-i' for editing files in place (in the file itself). replace everything until the first colon ':' excluding, in other words, remove the headers from each line.
sed -i 's/^\(: \)//g' 505942.txt # Remove the first colon and the subsequent white space.
sed -i 's/^/"/' 505942.txt # Add double quotes in the beginning of each line. Quotes whill help you to parse the final comma seperated value file, since some of the fields seem to already contain commas.
sed -i 's/$/",/' 505942.txt # Add double quotes in the end of each line.
cat 505942.txt | xargs -n10 -d'\n' > 505942-after-xargs.txt # Join every 10 lines of the file.
sed -i 's/,$//' 505942-after-xargs.txt # Remove the last comma from each line.

sed -n 1,10p 505942.txt > 505942-headers.txt # Keep the first 10 lines from which you will extract the headers.
sed -i 's/:.*//' 505942-headers.txt # Remove everything after (including) the first colon.
sed -i 's/^/"/' 505942-headers.txt # Similar to above command.
sed -i 's/$/",/' 505942-headers.txt # Similar to above command.
cat 505942-headers.txt | xargs -n10 -d'\n' > 505942-headers-after-xargs.txt # Similar to above command.
sed -i 's/,$//' 505942-headers-after-xargs.txt # Similar to above command.

cat 505942-after-xargs.txt >> 505942-headers-after-xargs.txt # Join the files; append to the header file.

cat 505942-headers-after-xargs.txt # Check everything seems fine.
cp 505942-headers-after-xargs.txt 505942.csv # Copy to the final .csv file.

Postscripts

Contents of 505942.txt:

Group Title: SRG-OS-000067-GPOS-00035
Rule ID: SV-96463r1_rule
Severity: CAT II
Rule Version (STIG-ID): AOSX-13-067035
Rule Title: The macOS system must enable certificate for smartcards.
Vulnerability Discussion: To prevent untrusted certificates the certificates on a smartcard card must be valid in these ways: its issuer is system-trusted, the certificate is not expired, its "valid-after" date is in the past, and it passes CRL and OCSP checking.
Check Content: To view the setting for the smartcard certification configuration, run the following command: sudo /usr/sbin/system_profiler SPConfigurationProfileDataType | /usr/bin/grep checkCertificateTrust If the output is null or not "checkCertificateTrust = 1;" this is a finding.
Fix Text: This setting is enforced using the "Smartcard" configuration profile.
CCI: CCI-000186
Group ID (Vulid): V-81749
Group Title: SRG-OS-000067-GPOS-00035
Rule ID: SV-96463r1_rule
Severity: CAT II
Rule Version (STIG-ID): AOSX-13-067035
Rule Title: The macOS system must enable certificate for smartcards.
Vulnerability Discussion: To prevent untrusted certificates the certificates on a smartcard card must be valid in these ways: its issuer is system-trusted, the certificate is not expired, its "valid-after" date is in the past, and it passes CRL and OCSP checking.
Check Content: To view the setting for the smartcard certification configuration, run the following command: sudo /usr/sbin/system_profiler SPConfigurationProfileDataType | /usr/bin/grep checkCertificateTrust If the output is null or not "checkCertificateTrust = 1;" this is a finding.
Fix Text: This setting is enforced using the "Smartcard" configuration profile.
CCI: CCI-000186

Contents of 505942.csv:

"Group ID (Vulid)", "Group Title", "Rule ID", "Severity", "Rule Version (STIG-ID)", "Rule Title", "Vulnerability Discussion", "Check Content", "Fix Text", "CCI"
"V-81749", "SRG-OS-000067-GPOS-00035", "SV-96463r1_rule", "CAT II", "AOSX-13-067035", "The macOS system must enable certificate for smartcards.", "its issuer is system-trusted, the certificate is not expired, its "valid-after" date is in the past, and it passes CRL and OCSP checking.", "sudo /usr/sbin/system_profiler SPConfigurationProfileDataType | /usr/bin/grep checkCertificateTrust If the output is null or not "checkCertificateTrust = 1;" this is a finding.", "This setting is enforced using the "Smartcard" configuration profile.", "CCI-000186"
"V-81749", "SRG-OS-000067-GPOS-00035", "SV-96463r1_rule", "CAT II", "AOSX-13-067035", "The macOS system must enable certificate for smartcards.", "its issuer is system-trusted, the certificate is not expired, its "valid-after" date is in the past, and it passes CRL and OCSP checking.", "sudo /usr/sbin/system_profiler SPConfigurationProfileDataType | /usr/bin/grep checkCertificateTrust If the output is null or not "checkCertificateTrust = 1;" this is a finding.", "This setting is enforced using the "Smartcard" configuration profile.", "CCI-000186"

Answer 2

Split each by one delimiter, treating multiple logical fields as one field. Then, split that field by the other delimiter. Lastly write out the whole record.

It's probably not a "pure" solution, but this is bash...