SCENARIO: Import organization structure

Contoso Corporation (Level 0)
├── North America Division (Level 1)
│ ├── USA Region (Level 2)
│ │ ├── California Office (Level 3)
│ │ └── Texas Office (Level 3)
│ └── Canada Region (Level 2)
│ └── Toronto Office (Level 3)
└── Europe Division (Level 1)
├── UK Region (Level 2)
└── Germany Region (Level 2)

CHALLENGE:
– ParentAccountId lookup must reference existing records
– Must insert parents before children
– Need GUIDs to reference parents

TAB: Bulk Data → Bulk Ingestion with Lookups

STEP-BY-STEP:

1. PREPARE DATA (Level-by-Level):
────────────────────────────────
File 1: level_0_root.json
[
{
“name”: “Contoso Corporation”,
“accountnumber”: “ROOT-001”
}
]

File 2: level_1_divisions.json
[
{
“name”: “North America Division”,
“accountnumber”: “DIV-NA”,
“parent_account_number”: “ROOT-001” ← Match field!
},
{
“name”: “Europe Division”,
“accountnumber”: “DIV-EU”,
“parent_account_number”: “ROOT-001”
}
]

File 3: level_2_regions.json
[
{
“name”: “USA Region”,
“accountnumber”: “REG-USA”,
“parent_account_number”: “DIV-NA”
},
{
“name”: “Canada Region”,
“accountnumber”: “REG-CAN”,
“parent_account_number”: “DIV-NA”
},
{
“name”: “UK Region”,
“accountnumber”: “REG-UK”,
“parent_account_number”: “DIV-EU”
}
]

2. CONFIGURE WIZARD MODE:
────────────────────────────────
Group 1: Level 0 (Root)
┌────────────────────────────┐
│ Environment: Production │
│ Target Table: account │
│ Upload: level_0_root.json │
│ Lookup Columns: NONE │ ← No parent!
└────────────────────────────┘

Group 2: Level 1 (Divisions)
┌────────────────────────────────────────────┐
│ Environment: Production │
│ Target Table: account │
│ Upload: level_1_divisions.json │
│ Lookup Column 1: │
│ Source Column: parent_account_number │
│ Lookup Table: account │
│ Match Field: accountnumber │
│ Binding Field: parentaccountid │
└────────────────────────────────────────────┘

Group 3: Level 2 (Regions)
┌────────────────────────────────────────────┐
│ Environment: Production │
│ Target Table: account │
│ Upload: level_2_regions.json │
│ Lookup Column 1: │
│ Source Column: parent_account_number │
│ Lookup Table: account │
│ Match Field: accountnumber │
│ Binding Field: parentaccountid │
└────────────────────────────────────────────┘

3. EXECUTE:
────────────────────────────────
Click: “🚀 Run All Lookup Ingestion Groups”

WHAT HAPPENS:
✅ Group 1 runs first → Creates root (Contoso Corp)
✅ Group 2 runs second → Creates divisions, links to root
✅ Group 3 runs third → Creates regions, links to divisions
✅ All parent-child relationships preserved!

4. RESULTS:
────────────────────────────────
✅ Level 0: 1 record created
✅ Level 1: 2 records created (with parent links)
✅ Level 2: 3 records created (with parent links)
✅ Total: 6 records, full hierarchy!

TAB: Bulk Data → Bulk Ingestion with Lookups → Direct @odata.bind

WHEN TO USE:
– You already have GUIDs from another system
– You’ve done a previous export with GUIDs
– You want maximum control

APPROACH:

1. INSERT ROOT FIRST (No Parent):
────────────────────────────────
[
{
“name”: “Contoso Corporation”,
“accountid”: “11111111-1111-1111-1111-111111111111”
}
]

2. INSERT LEVEL 1 (With Parent GUID):
────────────────────────────────
[
{
“name”: “North America Division”,
“accountid”: “22222222-2222-2222-2222-222222222222”,
“parentaccountid@odata.bind”: “/accounts(11111111-1111-1111-1111-111111111111)”
},
{
“name”: “Europe Division”,
“accountid”: “33333333-3333-3333-3333-333333333333”,
“parentaccountid@odata.bind”: “/accounts(11111111-1111-1111-1111-111111111111)”
}
]

