Project Import from SQL Server

This guide explains how to import projects from a SQL Server database export into the Newton application.

Overview

The project import functionality allows you to migrate projects from a legacy SQL Server database export (JSON format) into the Newton project management system. The import process transforms SQL Server data to match Newton's data model and handles various edge cases and validation.

Features

• Data Transformation: Maps SQL Server fields to Newton project structure
• Duplicate Detection: Skips projects that already exist (based on project number)
• Field Mapping: Handles NameOhneNummer, Beschreibung, Nummer, CreatedTime, ModifiedTime
• Status Mapping: Converts SQL Server status codes to Newton project statuses
• Date Handling: Converts SQL Server datetime format with validation
• Error Handling: Comprehensive logging and error reporting
• Dry Run Mode: Preview import without making changes
• Progress Tracking: Shows import progress and statistics

Data Mapping

Field Transformations

SQL Server Field	Newton Field	Transformation
Nummer	projectNumber	Remove all spaces
NameOhneNummer + Beschreibung	name	Concatenate with " - ", limit to 200 chars
Status	status	Map to Newton enum values
CreatedTime	createdAt	Convert to Date object
ModifiedTime	updatedAt	Convert to Date object

Status Mapping

SQL Server Status	Newton Status
0, 1	OPEN
2	IN_PROGRESS
3	COMPLETED
4	ARCHIVED
Others	OPEN (default)

Date Processing

• Handles SQL Server datetime format: "YYYY-MM-DD HH:mm:ss"
• Converts to UTC timestamps
• Validates dates and handles invalid entries (e.g., 1899-12-30)
• Falls back to current date for invalid entries

Usage

Command Syntax

nx run api:import-projects <path-to-json-file> [options]

Options

• --dry-run: Preview import without making changes
• --force: Skip confirmation prompt and import directly

Examples

# Dry run to preview import
nx run api:import-projects ./data/Projekt_2025-09-22_102226.json --dry-run

# Perform actual import with preview
nx run api:import-projects ./data/Projekt_2025-09-22_102226.json

# Force import without preview
nx run api:import-projects ./data/Projekt_2025-09-22_102226.json --force

Step-by-Step Import Process

1. Prepare the Data File

Ensure your SQL Server export is in JSON format with an array of project objects:

[
  {
    "CreatedTime": "2016-08-05 16:05:15",
    "ModifiedTime": "2023-04-20 11:26:27",
    "Nummer": "216 23 050",
    "NameOhneNummer": "Bolzplatz FCE",
    "Beschreibung": "Football field renovation",
    "Status": 2,
    "ID": 4082
  }
  // ... more projects
]

2. Place Data File

Place the JSON file in an accessible location, e.g.:

./data/Projekt_2025-09-22_102226.json

3. Run Dry Run

First, run a dry run to see what will be imported:

nx run api:import-projects ./data/Projekt_2025-09-22_102226.json --dry-run

Expected Output:

=== IMPORT STATISTICS ===
Total records in file: 1142
Valid records for import: 1141
Existing projects (would be skipped): 1
Records with errors: 0
=== DRY RUN COMPLETED ===

4. Perform Import

If the dry run looks good, perform the actual import:

nx run api:import-projects ./data/Projekt_2025-09-22_102226.json

Expected Output:

=== IMPORT PREVIEW ===
Total records in file: 1142
Valid records for import: 1141
Existing projects (will be skipped): 1
Records with errors (will be skipped): 0
=== STARTING IMPORT ===
Imported 100 projects so far...
Imported 200 projects so far...
...
Import completed! Imported: 1141, Skipped: 1

Error Handling

Common Issues

1. File Not Found

2. Error: File not found: ./path/to/file.json

3. Solution: Check file path and ensure file exists

4. Invalid JSON Format

5. Error: File processing error: Unexpected token

6. Solution: Validate JSON format using online JSON validator

7. Missing Required Fields

8. Error: Missing project number for project ID 123

9. Solution: Ensure all records have valid Nummer field

10. Database Connection Issues

11. Error: Database connection failed
12. Solution: Check database configuration and connectivity

Validation Rules

The import will skip records that:

• Have empty or missing Nummer field
• Have empty name after transformation
• Cause database constraint violations
• Have invalid data types

Performance Considerations

• Batch Processing: Projects are imported one at a time with progress logging
• Memory Usage: Large files (>10MB) are processed efficiently in chunks
• Database Load: Uses TypeORM with optimized queries
• Progress Tracking: Shows progress every 100 imported projects

Post-Import Verification

After import, verify the data:

1. Check Project Count

sql SELECT COUNT(*) FROM project;

1. Verify Sample Projects

sql SELECT projectNumber, name, status, createdAt, updatedAt FROM project ORDER BY createdAt DESC LIMIT 10;

1. Check for Duplicates sql SELECT projectNumber, COUNT(*) FROM project GROUP BY projectNumber HAVING COUNT(*) > 1;

Rollback Strategy

If you need to rollback the import:

1. Identify Imported Projects

sql -- Find projects imported after a specific time SELECT id, projectNumber, name FROM project WHERE createdAt > '2025-09-22 10:00:00' ORDER BY createdAt;

1. Delete Imported Projects (⚠️ Use with caution) sql DELETE FROM project WHERE createdAt > '2025-09-22 10:00:00' AND projectNumber LIKE '225%'; -- Adjust pattern as needed

Troubleshooting

Debug Mode

For detailed debugging, check the application logs during import. The service provides comprehensive logging for:

• File processing status
• Data transformation details
• Database operation results
• Error details with context

Common Solutions

1. Large File Processing

2. For files >100MB, consider splitting into smaller chunks

3. Monitor memory usage during import

4. Character Encoding Issues

5. Ensure JSON file is UTF-8 encoded

6. Check for special characters in project names

7. Date Format Issues

8. The importer handles various SQL Server date formats
9. Invalid dates are replaced with current timestamp

Integration with Newton Workflow

After successful import:

1. Project Review: Imported projects appear in the project list with proper numbering
2. Status Management: Projects maintain their original status from SQL Server
3. Date Tracking: Original creation and modification dates are preserved
4. Further Configuration: You may need to assign project managers, contacts, or work packages

Command Reference

Full Command Options

nx run api:import-projects <json-file-path> [--dry-run] [--force]

Environment Variables

The command uses the same database configuration as the main application:

• DB_HOST: Database host
• DB_PORT: Database port
• DB_USERNAME: Database username
• DB_PASSWORD: Database password
• DB_NAME: Database name

Exit Codes

• 0: Success
• 1: Error (file not found, database error, etc.)

---

For additional support or questions about the import process, refer to the Newton API documentation or contact the development team.