Ugly CSV Adventure and possible clean-whitespace issue

[Gary-Armstrong]

I'm having a real adventure trying to fix a CSV produced by AWS Inspector. Miller actually did pretty well with clean-whitespace but it did give me a bit of feedback to pass along. I also want to see if I can get some pro-tips on getting this automated.

Here is what I'm dealing with. Sample is from the full CSV with columns 3,19 cut.

Finding,Recommendation
"
    Instance i-01aae074f79eaa71f is not compliant with rule 1.7.1.3 Ensure remote login warning banner is configured properly, 2.2.0 CIS CentOS Linux 7 Benchmark.

    Applicable profiles: Level 1 - Server, Level 2 - Workstation, Level 2 - Server, Level 1 - Workstation.
            ","


                        Edit the /etc/issue.net file with the appropriate contents according to your site policy, remove any instances of \m , \r , \s , or \v :
                        # echo ""Authorized uses only. All activity may be monitored and reported."" > /etc/issue.net




               "
"
    Instance i-01aae074f79eaa71f is not compliant with rule 5.2.8 Ensure SSH root login is disabled, 2.2.0 CIS CentOS Linux 7 Benchmark.

    Applicable profiles: Level 1 - Server, Level 2 - Workstation, Level 2 - Server, Level 1 - Workstation.
            ","
...

It goes on like this for 115 rows. But let's check on that whitespace using bat -A:

Finding,Recommendation␊
"␊
····Instance·i-01aae074f79eaa71f·is·not·compliant·with·rule·1.7.1.3·Ensure·remote·login·warning·banner·is·configured·properly,·2.2.0·CIS·CentOS·Linux·7·Benchmark.············␊
␊
····Applicable·profiles:·Level·1·-·Server,·Level·2·-·Workstation,·Level·2·-·Server,·Level·1·-·Workstation.␊
············","␊
··················␊
·····················␊
························Edit·the·/etc/issue.net·file·with·the·appropriate·contents·according·to·your·site·policy,·remove·any·instances·of·\m·,·\r·,·\s·,·or·\v·:␊
························#·echo·""Authorized·uses·only.·All·activity·may·be·monitored·and·reported.""·>·/etc/issue.net␊
························␊
·························␊
·····················␊
··················␊
···············"␊
"␊
····Instance·i-01aae074f79eaa71f·is·not·compliant·with·rule·5.2.8·Ensure·SSH·root·login·is·disabled,·2.2.0·CIS·CentOS·Linux·7·Benchmark.············␊
␊
····Applicable·profiles:·Level·1·-·Server,·Level·2·-·Workstation,·Level·2·-·Server,·Level·1·-·Workstation.␊
...

Aye caramba! After mlr clean-whitespace the situation improves very much, doing exactly what I want, except for one oddity:

Finding,Recommendation␊
"Instance·i-01aae074f79eaa71f·is·not·compliant·with·rule·1.7.1.3·Ensure·remote·login·warning·banner·is·configured·properly,·2.2.0·CIS·CentOS·Linux·7·Benchmark.·Applicable·profiles:·Level·1·-·Server,·Level·2·-·Workstation,·Level·2·-·Server,·Level·1·-Workstation.","Edit·the·/etc/issue.net·file·with·the·appropriate·contents·according·to·your·site·policy,·remove·any·instances·of·\m·,·\r·,·\s·,·or·\v·:␊
#·echo·""Authorized·uses·only.·All·activity·may·be·monitored·and·reported.""·>·/etc/issue.net"␊
"Instance·i-01aae074f79eaa71f·is·not·compliant·with·rule·5.2.8·Ensure·SSH·root·login·is·disabled,·2.2.0·CIS·CentOS·Linux·7·Benchmark.·Applicable·profiles:·Level·1·-·Server,·Level·2·-·Workstation,·Level·2·-·Server,·Level·1·-·Workstation.","Edit·the·/etc/ssh/sshd_config·file·to·set·the·parameter·as·follows:␊
PermitRootLogin·no"␊

