Hope you guys are doing well.

I'm stuck with separating column using SED. Here are two entries of my CSV file and I need help in separating "Organization Location" column into two columns "Organization City" and "Organization State".

Can someone please help me?

Year Rank Organization ID Organization Name Organization Location Private Income
2004 1 321092 Salvation Army Alexandria, Va. $1,324,089,000
2004 2 321148 American Cancer Society Alexandria, Va. $794,000,000

Welcome.

sed may not be the best language for the task and maybe not AWK either. But if the file has tabs between the fields, you might get by with either. If not, then perl5 with Text::CSV would be the way to parse it.

So, two questions: are their tabs between the fields and can you show us what you have so far written in sed?

Doesn't much look like a CSV - but that dollar field might make a mess of things. Let's see some real data - anonymised would be a good idea.
Agree sed is the wrong tool, but awk should be usable if you know it.

Thanks for your reply guys, I need a solution only using SED.

Please check attached sample data screenshot so you will get some more idea :- https://prnt.sc/jtib1t

Thanks again.

-Amy

why do you need sed? is this a homework?
In your example state is missing in line 3. How do you handle that case?
Is this really a CSV (comma separated values) file?

Can you show us the data in the file directly, as with cat or less, rather than as it's shown in a spreadsheet program?
The spreadsheet program may be formatting data to display it by adding dollar signs and commas to the numeric data, etc.

And please explain why sed is a requirement. Is this homework?
[simulpost]

It sounds like homework. What have you tried so far? You would need to nest awk statements in sed. But if you can't use awk, that won't work. If you are able to use more than one iteration sed might be feasible.

This is an output of cat abc.csv file command. This is actually a homework given by my teacher and he specifically mentioned to split the column using SED.

Year,Rank,Organization ID,Organization Name,Organization Location,Private Income,Total Assets,Service Expense,Fundraising Expense
2004,1,321092,Salvation Army,"Alexandria, Va.","$1,324,089,000","$3,039,922,000","$2,126,200,000","$123,791,000"
2004,2,321148,American Cancer Society,Atlanta,"$794,000,000","$836,295,000","$610,639,000","$188,150,000"
2004,3,321036,Gifts In Kind International,"Alexandria, Va.","$787,192,199","$790,561,819","$792,432,766","$167,242"

OK. That's your work to do, then.
We may help if you get stuck, but you need to try first. Take a shot. See what happens.

Know that will not change the contents of the file, but will only display the result on the terminal, so there's no harm in trying anything.

1 members found this post helpful.

Hi Sean,

Thank you for your reply!

I did try but can you please provide me a hint how to get the exact column using SED?

Show us what you tried...

I tried this command:- sed -n '1p'|awk '{print $5}' abc.csv but it's not providing correct output:-

it is not trivial, because commas in that file are not always separators, but part of the string (like "$123,791,000").
I would try something like:
1. replace comma within " " with : or ; or something else - but probably not required
2. find the 5th field
3. split it
4. restore the original comma

1 members found this post helpful.

Can you please explain little bit more?

Hi amy441,

The reason your instructor told you to use sed is because he wants you to learn the sed command. It was not because he wanted you to learn only how to do the assignment. Would you like to go to a doctor who only knows the examples given in class? Or would you rather go to a doctor who knows the practice of medicine.

If we simply tell you the answer, the whole purpose of school is lost. You won't learn anything. So, read the sed man page. You will probably need to use more than one command line, so you can write a script.

1 members found this post helpful.