timesheetconv

Brendan Kidwell
Assisted by Claude Code
2026-10-26

This tool loads Costpoint timesheet exports (input/<fiscal year>/<YYYY-MM-DD>.xlsx) and SQL view files (views/*.sql) into an SQLite database on your local machine.

With your timesheet data in SQLite, and a tool like DBGate or DBeaver, it's much easier to run queries on your timesheets than in the Costpoint UI.

Requirements

You must have uv installed. uv will install Python for you if it's not installed.

Setup

Export your timesheets to Excel -- You'll probably want to go from the beginning of the current year, until now.

To export a timesheet, click the "🖶" (Print) button in the toolbar on the right, then "Export to Excel" (not the "⋮" button). You should get an Excel file.

Create a folder in this project called input, and place all of your exported timesheets in a folder named for the current fiscal year. For example, your first timesheet might be:

./input/2026-2027/2026-04-10.xlsx
or
./input/2026/2026-01-09.xlsx

Usage

From a command shell, navigate to this project's root folder and run

uv run timesheetconv

timesheetconv will:

1. Create ./data/timesheet.sqlite if it doesn't exist.
2. Sync ./input/*/*.xlsx into the database.
3. Sync any Views (SQL files) you have in ./views into the database.

Then you can open timesheet.sqlite in a database viewer such as DBCate or DBeaver, and browse and query the data.

If you add more timesheets or revise old timesheets, just export them to the same place and run timesheetconv again. The changes will be merged into the database.

Dry Run

This command will do a dry-run and report what will change from the import but won't actually write to the database:

uv run timesheetconv --dry-run  # show what would change, save nothing

Development Scripts

uv run pytest
uv run ruff format
uv run ruff check

Database

Table	Contents
timesheets	One row per input file. name is the period's last Friday (YYYY-MM-DD); also fiscal_year, start_date, end_date, Costpoint revision, source_file.
projects	One row per unique Project code + Pay Type found in any timesheet, with the latest charge_description. name starts as the charge description and is never overwritten, so you can edit it.
hours	One row per non-blank Line × Day cell: timesheet_id, fiscal_year, line_num, date (YYYY-MM-DD), project_code, pay_type, hours (may be negative for corrections).

Notes:

• Projects are keyed on Project code and Pay Type because one code can be used with several pay types (00400.PTO is both sick time and vacation). Join hours to projects with USING (project_code, pay_type).
• Blank day cells don't get rows in hours; a line with no hours at all only contributes its project to projects.
• Hours are matched on (timesheet, line number, date) when syncing, and rows are inserted, updated or deleted to match the file. Line numbers can change between revisions of a timesheet, which shows up as updates/deletes; totals always match the latest export.
• If an input file is removed, its timesheet and hours are deleted. A project that no longer appears in any timesheet is deleted too, unless you've given it a custom name, in which case it is kept.

Views

Each views/<name>.sql file holds a single SELECT statement. It becomes a view named after the file with dashes changed to underscores, e.g. paid-time-off.sql → paid_time_off. Views can refer to each other. Every sync drops and recreates all views that came from views/, and removes ones whose file is gone; views you create directly in your SQLite GUI are left alone.