It's hard to see the issue, but there is still a LF after the colon in the Recommendation column, or perhaps before the octothorpe that follows the colon. All I did was mlr --csv clean-whitespace and while it seems odd that it would behave this way on purpose, I'm quite possibly doing it wrong.

Any advice is welcome.

[Gary-Armstrong]

An interesting (?) quirk, both Miller and Csvkit format this CSV the same and if I cut (using either csvcut or mlr) columns 3, 16, and 19 instead of only 3, 19 then it is formatted well by both tools. I'm new to the CSV scene so I don't really understand what is going on here.

[aborruso]

Please could you share in gist a sample input csv file?

[Gary-Armstrong]

Sure, the 2-column cut is as above. I will share the original, the 2-column, and the 3-column cut.

[Gary-Armstrong]

While digging around the dozen-plus test files I have been working on, I determined that the newline persists during any cut and I was actually looking at one that I converted to JSON, mostly demangled, and converted back to CSV. Sorry about that.

In any case, the promised cut-only files:
Original 28-column, 115-row CSV: https://gist.github.com/Gary-Armstrong/c0fa8631ead9db7c7d44ac471cd9b0a8
Columns 3,12,19: https://gist.github.com/Gary-Armstrong/9db4a9cab30e260bf5d5122a54c79d37
Columns 3,19: https://gist.github.com/Gary-Armstrong/59af7039f97619ea1f81e4e1e4c9df5d

As a bonus, the demanglement process I used.
Use csvcut to get columns 3,12,19 which (seemed to) work better in later steps than only 3,19.
Send that CSV through csvjson.
Send resultant JSON through perl -pe 's/\\n//g; s/" +/"/g; s/ +"/"/g; s/ +/ /g;' .
Send result through jq to format the JSON.
Send that JSON through Csvkit's in2csv.

That mostly worked, allowing Excel to import it, but it is pretty fragile.

[aborruso]

Thank you very much now I have all. You goal is to remove all carriage return?

If no, could you make an example only for one column (for column xxx my goal is to ... ).

Thank you

[aborruso]

If you run

curl -kL "https://gist.githubusercontent.com/Gary-Armstrong/c0fa8631ead9db7c7d44ac471cd9b0a8/raw/ef1535f6ca56c5aa52d3dfa37388e23ed46824e6/AWS%2520Inspector%252028%2520column%2520CSV" | \
mlr --csv put -S 'for (k in $*) {$[k] = gsub($[k], "\n", " ");}' then clean-whitespace >output.csv

you this output https://gist.github.com/aborruso/b27acbb257beb41ff3a17c29d5bd56e4

Is this what you want?

[Gary-Armstrong]

Yes, that looks great, and more importantly, Excel can import it.
I really appreciate the guidance, I will go back to the docs and work to understand how you solved this.
Thanks!

[aborruso]

Only 3 columns

curl -kL "https://gist.githubusercontent.com/Gary-Armstrong/c0fa8631ead9db7c7d44ac471cd9b0a8/raw/ef1535f6ca56c5aa52d3dfa37388e23ed46824e6/AWS%2520Inspector%252028%2520column%2520CSV" | \
mlr --csv -N cut -f 3,12,19 then put -S 'for (k in $*) {$[k] = gsub($[k], "\n", " ");}' then clean-whitespace >3c_output.csv

the output is this https://gist.github.com/aborruso/1cfbb905d82c749114b8ace5d04e7212

[Gary-Armstrong]

Perfect, thanks again!

[Gary-Armstrong]

Like I said, I'll go back to the docs and work to understand this process. I might refine it a bit, in my spare time, since it would be nice to retain the internal newlines but remove the ones between the outer quotes and the text. However this is extremely usable, and I expect the AWS Inspector system will get an overhaul before EOY and this process may not be required.

[aborruso]

@Gary-Armstrong a white space is not a carriage return. The clean-whitespace verb removes wrong white spaces.

I have used the put verb and a DLS for loop to remove globally each \n char in every file cells.