Excel数据导入database时遇null值后无法复制全部数据求助

Got it, let's tackle this frustrating import issue—those missing rows and random nulls after that horizontal line are classic signs of Excel formatting quirks throwing off your import tool. I’ve seen this exact scenario with files that have section dividers (like that "--------" row) or inconsistent cell formatting. Here’s a robust Python-based solution that’ll clean your data and import everything properly:

Step 1: Install Required Libraries #

First, make sure you have these tools installed (run this in your terminal):

pip install pandas openpyxl sqlalchemy

• pandas: Handles Excel reading and data cleaning effortlessly
• openpyxl: Supports modern .xlsx files
• sqlalchemy: Creates a reliable connection to almost any database (MySQL, PostgreSQL, SQL Server, etc.)

Step 2: Full Import Code #

This script will skip problematic rows, clean up nulls, and push all valid data to your database. Replace the placeholder values with your actual file path and database details:

import pandas as pd
from sqlalchemy import create_engine

# ----------------------
# Configuration (Edit Me!)
# ----------------------
EXCEL_FILE_PATH = "your_excel_file.xlsx"
SHEET_NAME = "Sheet1"  # Replace with your sheet name
DATABASE_CONNECTION_STRING = "mysql+pymysql://username:password@host:port/database_name"
# For other databases:
# PostgreSQL: "postgresql://username:password@host:port/database_name"
# SQL Server: "mssql+pyodbc://username:password@dsn_name"
TABLE_NAME = "your_target_table"

# ----------------------
# Data Cleaning & Import
# ----------------------
def clean_excel_data(df):
    # Drop rows where all values are null (common after section dividers)
    df = df.dropna(how="all")
    
    # Remove rows that are just horizontal lines (like "--------")
    # We'll check if any row has a value matching this pattern
    df = df[~df.apply(lambda row: any(str(cell).strip() == "--------" for cell in row), axis=1)]
    
    # Optional: Fill minor nulls with default values if needed (adjust as per your data)
    # df = df.fillna({"column_name": "default_value"})
    
    return df

# Read the Excel file—we'll read all rows first to capture everything
df = pd.read_excel(EXCEL_FILE_PATH, sheet_name=SHEET_NAME, header=0)

# Clean the data to remove problematic rows
cleaned_df = clean_excel_data(df)

# Verify the cleaned data (uncomment to check in console)
# print(cleaned_df.head(20))
# print(f"Total rows after cleaning: {len(cleaned_df)}")

# Connect to database and import
engine = create_engine(DATABASE_CONNECTION_STRING)
cleaned_df.to_sql(TABLE_NAME, engine, if_exists="replace", index=False)

print(f"Successfully imported {len(cleaned_df)} rows to {TABLE_NAME}!")

Why This Works #

• Skips empty rows: The dropna(how="all") removes any completely blank rows that might be causing the import to stop early.
• Eliminates divider rows: The lambda check specifically targets rows with that "--------" line and removes them, so the import doesn’t hit a roadblock there.
• Flexible database support: SQLAlchemy works with all major databases—you just need to adjust the connection string to match your setup.
• Verifiable steps: You can uncomment the print statements to double-check that the cleaned data includes all the rows you expect before importing.

Quick Debug Tips #

1. If you’re still missing rows, check if your Excel has merged cells—run df.info() to see if any columns have unexpected data types, which might indicate merged cell issues.
2. For older .xls files, replace openpyxl with xlrd (install via pip install xlrd==1.2.0 since newer versions don’t support .xls).

内容的提问来源于stack exchange，提问作者User