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

Answer 1

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.

Answer 2

Since you seem to want to preserve the internal spacing I'd do similar to @cherdt's solution, but you can capture the spaces like

sed -e 's/\(  \+\)/,\1/g' input

which will put a comma before every block of 2 or more spaces without changing the number of spaces.

Answer 3

If the columns are separated by tabs (looks like they do) , then this should work:

$awk -F "[\t]*" '{print $1", "$2", "$3", "$4", "}' b.txt
smmsp, Purpose - Service account for Sendmail; Owner - sysadmin; SERVICE ACCOUNT, n, Account expires : never, 

If columns are not separated by tabs but from spaces, then using space as separator is a bit confusing, since space exists even between data.

I would read the file and store it in an array like this:

$while IFS=" " read -a line;do for i in {0,11,12,16};do line[$i]+=",";done;echo "${line[@]}";done <b.txt
smmsp, Purpose - Service account for Sendmail; Owner - sysadmin; SERVICE ACCOUNT, n, Account expires : never,
smmsp, Purpose - Service account for Sendmail; Owner - sysadmin; SERVICE ACCOUNT, n, Account expires : never,
smmsp, Purpose - Service account for Sendmail; Owner - sysadmin; SERVICE ACCOUNT, n, Account expires : never,
smmsp, Purpose - Service account for Sendmail; Owner - sysadmin; SERVICE ACCOUNT, n, Account expires : never,
smmsp, Purpose - Service account for Sendmail; Owner - sysadmin; SERVICE ACCOUNT, n, Account expires : never,

I have noted that in terms of space separatation, i need to add comma in array elements line[0], line[11], line[12] and line[16].

This tricks requires all lines to have the same format.
If not you need to read each array element and insert the comma where you want it.

If you have a lot of lines and you care about performance, you could built an awk script to process all variables (from $1 up to $NF) in a similar way but inside an awk script (i'm not good in awk..)