我已经为我的部门制作了一份最佳实践表,它在我自己的计算机上完美运行(en-US 区域设置(,但是当我将其发送给同事时,一切都搞砸了(da-DK 区域设置(。
一半的部门使用en-US,另一半使用da-DK。并不是每个人都可以使用相同的选项,因此我想创建一个可以自动处理两者的工作表。
在 en-US 中创建的 csv 文件使用","分隔符和"."表示十进制,而 da-DK 设置使用 ";" 作为分隔符,","作为小数。
我该如何最好地解决这个问题?
我有以下两个相同的数据查询
数据1.csv:
Panel/Node/Case, MXX (kNm/m), MYY (kNm/m)
1/ 1/ 1, 145.46, 145.46
1/ 1/ 2, 98.83, 98.83
1/ 1/ 3 (C), 244.30, 244.30
1/ 2/ 1, 19.80, 19.80
1/ 2/ 2, 13.46, 13.46
1/ 2/ 3 (C), 33.26, 33.26
1/ 3/ 1, 19.80, 19.80
1/ 3/ 2, 13.46, 13.46
1/ 3/ 3 (C), 33.26, 33.26
1/ 4/ 1, 145.46, 145.46
1/ 4/ 2, 98.83, 98.83
1/ 4/ 3 (C), 244.30, 244.30
数据2.csv:
Panel/Node/Case; MXX (kNm/m); MYY (kNm/m)
1/ 1/ 1; 145,46; 145,46
1/ 1/ 2; 98,83; 98,83
1/ 1/ 3 (C); 244,30; 244,30
1/ 2/ 1; 19,80; 19,80
1/ 2/ 2; 13,46; 13,46
1/ 2/ 3 (C); 33,26; 33,26
1/ 3/ 1; 19,80; 19,80
1/ 3/ 2; 13,46; 13,46
1/ 3/ 3 (C); 33,26; 33,26
1/ 4/ 1; 145,46; 145,46
1/ 4/ 2; 98,83; 98,83
1/ 4/ 3 (C); 244,30; 244,30
我正在考虑使用替换值函数来替换我需要的字符,但由于两个查询都包含一个",",这证明很困难。
也许有一些动态/内置的方法来检测文件内容的区域设置和分隔符。我还没有找到。在有人出现并指出更好的方法之前,也许以下可以作为临时解决方案。
- 下面代码中的
parseCsv
函数尝试推断分隔符(它假定分隔符将始终出现在 CSV 的第一行中,紧跟在Panel/Node/Case
之后(。 - 它还尝试将最后两列的值从文本转换为数字(通过将
,
替换为.
(。如果不希望此行为,可以从代码中删除它。此外,此转换仅适用于对小数分隔符使用.
的区域设置。(如果需要支持其他区域性/区域设置,尝试推断区域性/区域设置并将其作为第二个参数传递给Number.FromText
可能更有意义。
let
data1 = Text.ToBinary(
"Panel/Node/Case, MXX (kNm/m), MYY (kNm/m)
1/ 1/ 1, 145.46, 145.46
1/ 1/ 2, 98.83, 98.83
1/ 1/ 3 (C), 244.30, 244.30
1/ 2/ 1, 19.80, 19.80
1/ 2/ 2, 13.46, 13.46
1/ 2/ 3 (C), 33.26, 33.26
1/ 3/ 1, 19.80, 19.80
1/ 3/ 2, 13.46, 13.46
1/ 3/ 3 (C), 33.26, 33.26
1/ 4/ 1, 145.46, 145.46
1/ 4/ 2, 98.83, 98.83
1/ 4/ 3 (C), 244.30, 244.30", TextEncoding.Utf8),
data2 = Text.ToBinary(
"Panel/Node/Case; MXX (kNm/m); MYY (kNm/m)
1/ 1/ 1; 145,46; 145,46
1/ 1/ 2; 98,83; 98,83
1/ 1/ 3 (C); 244,30; 244,30
1/ 2/ 1; 19,80; 19,80
1/ 2/ 2; 13,46; 13,46
1/ 2/ 3 (C); 33,26; 33,26
1/ 3/ 1; 19,80; 19,80
1/ 3/ 2; 13,46; 13,46
1/ 3/ 3 (C); 33,26; 33,26
1/ 4/ 1; 145,46; 145,46
1/ 4/ 2; 98,83; 98,83
1/ 4/ 3 (C); 244,30; 244,30", TextEncoding.Utf8),
parseCsv = (someFile as binary) =>
let
lines = Lines.FromBinary(someFile, QuoteStyle.Csv, false, TextEncoding.Utf8),
firstLine = List.First(lines),
expectedDelimiterPosition = Text.Length("Panel/Node/Case"),
delimiterInferred = Text.At(firstLine, expectedDelimiterPosition),
csv = Csv.Document(someFile, [Delimiter = delimiterInferred, Encoding = TextEncoding.Utf8, QuoteStyle = QuoteStyle.Csv]),
promoted = Table.PromoteHeaders(csv, [PromoteAllScalars=true]),
lastTwoColumnsAsNumbers =
let
lastTwoHeaders = List.LastN(Table.ColumnNames(promoted), 2),
replaceAndConvertToNumber = (someText as text) as number => Number.From(Text.Replace(someText, ",", ".")),
transformers = List.Transform(lastTwoHeaders, each {_, replaceAndConvertToNumber, type number}),
transformed = Table.TransformColumns(promoted, transformers)
in transformed
in lastTwoColumnsAsNumbers,
parsed1 = parseCsv(data1),
parsed2 = parseCsv(data2),
parsed3 = parseCsv(File.Contents("C:UsersMRCHDesktopData1.csv"))
in
parsed3
- 要实现这一点,您可以复制上面的代码,创建一个空白查询(在我的 Excel 版本中,我通过以下方式执行此操作:数据>从其他来源获取>数据>从空白查询获取数据(,单击高级编辑器(靠近左上角(,删除任何现有代码,粘贴您复制的内容,然后单击"完成"。
- 例如,要使
parseCsv
函数与文件路径一起使用,您可以将parsed1 = parseCsv(data1)
更改为parsed1 = parseCsv(File.Contents("SOME_FILE_PATH"))
其中SOME_FILE_PATH
是计算机上要Data1.csv
的文件路径(保留双引号(。 - 在查询编辑器中,您可以单击并查看表达式/步骤
parsed1
和parsed2
(这基本上是parseCsv
函数分别返回的Data1.csv
和Data2.csv
(。data1
和data2
只是为了演示目的,您可以将它们替换为 CSV 的实际二进制内容。
如果这没有帮助,请告诉我我可以在哪里改进我的解释。