Best way to enter data in DB that is spread out date wise I have an Excel file that, to keep it simple here, has 3 columns: ActivityID, ActivityName and TotalHours.

TotalHours column is basically all the hours allotted to that specific ActivityID and the file can have about ~1500 of these activityID equating to 1500 rows. 

Now the thing is that there are additional columns in the file which break down the TotalHours by week dates. These hours are split into different week periods but adding them all up equals TotalHours.

 

I don't want to end up dynamically creating table columns in my DB as I could end up with 100+ fields. One solution is that I could read the Excel just like it is and do calculations on the fly but this would then require the file to be strictly templatized meaning the data needs to be consistent and be in specified cells for me to read them. This is something that I am trying to avoid.

Is there any better way that you know I can approach this problem?

Thanks
Am I understanding that you pretty much have a work order, and that work order can be worked on for a number of hours on any day, and you want to store how many hours someone worked it each day? So if I work on a project for 2 hours on the 3rd, and 5 hours on the 10th, then it would store 12 hours in the total column?

If that's the case, I would approach it in a more event based method. Have a table for the activity (ActivityID, ActivityName...). Then Have a table for work entries for the activity (ActivityID, PersonWorking, DateWorked, PersonWorked...). The hours will each only be entered into the Work table, and have a view that would sum up each activity and show the days worked along with the total. And as a note, that database systems can usually handle a lot of rows listing 4-5 fields a bit easier than trying to just have 10 years worth of dates in the column headers and just spread everything out.

In my experience it is almost never a good idea to 1)store a computed value based on data in the same record row, and 2)store date data as field names. Unless you have a VERY SPECIFIC set of dates that will apply to all projects/activities, don't use them as field headers.