Skip to main content

Excel Import Basics

Transform spreadsheet chaos into structured database organization by importing Excel files directly into your database.

VIDEO TUTORIAL⏱️ 3 mins

📹 Excel to Database Migration

Learn how to import Excel spreadsheets into a structured database and apply intelligent data analysis.

Overview​

The Excel import feature enables organizations to migrate from unstructured spreadsheet data to organized database structures. This transformation allows you to apply advanced intelligence capabilities, maintain data integrity, and create a proper data catalog.

Accessing the Import Feature​

To import data:

  1. Navigate to the Data Sources page
  2. Click the Import button (with upload icon) in the top-right corner
  3. The button is disabled if no database connections are available

Supported File Formats​

Currently supported formats:

  • Microsoft Excel (.xlsx, .xls) - ✅ Available
  • CSV Files (.csv) - Coming Soon
  • XML Files (.xml) - Coming Soon

Import Process​

The import process consists of three simple steps:

Step 1: Select File Type​

  1. Click the Import button from the Data Sources page
  2. Choose Microsoft Excel from the file type options
  3. Click Next to proceed

Step 2: Upload Your File​

  1. Click to upload or drag and drop your Excel file
  2. The system displays when file is selected:
    • File name
    • File size in MB
    • Green checkmark icon indicating successful selection
  3. Click Next to continue

Step 3: Configure Import​

Configure your import settings:

Database Name​

  • Enter a name for the new database (e.g., "real_estate_crm_tracker")
  • Automatically cleaned to be SQL-safe:
    • Converts to lowercase
    • Replaces spaces and special characters with underscores
    • Ensures it doesn't start with a number
    • Removes multiple underscores and leading/trailing underscores
    • Limited to 63 characters (PostgreSQL limit)
  • If left empty, auto-generates from the uploaded filename

Connection Selection​

  • Choose an existing database connection where data will be imported
  • Use the search box to filter connections
  • Shows connection cards with database type and details

Step 4: Import Execution​

After clicking Import Data:

  1. The import dialog closes
  2. An Agent Progress Dialog opens showing:
    • Title: "Importing Data"
    • Description: "Processing your Excel file and importing data into the database"
    • Real-time progress updates
    • Enhanced mode with logs
  3. The system performs:
    • Schema analysis of Excel structure
    • Relationship detection between sheets
    • Table creation for each Excel sheet
    • Data migration to the database
    • Catalog generation
  4. Upon completion, automatically redirects to the new data source page

Post-Import Processing​

After the import completes and you're redirected to the new data source, Autonify agents automatically:

1. Data Scanning​

  • Scans all imported tables
  • Builds complete table and column metadata
  • Creates searchable data catalog

2. Sensitivity Classification​

  • Identifies sensitive data (PII, PHI, financial)
  • Tags columns with appropriate sensitivity levels
  • Helps with compliance and security

3. Auto-Documentation​

  • Generates descriptions for tables and columns
  • Documents data types and relationships
  • Creates business-friendly documentation

4. Data Quality Analysis​

  • Assesses data integrity
  • Checks completeness
  • Validates formats and patterns
  • Identifies potential quality issues

Example Use Case: Real Estate Data​

The video demonstrates importing real estate data containing:

  • Transactions: Sales and purchase records
  • Agents: Real estate agent information
  • Viewings: Property viewing schedules
  • Clients: Customer information
  • Properties: Available property listings

Each Excel sheet becomes a separate database table, maintaining the relationships and structure while enabling advanced database capabilities.

Viewing Imported Data​

After import, you can:

Database View​

  • Access the PostgreSQL database directly
  • View all created tables
  • Query data using SQL

Data Catalog​

  • Browse imported tables in the catalog
  • View column details and data types
  • See automatically generated documentation

Agent Results​

  • Review sensitivity classifications
  • Check data quality scores
  • View auto-generated documentation

Import Settings​

The import uses these default settings:

  • Import Mode: Append (adds data to existing tables if they exist)
  • Batch Size: 1000 records at a time
  • Create Tables: Automatically creates tables if they don't exist

Best Practices​

Before Import​

  1. Clean Your Excel File:

    • Remove empty sheets
    • Ensure consistent column headers
    • Remove special formatting that might interfere

  2. Organize Data:

    • Each sheet should represent one entity type
    • Use consistent naming conventions
    • Ensure key columns are present for relationships

  3. Organize Large Datasets:

    • Consider splitting very large datasets if necessary for better performance

Naming Conventions​

  • Use descriptive database names
  • Avoid special characters in sheet names
  • Keep names concise but meaningful

Data Preparation​

  • Ensure date formats are consistent
  • Remove calculated fields (recreate as views later)
  • Standardize text fields (consistent capitalization)

Troubleshooting​

Common Issues​

IssueCauseSolution
Import failsFile processing issueCheck file format and ensure it's a valid Excel file
Tables not createdInvalid sheet namesRemove special characters from sheet names
Data missingHidden sheetsUnhide all sheets before import
Incorrect data typesMixed data in columnsEnsure consistent data types per column

Import Validation​

After import, verify:

  1. Row counts match source Excel file
  2. All sheets were imported as tables
  3. Data types are correctly assigned
  4. Relationships are preserved

Limitations​

Current limitations to be aware of:

  • Excel formulas are not imported (only values)
  • Formatting and styles are not preserved
  • Pivot tables are not imported
  • Only .xlsx and .xls formats supported

Next Steps​

After successful import:

  1. Review the Catalog: Browse imported tables and columns
  2. Check Data Quality: Review quality scores and issues
  3. Validate Sensitivity: Confirm sensitivity classifications
  4. Set Up Monitoring: Configure data quality rules
  5. Create Views: Build views for reporting needs

Related Topics​