Help me Normalize
May 4, 2012 7:27 AM Subscribe
I need to split a field in a database into lots of fields, in order to normalise the database, but I can't figure out how to do it.
posted by Just this guy, y'know to computers & internet (6 answers total) 2 users marked this as a favorite
I want to make a one shot (or as few shots as possible) way to split up this field.
I've used excel to stitch together a bunch of XML files and pulled out two fields: RouteName(primary key) and Nodes.
The problem is that Nodes is actually a load of numbers which need to be separate.
REDROUTE / 125 456 88547 9658
BLUEROUTE / 125 554 88754 9857
So it should be RouteName, RouteOrder, Node (with the primary key being RouteName and RouteOrder)
REDROUTE / 0 / 125
REDROUTE / 1 / 456
REDROUTE / 2 / 88547
and so on.
The problem is that there are a lot of these, 7000 ish routes, with up to 116 nodes. My version of excel can't handle the whole lot.
It feels like this would be a pretty common problem and there would be a function built in somewhere to handle it.
The reason it needs to be a simple straightforward method is that I need to write it up and hand it over to less technical people to do possibly quite regularly as part of a longer process.