Data Import Tool Documentation¶

Menu Location: Tools > Data Import

Access Level: Administrator

Last Updated: 2026-03-01

Overview¶

The Data Import Tool allows administrators to bulk import data from CSV or XLSX files into database tables. It provides a three-step wizard for file upload, column mapping, and data insertion with validation and error reporting.

Primary Functions:

Upload CSV or XLSX files for data import
Map file columns to database fields
Preview data before importing
Execute bulk inserts with error handling
Review import results and error details

Page Layout¶

Step 1: File Upload¶

Info Callout: Instructions for file selection
File Upload Field: Accepts .csv or .xlsx files
Upload Button: Proceeds to column mapping
Cancel Link: Returns to admin area

Step 2: Column Mapping¶

Info Callout: Explanation of mapping process
File Summary: Shows filename and row count
Mapping Table: Three columns (File Column, Sample Data, Database Field)
Continue Button: Proceeds to preview
Start Over Link: Returns to file upload

Step 3: Preview & Confirm¶

Warning Callout: Emphasizes action is irreversible
Import Summary: File details, row count, column count
Preview Table: First 10 rows of mapped data
Confirm Import Button: Executes the import
Navigation Links: Back to mapping or start over

Step 4: Results¶

Success/Error Callouts: Import completion status
Statistics: Successful and failed row counts
Error Details: Specific error messages for failed rows
Action Links: Import another file or return to admin

Import Workflow¶

Step 1: File Upload¶

Process:

Navigate to tool from table admin page
Click "Choose File" button
Select CSV or XLSX file
Click "Upload and Continue"
File is parsed and stored in session
Headers extracted, data rows counted
Redirects to Step 2

Supported Formats:

CSV: Comma-delimited text files
XLSX: Excel 2007+ spreadsheet files

File Requirements:

First row must contain column headers
Empty rows are automatically skipped
Maximum file size limited by PHP configuration
UTF-8 encoding recommended for special characters

Validation:

File extension must be .csv or .xlsx
File must not be empty
At least one data row required (beyond header)
Parseable format required

Step 2: Column Mapping¶

Process:

Review file columns and sample data
For each file column, select matching database field
Leave unmapped if column should be skipped
Auto-matching attempts to pair columns by name
Click "Continue to Preview"
Mapping stored in session
Redirects to Step 3

Mapping Table:

File Column: Column name from uploaded file
Sample Data: First row's value for this column (truncated to 100 chars)
Map to Database Column: Dropdown showing available database fields

Auto-Matching Logic:

Case-insensitive name comparison
Handles spaces converted to underscores
Example: "First Name" auto-maps to "first_name"
Manual override always available

Excluded Columns:

Auto-increment fields (typically "id")
These cannot be manually set via import
Shown in dropdown but skipped in interface

Skip Columns:

Select "-- Skip this column --" to ignore
Useful for file columns not needed in database
Reduces clutter and potential errors

Step 3: Preview & Confirm¶

Process:

Review import summary statistics
Examine first 10 rows of mapped data
Verify column mapping appears correct
Click "Confirm Import" if ready
Or click "Back to Mapping" to adjust
Confirmation prompt warns action is irreversible
Clicking OK executes import
Redirects to Step 4 with results

Preview Table:

Shows database field names as column headers
Includes source file column name as subtitle
First 10 rows displayed
"...and N more rows" note if file larger

Safety Features:

Explicit "cannot be undone" warning
JavaScript confirmation dialog
Preview before execution
Back button to adjust mapping

Step 4: Results & Error Reporting¶

Process:

System attempts INSERT for each row
Successful rows counted and reported
Failed rows counted with error messages
Results stored in session
Success/error callouts displayed
Error details shown in scrollable well
Links provided for next actions

Success Information:

Green callout with checkmark icon
"Successfully imported N rows" message
Count of successful insertions

Error Information:

Red callout with X icon
"N rows failed to import" message
Detailed error list with row numbers

Error Detail Format:

Row 5: Duplicate entry for key 'email'
Row 12: Cannot insert NULL into required field 'first_name'
Row 23: Invalid date format for field 'created_date'

Next Actions:

Import Another File: Returns to Step 1 for same table
Back to Admin: Returns to table admin page

Column Mapping¶

Mapping Options¶

Required vs Optional Fields:

Database determines which fields required
Import will fail if required field unmapped AND has no default
Optional fields can be left unmapped
NULL/empty values used for unmapped optional fields

Data Type Considerations:

Tool doesn't validate data types during mapping
Validation occurs during INSERT
Type mismatches cause row to fail with error message
Example: Text into integer field fails

Best Practices:

Map all required database fields
Verify data types align (dates, numbers, text)
Use sample data to confirm correct mapping
Map primary key only if intentionally setting IDs
Skip unnecessary file columns

Auto-Increment ID Handling¶

Default Behavior:

Auto-increment fields (usually "id") automatically excluded
Cannot be mapped in interface
Database assigns sequential IDs automatically

If You Need to Set IDs:

