Convert txt to csv

Shell Programming and Scripting
1

Hi - I am looking to convert the following text to csv. The columns may not always have data in them and they may have varying spaces but I still need to have a comma there anyway:

Sample Data:

```text
Name                  Email                                    Location         Phone
Tom Smith          toms@me.com                     home              +65 5555 5555
John,,,
Jerry T. Wright   jerry@me.com                      NY office       +1 212 555 5555
Phil Dell             phil@me.com                                              +1 212 555 1111
```


Need it to look like:

Name,Email,Location,Phone
Tom Smith,toms@me.com,home,+65 5555 5555
John,,,
Jerry T. Wright,jerry@me.com,NY office,+1 212 555 5555
Phil Dell,phil@me.com,,+1 212 555 1111

Appreciate any help!
JP

2

Hi JPBovaird,

Not so elegant, but it seems to work:

sed -e 's/[ ]*\([a-z0-9_-.]*@[a-z0-9_-.]*\)[ ]*/,\1,/g;
s/[ ]*\([0-9 ]+\)/,\1/;
s/[ ]*Email[ ]*/,Email,/;
s/Location[ ]*Phone/Location,Phone/;s/[ ]*+/+/'  inputfile > outputfile

Hope it helps.

Regards

1 Like
3 
sed -e 's/[\+][0-9 ]*/,&,/' -e 's/[a-z0-9_-.]*@[a-z0-9_-.]*/,&,/' inputfile
1 Like
4

cgkmal - Thanks for your reply! It's not writing the additional commas where there is no data. Any idea?

Output is below:

Name,Email,Location,Phone
Tom Smith,toms@me.com,home,+65 5555 5555
John
Jerry T. Wright,jerry@me.com,NY office,+1 212 555 5555
Phil Dell,phil@me.com,+1 212 555 1111

For instance - after John, there should be 3 commas and for Phil's location there should be 2 commas between email and phone.

tene - also thanks for your help!

5

You're welcome JPB,

Only that depending if the data vary, it will be necessary to modify the code, but based on your sample please try:

sed -e 's/[ ]*\([a-z0-9_-.]*@[a-z0-9_-.]*\)[ ]*/,\1,/g;
s/[ ]*\([0-9 ]+\)/,\1/;
s/[ ]*Email[ ]*/,Email,/;
s/Location[ ]*Phone/Location,Phone/;s/[ ]*+/+/;
s/\(@.*\.[a-z]\{3,4\},\)+/\1,+/;
s/\([a-zA-Z .]$\)/\1,,,/;s/,Phone,,,/,Phone/' inputfile

Best regards