Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
PQ: Конвертация столбцов с датами и суммами с разными форматами в один общий
 
#1
16.06.2022 18:26:21
Коллеги, добрый вечер,

Ситуация следующая: коллеги прислали набор файлов csv с 2019-2022 год, попросили обработать.
Каждый файл - это конкретный месяц конкретного года с набором данных.

Проблема в том, что в исходниках разные форматы: в январском файле дата выглядит как 05-09-2022, в февральском - 9\5\2022, ну и так далее.
Аналогичная ситуация с суммами - в одних файлах формат, в котором тысячный разделитель это точка, а десятичный - запятая (например 56.233,71), а в других - наоборот. Встречается что-то вроде такого: 21,76,65,678.22

Нужно: привести все даты любого формата из исходного состояния к формату ru-RU; а суммы - к формату, в котором тысячного разделителя не будет, а десятичный - это запятая. Уточнение: если это вообще возможно средствами PQ, без вмешательства в исходники.

Что пробовал:
1) с суммами - находить в каждой ячейке столбцов с суммами (их всего два пока что) знак с помощью Text.Middle и Text.Length, и в зависимости от знака проводить манипуляции с Replace.Text каждой ячейки. Работает, но прям сильно-сильно медленнее стало подгружать;
2) с датами - использовать Convert to Local, но возникают ошибки, а способ выше не стал применять пока что.

Посоветуйте варианты, спасибо заранее.
 
 
 
#2
16.06.2022 18:55:56
Framed, примеры файлов давайте, 3-4 штуки с разными комбинациями написаний. Только реальных, строк по 5 хватит. Всякое чуйствительное мне не нужно - можете поменять, только числа с датами не трогайте.
Вот горшок пустой, он предмет простой...
 
 
 
#3
16.06.2022 23:25:02
PooHkrd,

Столбцы с суммами - DMBTR, WRBTR
Даты - AUGDT, ZFBDT
Изменено: Framed - 16.06.2022 23:25:54
 
 
 
#4
17.06.2022 01:08:38
Framed, даты везде в формате "mm.dd.yyyy"? Или могут быть разные? Т.е. меняются только разделители?
Вот горшок пустой, он предмет простой...
 
 
 
#5
17.06.2022 01:16:38
PooHkrd,

В теории могут быть любыми.
Практически встречал пока только три варианта (они ниже), в т.ч. американский (данных по американскому формату больше всего):

1. dd.mm.yyyy;
2. m/d/yyyy;
3. dd-mm-yyyy.

Добавлю, что в одном файле присутствует лишь один формат; смешения не замечено.
Изменено: Framed - 17.06.2022 02:39:35
 
 
 
#6
17.06.2022 09:45:28
Framed, и сколько таких файлов всего? если порядка сотни (можно и больше), то я бы заморочился и отработал бы каждый под нужный формат и пересохранил в csv
 
 
 
#7
17.06.2022 10:53:48
mechanix 85, вероятно, мы так и сделаем в конечном итоге, согласен с вами. Файлов сейчас: около 40.
 
 
 
#8
17.06.2022 11:23:15
Framed, если в файле данных будут только даты вида aa/bb/yyyy, где ни aa, ни bb не превышает 12, то локаль не определить.
Попробуйте так:
Функция, определяющая локаль дат:
f_DateLocal
Код
( table as table, columns as list ) =>
  let
    clmns        = Table.SelectColumns ( table, columns ),
    dates        = List.Buffer ( List.RemoveMatchingItems ( List.Combine ( Table.ToColumns ( clmns ) ), { "" } ) ),
    secondNumber = List.Transform ( dates, ( x ) => Number.From ( Text.SplitAny ( x, Text.Remove ( x, { "0" .. "9" } ) ){1} ) ),
    local        = if List.Max ( secondNumber ) > 12 then "en-US" else "ru-RU"
  in
    local

