Using Excel and CSV files as a permanent data store is ill-advised. Using Excel or CSV for data entry is even worse. The fact that you need to bring data from Excel/CSV into a database on an automated schedule suggests that you are doing one or both.

I do understand that sometimes you are stuck with what you were given, but you can push for a better data storage model. It may take time, but in the long-run it will save you a lot of hair-pulling.

First of all, data should be stored in a database. Excel is free-form and doesn't enforce any constraints on the data, data types, or even where you can put data on the worksheet. You can protect a sheet to enforce some of this, but it will always fall short. If someone inserts a column, your import process fails. Or worse, it keeps running while silently cranking bad data. You want to avoid this. There's a lot of other short-comings, but I think it's better to look at the advantages: SQL Server has forces data types, data constraints, foreign key constraints, triggers, and a whole host of robust functions all suited for one purpose: good, easy to manage data.

The other major problem with Excel is that only one person can modify the worksheet at a time. I'm sure you've already run into this, and it can be a real pain to manage.

So you put your data in a database, how then do your users modify the data? Well, this is definitely the hard part, but there are some fairly easy solutions. Probably the easiest is by using a Microsoft Access .ADP file (Access Data Project). You can create a new ADP from the new dialog, and it will immediately ask to connect to a database. All the tables in the database are instantly available, and you can start building forms to modify the data. The nice thing is your user base probably already knows Access fairly well. Even better, you probably already know Access quite well and can build forms fairly quickly. The other nice thing is it can use Windows integrated security, and will enforce the database access restrictions at the application level with no login. I have a "Lookup tables" .ADP file where I work. Several lookup tables are available for users to modify (like state to sales region mappings). The foreign key relationships of the host database are of course enforced, so I can force them to pick valid state and sales region names (and Access lets you build handy drop-down lists). All of the tables are available to edit, but only if the Windows user has access. I have one database role for each group of tables. Users get assigned to these roles when they need to modify the data. Users can't modify data they don't need to. You could accomplish the same security in Excel by using Windows security, but I doubt you do, because it's much harder to manage. An ADP file offers a lot, while still being very quick to deploy; usually one to a few days is all. One thing you must know however: your tables must have primary keys, or they will be read only in Access.

An even more robust option would be offering a web interface, but this will require more time and money. Microsoft could discontinue support for ADP files, but HTML isn't going anywhere. If you really need a lot of data modified, though, a good web interface really is your best choice. I highly recommend just grabbing some third-party app that does it all. I don't have a specific in mind, but you should be able to find one that works just like Excel and integrates with Windows security. This will offer a high level of comfort for your users. It shouldn't be too expensive, which would make it easier to sell to your boss.

You could of course program you own interface, but you're not going to program something that truly works like Excel and integrates with Windows security. You're just not. So don't.

Once you have the data in your database (where it belongs), and you're your users the ability to modify it, reporting on it is easy. Although they might not immediately want to give up their precious Excel, in the end they will thank you because you can give them truly great real-time reporting. Oh, and two people can modify the same table at once.

--J