Having trouble parsing a csv file from the commandline

Question

I have a CSV file that I've been working on for most of the day and I'm not having any luck getting it parsed properly using a Regex with awk.

awk is not processing the Regex as expected.

Here are the inputs:

• GNU Awk 4.1.4, API: 1.1 (GNU MPFR 3.1.5-p2, GNU MP 6.1.2)
• Regex: /(\[(.*?)\])|[^,]+/g
• Sample text hostname,hostname.domain.com,hostname.domain.com,windows,6.2.9200,1.2.3,location,environment,[role1, role2, role3],[recipe1, recipe2, recipe3],2019-01-10 06:06:31
• Raw text (before stripping out double-quotes, which I do at a step not listed explicitly in this question): hostname,hostname.domain.com,hostname.domain.com,windows,6.2.9200,1.2.3,location,environment,"[""role1"", ""role2"", ""role3""]","[""recipe1"", ""recipe2"", ""recipe3""]",2019-01-10 06:06:31

When I run this through Regexr.com, it shows the proper matches:

I pipe from cat -> sed -> awk (the sample text above is what comes out of sed) and run the following command (I only want the first 9 fields, which includes the entirety of the first field enclosed in [], but not anything after that):

awk '/(\[(.*?)\])|[^,]+/g{print $1,$2,$3,$4,$5,$6,$7,$8,$9}'

What I'm expecting to see as an output: hostname,hostname.domain.com,hostname.domain.com,windows,6.2.9200,1.2.3,location,environment,[role1, role2, role3]

NOTE: The important part about this is treating the field with the roles (between the brackets) as a single field, or at least including all of the roles in the output, but none of the recipes)

What I'm actually getting out is the full line that was fed in.

From playing around with the variables, I found the following field assigments coming out of awk:

• $1 = hostname,hostname.domain.com,hostname.domain.com,windows,6.2.9200,1.2.3,location,environment,[role1,
• $2 = role2,
• $3 = role3],[recipe1,
• $4 = recipe2,
• $5 = recipe3],2019-01-10
• $6 = 06:06:31

I've tried using the accepted answer from This Stack Overflow question outright, and I've tried tweaking it to use [] as the delimiters instead of ", and that gets me closer, but it's still not treating the role field as a single field.

Answer 1

If you're dealing with a complex CSV file - in particular, one whose fields may contain quoted delimiters (in this case, commas) then a proper CSV parser is going to save a lot of headaches e.g. with csvtool

$ echo 'hostname,hostname.domain.com,hostname.domain.com,windows,6.2.9200,1.2.3,location,environment,"[""role1"", ""role2"", ""role3""]","[""recipe1"", ""recipe2"", ""recipe3""]",2019-01-10 06:06:31' | 
    csvtool col 1-9 -
hostname,hostname.domain.com,hostname.domain.com,windows,6.2.9200,1.2.3,location,environment,"[""role1"", ""role2"", ""role3""]"

or (to remove the quotes)

$ echo 'hostname,hostname.domain.com,hostname.domain.com,windows,6.2.9200,1.2.3,location,environment,"[""role1"", ""role2"", ""role3""]","[""recipe1"", ""recipe2"", ""recipe3""]",2019-01-10 06:06:31' | 
    csvtool col 1-9 - | tr -d '"'
hostname,hostname.domain.com,hostname.domain.com,windows,6.2.9200,1.2.3,location,environment,[role1, role2, role3]

---

If you can't obtain a standalone CSV parser such as csvtool, then both Perl and Python have CSV modules e.g.

perl -MText::CSV -lpe '
  BEGIN{$p = Text::CSV->new()} 
  $_ = join ",", map { $_ = s/"//gr } ($p->fields())[0..8] if $p->parse($_)
'

Answer 2

By default, awk will use whitespace to define fields, which explains why you're getting the output you see. Since you want to use a comma to separate the fields, you need to say so with -F:

awk -F, '{...}' 

To have awk print comma separated output, you need to set the OFS variable:

awk -F, -vOFS=, '{...}' 

The real difficulty here is that you are then trying to treat [role1, role2, role3] as a single field, but that's 3 fields. There are commas there, so that will be split into [role1, role2 and role3]. If you know there will always be exactly 3 fields there, it's easy:

$ awk -F, -vOFS=, '{print $1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11}' file
hostname,hostname.domain.com,hostname.domain.com,windows,6.2.9200,1.2.3,location,environment,[role1, role2, role3]

---

However, based on the raw data you have now added, while a proper CSV parser will always be the better approach, you can still do it in awk. Just run this on your original input data:

$ awk -F']' -vOFS=, '{gsub(/"/,"");print $1"]"}' file
hostname,hostname.domain.com,hostname.domain.com,windows,6.2.9200,1.2.3,location,environment,[role1, role2, role3]

The trick is to use ] as the field separator and tell awk to only print the 1st field. This will print everything up to the first ]. We then add back the ] (since that is removed when the fields are built). The gsub removes all the quotes.