\r \r

DGN to Excel Conversion\r

\r

Business Case\r

\r

Problem Statement\r

DGN files are common in infrastructure and civil engineering:\r

• Transportation and highway design\r
• Bridge and tunnel projects\r
• Utility networks\r
• Rail infrastructure\r \r Extracting structured data from DGN files for analysis and reporting can be challenging.\r \r

Solution\r

Convert DGN files to structured Excel databases, supporting both v7 and v8 formats.\r \r

Business Value\r

• Infrastructure support - Civil engineering focused\r
• Legacy format support - V7 and V8 DGN files\r
• Data extraction - Levels, cells, text, geometry\r
• Batch processing - Process multiple files\r
• Structured output - Excel format for analysis\r \r

Technical Implementation\r

\r

CLI Syntax\r

DgnExporter.exe \x3Cinput_dgn>\r
```\r
\r
### Supported Versions\r
| Version | Description |\r
|---------|-------------|\r
| V7 DGN | Legacy MicroStation format (pre-V8) |\r
| V8 DGN | Modern MicroStation format |\r
| V8i DGN | MicroStation V8i format |\r
\r
### Output Format\r
| Output | Description |\r
|--------|-------------|\r
| `.xlsx` | Excel database with all elements |\r
\r
### Examples\r
\r
```bash\r
# Basic conversion\r
DgnExporter.exe "C:\Projects\Bridge.dgn"\r
\r
# Batch processing\r
for /R "C:\Infrastructure" %f in (*.dgn) do DgnExporter.exe "%f"\r
\r
# PowerShell batch\r
Get-ChildItem "C:\Projects\*.dgn" -Recurse | ForEach-Object {\r
    & "C:\DDC\DgnExporter.exe" $_.FullName\r
}\r
```\r
\r
### Python Integration\r
\r
```python\r
import subprocess\r
import pandas as pd\r
from pathlib import Path\r
from typing import List, Optional, Dict, Any\r
from dataclasses import dataclass\r
from enum import Enum\r
\r
\r
class DGNElementType(Enum):\r
    """DGN element types."""\r
    CELL_HEADER = 2\r
    LINE = 3\r
    LINE_STRING = 4\r
    SHAPE = 6\r
    TEXT_NODE = 7\r
    CURVE = 11\r
    COMPLEX_CHAIN = 12\r
    COMPLEX_SHAPE = 14\r
    ELLIPSE = 15\r
    ARC = 16\r
    TEXT = 17\r
    SURFACE = 18\r
    SOLID = 19\r
    BSPLINE_CURVE = 21\r
    POINT_STRING = 22\r
    DIMENSION = 33\r
    SHARED_CELL = 35\r
\r
\r
@dataclass\r
class DGNElement:\r
    """Represents a DGN element."""\r
    element_id: int\r
    element_type: int\r
    type_name: str\r
    level: int\r
    color: int\r
    weight: int\r
    style: int\r
\r
    # Geometry\r
    range_low_x: Optional[float] = None\r
    range_low_y: Optional[float] = None\r
    range_low_z: Optional[float] = None\r
    range_high_x: Optional[float] = None\r
    range_high_y: Optional[float] = None\r
    range_high_z: Optional[float] = None\r
\r
    # Cell/Text specific\r
    cell_name: Optional[str] = None\r
    text_content: Optional[str] = None\r
\r
\r
@dataclass\r
class DGNLevel:\r
    """Represents a DGN level."""\r
    number: int\r
    name: str\r
    is_displayed: bool\r
    is_frozen: bool\r
    element_count: int\r
\r
\r
class DGNExporter:\r
    """DGN to Excel converter using DDC DgnExporter CLI."""\r
\r
    def __init__(self, exporter_path: str = "DgnExporter.exe"):\r
        self.exporter = Path(exporter_path)\r
        if not self.exporter.exists():\r
            raise FileNotFoundError(f"DgnExporter not found: {exporter_path}")\r
\r
    def convert(self, dgn_file: str) -> Path:\r
        """Convert DGN file to Excel."""\r
        dgn_path = Path(dgn_file)\r
        if not dgn_path.exists():\r
            raise FileNotFoundError(f"DGN file not found: {dgn_file}")\r
\r
        cmd = [str(self.exporter), str(dgn_path)]\r
        result = subprocess.run(cmd, capture_output=True, text=True)\r
\r
        if result.returncode != 0:\r
            raise RuntimeError(f"Export failed: {result.stderr}")\r
\r
        return dgn_path.with_suffix('.xlsx')\r
\r
    def batch_convert(self, folder: str,\r
                      include_subfolders: bool = True) -> List[Dict[str, Any]]:\r
        """Convert all DGN files in folder."""\r
        folder_path = Path(folder)\r
        pattern = "**/*.dgn" if include_subfolders else "*.dgn"\r
