Dynamically importing data into excel from a Database

[BBxcl]

Hi,

I have a spreadsheet with 40k rows or so which is added onto every week. Each row has an Employee ID, Employee’s Shift Number and Hours worked column (see attached image for sample data).

The problem is that I have the work out the Hours worked from a database to which I have an ODBC link setup to using SQL (The query would be something as follows:

SELECT hours_worked FROM employee_hrs WHERE employee_id = <employee id in excel> and shift_no = <Shift Number in excel>

My first instinct was to import the whole database table using Power Query into a separate worksheet and then use a 2 criteria index and match to find the house worked. The problem with this is that the database table has about 400k records (and increasing) so this approach is not efficient at all and is extremely slow since I end up using an array formula to lookup as well.

Is there a better, more efficient way of doing this so that the hours worked column is populated using the criteria above?

Many thanks in advance for your time and help!

 

[HighAndWilder]

If this is carried out each week then surely the records that you need can be queried and then extracted using a date that relates to the week required.

Is there a shift date in the source data?

 

[BBxcl]

If this is carried out each week then surely the records that you need can be queried and then extracted using a date that relates to the week required.

Is there a shift date in the source data?

Yes there is a shift date column as well in both excel and in the database.

The problem is that the database will have ALL records of employees that worked on that date. However, the excel file only has a select few employees for the day which I need to work out the hours for to be used for further analysis. So the best way for me to work it out would be to use employee id and shift number in conjunction with each other as each employee could have done more than 1 shift on the same day as well.

This is to be carried out every week from now but hasn’t been carried out for the past few months so I’m left with 40k or so rows that need this info.

I don’t know of an efficient method to get the records from the database into the excel column to be used in further analysis..

 

[HighAndWilder]

This is how I would do it.
Do this manually at first and write down the steps and any issues as you go along.
Query and extract ALL records for a particular period.
Import this data into Excel. This can be into a separate workbook to the one you are using for the anaylsis but I would use the same workbook.
Add a column to identify which rows you need to keep.
Add a formula to identify which employees you are interested in. This can be a lookup on your Excel list of employees.
Delete those rows in the imported data that you don't need or just copy those that you do need into your existing worksheet.

Once you have done this manually and it works you can start to automate it.