Contact system administrator
May require manual import via SQL
Risk of duplicate key errors if IDs already exist

Common Mapping Scenarios¶

Scenario 1: Perfect Column Name Match

File Column: first_name
Database Field: first_name
Auto-Match: Yes
Action: Verify and continue

Scenario 2: Similar Names

File Column: First Name
Database Field: first_name
Auto-Match: Yes (space to underscore)
Action: Verify and continue

Scenario 3: Different Names

File Column: Customer Name
Database Field: full_name
Auto-Match: No
Action: Manually select "full_name"

Scenario 4: Extra File Columns

File Column: Internal Notes
Database Field: (none needed)
Auto-Match: No
Action: Select "-- Skip this column --"

Scenario 5: Split Columns

File Columns: First Name, Last Name
Database Field: full_name
Action: Cannot combine - may need pre-processing

Common Use Cases¶

Use Case 1: Import Customer List from Excel¶

Goal: Bulk import new customer records from spreadsheet

Steps:

Prepare Excel file with customer data
Ensure first row has column headers
Save as .xlsx or export as .csv
Navigate to admin-import.php?table=custy
Upload file
Map columns (email, first_name, last_name, phone, etc.)
Preview first 10 records
Confirm and import
Review results for errors

Example: Import 500 customer records from marketing list. 495 succeed, 5 fail due to duplicate emails. Review errors, update source data, re-import 5 records.

Tips:

Validate email addresses before import
Check for duplicates in source file first
Include phone number formatting
Set account_type and other defaults in source

Use Case 2: Bulk Product Update¶

Goal: Update multiple product records from CSV

Steps:

Export current products to CSV
Edit prices, descriptions, availability in Excel
Save as CSV
Navigate to admin-import.php?table=menu
Upload edited CSV
Map id column (to update existing records)
Map fields to update
Preview changes
Confirm import

Warning:

Including ID column updates existing records
Without ID, creates new duplicate records
Test with small batch first

Use Case 3: Import Historical Order Data¶

Goal: Migrate order records from old system

Steps:

Export orders from legacy system
Transform data to match cust_order schema
Ensure customer IDs already exist
Format dates as YYYY-MM-DD or timestamps
Navigate to admin-import.php?table=cust_order
Upload transformed CSV
Map all required fields carefully
Preview extensively (check dates, amounts)
Confirm import
Verify order totals and customer links

Critical Fields:

customer (must match existing custy.id)
deliver_on (use Unix timestamp)
payment_total (numeric, no currency symbols)
status (use valid status code)

Tips:

Import in small batches (100-500 rows)
Verify foreign keys exist first
Check timestamp format matches system
Test with 10 records before full import

Use Case 4: Update Email Addresses¶

Goal: Correct email addresses for bounced emails

Steps:

Export bounce report with customer IDs and new emails
Create CSV with: id, email columns
Navigate to admin-import.php?table=custy
Upload CSV
Map id to id, email to email
Preview to verify ID matches
Confirm import
Check updated customer records

Warning:

ID column causes UPDATE not INSERT
Verify IDs are correct before import
Email validation only happens at database level

Use Case 5: Bulk Add Promotion Tags¶

Goal: Tag multiple customers for targeted campaign

Steps:

Identify customer IDs for campaign
Create CSV with customer_id, tag_id, created_date
Navigate to admin-import.php?table=promotions_tagging
Upload CSV
Map columns appropriately
Preview to verify customer/tag associations
Confirm import
Verify tags in customer records

Tips:

Ensure tag_id exists in PromotionsTags table
Use current timestamp for created_date
Check for existing tags to avoid duplicates

Troubleshooting¶

File Upload Fails¶

Symptoms: Error message after clicking "Upload and Continue"

Common Errors:

"Please upload a CSV or XLSX file": Wrong file extension
"The uploaded file is empty": File has no data rows
"No data found in file": Only header row, no data

Solutions:

Verify file extension is .csv or .xlsx
Open file to confirm data rows exist
Check file isn't corrupted
Ensure at least one row beyond header
Try exporting from source system again

Column Mapping Doesn't Save¶

Symptoms: Clicking "Continue to Preview" returns to mapping or shows error

Check:

Verify at least one column is mapped
Check if required fields are unmapped
Ensure mapping selections were saved

Solutions:

Map at least one column before continuing
Check that dropdown selections took effect
Reload page if browser cached old state

All Rows Fail During Import¶

Symptoms: Results page shows 0 successful, all rows failed

Common Causes:

