[SOLVED] Help with cleaning up a csv file

shelby · 04-30-2014, 11:14 AM

I am not quite sure how to attack this one. I have a CSV file (see example below). I need to clean up the first column. In the area that has quotes I just want Jsmith, I want the cn= and everything past the Jsmith gone in the quotes. See below.

How my CSV looks:
"cn=Jsmith,ou=Employee,ou=Library,o=Users”,Jsmith2,,,jim@email.com,jim

What I need after clean up
Jsmith,Jsmith2,,,jim@email.com,jim

Thanks...

szboardstretcher · 04-30-2014, 11:22 AM

This should get you pretty close to what you want. Some extra manipulation will be needed.

Code:

cut -d "," -f 1,5,8,9

grail · 04-30-2014, 11:47 AM

Code:

sed -r 's/^[^=]+=([^,]+).*"/\1/'

szboardstretcher · 04-30-2014, 11:54 AM

Doesn't work properly on Centos 6.5:

Code:

[root@dev ~]# echo "cn=Jsmith,ou=Employee,ou=Library,o=Users”,Jsmith2,,,jim@email.com,jim" | sed -r 's/^[^=]+=([^,]+).*"/\1/'
cn=Jsmith,ou=Employee,ou=Library,o=Users”,Jsmith2,,,jim@email.com,jim

[root@dev ~]# sed --version
GNU sed version 4.2.1

shelby · 04-30-2014, 12:08 PM

Quote:

Originally Posted by grail

Code:

sed -r 's/^[^=]+=([^,]+).*"/\1/'

This works perfectly...Thank you!!!

shelby · 04-30-2014, 12:10 PM

Quote:

Originally Posted by szboardstretcher

Doesn't work properly on Centos 6.5:

Code:

[root@dev ~]# echo "cn=Jsmith,ou=Employee,ou=Library,o=Users”,Jsmith2,,,jim@email.com,jim" | sed -r 's/^[^=]+=([^,]+).*"/\1/'
cn=Jsmith,ou=Employee,ou=Library,o=Users”,Jsmith2,,,jim@email.com,jim

[root@dev ~]# sed --version
GNU sed version 4.2.1

I had to update my sed command to 4.2.2 and it worked fine. I am working in OS X so I had to update via MacPorts. Maybe you can update yours via yum?

grail · 04-30-2014, 01:18 PM

@szboardstretcher - The issue is you copied the input from the user and the second set of double quotes is actually something else ... not sure what. Also, if you use double quotes around double quotes
you will lose the original form, try:

Code:

echo '"cn=Jsmith,ou=Employee,ou=Library,o=Users",Jsmith2,,,jim@email.com,jim' | sed -r 's/^[^=]+=([^,]+).*"/\1/'

szboardstretcher · 04-30-2014, 01:20 PM

Derp. Sorry about that! Yeah, looks real nice now.

grail · 04-30-2014, 02:49 PM

All good .. took me a while to figure out why it wasn't work straight away for me too

szboardstretcher · 04-30-2014, 02:54 PM

Aye. Its been a long day

I eventually just put it into a file as-is and cat'd the file through a pipe to the command. Your command works nicely.