3

I am currently migrating a bunch of data from several Excel files of similar formats into a more structured format.

Imagine Tabular data like this in File 1:

Customer | Cabinet : Device : Port | Cabinet : Secondary Device : Port | ...
ACME     | C1      : Dv1    : 12   | C1      : Dv2              : 11   | ...
Foo Ltd. | C1      : Dv1    : 13   | C1      : Dv2              : 14   | ...

And Tabular data in File 2:

Customer | Cabinet : Device : Module : Port | Cabinet : Secondary Device : Port | ...
Bar Ltd. | C1      : Dv13   : 23     : 1    | A       : E                : 1
LLL Ltd. | C1      : Dv13   : 24     : 1    | A       : E                : 2
Weird Inc| C1      : Dv12   :        : 12   | C       : F                : 1

And there are one hundred more sheets, each with its own extra columns like "Slot" or "Subsystem".

Unfortunately this makes two big problems from my point of view:

  • Modelling this in a relational database is disproportionally painful
  • When presenting the data to a user I have to stick to the old presentation as much as possible (tight schedule, readability, easy adaption for users)

Therefore the presentation looks almost like in Excel. But now the problem:

The column title must be the same for all hundred sheet. My solution was that all tables now have the same titles:

Customer | Cabinet : Device : Port | Cabinet : Secondary Device : Port | ...

And when I migrate "Module: 23, Port: 1" this will be saved in the field "Port" as "Module 23 : Port 1". And it looks like this:

Customer | Cabinet : Device : Port               | Cabinet : Secondary Device : Port | ...
Bar Ltd. | C1      : Dv13   : Module 23 : Port 1 | A       : E                : 1
LLL Ltd. | C1      : Dv13   : Module 24 : Port 1 | A       : E                : 2

In fact I am quite happy with this solution but I found out that this creates confusion. So I think of this as a solution:

Customer | Cabinet : Device : Port               | Cabinet : Secondary Device : Port | ...
Bar Ltd. | C1      : Dv13   : Module 23 • Port 1 | A       : E                : 1
LLL Ltd. | C1      : Dv13   : Module 24 • Port 1 | A       : E                : 2

What do you think? Is • ok as a separator? Have other ideas?

The basic problem for choosing a separator is: the Excel sheets contain in some cases the most awkward characters, it's already amazing that : is used as a separator...

EDIT: An additional note: For reasons of simplicity, but also for consistency I want the headers in all table presentations to look the same.

EDIT2: I don't limit myself to ASCII characters. Any unicode symbol or even images are fine.

4 Answers 4

3

Whatever separator you choose, it will be ambiguous since it is missing in the header row (looks like there's a header missing and module is in the wrong column).

Perhaps you could try grouping the subsidiary values e.g.

Customer | Cabinet : Device : Port              | Cabinet : Secondary Device : Port | ...
Bar Ltd. | C1      : Dv13   : [Module 23]Port 1 | A       : E                : 1
LLL Ltd. | C1      : Dv13   : [Module 24]Port 1 | A       : E                : 2

Or better, since the column implies port is primary and module secondary:

Customer | Cabinet : Device : Port              | Cabinet : Secondary Device : Port | ...
Bar Ltd. | C1      : Dv13   : Port 1[Module 23] | A       : E                : 1
LLL Ltd. | C1      : Dv13   : Port 1[Module 24] | A       : E                : 2
1

It looks like you are limiting yourself to using ASCII characters to present data. This is not a good idea. These days computers are capable of using graphics to present data on the screen.

I can understand that your planning does not allow a more fundamental approach to the problem, but the things you mention (easy adoption and readability) are are in fact good arguments not to proceed with your idea.

If you do want to stick with ASCII characters only you indeed have very few options and I don't think you will find a character that makes this more readable. Your suggested character • is not a bad choice in the given circumstances.

This being said I would like to suggest that you do your best to convince whoever is responsible for your budgets to foresee extra budget. What helps a lot in the convincing process is to make a mockup of what it would look like if you present the data in a modern GUI. Show people that and then show a mockup of the current approach.

2
  • Surely I don't want to limit myself to ASCII characters, even images are fine. Extra budget is impossible for political reasons, in fact the less budget I use, the more budget I get afterwards to improve...
    – user694971
    Sep 29, 2011 at 13:07
  • In that case I would not mind too much. I would even try to make it look as bad as possible to increase the future budget. But that's my bad attitude, don't take an example out of it. Sep 29, 2011 at 13:35
1

I will avoid the discussion of whether this is a good way of representing the data overall as you have already given your reasons for the constraints.

Your solution is an improvement on the version without the • as a separator, but not the best improvement in my opinion.

Before hand you gave 3 headings (Cabinet : Device : Port) but then had 4 items under that. That is always going to cause confusion (as you said it had done). Whatever you use as a separator there it is an improvement.

But if you're giving data such as "Module 23 • Port 1", then why not have the heading as: "Cabinet : Device : Module: Port", giving you something like this:

Customer | Cabinet : Device : Module : Port | Cabinet : Secondary Device : Port | ...
Bar Ltd. | C1      : Dv13   : M 23   : P 1  | A       : E                : 1    |
LLL Ltd. | C1      : Dv13   : M 24   : P 1  | A       : E                : 2    |    
2
  • Thanks for the feedback, I updated the question. In a lot of cases it happens that there are rows that don't have a module. This was one of the many reasons that the Excel sheets haven't been machine readable until recently. For mostly technical reasons it is disproportionate effort needed to have a custom header for each table presentation.
    – user694971
    Sep 29, 2011 at 13:04
  • If some of the Ports don't have Modules, then I might put Ports first so that they all line up and become more scannable. So perhaps try "Port 1 • Module 23" or "Port 1 (Module 23)"?
    – Evan
    Sep 29, 2011 at 17:29
0

Would it be possible to decode the source for viewing only? Then you could use something unique for a separator and replace it on view with something natural looking like a dash (or anything you want, really.)

If it need to be different than a dash, I'd look for something that is close to an arrow pointing right

Module x > port 16 or something

http://en.wikipedia.org/wiki/List_of_Unicode_characters gives a host of options, I think http://en.wikipedia.org/wiki/List_of_Unicode_characters#Geometric_shapes that gives you ▶, ▷, ▸, ▹ fits you best. Those are common enough to not alienate your users (like something that is really weird) but probably esoteric enough for it not showing up in module names or something. And they are apparently common enough to be available on my system (with no special fonts installed for that.)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.