How to add comma after each column?

Question

I need to do this so when I open the .csv in excel each column has its own tab.

My text looks like this:

smmsp              Purpose - Service account for Sendmail; Owner - sysadmin; SERVICE ACCOUNT                            n    Account expires : never
samba              Purpose - Service account for Samba; Owner - sysadmin; SERVICE ACCOUNT                               n    Account expires : never
puppet             Purpose - Service account for Puppet; Owner - sysadmin; SERVICE ACCOUNT                              n    Account expires : never

I need it like that:

smmsp,              Purpose - Service account for Sendmail; Owner - sysadmin; SERVICE ACCOUNT,                            n,    Account expires : never,
samba,              Purpose - Service account for Samba; Owner - sysadmin; SERVICE ACCOUNT,                               n,    Account expires : never,
puppet,             Purpose - Service account for Puppet; Owner - sysadmin; SERVICE ACCOUNT,                              n,    Account expires : never,

I tried to pipe the outpu to awk 'gsub(" ", ",", $1)' FS=, OFS=, but it replace every single space with a comma

Solution

The input file appears to be in fixed-width format. You should be able to open this in Excel and specify the location of each column without any modification.

However, since it appears that all of the fields in your input sample contain at least 2 or more spaces of padding, you could replace all occurrences of 2 or more spaces with a comma:

sed -r 's/  \+/,/g' input.txt

Caveats:

• The above requires extended regular expressions (-r), available in GNU's sed
• If any of the text fields are long enough that they fill the fixed width field, or leave only one space, it will fail to insert the comma.

Here's another way that places the commas at the end of the fixed width fields, and then removes the extra spaces. The width of the fixed width fields in this example are based on your input sample:

sed 's/\(.\{19\}\)\(.\{101\}\)\(.\{5\}\)/\1,\2,\3,/' columns.txt | sed 's/ *,/,/g'

This latter method does not require extended regular expressions and should work even if an entry fills the entire fixed width field.

Comments

[George Vasiliou]

@Nick_baba Updated.