Jump to content

csv sorting


Marty Walker
 Share

Recommended Posts

Hi,

Got a waaaaaay off topic question to ask the brains here. I've got a csv/spreadsheet that looks something like this:

Name | Categories <- Header row

Frank | Apple, Oranges, Plums

Alex | Plums, Apples, Oranges, Pears

Matt | Apples, Oranges, Pears, Lemons

...etc

I know how to split the categories column into separate columns.

What I don't know how to do is order each category column so that there's an Apples column an Oranges col and so on, so that only the Apples column will contain rows that have an Apple, Orange etc.

Cheers

Marty

Link to comment
Share on other sites

Where do you want to do this splitting and ordering? In Excel, PW (programmatically), text editor?

Do want something like:

name | apples | oranges | plums

John | √ | X | √

Peter | X | √ | √

or

apples (list of names with apples)

John

oranges

Peter

plums

John

Peter

Link to comment
Share on other sites

Hi guys,

I have a solution worked out - by manually moving cells around.

Here's what it looked liked before:

Image%202012.09.05%206:28:27%20PM.png

And after:

Image%202012.09.05%206:28:47%20PM.png

Laborious for sure, but's that the only way I know how to do it.

No rush for any pointers as I have it sorted for now. If I can figure out a 'simpler' way to format this for the next time that'd be fantastic.

Regards

Marty

Link to comment
Share on other sites

This is not a straight answer to your question but if you work a lot with messy data, cleaning and data transformation you could take a look at Google Refine . This is a really powerfull tool and not hard to use. I haven't used it for a while but i think it would be quite easy to transform the data to the format you desire.

  • Like 2
Link to comment
Share on other sites

I've worked with a spreadsheet exactly like that before and had to do something similar. What I ended up doing was reading it in via PHP, converting the column with the data in it to the format I needed, and then wrote it back out as another CSV. Then loaded back in Excel to work with it. But I'd check out that Google Refine first. If you find that doesn't do the trick, let me know and I can explain how you can do it with PHP.

Link to comment
Share on other sites

 Share

  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...