\r
        results = []\r
        for dgn_file in folder_path.glob(pattern):\r
            try:\r
                output = self.convert(str(dgn_file))\r
                results.append({\r
                    'input': str(dgn_file),\r
                    'output': str(output),\r
                    'status': 'success'\r
                })\r
                print(f"✓ Converted: {dgn_file.name}")\r
            except Exception as e:\r
                results.append({\r
                    'input': str(dgn_file),\r
                    'output': None,\r
                    'status': 'failed',\r
                    'error': str(e)\r
                })\r
                print(f"✗ Failed: {dgn_file.name} - {e}")\r
\r
        return results\r
\r
    def read_elements(self, xlsx_file: str) -> pd.DataFrame:\r
        """Read converted Excel as DataFrame."""\r
        return pd.read_excel(xlsx_file, sheet_name="Elements")\r
\r
    def get_levels(self, xlsx_file: str) -> pd.DataFrame:\r
        """Get level summary."""\r
        df = self.read_elements(xlsx_file)\r
\r
        if 'Level' not in df.columns:\r
            raise ValueError("Level column not found")\r
\r
        summary = df.groupby('Level').agg({\r
            'ElementId': 'count'\r
        }).reset_index()\r
        summary.columns = ['Level', 'Element_Count']\r
        return summary.sort_values('Level')\r
\r
    def get_element_types(self, xlsx_file: str) -> pd.DataFrame:\r
        """Get element type statistics."""\r
        df = self.read_elements(xlsx_file)\r
\r
        type_col = 'ElementType' if 'ElementType' in df.columns else 'Type'\r
        if type_col not in df.columns:\r
            return pd.DataFrame()\r
\r
        summary = df.groupby(type_col).agg({\r
            'ElementId': 'count'\r
        }).reset_index()\r
        summary.columns = ['Element_Type', 'Count']\r
        return summary.sort_values('Count', ascending=False)\r
\r
    def get_cells(self, xlsx_file: str) -> pd.DataFrame:\r
        """Get cell references (similar to blocks in DWG)."""\r
        df = self.read_elements(xlsx_file)\r
\r
        # Filter to cell elements\r
        cells = df[df['ElementType'].isin([2, 35])]  # CELL_HEADER, SHARED_CELL\r
\r
        if cells.empty or 'CellName' not in cells.columns:\r
            return pd.DataFrame(columns=['Cell_Name', 'Count'])\r
\r
        summary = cells.groupby('CellName').agg({\r
            'ElementId': 'count'\r
        }).reset_index()\r
        summary.columns = ['Cell_Name', 'Count']\r
        return summary.sort_values('Count', ascending=False)\r
\r
    def get_text_content(self, xlsx_file: str) -> pd.DataFrame:\r
        """Extract all text from DGN."""\r
        df = self.read_elements(xlsx_file)\r
\r
        # Filter to text elements\r
        text_types = [7, 17]  # TEXT_NODE, TEXT\r
        texts = df[df['ElementType'].isin(text_types)]\r
\r
        if 'TextContent' in texts.columns:\r
            return texts[['ElementId', 'Level', 'TextContent']].copy()\r
        return texts[['ElementId', 'Level']].copy()\r
\r
    def get_statistics(self, xlsx_file: str) -> Dict[str, Any]:\r
        """Get comprehensive DGN statistics."""\r
        df = self.read_elements(xlsx_file)\r
\r
        stats = {\r
            'total_elements': len(df),\r
            'levels_used': df['Level'].nunique() if 'Level' in df.columns else 0,\r
            'element_types': df['ElementType'].nunique() if 'ElementType' in df.columns else 0\r
        }\r
\r
        # Calculate extents\r
        for coord in ['X', 'Y', 'Z']:\r
            low_col = f'RangeLow{coord}'\r
            high_col = f'RangeHigh{coord}'\r
            if low_col in df.columns and high_col in df.columns:\r
                stats[f'min_{coord.lower()}'] = df[low_col].min()\r
                stats[f'max_{coord.lower()}'] = df[high_col].max()\r
\r
        return stats\r
\r
\r
class DGNAnalyzer:\r
    """Advanced DGN analysis for infrastructure projects."""\r
\r
    def __init__(self, exporter: DGNExporter):\r
        self.exporter = exporter\r
\r
    def analyze_infrastructure(self, dgn_file: str) -> Dict[str, Any]:\r
        """Analyze DGN for infrastructure elements."""\r
        xlsx = self.exporter.convert(dgn_file)\r
        df = self.exporter.read_elements(str(xlsx))\r