3. INSERT LEVEL 2:
────────────────────────────────
[
{
“name”: “USA Region”,
“parentaccountid@odata.bind”: “/accounts(22222222-2222-2222-2222-222222222222)”
}
]

PROCESS:
– Select Environment and Table
– Paste Level 0 JSON → Ingest
– Paste Level 1 JSON → Ingest
– Paste Level 2 JSON → Ingest
– ✅ Done! Hierarchy created!

TAB: Python Gen → Generate Script

SCENARIO: Extract from CSV, clean, handle hierarchy, ingest

1. GENERATE PYTHON SCRIPT:
────────────────────────────────
– Navigate: 🐍📝 Python Gen
– Select: “📦 Bulk Import from CSV”
– Configure:
Client ID: your-client-id
Environment URL: https://yourorg.crm.dynamics.com
Table: account
– Click: “🐍 Generate Python Script”

2. CUSTOMIZE SCRIPT FOR HIERARCHY:
────────────────────────────────

GENERATED SCRIPT (Modified for Hierarchy):

import msal
import requests
import pandas as pd
import json

# Configuration
CLIENT_ID = “your-client-id”
TENANT_ID = “your-tenant-id”
ENV_URL = “https://yourorg.crm.dynamics.com”
TABLE_NAME = “account”

# Authentication (MSAL code here…)

# Read CSV
df = pd.read_csv(“organization_hierarchy.csv”)

# DATA CLEANING with pandas
df[‘name’] = df[‘name’].str.strip() # Remove whitespace
df[‘accountnumber’] = df[‘accountnumber’].str.upper() # Uppercase
df = df.dropna(subset=[‘name’]) # Remove null names
df = df.drop_duplicates(subset=[‘accountnumber’]) # Remove duplicates

# Sort by level (parents first!)
df = df.sort_values(‘hierarchy_level’)

# HIERARCHICAL INGESTION
guid_map = {} # Map account_number → GUID

for index, row in df.iterrows():
record = {
“name”: row[‘name’],
“accountnumber”: row[‘accountnumber’]
}

# Add parent lookup if exists
if pd.notna(row[‘parent_account_number’]):
parent_guid = guid_map.get(row[‘parent_account_number’])
if parent_guid:
record[“parentaccountid@odata.bind”] = f”/accounts({parent_guid})”

# Create record
response = requests.post(
f”{ENV_URL}/api/data/v9.2/accounts”,
headers=headers,
json=record
)

if response.ok:
# Extract GUID from response
created_id = response.headers[‘OData-EntityId’].split(‘(‘)[1].split(‘)’)[0]
guid_map[row[‘accountnumber’]] = created_id
print(f”✅ Created: {row[‘name’]} → {created_id}”)
else:
print(f”❌ Error: {row[‘name’]} → {response.text}”)

print(f”🎉 Hierarchy complete! Created {len(guid_map)} records”)

WHAT THIS DOES:
✅ Reads CSV with pandas
✅ Cleans data (strip, uppercase, dedupe)
✅ Sorts by hierarchy level
✅ Inserts parents first
✅ Tracks GUIDs in memory
✅ Links children to parents
✅ Handles any depth of hierarchy!

DATASET:
────────────────────────────────
File: customers_5million.csv
Size: 2.5 GB
Rows: 5,000,000
Columns: 25CHALLENGES:
❌ Can’t load 2.5GB into memory at once
❌ Can’t send 5M records in one API call
❌ Need to track progress
❌ Need to handle failures
❌ Need to resume if interrupted

TAB: Bulk Data → Bulk Ingestion (Manual or File Upload)

LIMITATIONS:
⚠️ This method works best for up to ~100,000 records
⚠️ For millions, use Python method below

PROCESS:

1. SPLIT FILE:
────────────────────────────────
Split 5M records into chunks of 10,000 each
= 500 files of 10,000 records

Tools: Python pandas, Excel Power Query, or:

import pandas as pd

# Read in chunks
chunk_size = 10000
for i, chunk in enumerate(pd.read_csv(‘customers_5M.csv’, chunksize=chunk_size)):
chunk.to_json(f’chunk_{i}.json’, orient=’records’)

