I've made a best practice sheet for my department, which works perfectly on my own computer (en-US regional settings), but when I sent it to a coworker, everything blew up (da-DK regional settings).
Half the department uses en-US and the other half uses da-DK. It's not an option for everybody to use the same, so I'd like to create a sheet that can handle both automatically.
The csv files created in en-US uses "," delimiter and a "." for decimal, while the da-DK settings uses ";" as delimiter and "," for decimals.
How would I best go about this issue?
I have the following two identical data queries
Data1.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
Data2.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
I was considering to use a replace values function to replace the characters I need, but as both queries contain a "," that proves difficult.
Maybe there is some dynamic/built-in means of detecting a file's content's locale and delimiter. I haven't found one yet. Until someone comes along and points out a better way of doing this, maybe the below can serve as an interim solution.
parseCsv function in the code below attempts to infer the delimiter (it assumes that the delimiter will always be present in the first line of the CSV, immediately after Panel/Node/Case)., with .). If you don't want this behaviour, you can delete it from the code. Also, this transformation will only work for locales that use . for the decimal separator. (If you need to support other cultures/locales, it might make more sense to try to infer the culture/locale and pass it as a second argument to 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:\Users\MRCH\Desktop\Data1.csv"))
in
parsed3
parseCsv function work with a file path, you could for example change parsed1 = parseCsv(data1) to parsed1 = parseCsv(File.Contents("SOME_FILE_PATH")) where SOME_FILE_PATH is the file path to Data1.csv on your machine (keep the double quotes).parsed1 and parsed2 (which are basically what the parseCsv function returns for Data1.csv and Data2.csv respectively). data1 and data2 are just there for demonstrative purposes and you'd replace them with the actual binary content of your CSVs.If that doesn't help, let me know where I can improve my explanation.