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.