如何在Excel/PowerQuery中优雅导入格式不一致的Fortinet防火墙键值对日志文件

兄弟，我太懂你这种被Fortinet奇葩日志格式折腾的痛苦了！明明是导出的CSV，却偏偏键值对的顺序、数量都不固定，默认用PowerQuery导入直接列就乱了，还要折腾VBA或者Python？完全没必要，咱们在PowerQuery里就能优雅搞定，给你捋个清晰的解决思路：

核心思路 #

不管日志每行的键值对怎么变，咱们先把每行整串的文本拆成键值对列表，再转换成PowerQuery的「记录」类型（类似字典），最后统一扩展成表格——这样所有出现过的键都会成为列，缺失值自动留空，完美适配格式不一致的问题。

具体操作步骤 #

1. 导入原始日志到PowerQuery

   • 打开Excel，点击「数据」>「自文本/CSV」，选中你的防火墙日志文件
   • 在导入预览界面，选择「加载到」>「仅创建连接」，然后在「数据连接」面板里右键点击这个连接，选择「编辑」进入PowerQuery编辑器

2. 清理文本并拆分键值对
   此时你会看到每行是一整串带引号的键值对，先添加自定义列来清理格式：

   #"Added Custom" = Table.AddColumn(Source, "CleanedKV", each 
       let
           // 把日志里的双引号（""）替换成单引号，再去掉首尾的外层引号
           CleanText = Text.Trim(Text.Replace([Column1], """", "'"), "'"),
           // 按','拆分出每个单独的键值对（比如itime=1682240966）
           KVList = Text.Split(CleanText, "','")
       in
           KVList
   )
   

3. 把键值对列表转换成「记录」
   记录是PowerQuery里的键值对结构，不管顺序如何都能精准保存：

   #"Converted to Records" = Table.AddColumn(#"Added Custom", "LogRecord", each 
       Record.FromList(
           // 提取每个键值对的值部分
           List.Transform([CleanedKV], each Text.AfterDelimiter(_, "=")),
           // 提取每个键值对的键部分
           List.Transform([CleanedKV], each Text.BeforeDelimiter(_, "="))
       )
   )
   

4. 扩展记录为统一表格
   这一步会自动收集所有出现过的键作为列，不管每行有没有这个键：

   // 先收集所有可能的键名
   AllKeys = List.Distinct(List.Combine(Table.Column(#"Converted to Records", "LogRecord"))),
   // 扩展记录列
   #"Expanded Records" = Table.ExpandRecordColumn(#"Converted to Records", "LogRecord", AllKeys, AllKeys),
   // 移除不需要的中间列
   #"Cleaned Table" = Table.RemoveColumns(#"Expanded Records", {"Column1", "CleanedKV"})
   

5. 可选：优化时间字段
   可以把itime时间戳转换成可读的日期时间：

   #"Added DateTime" = Table.AddColumn(#"Cleaned Table", "LogDateTime", each DateTime.FromUnixTimestamp(Number.From([itime])))
   

为什么这个方法管用？ #

• 完全不依赖固定的列顺序或数量，不管Fortinet导出的日志怎么变，都能自动适配
• 全程在PowerQuery内完成，不需要跳出Excel环境，操作简洁
• 最终生成的表格结构统一，方便后续的筛选、分析

备注：内容来源于stack exchange，提问作者PerryW