Страницы: 1
RSS
Формирование кода ФИАС по строке адреса, Формирование кода ФИАС по строке адреса
 
Здравствуйте!
Как всегда неожиданно ввели новую форму, где помимо строки адреса необходимо заполнить поле с кодом ФИАС. Заполняемая таблица в MS Excel (2016) может иметь до 5000 записей. Можно ли как-то автоматизировать этот процесс, имея строковый адрес, автоматом получать код ФИАС и записывать его в ячейку?
Может, кто сталкивался с такой задачей... Файл с примером прилагаю. Необходимо заполнить столбец 6.
Изменено: Rodo - 15.02.2023 13:46:14
 
Rodo,  а как было найдено первое значение-пример (ячейка F5)?
Нужны коды улиц, например "Пожарная часть"?
Как быть с ячейками, в которых улиц несколько?

как вариант - простое решение по 10 копеек за строку )
https://dadata.ru/api/clean/address/#restrictions

там же есть и бесплатный вариант
https://dadata.ru/api/suggest/fias/#request
Изменено: nilske - 15.02.2023 14:27:27
 
Цитата
Rodo написал:
Заполняемая таблица в MS Excel (2016) может иметь до 5000 записей.
Вот еще адрес. Им можно отправить сразу все записи пакетом.
Альтернатива - закачать себе ФИАС. Архив - 36 ГБ.
Владимир
 
Цитата
написал:
Rodo,  а как было найдено первое значение-пример (ячейка F5)?
Нужны коды улиц, например "Пожарная часть"?
Как быть с ячейками, в которых улиц несколько?

как вариант - простое решение по 10 копеек за строку )
https://dadata.ru/api/clean/address/#restrictions

там же есть и бесплатный вариант
https://dadata.ru/api/suggest/fias/#request
Первое значение кода ФИАС было скопировано с сайта https://фиас.онлайн (^C+^V).
Нужны только коды ФИАС с уровнем "до улицы".
Ячейки с несколькими улицами обработаем до одной улицы (случайно проскочило).
 
Цитата
написал:
Цитата
Rodo написал:
Заполняемая таблица в MS Excel (2016) может иметь до 5000 записей.
Вот еще  адрес . Им можно отправить сразу все записи пакетом.
Альтернатива - закачать себе  ФИАС . Архив - 36 ГБ.
Хотелось бы, конечно, не платить за обработку таблиц, т.к. это в з/п не учтут...
Скачать базу ФИАС думали, но обрабатывать 36 ГБ... памяти и производительности ПК не хватит...
Видится решение, типа, в цикле, через API обратиться к сайту, подставить строковые данные адреса, получить код ФИАС и записать его в ячейку Excel.
 
Первое значение было получено с сайта https://фиас.онлайн, подставили строку адреса и получили код ФИАС.
Желательно получить коды ФИАС с уровнем глубины "до улицы".
Несколько улиц в ячейке не будет. (ошибка нормализации табл.)
Изменено: Rodo - 16.02.2023 14:09:40
 
пожарная часть
 
Тимофеев, а для фермы и Комсомола тоже так можно? )

Кажется понял, если нет соответствующего адреса, берём ID населённого пункта.
Изменено: nilske - 15.02.2023 15:41:59
 
Если нет улицы дает id населенного пункта - поэтому Ферма = Пожарка
 
Дома, это просто детализация. Есть всё и № дома, корпуса, помещения... Потом это можно будет добавить или убрать уже в Excel, главное, общее решение проблемы по автоматизированному заполнению кода ФИАС хотя бы до улицы.
Изменено: Rodo - 16.02.2023 14:07:50
 