Обработка всех файлов в папке:
Код
let
  files = Folder.Files ( "C:\Users\kovalev.i\Downloads\Dates\Sources" ),
  getData = Table.TransformColumns (
    files[[Content]],
    {
      {
        "Content",
        ( file ) =>
          [
            csv = Csv.Document ( file, [ Delimiter = ",", Encoding = 1251, QuoteStyle = QuoteStyle.None ] ),
            skip = Table.Skip ( csv, 1 ),
            promHeads = Table.PromoteHeaders ( skip, [ PromoteAllScalars = true ] ),
            local = f_DateLocal ( promHeads, { "AUGDT", "ZFBDT" } ),
            transform = Table.TransformColumns (
              promHeads,
              List.Transform ( { "AUGDT", "ZFBDT" }, ( x ) => { x } & { ( y ) => Date.From ( y, local ) } & { type date } )
                & List.Transform (
                  { "DMBTR", "WRBTR" },
                  ( x ) =>  { x }
                    & {
                      ( y ) => try Number.From ( y ) otherwise try Number.FromText ( y, "en-US" ) otherwise Number.From ( Text.Remove ( y, { "." } ) )
                    }
                    & { type number }
                )
            )
          ][transform]
      }
    }
  ),
  combine = Table.Combine ( getData[Content] )
in
  combine
 
 
 
