Best way to enter data in DB that is spread out date wise

Question

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

Answer 1

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.

Answer 2

Use OPENROWSET to read the Excel file. This will return a resultset with one row per row of the Excel file and one column per Excel column including all the extra "break down" columns on the right. However, it will not materialize (i.e. permanently store) this resultset in your application database.

Write the import procedure to reference only the columns you actually need. I'm guessing the names of these are sufficiently consistent over time that this will be stable. The code will look something like

insert MyTable (ActivityID, ActivityName, TotalHours)
select ActivityID, ActivityName, TotalHours
from OPENROWSET(<various parameters go here>);