2. UPLOAD CHUNKS SEQUENTIALLY:
────────────────────────────────
For each chunk file:
– Upload to Bulk Data tab
– Click “Ingest Data”
– Wait for completion
– Upload next chunk

Progress: Chunk 1/500… Chunk 2/500… etc.

PROS:
✅ No coding required
✅ Visual progress tracking
✅ Error handling built-in

CONS:
❌ Manual for each chunk (500 uploads!)
❌ Time-consuming for millions
❌ Better for smaller datasets

TAB: 🐍📝 Python Gen → Generate “Bulk Import from CSV”

WHY THIS IS BEST:
✅ Automatic batching (100 records per API call)
✅ Progress tracking built-in
✅ Memory efficient (streaming)
✅ Error recovery and retry logic
✅ Resume capability
✅ Fully automated – run overnight!

GENERATED SCRIPT:

import msal
import requests
import pandas as pd
import json
import time
from datetime import datetime

# Configuration
CLIENT_ID = “your-client-id”
TENANT_ID = “your-tenant-id”
CLIENT_SECRET = “your-secret” # For automation
ENV_URL = “https://yourorg.crm.dynamics.com”
TABLE_NAME = “contact”
BATCH_SIZE = 100 # Dataverse Web API limit

# Files
CSV_FILE = “customers_5million.csv”
CHECKPOINT_FILE = “ingestion_checkpoint.json”
ERROR_LOG = “ingestion_errors.json”

# Authentication
def get_access_token():
app = msal.ConfidentialClientApplication(
CLIENT_ID,
authority=f”https://login.microsoftonline.com/{TENANT_ID}”,
client_credential=CLIENT_SECRET
)
result = app.acquire_token_for_client(scopes=[f”{ENV_URL}/.default”])
return result[“access_token”]

# Load checkpoint (for resume capability)
def load_checkpoint():
try:
with open(CHECKPOINT_FILE, ‘r’) as f:
return json.load(f)
except:
return {“last_processed_row”: 0, “total_success”: 0, “total_errors”: 0}

# Save checkpoint
def save_checkpoint(checkpoint):
with open(CHECKPOINT_FILE, ‘w’) as f:
json.dump(checkpoint, f)

# Log errors
def log_error(row_num, error):
try:
with open(ERROR_LOG, ‘a’) as f:
f.write(json.dumps({“row”: row_num, “error”: str(error),
“timestamp”: datetime.now().isoformat()}) + ‘\n’)
except:
pass

# Main ingestion function
def ingest_large_dataset():
print(“🚀 Starting large volume ingestion…”)
print(f”📁 File: {CSV_FILE}”)

# Get token
token = get_access_token()
headers = {
“Authorization”: f”Bearer {token}”,
“Content-Type”: “application/json”,
“OData-MaxVersion”: “4.0”,
“OData-Version”: “4.0”
}

# Load checkpoint
checkpoint = load_checkpoint()
start_row = checkpoint[“last_processed_row”]

print(f”📊 Resuming from row {start_row}”)

# Read CSV in chunks (memory efficient!)
chunk_size = 10000 # Read 10K rows at a time
batch = []
batch_requests = []
row_num = 0

for chunk in pd.read_csv(CSV_FILE, chunksize=chunk_size):
# Skip already processed rows
if row_num + len(chunk) <= start_row:
row_num += len(chunk)
continue

# Clean data with pandas
chunk = chunk.fillna(”) # Handle nulls
chunk = chunk.drop_duplicates(subset=[’email’]) # Dedupe

for index, row in chunk.iterrows():
row_num += 1

# Skip if already processed
if row_num <= start_row: continue # Transform to Dataverse format record = { “firstname”: row[‘first_name’], “lastname”: row[‘last_name’], “emailaddress1”: row[’email’], “telephone1”: row[‘phone’], # … more fields } # Add to batch batch.append(record) # When batch is full, send it if len(batch) >= BATCH_SIZE:
success = send_batch(batch, headers, batch_requests, row_num)

if success:
checkpoint[“total_success”] += len(batch)
else:
checkpoint[“total_errors”] += len(batch)

# Update checkpoint
checkpoint[“last_processed_row”] = row_num
save_checkpoint(checkpoint)

