Employee Skills Importer

This skill automates the process of importing employee skills from CSV files into a Supabase database. It parses the CSV, checks what already exists in the database, and generates idempotent SQL scripts to insert missing data.

Overview

The skill performs a 3-step process:

1. Identify and insert missing skill categories - Extract categories from CSV headers, check database, generate INSERT script
2. Identify and insert missing skills - Extract skills with their categories, check database, generate INSERT script
3. Generate employee_skills INSERT script - Map employees by name, link skills, create final INSERT statements

CSV Format Requirements

The CSV must have:

• Row 1: Empty or metadata (ignored)
• Row 2: Skill category names spanning multiple columns
• Row 3+: Individual skill names (column headers, may span multiple rows due to line breaks)
• Employee data rows: Employee data with First Name, Last Name in first two columns, followed by skill experience values

Example structure:

,,,,,,.NET,,,,,Front-end,,,Java,,,
First Name,Last Name,Full Name,Unit,...,C#,ASP.net,MVC,...,JavaScript,HTML,CSS,...,Java,Spring,...
John,Doe,John Doe,Unit 1,...,5,4,3,...,6,6,5,...,0,0,...

Workflow

Step 1: Skill Categories

1. Parse row 2 to extract unique category names
2. Query the database to check existing categories:

   SELECT name FROM skill_categories
   

3. Generate idempotent INSERT for missing categories:

   INSERT INTO skill_categories (name) 
   VALUES ('Category1'), ('Category2'), ('Category3')
   ON CONFLICT (name) DO NOTHING;
   

Step 2: Skills

1. Parse skill name rows and map to categories from row 2
2. Query database for existing skills:

   SELECT s.name, sc.name as category_name 
   FROM skills s 
   LEFT JOIN skill_categories sc ON s.category_id = sc.id
   

3. For each skill to insert:
   • Find the category_id using a subquery
   • Generate idempotent INSERT:

   INSERT INTO skills (name, category_id)
   VALUES 
     ('C#', (SELECT id FROM skill_categories WHERE name = '.NET')),
     ('JavaScript', (SELECT id FROM skill_categories WHERE name = 'Front-end'))
   ON CONFLICT (name) DO NOTHING;
   

Step 3: Employee Skills