分号分隔文件导入SQL Server遇列偏移问题求助

Hey there, I feel your pain—dealing with messy flat files from rigid legacy systems is always a headache. Since you can't get the source system to fix the file, and you want to avoid writing complex Script Tasks, here are a few no-fuss methods to clean up those extra semicolons before loading into SQL Server:

Method 1: PowerShell Script (Windows Built-In) #

PowerShell comes pre-installed on Windows, so this is a quick, code-light solution. This script will fix each line by keeping only the first and last semicolon (since your valid rows should have exactly 2), replacing any extra semicolons in the middle (your Address field area) with spaces (you can adjust this to remove them entirely if needed).

1. Open Notepad and paste this script, updating the file paths to match your source and output locations:

# Define your source and output file paths
$sourcePath = "C:\Your\Source\File.csv"
$outputPath = "C:\Your\Processed\File_Cleaned.csv"

# Read all lines from the source file
$allLines = Get-Content -Path $sourcePath

# Process each line to clean extra semicolons
$cleanedLines = foreach ($line in $allLines) {
    # Keep the header as-is (assuming it's correctly formatted)
    if ($line -match "^ID;DESCRIPTION;VALUE$") {
        $line
        continue
    }
    
    # Split the line into parts using semicolons
    $lineParts = $line -split ';'
    
    # Extract ID (first part) and VALUE (last part)
    $id = $lineParts[0]
    $value = $lineParts[-1]
    
    # Combine all middle parts, replacing semicolons with spaces
    $cleanDescription = $lineParts[1..($lineParts.Count - 2)] -join ' '
    
    # Reconstruct the clean line
    "$id;$cleanDescription;$value"
}

# Save the cleaned lines to a new file
$cleanedLines | Set-Content -Path $outputPath -Encoding UTF8

2. Save the file as CleanFile.ps1
3. Right-click the script and select "Run with PowerShell" (or run it from a PowerShell window)

Method 2: Excel GUI (No Code Required) #

If you prefer a point-and-click approach, Excel can handle this easily:

• Open Excel, go to the Data tab > From Text/CSV
• Select your messy file, choose "Semicolon" as the delimiter, then click Load (you'll see columns are offset—this is expected)
• Identify your ID column (first column), your VALUE column (last column), and all the middle columns that make up your Address field
• Insert a new empty column next to your Address columns. Use the TEXTJOIN function to merge the middle columns, replacing semicolons with spaces:

  =TEXTJOIN(" ", TRUE, B2:D2)
  

  (Adjust B2:D2 to match the range of your Address columns)
• Drag the formula down to apply it to all rows
• Now copy the ID column, your merged Address column, and the VALUE column
• Paste them into a new Excel sheet, then save this new sheet as a CSV (Comma delimited) file—you can then change the extension back to .csv and use semicolons as the delimiter when importing to SQL Server

Method 3: Command Line with sed (Git Bash/WSL) #

If you have Git Bash installed (or use Windows Subsystem for Linux), you can use the sed command to clean the file in one line:

sed -E 's/^([^;]+);(.*);([^;]+)$/\1;\2;\3/; s/;/ /g2' your_source_file.csv > cleaned_file.csv

This command captures the ID, middle content, and VALUE, then replaces every semicolon starting from the second one with a space.

Important Notes #

• Always test these methods on a small sample of your file first to make sure the cleaned data retains all necessary information
• If you don't want spaces replacing the semicolons, change the -join ' ' in PowerShell to -join '', or adjust the sed command's space to an empty string

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