\r
        analysis = {\r
            'file': dgn_file,\r
            'statistics': self.exporter.get_statistics(str(xlsx)),\r
            'levels': self.exporter.get_levels(str(xlsx)).to_dict('records'),\r
            'element_types': self.exporter.get_element_types(str(xlsx)).to_dict('records'),\r
            'cells': self.exporter.get_cells(str(xlsx)).to_dict('records')\r
        }\r
\r
        # Identify infrastructure-specific elements\r
        if 'ElementType' in df.columns:\r
            # Lines and shapes (often roads, boundaries)\r
            lines = df[df['ElementType'].isin([3, 4, 6, 14])].shape[0]\r
            analysis['linear_elements'] = lines\r
\r
            # Complex elements (often structures)\r
            complex_elements = df[df['ElementType'].isin([12, 14, 18, 19])].shape[0]\r
            analysis['complex_elements'] = complex_elements\r
\r
            # Annotation elements\r
            annotations = df[df['ElementType'].isin([7, 17, 33])].shape[0]\r
            analysis['annotations'] = annotations\r
\r
        return analysis\r
\r
    def compare_revisions(self, dgn1: str, dgn2: str) -> Dict[str, Any]:\r
        """Compare two DGN revisions."""\r
        xlsx1 = self.exporter.convert(dgn1)\r
        xlsx2 = self.exporter.convert(dgn2)\r
\r
        df1 = self.exporter.read_elements(str(xlsx1))\r
        df2 = self.exporter.read_elements(str(xlsx2))\r
\r
        levels1 = set(df1['Level'].unique()) if 'Level' in df1.columns else set()\r
        levels2 = set(df2['Level'].unique()) if 'Level' in df2.columns else set()\r
\r
        return {\r
            'revision1': dgn1,\r
            'revision2': dgn2,\r
            'element_count_diff': len(df2) - len(df1),\r
            'levels_added': list(levels2 - levels1),\r
            'levels_removed': list(levels1 - levels2),\r
            'common_levels': len(levels1 & levels2)\r
        }\r
\r
    def extract_coordinates(self, xlsx_file: str) -> pd.DataFrame:\r
        """Extract element coordinates for GIS integration."""\r
        df = self.exporter.read_elements(xlsx_file)\r
\r
        coord_cols = ['ElementId', 'Level', 'ElementType']\r
        for col in ['RangeLowX', 'RangeLowY', 'RangeLowZ',\r
                    'RangeHighX', 'RangeHighY', 'RangeHighZ',\r
                    'CenterX', 'CenterY', 'CenterZ']:\r
            if col in df.columns:\r
                coord_cols.append(col)\r
\r
        return df[coord_cols].copy()\r
\r
\r
class DGNLevelManager:\r
    """Manage DGN level structures."""\r
\r
    def __init__(self, exporter: DGNExporter):\r
        self.exporter = exporter\r
\r
    def get_level_map(self, xlsx_file: str) -> Dict[int, str]:\r
        """Create level number to name mapping."""\r
        df = self.exporter.read_elements(xlsx_file)\r
\r
        if 'Level' not in df.columns:\r
            return {}\r
\r
        # MicroStation levels are typically numbered 1-63 (V7) or unlimited (V8)\r
        level_map = {}\r
        for level in df['Level'].unique():\r
            level_map[int(level)] = f"Level_{level}"\r
\r
        return level_map\r
\r
    def filter_by_levels(self, xlsx_file: str,\r
                         levels: List[int]) -> pd.DataFrame:\r
        """Filter elements by level numbers."""\r
        df = self.exporter.read_elements(xlsx_file)\r
        return df[df['Level'].isin(levels)]\r
\r
    def get_level_usage_report(self, xlsx_file: str) -> pd.DataFrame:\r
        """Generate level usage report."""\r
        df = self.exporter.read_elements(xlsx_file)\r
\r
        if 'Level' not in df.columns or 'ElementType' not in df.columns:\r
            return pd.DataFrame()\r
\r
        # Cross-tabulate levels and element types\r
        report = pd.crosstab(df['Level'], df['ElementType'], margins=True)\r
        return report\r
\r
\r
# Convenience functions\r
def convert_dgn_to_excel(dgn_file: str,\r
                         exporter_path: str = "DgnExporter.exe") -> str:\r
    """Quick conversion of DGN to Excel."""\r
    exporter = DGNExporter(exporter_path)\r
    output = exporter.convert(dgn_file)\r
    return str(output)\r