Вариант штатными средствами - его надо будет разобрать после "guid":" до ","ifnsfl"  - 36 символов вроде в коде
Код
=ПСТР(
ВЕБСЛУЖБА("https://kladr-api.ru/api.php?query=
Чувашская Республика, Алатырский, с.Сойгино, ул.Пионерская
&oneString=1&limit=50&withParent=1");
ПОИСК("guid";
ВЕБСЛУЖБА("https://kladr-api.ru/api.php?query=
Чувашская Республика, Алатырский, с.Сойгино, ул.Пионерская
&oneString=1&limit=50&withParent=1"))+7;
36)
Изменено: Тимофеев - 15.02.2023 17:13:12
 
с помощью power query и dadata можно делать примерно так:

Код
// доработать и превратить в функцию
let
    city = "сойгино",
    street = "", // либо пишем улицу, либо оставляем значение пустым (для "пожарной части", "фермы" и т.п.)
    url = "https://suggestions.dadata.ru/suggestions/api/4_1/rs/suggest/fias",
    token = "указать_токен", // получить можно бесплатно в личном кабинете
    headers = [
                #"Content-Type" = "application/json",
                #"Accept" = "application/json",
                #"Authorization" = "Token " & token
                ],
    data = Json.Document(Web.Contents(url,
            [
                Headers = headers,
                Content = Json.FromValue([query=city & " " & street])
            ]
                )),
    toTable = Record.ToTable(data),
    expandValue = Table.ExpandListColumn(toTable, "Value"),
    expandValue1 = Table.ExpandRecordColumn(expandValue, "Value", {"value", "unrestricted_value", "data"}, {"value.1", "unrestricted_value", "data"}),
    expandedData = Table.ExpandRecordColumn(expandValue1, "data", {"fias_id"}, {"fias_id"}),
    selectColumn = Table.SelectColumns(expandedData,{"fias_id"}),
    selectID = Table.FirstN(selectColumn, 1)
in
    selectID // 34c6b6ae-373e-478f-9b19-d15c31f1bb27
Изменено: nilske - 15.02.2023 16:58:03
 
Решение из 9 На Вашем примере изобразил:
если с улицей косяк, берет населенный пункт
Странно с Карла Маркса идет в ошибку по улице (берет населенный пункт), если поменять в данных на К.Маркса то выдаст результат
Код
=ЕСЛИОШИБКА(
ЕСЛИОШИБКА(
ПСТР(ВЕБСЛУЖБА("https://kladr-api.ru/api.php?query="&
ОБЪЕДИНИТЬ(", ";1;B5:E5)&
"&oneString=1&limit=50&withParent=1");
ПОИСК("guid";
ВЕБСЛУЖБА("https://kladr-api.ru/api.php?query="&
ОБЪЕДИНИТЬ(", ";1;B5:E5)&
"&oneString=1&limit=50&withParent=1"))+7;36);
ПСТР(ВЕБСЛУЖБА("https://kladr-api.ru/api.php?query="&
ОБЪЕДИНИТЬ(", ";1;B5:D5)&
"&oneString=1&limit=50&withParent=1");
ПОИСК("guid";ВЕБСЛУЖБА("https://kladr-api.ru/api.php?query="&
ОБЪЕДИНИТЬ(", ";1;B5:D5)&
"&oneString=1&limit=50&withParent=1"))+7;36));
"не найден")
Изменено: Тимофеев - 15.02.2023 17:33:36
 
Благодарю! Всё заработало...
Изменено: Rodo - 16.02.2023 14:10:07
 
Здравствуйте.
Стал адаптировать предложенное решение на компьютере на непосредственной работе и выпадает ошибка.
На рабочем компьютере стоит MS Excel (2013), пришлось заменить формулу _xlfn.TEXTJOIN на СЦЕПИТЬ. Стал разбирать формулу по функциям, определил, что не работает встроенная функция MS Excel  =ВЕБСЛУЖБА().
При формировании API-запроса получается строка типа =ВЕБСЛУЖБА("https://kladr-api.ru/api.php?query=Чувашская Республика, Яльчикский, д.Тораево, ул.Карьерная&oneString=1&limit=50&withParent=1 ") , которая возвращает значение ошибки #ЗНАЧ! Пробовал использовать пример с сайта https://kladr-api.ru   =ВЕБСЛУЖБА("https://kladr-api.ru/api.php?query=Москва&oneString=1&limit=50&withParent=1") .
Тоже функцией ВЕБСЛУЖБА() возвращается ошибка #ЗНАЧ!
Разрешения в MS Excel все стоят. Проверил работу с API ЦБ РФ =ВЕБСЛУЖБА("http://www.cbr.ru/scripts/XML_dynamic.asp?date_req1=01.01.2022&date_req2=01.02.2022&VAL_NM_RQ=R01235") , всё работает, запрос возвращён без ошибки.
Есть ли решение данной проблемы или я где-то допустил ошибку?

ЗЫ
Написал в службу поддержки "ФИАС в облаке" <support@fias-api.ru>... ответили: "... к сожалению, мы не можем прокомментировать работу метода ВЕБСЛУЖБА в Excel.Скорее всего этот метод ожидает получение адрес веб-службы (которая работает по протоколу SOAP и передаем данные в XML формате). Наш сервис такой формат данных не поддерживает".
Изменено: Rodo - 16.02.2023 14:10:27
 
Решение - поставьте Excel новее
Заменил в файле на сцепку B5&", "&C5&", "&D5
Изменено: Тимофеев - 16.02.2023 13:51:15
 
В Excel 2016 указанная формула
Код
=ВЕБСЛУЖБА("https://kladr-api.ru/api.php?query=Чувашская Республика, Яльчикский, д.Тораево, ул.Карьерная&oneString=1&limit=50&withParent=1")

не выдает ошибок.
Возможно, в Excel 2013 проблема с использованием символов, не допустимых в стандарте URL (в Вашем случае - буквы русского алфавита).
Попробуйте записать так:
Код
=ВЕБСЛУЖБА("https://kladr-api.ru/api.php?query=" & КОДИР.URL("Чувашская Республика, Яльчикский, д.Тораево, ул.Карьерная") & "&oneString=1&limit=50&withParent=1")

И пожалуйста, уберите все цитаты из всех своих сообщений.
Владимир
 
Да лишним не будет записать с кодировкой
Код
=КОДИР.URL(B5&", "&C5&", "&D5)
Изменено: Тимофеев - 16.02.2023 13:54:12
 
Почему-то всё равно функция =ВЕБСЛУЖБА() выдаёт ошибку #ЗНАЧ!
Изменено: Rodo - 16.02.2023 15:10:36
 
На всякий случай, выгрузите книгу Excel с указанной формулой (которая выдает ошибочное значение).
Владимир
 
Rodo, скобку переставьте в вашей формуле после E18, а не D18 - а то передаете половину кодированного - половину русского
Попробуйте на другом компе - найдете комп на котором будет работать - они Вам быстро переведут данные и пришлют обратно )
Изменено: Тимофеев - 16.02.2023 15:10:15
 
У меня файл из #19 при загрузке выдает предупреждение, что служба WEBSERVICE отключена. После включения и переввода (достаточно удалить и добавить последний символ) формула пересчитывается и выдает правильное значение.

Может быть, дефект версии Excel 2013 (сейчас нет компьютера для проверки).
Изменено: sokol92 - 16.02.2023 14:47:50
Владимир
 
Скобку переставил, в примере и на прикреплённом скрине видно. Всё равно ошибка выпадает... (#ЗНАЧ!)
 
у меня в 2019 работает, в 2016 - нет.
 
Спасибо за помощь и участие.
 
Rodo, если на Power Query решение не подходит, а Вебслужба не работает, то можно попробовать через макрос (UDF).
   Напишите, если такое решение является интересным и подходящим, чтобы зря не стараться )
 
Нашёл сайт с сервисами по ФИАС. Функция =ВЕБСЛУЖБА() у Excel (2013) работает с примером с сайта корректно яч.F22 прилагаемого файла. Осталось только теперь понять как сформировать запрос по поиску кода ФИАС из строки с адресом. Пробовал формулу в яч.F23, но запрос возвращён сайтом пустым.
Изменено: Rodo - 16.02.2023 20:46:09
 
Цитата
nilske написал:
Rodo, если на Power Query решение не подходит, а Вебслужба не работает, то можно попробовать через макрос (UDF).
   Напишите, если такое решение является интересным и подходящим, чтобы зря не стараться )

Спасибо за предложение, но я боюсь, что не смогу объяснить безопасникам, чтобы они сделали исключение на запуск макросов.
 
Здравствуйте! Помогите пожалуйста, мне нужно чтобы не только до улицы код ФИАС определялся, а еще учитывая дом. Как отредактировать формулу? у меня к сожалению не получилось. Использовал последний прикрепленный файл Rodo
Страницы: 1
Наверх