I'm guessing there is a tool somewhere that will take a set of data from a database and present it like a spreadsheet for import/export of updates. Anyone have a pointer?
Keeping data clean and consistent is so important to network operations that I think this is relevant to discuss for a bit. First of all, assuming Windows is the client OS and the data is on a server somewhere, you may find it easier to use Access to get at the data, join tables, sort it and filter out unwanted rows. Then export it to an Excel spreadsheet. But not all corporate setups include Access, so you can do much the same directly from Excel. Look in Data->Import External Data->New Database Query, then select your data source, table/query/view, columns, etc. At the end you can select "View or edit query in Microsoft Query" and you will get a query builder than can help you sift out only the rows and columns that you need to import. These queries can be saved so that in future you simply rerun the query Data->Refresh Data and get fresh up-to-date data. For updating a central database, you either need to develop applications or use a general purpose tool like MS Access. Usually spreadsheets are used to store fairly straightforward tables so building an update application is not necessarily that complex. For instance an Excel spreadsheet template can contain a VB subroutine that takes a row of data and turns it into an SQL UPDATE or INSERT statement. To an IT person this all may sound rather crude and hardly any better than just keeping a bunch of spreadsheets, but they probably never have to deal with the consequences of dirty and inconsistent data. Spreadsheets tend to breed. They get copied around in email and pretty soon, people make mistakes and throw away the updated version, not the old one. Or other people, building a new spreadsheet think that person X has the definitive spreadsheet with all the latest IP address allocations, not realizing that this is a second hand copy of another master spreadsheet, and person X only gets a copy whenever an upgrade project completes, every few months. Meanwhile, operations is busy rationalising PoP layout and all the subnetting changes but nobody writes it down except in the one project managers planning spreadsheet. The key principle here is to keep all important data in tables on a database server, and make sure that everybody understands that these tables are the one and only true source for this data. And, of course, make sure that server is backed up properly and you have a disaster-recovey clone server ready for action when needed. As long as the data is on a DB server, non-Windows systems should have no problems with accessing it. Scripting languages, Open Office, web servers and so on, can all share the same data. Note that I am suggesting this be done, separate from the kind of OFFICIAL corporate databases that run financial, ordering and billing applications. Those databases are always locked down by the DBAs and no tables are added to them without being properly designed and approved by DBAs, data architects, app developers, etc. I am suggesting that you run a separate DB server to attract all the data that usually gets squirreled away in spreadsheets, to entice employees to share data and cooperate, without a lot of bureaucracy in the way. DBAs can help a bit, advise a bit, but they should not be able to forbid people to set up a table or index or query/view. This suggestion is to treat the DB server like a general service to all employees, like telephones or meeting rooms. --Michael Dillon