\r
\r
def analyze_dgn(dgn_file: str,\r
                exporter_path: str = "DgnExporter.exe") -> Dict[str, Any]:\r
    """Analyze DGN file and return summary."""\r
    exporter = DGNExporter(exporter_path)\r
    analyzer = DGNAnalyzer(exporter)\r
    return analyzer.analyze_infrastructure(dgn_file)\r
```\r
\r
## Output Structure\r
\r
### Excel Sheets\r
| Sheet | Content |\r
|-------|---------|\r
| Elements | All DGN elements with properties |\r
| Levels | Level definitions |\r
| Cells | Cell library |\r
\r
### Element Columns\r
| Column | Type | Description |\r
|--------|------|-------------|\r
| ElementId | int | Unique element ID |\r
| ElementType | int | Type code (3=Line, 17=Text, etc.) |\r
| Level | int | Level number |\r
| Color | int | Color index |\r
| Weight | int | Line weight |\r
| Style | int | Line style |\r
| RangeLowX/Y/Z | float | Bounding box minimum |\r
| RangeHighX/Y/Z | float | Bounding box maximum |\r
| CellName | string | Cell name (for cell elements) |\r
| TextContent | string | Text content (for text elements) |\r
\r
## Quick Start\r
\r
```python\r
# Initialize exporter\r
exporter = DGNExporter("C:/DDC/DgnExporter.exe")\r
\r
# Convert DGN to Excel\r
xlsx = exporter.convert("C:/Projects/Highway.dgn")\r
print(f"Output: {xlsx}")\r
\r
# Read elements\r
df = exporter.read_elements(str(xlsx))\r
print(f"Total elements: {len(df)}")\r
\r
# Get level statistics\r
levels = exporter.get_levels(str(xlsx))\r
print(levels)\r
\r
# Get element types\r
types = exporter.get_element_types(str(xlsx))\r
print(types)\r
```\r
\r
## Common Use Cases\r
\r
### 1. Infrastructure Analysis\r
```python\r
exporter = DGNExporter()\r
analyzer = DGNAnalyzer(exporter)\r
\r
analysis = analyzer.analyze_infrastructure("highway.dgn")\r
print(f"Total elements: {analysis['statistics']['total_elements']}")\r
print(f"Linear elements: {analysis['linear_elements']}")\r
print(f"Annotations: {analysis['annotations']}")\r
```\r
\r
### 2. Level Audit\r
```python\r
exporter = DGNExporter()\r
xlsx = exporter.convert("bridge.dgn")\r
levels = exporter.get_levels(str(xlsx))\r
\r
# Check for unused standard levels\r
for idx, row in levels.iterrows():\r
    print(f"Level {row['Level']}: {row['Element_Count']} elements")\r
```\r
\r
### 3. GIS Integration\r
```python\r
analyzer = DGNAnalyzer(exporter)\r
xlsx = exporter.convert("utilities.dgn")\r
coords = analyzer.extract_coordinates(str(xlsx))\r
\r
# Export for GIS\r
coords.to_csv("coordinates.csv", index=False)\r
```\r
\r
### 4. Revision Comparison\r
```python\r
analyzer = DGNAnalyzer(exporter)\r
diff = analyzer.compare_revisions("rev1.dgn", "rev2.dgn")\r
print(f"Elements changed: {diff['element_count_diff']}")\r
```\r
\r
## Integration with DDC Pipeline\r
\r
```python\r
# Infrastructure pipeline: DGN → Excel → Analysis\r
from dgn_exporter import DGNExporter, DGNAnalyzer\r
\r
# 1. Convert DGN\r
exporter = DGNExporter("C:/DDC/DgnExporter.exe")\r
xlsx = exporter.convert("highway_project.dgn")\r
\r
# 2. Analyze structure\r
stats = exporter.get_statistics(str(xlsx))\r
print(f"Elements: {stats['total_elements']}")\r
print(f"Levels: {stats['levels_used']}")\r
\r
# 3. Extract for GIS\r
analyzer = DGNAnalyzer(exporter)\r
coords = analyzer.extract_coordinates(str(xlsx))\r
coords.to_csv("for_gis.csv", index=False)\r
```\r
\r
## Best Practices\r
\r
1. **Check version** - V7 and V8 have different capabilities\r
2. **Reference files** - Process all reference files separately\r
3. **Level mapping** - Document level standards for your organization\r
4. **Coordinate systems** - Verify units and coordinate systems\r
5. **Cell libraries** - Export cells separately if needed\r
\r
## Resources\r
\r
- **GitHub**: [cad2data Pipeline](https://github.com/datadrivenconstruction/cad2data-Revit-IFC-DWG-DGN-pipeline-with-conversion-validation-qto)\r
- **DDC Book**: Chapter 2.4 - CAD Data Extraction\r
- **MicroStation**: Infrastructure-focused CAD software\r