# Progress report
print(f”✅ Processed: {row_num:,} / 5,000,000 ” +
f”({row_num/50000:.1f}%) | ” +
f”Success: {checkpoint[‘total_success’]:,} | ” +
f”Errors: {checkpoint[‘total_errors’]:,}”)

# Clear batch
batch = []
batch_requests = []

# Token refresh every 50K records
if row_num % 50000 == 0:
print(“🔄 Refreshing access token…”)
token = get_access_token()
headers[“Authorization”] = f”Bearer {token}”

# Brief pause to avoid throttling
time.sleep(0.1)

# Send remaining batch
if batch:
send_batch(batch, headers, batch_requests, row_num)
checkpoint[“total_success”] += len(batch)
checkpoint[“last_processed_row”] = row_num
save_checkpoint(checkpoint)

print(“\n🎉 INGESTION COMPLETE!”)
print(f”📊 Total Processed: {row_num:,}”)
print(f”✅ Successful: {checkpoint[‘total_success’]:,}”)
print(f”❌ Errors: {checkpoint[‘total_errors’]:,}”)
print(f”📄 Error log: {ERROR_LOG}”)

def send_batch(batch, headers, batch_requests, row_num):
“””Send batch using Batch API”””
try:
# Create batch request
batch_id = f”batch_{row_num}”
changeset_id = f”changeset_{row_num}”

# Build batch payload
boundary = f”batch_{batch_id}”
changeset_boundary = f”changeset_{changeset_id}”

# … (Batch API formatting code)

# Send batch
response = requests.post(
f”{ENV_URL}/api/data/v9.2/$batch”,
headers={“Content-Type”: f”multipart/mixed;boundary={boundary}”,
“Authorization”: headers[“Authorization”]},
data=batch_payload
)

if response.status_code == 200:
return True
else:
log_error(row_num, response.text)
return False

except Exception as e:
log_error(row_num, str(e))
return False

# RUN IT!
if __name__ == “__main__”:
ingest_large_dataset()

EXECUTION:

python ingest_5million.py

OUTPUT:

🚀 Starting large volume ingestion…
📁 File: customers_5million.csv
📊 Resuming from row 0
✅ Processed: 10,000 / 5,000,000 (0.2%) | Success: 10,000 | Errors: 0
✅ Processed: 20,000 / 5,000,000 (0.4%) | Success: 20,000 | Errors: 0
✅ Processed: 30,000 / 5,000,000 (0.6%) | Success: 30,000 | Errors: 0
🔄 Refreshing access token…
✅ Processed: 50,000 / 5,000,000 (1.0%) | Success: 50,000 | Errors: 0
…
✅ Processed: 5,000,000 / 5,000,000 (100%) | Success: 4,998,523 | Errors: 1,477
🎉 INGESTION COMPLETE!
📊 Total Processed: 5,000,000
✅ Successful: 4,998,523
❌ Errors: 1,477
📄 Error log: ingestion_errors.json

TIME: ~8-12 hours for 5 million records (overnight run)

TAB: 🐍 Python (for custom scripting)

ADVANCED ETL SCENARIO:
Extract from multiple sources → Clean/Transform → Load to Dataverse

import pandas as pd
import msal
import requests

# EXTRACT from multiple sources
df_customers = pd.read_csv(‘customers.csv’)
df_orders = pd.read_csv(‘orders.csv’)
df_products = pd.read_csv(‘products.csv’)

# TRANSFORM with pandas
# 1. Clean data
df_customers[’email’] = df_customers[’email’].str.lower().str.strip()
df_customers = df_customers.drop_duplicates(subset=[’email’])
df_customers = df_customers[df_customers[’email’].str.contains(‘@’)] # Valid emails

# 2. Enrich data
df_customers[‘full_name’] = df_customers[‘first_name’] + ‘ ‘ + df_customers[‘last_name’]
df_customers[‘customer_tier’] = df_customers[‘total_spend’].apply(
lambda x: ‘Gold’ if x > 10000 else ‘Silver’ if x > 5000 else ‘Bronze’
)

