能否使用BCP工具批量导入多份结构匹配的Excel文件至SQL Server指定表？

Great question—yes, you absolutely can use BCP to batch import multiple files matching your Buchungen table structure! BCP itself only handles single files at a time, but you can wrap it in a loop (either PowerShell or batch script) to iterate through all your files. Let’s break down what went wrong with your PowerShell code, fix it, and also offer a batch alternative since you’re already using Task Scheduler with .bat files.

What’s Wrong With Your Original PowerShell Code? #

Your script has a few syntax and logic issues that caused it to fail:

• Malformed string for $source: You used unescaped double quotes inside double quotes, which breaks the variable assignment.
• Duplicate Get-ChildItem call: You tried to re-run Get-ChildItem inside the ForEach block instead of referencing the current file in the loop.
• Incomplete BCP parameters: You omitted the delimiter value after -t, and your parameter order was mixed up.

Fixed PowerShell Script #

Here’s a corrected version that will properly iterate through your files and run BCP for each one:

# Replace with your actual folder path (use single quotes to avoid escape issues)
$source = 'C:\Users\Desktop\01 Dell Latitude 5400\01 Main Folder\00 Excel to SQL\Demo\'
# Replace with your actual SQL Server name
$serverName = "YourSQLServerName"
$targetTable = "TMKPIReporting.dbo.Buchungen"

# Recursively get all CSV files (change to *.txt if your files are text format)
Get-ChildItem $source -Recurse -Filter *.csv | ForEach-Object {
    $filePath = $_.FullName
    Write-Host "Starting import for: $filePath"

    # Build and execute the BCP command
    $bcpCommand = "bcp $targetTable in `"$filePath`" -S$serverName -T -c -F2 -t`"|`""
    Invoke-Expression $bcpCommand

    # Check if import succeeded (BCP returns 0 on success)
    if ($LASTEXITCODE -eq 0) {
        Write-Host "✅ Successfully imported $filePath`n"
    } else {
        Write-Host "❌ Failed to import $filePath. Exit code: $LASTEXITCODE`n"
    }
}

Key Notes for the PowerShell Script: #

• $_.FullName: Gets the full path of each file, which handles spaces in folder/file names correctly.
• -t"|": Matches the delimiter you used in your working single-file BCP command.
• -F2: Skips the first row (header) just like your original command.
• -T: Uses Windows integrated authentication. If you need SQL authentication, replace -T with -U YourUsername -P YourPassword.
• Error checking: $LASTEXITCODE lets you verify if each BCP run succeeded, which is critical for debugging.

Alternative Batch Script (For Task Scheduler) #

Since you’re already using Task Scheduler with .bat files, here’s a batch script that achieves the same result:

@echo off
set "source=C:\Users\Desktop\01 Dell Latitude 5400\01 Main Folder\00 Excel to SQL\Demo\"
set "serverName=YourSQLServerName"
set "targetTable=TMKPIReporting.dbo.Buchungen"

REM Recursively loop through all CSV files (change to *.txt if needed)
for /r "%source%" %%f in (*.csv) do (
    echo Starting import for: %%f
    bcp %targetTable% in "%%f" -S%serverName% -T -c -F2 -t"|"
    
    REM Check if import succeeded
    if errorlevel 1 (
        echo ❌ Failed to import %%f
    ) else (
        echo ✅ Successfully imported %%f
    )
    echo.
)
REM Optional: Keep the window open to see results (remove if running via Task Scheduler)
pause

Key Notes for the Batch Script: #

• for /r: Recursively scans all subfolders under your source directory.
• %%f: Represents the current file in the loop; wrapping it in quotes handles spaces in paths.
• errorlevel 1: Checks if BCP returned a non-zero exit code (indicating failure).

Critical Pre-Requisites for Success #

1. File Format: Ensure your Excel files are saved as CSV/TXT with the correct delimiter (| in your case) and no extra formatting. If using UTF-8, save without BOM—BCP can struggle with UTF-8 BOMs.
2. Permissions: The account running the script (via Task Scheduler) needs:
   • Read access to the source file folder.
   • Write access to the Buchungen SQL table.
3. Server Access: The machine running the script must have network access to your SQL Server, and the account must be authorized to connect via -T (or use SQL credentials if needed).

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