Excel to Database Processing Project

This Python project reads Excel files with Portuguese column names and extracts information related to areas, questions, items, and actions, then stores this data in a database following a hierarchical structure.

Project Structure

carga_banco/
├── main.py                    # Main application entry point
├── config.py                  # Configuration settings
├── models.py                  # Database models
├── database.py                # Database connection and setup
├── excel_reader.py            # Excel file reading functionality
├── data_processor.py          # Data processing and database operations
├── requirements.txt           # Python dependencies
├── .env.example              # Environment variables template
├── create_sample_excel.py     # Script to create sample Excel file
├── test_application.py        # Test script to verify functionality
├── setup_database.py          # PostgreSQL database setup script
├── project_info.py            # Project overview and instructions
├── README.md                  # Project documentation
└── data/
    ├── input.xlsx            # Your Excel file (create with create_sample_excel.py)
    └── sample_data.txt       # Sample data in text format

Features

• Excel File Reading: Reads Excel files using pandas and openpyxl
• Portuguese Column Support: Handles Portuguese column names from your Excel files
• Hierarchical Data Structure: Follows the hierarchy: Area → Question → Item → Action
• Database Storage: Stores data in PostgreSQL (default) or SQLite
• Relational Data: Creates proper relationships between all entities
• Error Handling: Comprehensive error handling and logging
• Data Preview: Shows data preview before processing

Excel File Structure

Your Excel file should contain the following columns:

Portuguese Column	English Translation	Database Field
Area	Area	area
Questao	Question Description	question_description
Questao Abreviada	Question Title	question_title
Item Verificacao	Item Description	item_description
Item Verificacao Abreviada	Item Title	item_title
Questao Auxiliar	Action Description	action_description

Data Hierarchy

The application follows this hierarchy when processing data:

1. Area → 2. Question → 3. Item → 4. Action

Each level is linked to the previous level, creating a proper relational structure.

Installation

1. Clone or download the project files

2. Install dependencies:

   pip install -r requirements.txt

3. Set up PostgreSQL database:

   -- Create database
   CREATE DATABASE excel_data;
   
   -- Create user (optional)
   CREATE USER excel_user WITH PASSWORD 'your_password';
   GRANT ALL PRIVILEGES ON DATABASE excel_data TO excel_user;

4. Set up environment variables:

   cp .env.example .env
   # Edit .env file with your PostgreSQL connection details

5. Set up database (optional - automated setup):

   python setup_database.py

Configuration

Environment Variables (.env file)

• DATABASE_URL: Database connection string (default: PostgreSQL)
• EXCEL_FILE_PATH: Path to your Excel file
• EXCEL_SHEET_NAME: Sheet name to read (leave empty for first sheet)
• EXCEL_HEADER_ROW: Header row index (0-based, default: 0)

Column Mappings (config.py)

The application expects Excel columns named:

• Area: Area information
• Question: Question text
• Item: Item name/description
• Actions: Action information

You can modify these mappings in config.py if your Excel file uses different column names.

Excel File Configuration

The application now supports flexible Excel file configuration:

Sheet Selection

• Default: Reads the first sheet
• Custom: Specify sheet name in EXCEL_SHEET_NAME environment variable
• Example: EXCEL_SHEET_NAME=Data to read a sheet named "Data"

Header Row Configuration

• Default: Uses row 0 (first row) as header
• Custom: Specify header row index in EXCEL_HEADER_ROW environment variable
• Example: EXCEL_HEADER_ROW=2 to use the third row as header

Usage Examples

# Use PostgreSQL (default)
DATABASE_URL=postgresql://username:password@localhost:5432/database_name

# Use SQLite (alternative)
DATABASE_URL=sqlite:///excel_data.db

Usage

1. Prepare your Excel file:

   • Ensure it has columns for Area, Question, Item, and Actions
   • Place it in the specified path (default: data/input.xlsx)

2. Run the application:

   python main.py

3. Check the results:

   • The application will show data preview and processing summary
   • Data will be stored in the configured database

Database Schema

The application creates four related tables in PostgreSQL:

• tb_area: Stores area information with UUID primary key
• tb_questao_base: Stores questions linked to areas with UUID foreign key
• tb_item_base: Stores items linked to questions with UUID foreign key
• tb_acao_base: Stores actions linked to items with UUID foreign key

Table Structure

-- Area table
CREATE TABLE tb_area (
    area_id uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
    nome varchar(100) NOT NULL,
    status VARCHAR(50) NOT NULL DEFAULT 'ATIVO',
    dt_criacao TIMESTAMP DEFAULT now(),
    criado_por uuid,
    dt_atualizacao TIMESTAMP,
    atualizado_por uuid
);

-- Question table
CREATE TABLE tb_questao_base (
    questao_base_id uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
    area_id uuid NOT NULL REFERENCES tb_area(area_id),
    titulo varchar(200) NOT NULL,
    descricao TEXT,
    tipo_base varchar(50) NOT NULL DEFAULT 'KATRU',
    dt_criacao TIMESTAMP DEFAULT now(),
    criado_por uuid,
    dt_atualizacao TIMESTAMP,
    atualizado_por uuid
);

-- Item table
CREATE TABLE tb_item_base (
    item_base_id uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
    questao_base_id uuid NOT NULL REFERENCES tb_questao_base(questao_base_id),
    titulo varchar(200) NOT NULL,
    descricao TEXT,
    tipo_base varchar(50) NOT NULL DEFAULT 'KATRU',
    dt_criacao TIMESTAMP DEFAULT now(),
    criado_por uuid,
    dt_atualizacao TIMESTAMP,
    atualizado_por uuid
);

-- Action table
CREATE TABLE tb_acao_base (
    acao_base_id uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
    item_base_id uuid NOT NULL REFERENCES tb_item_base(item_base_id),
    titulo varchar(200) NOT NULL,
    descricao TEXT,
    tipo_base varchar(50) NOT NULL DEFAULT 'KATRU',
    dt_criacao TIMESTAMP DEFAULT now(),
    criado_por uuid,
    dt_atualizacao TIMESTAMP,
    atualizado_por uuid
);

Customization

Adding New Fields

1. Update the models in models.py
2. Modify column mappings in config.py
3. Update the data extraction logic in excel_reader.py

Changing Database

1. Update DATABASE_URL in .env file
2. Install appropriate database driver (e.g., psycopg2-binary for PostgreSQL)

Modifying Data Processing

1. Edit data_processor.py to change how data is processed
2. Update validation rules and business logic as needed

Error Handling

The application includes comprehensive error handling for:

• File not found errors
• Database connection issues
• Data validation errors
• Processing failures

Next Steps

This is a foundation project. You can extend it by:

• Adding data validation
• Implementing data export functionality
• Adding a web interface
• Creating data analysis features
• Adding batch processing capabilities