如何将每日更新的TXT文件数据导入SQL Server表

一、核心思路 #

先筛选TXT中符合格式的目标行，提取对应字段后映射到SQL Server目标表，再通过定时任务每小时执行导入逻辑，同时避免重复导入。

二、步骤详解 #

1. 目标行识别规则 #

源TXT中仅包含][Success][的行是需要导入的（格式为时间 [操作类型][状态][账号][角色名] - (参数...)），其他如START MIX、Slot开头的行直接过滤。

用正则表达式精准匹配这些行：

^(\d{2}:\d{2}:\d{2}) \[([^\]]+)\]\[([^\]]+)\]\[([^\]]+)\]\[([^\]]+)\] - \(.*ChaosSuccessRate: (\d+).*ChaosMoney: (\d+)\)

各分组对应目标表字段：

• 分组1 → Time
• 分组2 → Type
• 分组3 → State
• 分组4 → AccountID
• 分组5 → User
• 分组6 → Rate
• 分组7 → ChaosMoney

2. 实现方案（两种常用方式） #

方案一：SSIS（适配SQL Server运维场景）

1. 创建SSIS包

   • 新建Integration Services项目，添加「数据流任务」。
   • 平面文件源：配置文件路径为动态变量（比如@[User::FilePath]，表达式设置为"C:\\Logs\\" + (DT_WSTR, 10)GETDATE(), "yyyy-MM-dd") + ".txt"），确保每日自动读取对应日期的文件。
   • 脚本组件：选择「转换」类型，输入列选整行内容。在脚本编辑器中用C#编写匹配逻辑，筛选目标行并提取字段：

     using System.Text.RegularExpressions;
     public override void Input0_ProcessInputRow(Input0Buffer Row)
     {
         string line = Row.Column0;
         Regex regex = new Regex(@"^(\d{2}:\d{2}:\d{2}) \[([^\]]+)\]\[([^\]]+)\]\[([^\]]+)\]\[([^\]]+)\] - \(.*ChaosSuccessRate: (\d+).*ChaosMoney: (\d+)\)");
         Match match = regex.Match(line);
         if (match.Success)
         {
             Row.Time = TimeSpan.Parse(match.Groups[1].Value);
             Row.Type = match.Groups[2].Value;
             Row.State = match.Groups[3].Value;
             Row.AccountID = match.Groups[4].Value;
             Row.User = match.Groups[5].Value;
             Row.Rate = int.Parse(match.Groups[6].Value);
             Row.ChaosMoney = long.Parse(match.Groups[7].Value);
             Row.IsValidRow = true;
         }
         else
         {
             Row.IsValidRow = false;
         }
     }
     

   • OLE DB目标：连接目标SQL Server数据库，将脚本组件输出的字段映射到目标表列。

2. 定时与去重

   • 在SQL Server代理中创建作业，每小时执行该SSIS包。
   • 给目标表加唯一约束避免重复导入：

     ALTER TABLE MixRecords ADD CONSTRAINT UC_MixRecord UNIQUE (Time, AccountID, User, Type);
     

     或每次导入后将源文件移动到存档目录重命名。

方案二：PowerShell脚本（适配自动化脚本场景）

1. 提取导入脚本

   # 配置参数
   $logPath = "C:\Logs\"
   $today = Get-Date -Format "yyyy-MM-dd"
   $filePath = Join-Path $logPath "$today.txt"
   $connectionString = "Server=YourSQLServer;Database=YourDB;Integrated Security=True;"
   $tableName = "MixRecords"
   
   # 正则匹配目标行
   $regex = [regex]'^(\d{2}:\d{2}:\d{2}) \[([^\]]+)\]\[([^\]]+)\]\[([^\]]+)\]\[([^\]]+)\] - \(.*ChaosSuccessRate: (\d+).*ChaosMoney: (\d+)\)'
   
   # 读取文件并提取数据
   $data = Get-Content $filePath | ForEach-Object {
       $match = $regex.Match($_)
       if ($match.Success) {
           [PSCustomObject]@{
               Time = [TimeSpan]$match.Groups[1].Value
               Type = $match.Groups[2].Value
               State = $match.Groups[3].Value
               AccountID = $match.Groups[4].Value
               User = $match.Groups[5].Value
               Rate = [int]$match.Groups[6].Value
               ChaosMoney = [long]$match.Groups[7].Value
           }
       }
   }
   
   # 批量导入SQL Server
   if ($data) {
       $dt = New-Object System.Data.DataTable
       $null = $dt.Columns.Add("Time", [System.TimeSpan])
       $null = $dt.Columns.Add("Type", [string])
       $null = $dt.Columns.Add("State", [string])
       $null = $dt.Columns.Add("AccountID", [string])
       $null = $dt.Columns.Add("User", [string])
       $null = $dt.Columns.Add("Rate", [int])
       $null = $dt.Columns.Add("ChaosMoney", [long])
   
       $data | ForEach-Object {
           $row = $dt.NewRow()
           $row.Time = $_.Time
           $row.Type = $_.Type
           $row.State = $_.State
           $row.AccountID = $_.AccountID
           $row.User = $_.User
           $row.Rate = $_.Rate
           $row.ChaosMoney = $_.ChaosMoney
           $dt.Rows.Add($row)
       }
   
       $bulkCopy = New-Object System.Data.SqlClient.SqlBulkCopy($connectionString)
       $bulkCopy.DestinationTableName = $tableName
       $bulkCopy.WriteToServer($dt)
   }
   

2. 定时与去重

   • 在Windows任务计划中创建任务，每小时执行该脚本。
   • 同样给目标表加唯一约束，或脚本中先查询已存在记录，仅导入新数据；也可在脚本执行后将源文件复制到存档文件夹标记为已处理。

3. 目标表建表语句 #

先在SQL Server中创建对应结构的表：

CREATE TABLE MixRecords (
    Time TIME(0) NOT NULL,
    Type NVARCHAR(100) NOT NULL,
    State NVARCHAR(50) NOT NULL,
    AccountID NVARCHAR(50) NOT NULL,
    [User] NVARCHAR(50) NOT NULL, -- User为关键字，加方括号
    Rate INT NOT NULL,
    ChaosMoney BIGINT NOT NULL,
    CONSTRAINT UC_MixRecord UNIQUE (Time, AccountID, [User], Type)
);

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