QPM2SQLite

A Go application that converts Qingping Air Monitor CSV data files to SQLite database format with automatic duplicate detection and removal.

Features

• 🔍 Automatic CSV Discovery: Scans current directory for all CSV files
• 📊 Qingping Format Support: Handles semicolon-delimited CSV files with European decimal format (comma as decimal separator)
• 🛡️ Duplicate Prevention: Automatically detects and skips duplicate records based on timestamp
• 🗄️ SQLite Storage: Stores all data in a unified SQLite database
• 📈 Progress Tracking: Shows detailed statistics during processing
• 🌍 Multi-language: Supports international number formats

Supported Data Fields

The application processes the following Qingping Air Monitor measurements:

• Timestamp: Date and time of measurement
• PM2.5: Particulate matter 2.5μm (μg/m³)
• PM10: Particulate matter 10μm (μg/m³)
• CO2: Carbon dioxide level (ppm)
• eTVOC: Total volatile organic compounds (VOC Index)
• Noise: Sound level (dB)
• Temperature: Air temperature (°C)
• Humidity: Relative humidity (%RH)

Installation

Prerequisites

• Go 1.21 or higher
• CGO enabled (for SQLite support)
• Make (optional, for using Makefile commands)

Build from Source

git clone https://github.com/yourusername/qpm2sqlite.git
cd qpm2sqlite
go mod tidy
go build -o qpm2sqlite

Using Makefile

# Build the binary
make build

# Install to system (requires sudo)
make install-system

# Remove from system (requires sudo)
make uninstall-system

Usage

Basic Usage

1. Place your Qingping CSV files in a directory
2. Navigate to that directory in terminal
3. Run the application:

./qpm2sqlite

Or if installed system-wide:

qpm2sqlite

Expected CSV Format

The application expects CSV files with semicolon (;) delimiters and the following header format:

Time;PM2.5 (μg/m³);PM10 (μg/m³);CO2 (ppm);eTVOC (VOC Index);Noise (dB);Temperature (°C);Humidity (%RH)
24/07/2025 18:15:00;12;13;568;52;40;24,8;69,6

Output

The application creates a SQLite database file named qingping_data.db in the current directory with the following table structure:

CREATE TABLE qingping_data (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    timestamp TEXT NOT NULL UNIQUE,
    pm25 REAL,
    pm10 REAL,
    co2 INTEGER,
    etvoc INTEGER,
    noise REAL,
    temperature REAL,
    humidity REAL
);

Example Output

Searching and processing CSV files...
Found 2 CSV files
Processing file: Living-room Air Monitor 20250724-20251014.csv
  Rows found in file: 7848
  New records inserted: 7848
Processing file: Bedroom Air Monitor 20250801-20251014.csv
  Rows found in file: 3420
  Duplicates skipped: 156
  New records inserted: 3264

Final statistics:
Processed rows from CSV: 11268
Records in database: 11112
Processing completed!

Development

Available Make Commands

make build          # Build the binary
make build-linux    # Build for Linux
make clean          # Clean build artifacts
make test           # Run tests
make deps           # Download dependencies
make run            # Run the application
make run-binary     # Build and run binary
make fmt            # Format code
make vet            # Vet code
make lint           # Run linter (requires golangci-lint)
make sec            # Check security (requires gosec)
make release        # Create release build
make help           # Show all available commands

Code Quality

The project includes several code quality tools:

• gofmt: Code formatting
• go vet: Static analysis
• golangci-lint: Comprehensive linting (optional)
• gosec: Security analysis (optional)

Database Schema

Table: qingping_data

Column	Type	Description
id	INTEGER	Primary key (auto-increment)
timestamp	TEXT	Measurement timestamp (unique)
pm25	REAL	PM2.5 particles (μg/m³)
pm10	REAL	PM10 particles (μg/m³)
co2	INTEGER	CO2 level (ppm)
etvoc	INTEGER	eTVOC index
noise	REAL	Noise level (dB)
temperature	REAL	Temperature (°C)
humidity	REAL	Humidity (%RH)

Duplicate Handling

The application automatically prevents duplicate records by:

1. Unique Constraint: Database enforces uniqueness on timestamp field
2. Pre-check Validation: Checks for existing records before insertion
3. Detailed Reporting: Shows count of duplicates skipped during processing

This allows you to safely run the application multiple times on the same files without creating duplicate entries.

File Structure

qpm2sqlite/
├── main.go          # Main application code
├── go.mod           # Go module definition
├── go.sum           # Go module checksums
├── Makefile         # Build automation
├── README.md        # This file
├── .gitignore       # Git ignore rules
└── LICENSE          # License file

Contributing

1. Fork the repository
2. Create a feature branch (git checkout -b feature/amazing-feature)
3. Commit your changes (git commit -m 'Add some amazing feature')
4. Push to the branch (git push origin feature/amazing-feature)
5. Open a Pull Request

License

This project is licensed under the Apache License 2.0 - see the LICENSE file for details.

Acknowledgments

• Built for processing Qingping Air Monitor data
• Uses go-sqlite3 for SQLite database operations

Support

If you encounter any issues or have questions:

1. Check the Issues page
2. Create a new issue with detailed information about your problem
3. Include sample CSV data (anonymized) if relevant

---

Note: Replace yourusername in the GitHub URLs with your actual GitHub username.