# 3. Join data
df_merged = df_customers.merge(
df_orders.groupby(‘customer_id’).agg({‘order_count’: ‘count’}),
left_on=’id’,
right_on=’customer_id’,
how=’left’
)

# 4. Handle nulls
df_merged = df_merged.fillna({‘order_count’: 0})

# 5. Data validation
df_merged = df_merged[df_merged[‘age’] >= 18] # Remove minors
df_merged = df_merged[df_merged[‘age’] <= 120] # Remove invalid ages

# LOAD to Dataverse
token = get_access_token() # MSAL auth

batch_size = 100
for i in range(0, len(df_merged), batch_size):
batch = df_merged[i:i+batch_size]

for _, row in batch.iterrows():
record = {
“firstname”: row[‘first_name’],
“lastname”: row[‘last_name’],
“emailaddress1”: row[’email’],
“crxxx_customertier”: row[‘customer_tier’],
“crxxx_ordercount”: int(row[‘order_count’])
}

# POST to Dataverse
response = requests.post(
f”{env_url}/api/data/v9.2/contacts”,
headers={“Authorization”: f”Bearer {token}”,
“Content-Type”: “application/json”},
json=record
)

if response.ok:
print(f”✅ Created: {row[‘full_name’]}”)
else:
print(f”❌ Error: {row[‘full_name’]} → {response.text}”)

PANDAS DATA CLEANING CAPABILITIES:

# Remove duplicates
df = df.drop_duplicates(subset=[’email’])

# Handle nulls
df = df.fillna({‘phone’: ‘N/A’, ‘age’: 0})
df = df.dropna(subset=[’email’]) # Drop rows with null email

# String cleaning
df[‘name’] = df[‘name’].str.strip().str.title()
df[’email’] = df[’email’].str.lower()
df[‘phone’] = df[‘phone’].str.replace(r’\D’, ”, regex=True) # Remove non-digits

# Data validation
df = df[df[‘age’] >= 18] # Filter
df = df[df[’email’].str.contains(‘@’)] # Valid emails only

# Type conversion
df[‘age’] = pd.to_numeric(df[‘age’], errors=’coerce’)
df[‘date’] = pd.to_datetime(df[‘date’], errors=’coerce’)

# Deduplication with priority
df = df.sort_values(‘last_modified’, ascending=False)
df = df.drop_duplicates(subset=[’email’], keep=’first’)

# Merging/Joining
df_enriched = df1.merge(df2, on=’customer_id’, how=’left’)

# Aggregation
df_summary = df.groupby(‘category’).agg({
‘revenue’: ‘sum’,
‘quantity’: ‘mean’,
‘orders’: ‘count’
})

# Conditional columns
df[‘tier’] = df[‘spend’].apply(lambda x: ‘A’ if x > 1000 else ‘B’)

SCENARIO: Import company org chart with 100,000 employees

DATA:
employees.csv (100,000 rows)
├── employee_id
├── name
├── email
├── manager_email ← Self-referencing!
└── department

TABS USED:
1️⃣ 🧹 Data Cleaning
2️⃣ Bulk Data → Wizard Mode
3️⃣ 📊 Visualizations (to verify)

PROCESS:

STEP 1: Clean Data (🧹 Data Cleaning Tab)
──────────────────────────────────────────
– Upload employees.csv
– Remove duplicates by email
– Validate email format
– Handle null manager emails (CEO has no manager)
– Export cleaned: employees_clean.csv

STEP 2: Level Detection (Python or Excel)
──────────────────────────────────────────
Add “hierarchy_level” column:
– If manager_email is null → level = 0 (CEO)
– If manager is level 0 → level = 1 (Execs)
– If manager is level 1 → level = 2 (VPs)
– etc.

Sort by level:
employees_level_0.csv (1 row – CEO)
employees_level_1.csv (10 rows – Execs)
employees_level_2.csv (100 rows – VPs)
employees_level_3.csv (1,000 rows – Directors)
employees_level_4.csv (10,000 rows – Managers)
employees_level_5.csv (88,889 rows – Staff)

STEP 3: Sequential Ingestion (Wizard Mode)
──────────────────────────────────────────
Group 1: Level 0
– Upload: employees_level_0.csv
– Target: contact
– Lookup: NONE

Group 2