Required field unmapped
Data type mismatches throughout file
Foreign key violations (referenced ID doesn't exist)
Unique constraint violations (duplicate values)

Solutions:

Review error messages for specific issues
Check database schema for required fields
Validate data types in source file
Verify foreign key IDs exist before import
Remove duplicate values from source

Some Rows Fail, Others Succeed¶

Symptoms: Partial import with error details

Common Issues:

Inconsistent data quality in source
Some rows violate constraints
Duplicate values in specific rows
Invalid format in some cells

Solutions:

Review error details for row numbers
Check those specific rows in source file
Fix data issues
Re-import failed rows only
Or fix all issues and re-import entire file

Import Succeeds But Data Looks Wrong¶

Symptoms: No errors but imported data doesn't match source

Check:

Verify column mapping was correct
Check if you mapped to wrong database fields
Confirm data transformations weren't needed
Review preview more carefully next time

Solutions:

May need to delete imported records
Fix mapping or source data
Re-import correctly
Use sample data preview more carefully

Table Admin (admin-edit.php?table=...) - View and manage imported records
Database Schema (/kiv/database_schema/) - Review table structure before import
Export Tool (export.php) - Export data before modification imports

Typical Workflow:

Table Admin → Notice bulk data needed → Import Tool → Upload and map
External system → Export data → Import Tool → Verify in Table Admin
Import Tool → Review errors → Fix source data → Re-import

Permissions & Access¶

Required Access Level: Administrator

Security Note:

Direct database insert access
Can corrupt data if used incorrectly
Should be restricted to trained staff
Always test with small sample first

Best Practices¶

Before Import¶

Review database schema to understand required fields
Validate source data for completeness and accuracy
Check for duplicates in source file
Test with 10-20 rows before full import
Backup database if updating existing records

During Import¶

Review sample data carefully during mapping
Double-check column mappings
Read preview table thoroughly
Understand which rows are being affected
Click through prompts deliberately, not quickly

After Import¶

Verify row counts match expectations
Review error details if any failures
Spot-check imported records in admin
Test functionality affected by import
Document what was imported and when

Things to Avoid¶

❌ Don't import without reviewing schema first
❌ Don't map columns blindly - verify each one
❌ Don't skip the preview step
❌ Don't import into production without testing first
❌ Don't import IDs unless intentionally updating records

Quick Reference Card¶

Task	Action/Location
Start import	Navigate to admin-import.php?table=[tablename]
Upload file	Choose file, click Upload and Continue
Skip file column	Select "-- Skip this column --"
Auto-map columns	Tool attempts automatically, verify each
Preview before import	Review Step 3 table carefully
Execute import	Click Confirm Import, confirm dialog
Review errors	Check Step 4 error details
Re-import failures	Fix source data, start over with failed rows
Import more data	Click "Import Another File"
Return to admin	Click "Back to Admin"

FAQs¶

Can I update existing records or only create new ones?¶

By default, the tool creates new records (INSERT). If you map the ID column, it MAY update existing records depending on database configuration, but this isn't guaranteed and can cause errors. For updates, use the table admin interface or contact system administrator.

What happens if I upload a file with 10,000 rows?¶

The import will attempt all 10,000 inserts. This may take a while and could timeout on very large files. For large imports, break into batches of 500-1000 rows.

Can I combine multiple file columns into one database field?¶

No, the current tool doesn't support data transformation or combining. You must pre-process your file to have one file column per database field.

What if my import fails halfway through?¶

Failed rows are logged but successfully inserted rows remain in database. You cannot automatically rollback. You'll need to manually remove successful rows or re-import all data after fixing issues.

Can I import dates and times?¶

Yes, but ensure your date format matches what the database expects. Unix timestamps work best. YYYY-MM-DD format typically works for date fields. Test with a small sample to verify.

Change Log¶

2026-03-01¶

Initial documentation created
All sections completed based on admin-import.php code review
Documented three-step wizard flow
Added column mapping details
Included error handling and troubleshooting
Noted CSV and XLSX support via PhpSpreadsheet

End of Documentation

For additional help, contact your system administrator or Kiva Logic support.

Data Import Tool Documentation¶

Overview¶

Page Layout¶

Step 1: File Upload¶

Step 2: Column Mapping¶

Step 3: Preview & Confirm¶

Step 4: Results¶

Import Workflow¶

Step 1: File Upload¶

Step 2: Column Mapping¶

Step 3: Preview & Confirm¶

Step 4: Results & Error Reporting¶

Column Mapping¶

Mapping Options¶

Auto-Increment ID Handling¶

Common Mapping Scenarios¶

Common Use Cases¶

Use Case 1: Import Customer List from Excel¶

Use Case 2: Bulk Product Update¶

Use Case 3: Import Historical Order Data¶

Use Case 4: Update Email Addresses¶

Use Case 5: Bulk Add Promotion Tags¶

Troubleshooting¶

File Upload Fails¶

Column Mapping Doesn't Save¶

All Rows Fail During Import¶

Some Rows Fail, Others Succeed¶

Import Succeeds But Data Looks Wrong¶

Related Pages¶

Permissions & Access¶

Best Practices¶

Before Import¶

During Import¶

After Import¶

Things to Avoid¶

Quick Reference Card¶

FAQs¶

Can I update existing records or only create new ones?¶

What happens if I upload a file with 10,000 rows?¶

Can I combine multiple file columns into one database field?¶

What if my import fails halfway through?¶

Can I import dates and times?¶

Change Log¶

2026-03-01¶