如何自动将Excel文件数据推送至SQL Server并实现自动同步？

Alright, let's tackle your Excel-to-SQL Server auto-sync problem—since you already have the pull direction sorted, we just need to flip the script and add that automation you're craving. Here are the most practical approaches, tailored to different skill levels and setups:

1. VBA + Windows Task Scheduler (stay in Excel, minimal extra tools) #

If you want to keep things rooted in Excel, write a VBA macro to push data, then use Windows Task Scheduler to run it automatically.

First, the VBA macro (paste this into your Excel file's ThisWorkbook module or a standard module):

Sub AutoPushToSQL()
    Dim conn As Object
    Dim sqlInsert As String
    Dim dataSheet As Worksheet
    Dim lastRow As Long
    Dim rowNum As Long
    
    ' Set up your worksheet and connection
    Set dataSheet = ThisWorkbook.Worksheets("YourDataTab") ' Replace with your sheet name
    Set conn = CreateObject("ADODB.Connection")
    
    ' SQL Server connection string (adjust to your instance/database/auth)
    conn.Open "Provider=SQLOLEDB;Data Source=YourSQLInstanceName;" & _
              "Initial Catalog=YourDatabase;User ID=YourSQLUser;Password=YourSQLPass;"
    
    ' Find the last row with data (assuming column A has your unique ID/key)
    lastRow = dataSheet.Cells(dataSheet.Rows.Count, "A").End(xlUp).Row
    
    ' Loop through rows (skip header row 1)
    For rowNum = 2 To lastRow
        ' Build insert query (escape single quotes with double quotes to avoid errors)
        sqlInsert = "INSERT INTO YourSQLTable (Col1, Col2, Col3) " & _
                    "VALUES ('" & Replace(dataSheet.Cells(rowNum, 1).Value, "'", "''") & "', " & _
                    dataSheet.Cells(rowNum, 2).Value & ", '" & Replace(dataSheet.Cells(rowNum, 3).Value, "'", "''") & "')"
        
        ' Run the query
        conn.Execute sqlInsert
    Next rowNum
    
    ' Clean up
    conn.Close
    Set conn = Nothing
    Set dataSheet = Nothing
End Sub

Then set up automation:

• Enable macros in your Excel file (save it as .xlsm)
• Add a Workbook_Open event to run the macro automatically when the file opens:

  Private Sub Workbook_Open()
      AutoPushToSQL
      ' Optional: Close Excel after sync
      ThisWorkbook.Close SaveChanges:=False
  End Sub
  

• Open Windows Task Scheduler → Create Task
  • Triggers: Set a schedule (e.g., daily at 8 AM, or when the file is modified)
  • Actions: Start a program → Path to excel.exe, add arguments: /e "C:\Full\Path\To\Your\File.xlsm"
  • Make sure the task runs under an account with access to both the Excel file and SQL Server.

2. Power Automate (no-code/low-code, cloud-based) #

Perfect if you don't want to mess with VBA or server settings. Works best if your Excel file is stored in OneDrive/SharePoint.

Steps to build the flow:

1. Create a new Cloud Flow in Power Automate
2. Choose a trigger:
   • Use "When a file is modified" if you want to sync immediately after Excel changes
   • Use "Recurrence" if you want scheduled syncs (e.g., hourly/daily)
3. Add an action: List rows present in a table (point it to your Excel table)
4. Add an Apply to each loop to iterate over every row
5. Inside the loop, add Insert row (V2) for SQL Server—map your Excel columns to SQL table fields
6. Optional: Add a "Send email" action to notify you when sync completes

Pro tip: Add a condition in the loop to skip rows that already exist in SQL (e.g., check if the Excel row's ID is not in the SQL table) to avoid duplicates.

3. SQL Server Agent + OpenRowset (server-side automation) #

If you want the sync to run entirely on the SQL Server side (no Excel client needed), use OPENROWSET to read the Excel file, then schedule the query with SQL Server Agent.

First, enable ad-hoc distributed queries on your SQL Server:

sp_configure 'show advanced options', 1;
RECONFIGURE;
sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;

Then write the sync query (adjust paths/columns to match your setup):

INSERT INTO YourSQLTable (Col1, Col2, Col3)
SELECT Col1, Col2, Col3
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
                'Excel 12.0 Xml;HDR=YES;Database=C:\SharedFolder\YourExcelFile.xlsx',
                'SELECT * FROM [YourDataSheet$]')
WHERE Col1 NOT IN (SELECT Col1 FROM YourSQLTable) -- Skip existing rows

Schedule it with SQL Server Agent:

1. Open SQL Server Management Studio → Expand SQL Server Agent → Create new Job
2. Add a Step → Type: Transact-SQL (T-SQL) → Paste the query above
3. Add a Schedule (e.g., daily at 9 AM)
4. Ensure the SQL Server Agent service account has read access to the Excel file's location (use a shared folder if the file is on another machine)

Note: You'll need the Microsoft ACE OLEDB 12.0 driver installed on the SQL Server.

4. SSIS (for complex workflows) #

If you need advanced logic (data validation, cleansing, incremental syncs with timestamps), use SQL Server Integration Services (SSIS).

1. Create a new SSIS package in Visual Studio
2. Add an Excel Source component to read your Excel data
3. Add a SQL Server Destination component to write to your table
4. Add transformations (e.g., "Lookup" to check for existing rows, "Derived Column" to clean data)
5. Deploy the package to SSIS Catalog or file system
6. Schedule it with SQL Server Agent or SSIS Catalog schedules

Key Tips for All Approaches #

• Avoid duplicates: Always add logic to skip rows that already exist in SQL (use unique keys, timestamps, or last-modified dates)
• Permissions: Ensure the account running the sync has read/write access to the Excel file and INSERT permissions on the SQL table
• Data type matching: Double-check that Excel cell types (e.g., numbers, dates) match SQL Server column types to avoid conversion errors

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