#9
17.06.2022 20:00:52
Вот еще вариант для любителей простого ручного труда)). Идея такая: Загружаем все файлы из папки, по примеру первого файла удаляем 1 верхнюю строку, далее первую строку в названия столбцов. Теперь на основном запросе: 1.в столбцах с суммами заменяем в столбце точки и запятые на пробел, потом разделяем столбец на два про правому пробелу и соединяем обратно уже с запятой, указываем формат числовой.  2. В столбцах с датами меняем "-" на ".",  добавляем условный столбец и если ячейка содердит "/" с помощью Text.BetweenDelimiters вытягиваем день. Также в новые столбцы вытягиваем месяц и потом год (с помощью Text.BeforeDelimiter и Text.AfterDelimiter). Соединяем столбцы в один с помощью "."  Теперь делаем еще один условный столбец и объединяем в нём даты и основного столбца и собранного. После удаляем основной и собранный столбцы, переименовываем объединённый столбец по имени основного и вставляем его на место удалённого основного столбца. Делаем формат дата. Всё.
Код
let
    Источник = Folder.Files("C:\Users\Павел\Desktop\Test"),
    #"Отфильтрованные скрытые файлы1" = Table.SelectRows(Источник, each [Attributes]?[Hidden]? <> true),
    #"Вызвать настраиваемую функцию1" = Table.AddColumn(#"Отфильтрованные скрытые файлы1", "Преобразовать файл из Test", each #"Преобразовать файл из Test"([Content])),
    #"Переименованные столбцы1" = Table.RenameColumns(#"Вызвать настраиваемую функцию1", {"Name", "Source.Name"}),
    #"Другие удаленные столбцы1" = Table.SelectColumns(#"Переименованные столбцы1", {"Source.Name", "Преобразовать файл из Test"}),
    #"Столбец расширенной таблицы1" = Table.ExpandTableColumn(#"Другие удаленные столбцы1", "Преобразовать файл из Test", Table.ColumnNames(#"Преобразовать файл из Test"(#"Пример файла"))),
    #"Условный столбец добавлен" = Table.AddColumn(#"Столбец расширенной таблицы1", "Пользовательская", each if Text.Contains([AUGDT], "/") then Text.BetweenDelimiters([AUGDT], "/", "/") else null),
    #"Добавлен пользовательский объект" = Table.AddColumn(#"Условный столбец добавлен", "Пользовательская2", each if Text.Contains([AUGDT], "/") then Text.BeforeDelimiter([AUGDT], "/") else null),
    #"Добавлен пользовательский объект1" = Table.AddColumn(#"Добавлен пользовательский объект", "Пользовательская3", each if Text.Contains([AUGDT], "/") then Text.AfterDelimiter([AUGDT], "/", 1) else null),
    #"Объединенные столбцы" = Table.CombineColumns(#"Добавлен пользовательский объект1",{"Пользовательская", "Пользовательская2", "Пользовательская3"},Combiner.CombineTextByDelimiter(".", QuoteStyle.None),"Сведено Дата"),
    #"Измененный тип" = Table.TransformColumnTypes(#"Объединенные столбцы",{{"Сведено Дата", type date}}),
    #"Замененные ошибки" = Table.ReplaceErrorValues(#"Измененный тип", {{"Сведено Дата", null}}),
    #"Замененное значение" = Table.ReplaceValue(#"Замененные ошибки","-",".",Replacer.ReplaceText,{"AUGDT"}),
    #"Условный столбец добавлен1" = Table.AddColumn(#"Замененное значение", "Пользовательская", each if [Сведено Дата] <> null then [Сведено Дата] else [AUGDT]),
    #"Измененный тип1" = Table.TransformColumnTypes(#"Условный столбец добавлен1",{{"Пользовательская", type date}}),
    #"Удаленные столбцы" = Table.RemoveColumns(#"Измененный тип1",{"Сведено Дата", "AUGDT"}),
    #"Переименованные столбцы" = Table.RenameColumns(#"Удаленные столбцы",{{"Пользовательская", "AUGDT"}}),
    #"Переупорядоченные столбцы" = Table.ReorderColumns(#"Переименованные столбцы",{"Source.Name", "BUKRS", "BELNR", "GJAHR", "BUZEI", "AUGDT", "AUGBL", "BSCHL", "KOART", "VBUND", "UMSKZ", "SHKZG", "MWSKZ", "DMBTR", "WRBTR", "H_HWAER", "TXBHW", "VALUT", "ZUONR", "SGTXT", "BEWAR", "ALTKT", "VORGN", "KOKRS", "KOSTL", "AUFNR", "VBELN", "VBEL2", "POSN2", "ANLN1", "ANLN2", "ANBWA", "BZDAT", "PERNR", "SAKNR", "HKONT", "KUNNR", "LIFNR", "FILKD", "XBILK", "GVTYP", "HZUON", "ZFBDT", "ZTERM", "ZLSCH", "ZLSPR", "HBKID", "REBZG", "REBZJ", "DOCLN", "MATNR", "WERKS", "MENGE", "MEINS", "EBELN", "EBELP", "PRCTR", "PROJK", "DMBE2", "DMBE3", "XRAGL", "UZAWE", "LOKKT", "XREF1", "XREF2", "KBLNR", ""}),
    #"Замененное значение1" = Table.ReplaceValue(#"Переупорядоченные столбцы","."," ",Replacer.ReplaceText,{"DMBTR", "WRBTR", "DMBE2"}),
    #"Замененное значение2" = Table.ReplaceValue(#"Замененное значение1",","," ",Replacer.ReplaceText,{"DMBTR", "WRBTR", "DMBE2"}),
    #"Разделить столбец по разделителю" = Table.SplitColumn(#"Замененное значение2", "DMBE2", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, true), {"DMBE2.1", "DMBE2.2"}),
    #"Измененный тип2" = Table.TransformColumnTypes(#"Разделить столбец по разделителю",{{"DMBE2.1", Int64.Type}, {"DMBE2.2", Int64.Type}}),
    #"Объединенные столбцы1" = Table.CombineColumns(Table.TransformColumnTypes(#"Измененный тип2", {{"DMBE2.1", type text}, {"DMBE2.2", type text}}, "ru-RU"),{"DMBE2.1", "DMBE2.2"},Combiner.CombineTextByDelimiter(",", QuoteStyle.None),"DMBE2"),
    #"Измененный тип3" = Table.TransformColumnTypes(#"Объединенные столбцы1",{{"DMBE2", type number}}),
    #"Разделить столбец по разделителю1" = Table.SplitColumn(#"Измененный тип3", "DMBTR", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, true), {"DMBTR.1", "DMBTR.2"}),
    #"Измененный тип4" = Table.TransformColumnTypes(#"Разделить столбец по разделителю1",{{"DMBTR.1", Int64.Type}, {"DMBTR.2", Int64.Type}}),
    #"Объединенные столбцы2" = Table.CombineColumns(Table.TransformColumnTypes(#"Измененный тип4", {{"DMBTR.1", type text}, {"DMBTR.2", type text}}, "ru-RU"),{"DMBTR.1", "DMBTR.2"},Combiner.CombineTextByDelimiter(",", QuoteStyle.None),"DMBTR"),
    #"Измененный тип5" = Table.TransformColumnTypes(#"Объединенные столбцы2",{{"DMBTR", type number}}),
    #"Разделить столбец по разделителю2" = Table.SplitColumn(#"Измененный тип5", "WRBTR", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, true), {"WRBTR.1", "WRBTR.2"}),
    #"Измененный тип6" = Table.TransformColumnTypes(#"Разделить столбец по разделителю2",{{"WRBTR.1", type number}, {"WRBTR.2", Int64.Type}}),
    #"Объединенные столбцы3" = Table.CombineColumns(Table.TransformColumnTypes(#"Измененный тип6", {{"WRBTR.1", type text}, {"WRBTR.2", type text}}, "ru-RU"),{"WRBTR.1", "WRBTR.2"},Combiner.CombineTextByDelimiter(",", QuoteStyle.None),"WRBTR"),
    #"Измененный тип7" = Table.TransformColumnTypes(#"Объединенные столбцы3",{{"WRBTR", type number}}),
    #"Замененное значение3" = Table.ReplaceValue(#"Измененный тип7","-",".",Replacer.ReplaceText,{"VALUT"}),
    #"Условный столбец добавлен2" = Table.AddColumn(#"Замененное значение3", "VALUT2", each if Text.Contains([VALUT], "/") then Text.BetweenDelimiters([VALUT], "/", "/") else null),
    #"Условный столбец добавлен3" = Table.AddColumn(#"Условный столбец добавлен2", "VALUT3", each if Text.Contains([VALUT], "/") then Text.BeforeDelimiter([VALUT], "/") else null),
    #"Условный столбец добавлен4" = Table.AddColumn(#"Условный столбец добавлен3", "VALUT4", each if Text.Contains([VALUT], "/") then Text.AfterDelimiter([VALUT], "/", 1) else null),
    #"Объединенные столбцы4" = Table.CombineColumns(#"Условный столбец добавлен4",{"VALUT2", "VALUT3", "VALUT4"},Combiner.CombineTextByDelimiter(".", QuoteStyle.None),"VALUT+"),
    #"Измененный тип8" = Table.TransformColumnTypes(#"Объединенные столбцы4",{{"VALUT+", type date}}),
    #"Замененные ошибки1" = Table.ReplaceErrorValues(#"Измененный тип8", {{"VALUT+", null}}),
    #"Условный столбец добавлен5" = Table.AddColumn(#"Замененные ошибки1", "Пользовательская", each if [#"VALUT+"] = null then [VALUT] else [#"VALUT+"]),
    #"Удаленные столбцы1" = Table.RemoveColumns(#"Условный столбец добавлен5",{"VALUT", "VALUT+"}),
    #"Переименованные столбцы2" = Table.RenameColumns(#"Удаленные столбцы1",{{"Пользовательская", "VALUT"}}),
    #"Измененный тип9" = Table.TransformColumnTypes(#"Переименованные столбцы2",{{"VALUT", type date}}),
    #"Переупорядоченные столбцы1" = Table.ReorderColumns(#"Измененный тип9",{"Source.Name", "BUKRS", "BELNR", "GJAHR", "BUZEI", "AUGDT", "AUGBL", "BSCHL", "KOART", "VBUND", "UMSKZ", "SHKZG", "MWSKZ", "DMBTR", "WRBTR", "H_HWAER", "TXBHW", "VALUT", "ZUONR", "SGTXT", "BEWAR", "ALTKT", "VORGN", "KOKRS", "KOSTL", "AUFNR", "VBELN", "VBEL2", "POSN2", "ANLN1", "ANLN2", "ANBWA", "BZDAT", "PERNR", "SAKNR", "HKONT", "KUNNR", "LIFNR", "FILKD", "XBILK", "GVTYP", "HZUON", "ZFBDT", "ZTERM", "ZLSCH", "ZLSPR", "HBKID", "REBZG", "REBZJ", "DOCLN", "MATNR", "WERKS", "MENGE", "MEINS", "EBELN", "EBELP", "PRCTR", "PROJK", "DMBE2", "DMBE3", "XRAGL", "UZAWE", "LOKKT", "XREF1", "XREF2", "KBLNR", ""}),
    #"Замененное значение4" = Table.ReplaceValue(#"Переупорядоченные столбцы1","-",".",Replacer.ReplaceText,{"ZFBDT"}),
    #"Условный столбец добавлен6" = Table.AddColumn(#"Замененное значение4", "ZFBDT2", each if Text.Contains([ZFBDT], "/") then Text.BetweenDelimiters([ZFBDT], "/", "/") else null),
    #"Условный столбец добавлен7" = Table.AddColumn(#"Условный столбец добавлен6", "ZFBDT3", each if Text.Contains([ZFBDT], "/") then Text.BeforeDelimiter([ZFBDT], "/") else null),
    #"Условный столбец добавлен8" = Table.AddColumn(#"Условный столбец добавлен7", "Пользовательская", each if Text.Contains([ZFBDT], "/") then Text.AfterDelimiter([ZFBDT], "/", 1) else null),
    #"Объединенные столбцы5" = Table.CombineColumns(#"Условный столбец добавлен8",{"ZFBDT2", "ZFBDT3", "Пользовательская"},Combiner.CombineTextByDelimiter(".", QuoteStyle.None),"ZFBDT+"),
    #"Измененный тип10" = Table.TransformColumnTypes(#"Объединенные столбцы5",{{"ZFBDT+", type date}}),
    #"Замененные ошибки2" = Table.ReplaceErrorValues(#"Измененный тип10", {{"ZFBDT+", null}}),
    #"Условный столбец добавлен9" = Table.AddColumn(#"Замененные ошибки2", "Пользовательская", each if [#"ZFBDT+"] = null then [ZFBDT] else [#"ZFBDT+"]),
    #"Измененный тип11" = Table.TransformColumnTypes(#"Условный столбец добавлен9",{{"Пользовательская", type date}}),
    #"Удаленные столбцы2" = Table.RemoveColumns(#"Измененный тип11",{"ZFBDT", "ZFBDT+"}),
    #"Переименованные столбцы3" = Table.RenameColumns(#"Удаленные столбцы2",{{"Пользовательская", "ZFBDT"}}),
    #"Переупорядоченные столбцы2" = Table.ReorderColumns(#"Переименованные столбцы3",{"Source.Name", "BUKRS", "BELNR", "GJAHR", "BUZEI", "AUGDT", "AUGBL", "BSCHL", "KOART", "VBUND", "UMSKZ", "SHKZG", "MWSKZ", "DMBTR", "WRBTR", "H_HWAER", "TXBHW", "VALUT", "ZUONR", "SGTXT", "BEWAR", "ALTKT", "VORGN", "KOKRS", "KOSTL", "AUFNR", "VBELN", "VBEL2", "POSN2", "ANLN1", "ANLN2", "ANBWA", "BZDAT", "PERNR", "SAKNR", "HKONT", "KUNNR", "LIFNR", "FILKD", "XBILK", "GVTYP", "HZUON", "ZFBDT", "ZTERM", "ZLSCH", "ZLSPR", "HBKID", "REBZG", "REBZJ", "DOCLN", "MATNR", "WERKS", "MENGE", "MEINS", "EBELN", "EBELP", "PRCTR", "PROJK", "DMBE2", "DMBE3", "XRAGL", "UZAWE", "LOKKT", "XREF1", "XREF2", "KBLNR", ""})
in
    #"Переупорядоченные столбцы2"
Изменено: Zagadka - 17.06.2022 20:04:20
 
 
 
Страницы: 1
Наверх