Load the named table from an Excel workbook (kite shop)
The workbook has a notes sheet, a printed sales sheet and a proper Excel table. Load the table.
- Level
- beginner
- Time
- ~6 min
- Task
- Build the query
- Access
- Free — no account needed
Loading the practice workspace…
About this exercise
What you'll practise
Navigate a workbook by item name and kind instead of by position.
Northwind Kites sells kites and accessories through three regional stores. Orders are exported from the till system every week. The weekly file is an Excel workbook.
Steps
SalesWorkbookis an Excel file. Excel.Workbook(SalesWorkbook) lists its items with columns Name, Data, Item, Kind and Hidden.- Load the item whose
ItemistblSalesand whoseKindisTable(not theSalessheet, which has a title row). - Return that table as it is: columns
OrderID,Customer,Qty,UnitPrice.
Your result must
- Columns: OrderID, Customer, Qty, UnitPrice
- No title or blank rows
- Picks the table by name, not by position
Input
- SalesWorkbook (excel)
- Sheet "Notes" (2 rows)
- Sheet "Sales" (15 rows)
- Table "tblSales" (13 rows)
Expected output columns
OrderID (text), Customer (text), Qty (number), UnitPrice (number)
| OrderID | Customer | Qty | UnitPrice |
|---|---|---|---|
| 0001 | Uma Duarte | 1 | 33.35 |
| 0002 | Bao Grieve | 4 | 47.66 |
| 0003 | Jun Castillo | 9 | 36.03 |