Основные ошибки при работе в КПИ ЕГИССО и через СМЭВ. Пути их устранения

5 способов преобразовать таблицу Excel в файл формата XML

После редактирования какой-либо таблицы часто возникает необходимость перевести Excel в xml. Не всем пользователям ПК, особенно начинающим, может быть понятно, как это правильно сделать. Сегодня покажу весь процесс наглядно в нескольких инструкциях.

Что такое форматы xls, xlsx и xml

Документы Excel могут быть нескольких форматов: xls, xlsx, xlsm, xlstx, xlstm, xlsb и т. д. Самые популярные – xls и xlsx.

XML – это язык разметки, как html. Он создан для хранения и транспортировки каких-либо данных. Используется в том числе для создания таблиц Excel и веб-страниц. По своей сути xml-формат — это структурированная база данных. Файл с таким расширением можно открыть в любом текстовом редакторе. В нем вы увидите код, который и содержит те или иные данные.

Если в вашей таблице Excel встроена карта (схема) XML, вы без труда сможете экспортировать данные из нее в файл xml: с помощью стандартной функции приложения Эксель или за счет сторонних онлайн-конвертеров.

Microsoft Excel

Преобразовать xlsx в xml можно с помощью простого сохранения файла в новом формате, а также через кнопку «Экспорт» в специальном меню разработчика.

Но есть ограничение по количеству строк: при экспорте можно сохранить до 65 536 строк. Если в таблице больше данных, программа сможет обработать число строк, которое определяется формулой: ЧСТРОК ОСТАТ 65537. К примеру, если лист содержит 70 000 строк, Эксель сохранит 4464 (70000 ОСТАТ 65537). Если это ваш случай, данные таблицы придется экспортировать в два файла xml.

Простое сохранение

Самый легкий способ – воспользоваться меню «Сохранить как» и выбрать xml в качестве нового формата. Он подходит для небольших и более простых таблиц, так как есть риск, что потом все другие программы будут некорректно работать с созданным файлом. Тем не менее, метод стоит рассмотреть как один из возможных.

Как сохранить таблицу xls/xlsx в формате xml:

• Сначала откройте файл в программе Excel. Если она установлена в качестве приложения по умолчанию, просто найдите документ в «Проводнике Windows» и кликните по нему дважды.
• Также можете щелкнуть по документу правой кнопкой мыши, а затем выбрать «Открыть с помощью» и «Excel».
• Либо запустите пустую таблицу с помощью ярлыка Excel на рабочем столе или в меню «Пуск».
• Выберите документ в списке недавних либо нажмите «Открыть другие книги».
• Если у вас уже открыта какая-то таблица в Экселе, нажмите на вкладку «Файл». Зайдите в раздел «Открыть» и нажмите «Обзор». Выберите, собственно, сам документ в «Проводнике».

Экспорт через панель разработчика

Этот способ увеличивает шансы, что созданный файл будет правильно восприниматься другими программами. Панель разработчика по умолчанию отключена в Экселе, поэтому сначала нужно ее включить в настройках, а затем запустить конвертацию.

В меню разработчика есть также функция «Импорт». Она помогает сделать обратную конвертацию – из xml в xls или xlsx. В нашем блоге есть отдельная статья, посвященная этой теме.

Если строк меньше 65 536

Как экспортировать данные таблицы в xml, если количество строк не выходит за рамки нормы:

• Сначала включите панель разработчика в настройках Экселя. Для этого кликните «Файл». Нажмите «Параметры».
• Зайдите в «Настроить ленту». Поставьте галочку «Разработчик» в списке справа. Нажмите «ОК» для сохранения настроек.
• Раскройте вкладку «Разработчик» в верхнем меню. Нажмите «Экспорт».
• Если в таблице несколько схем xml, дополнительно появится диалоговое окно. Выберите карту XML, которую вы хотите использовать, и кликните «ОК».
• В «Проводнике Windows» укажите новое имя документа при необходимости. Выберите папку для сохранения и подтвердите экспорт.

Если строк больше 65 536

Если таблица содержит больше 65 536 строк, придется создать два файла xml.

Вот как это сделать:

Таким образом, у вас на руках будет два файла xml. При необходимости их можно будет объединить для создания исходной таблицы.

Онлайн-конвертеры

Если программы Excel нет на вашем ПК или вам не удалось перевести xlsx в xml в этом приложении, воспользуйтесь онлайн-конвертером. Есть много сайтов, которые бесплатно преобразовывают таблицы в подобные документы. Расскажу лишь о нескольких из них.

Не переживайте о конфиденциальности: файлы будут доступны для скачивания только вам. А спустя сутки и вовсе будут удалены с серверов сайта.

Tableconvert

Tableconvert позволяет редактировать таблицу перед конвертацией. Самостоятельно создает для нее код, используя выбранный язык разметки и программирования: xml, html, sql и т. д. С помощью этого сервиса файлы также можно преобразовывать в jpg и pdf.

Сайт бесплатный. Можно по желанию оставить донат разработчикам.

• Перейдите на сайт и загрузите таблицу с ПК с помощью специальной кнопки.
• Либо нажмите «Выдержка URL», вставьте ссылку на файл, опубликованный в интернете, и щелкните «Экстракт Excel».
• При необходимости измените содержимое каких-либо ячеек в блоке «Редактор таблицы», который будет чуть ниже.

Zamzar

Zamzar предлагает несколько способов загрузки исходных файлов: с ПК, по ссылке в интернете, из облачных хранилищ (Box, Dropbox, Google Drive и OneDrive). И вообще в этом сервисе можно преобразовать множество форматов: аудио, видео, текстовые документы, электронные книги, изображения, архивы и т. д.

Одновременно можно сконвертировать несколько файлов. Схема та же: загружаете документы с таблицами, выбираете формат xml (если он не выбран по умолчанию) и нажимаете «Конвертировать».

Предварительно можно указать адрес электронной почты. На него придет ссылка для скачивания обработанного документа. Это на случай, если боитесь потерять страницу из виду.

Максимальный размер файла в бесплатной версии 50 Мб. Подписка стоит от 18$ до 70$ в зависимости от тарифа.

Onlineconvertfree

Onlineconvertfree похож во многом на Zamzar. Здесь тоже можно конвертировать разные файлы: фото, аудио, видео и т. д. По несколько штук за раз.

Даже без регистрации на сайте вы получаете доступ к разделу «Мои файлы». В нем будут храниться документы, которые вы ранее обработали в сервисе – в течение суток.

Если у вас установлен Microsoft Excel, сконвертировать таблицу в xml можно прямо в этой программе: с помощью обычного сохранения или функции «Экспорт» в меню разработчика. Если строк больше 65 536, придется создать два файла.

Редактируйте фото онлайн бесплатно в редакторе фотографий

Теперь не нужно искать фотошоп, платить за услуги редактирования. В интернете это можно сделать самому и бесплатно. Онлайн фото-редактор поможет оригинально, качественно обработать необходимую фотографию.

Онлайн – редактор снимков, который объединил в себе наиболее востребованные и удобные функции редактирования.

Редактор не нужно загружать на компьютер или ноутбук. Пользователю достаточно посетить наш сайт и пользоваться программой в онлайн режиме.

Редактор на русском функционирует оперативно, позволяет оперативно редактировать габаритные снимки. Посетитель может выбрать любое фото с любых источников, в том числе из социальных сетей. После редактирования изображений их можно выставить обратно.

Редактор активно пользуются тысячи посетителей. Мы периодически совершенствуем функции редактора, делаем их эффективнее, увлекательнее, не сложнее в пользовании.

Редактор – многофункциональный редактор, где для обработки фотографий онлайн можно выбрать: разнообразные наклейки; текстуру; тексты; ретушь; оригинальные рамки; с эффектами; коллажи и др.

Редактирование фотографий абсолютно бесплатно, также можно бесплатно пользоваться этим фото в будущем.

Желаете без проблем и качественно отредактировать снимок прямо сейчас? онлайн редактор быстро исправит недостатки, и улучшит качество любого фото!

Человеку не подвластно время. Фотоснимок позволяет сохранить самые дорогие минуты нашей жизни в первозданном облике. Снимок улавливает и передает настроение, эмоции, все тонкие жизненные моменты. С iPhotor для рисования такие воспоминания станут более впечатлительными, яркими и незабываемыми!

Фотография – один из видов искусства. Сам процесс фотографирования простой, но он способен зафиксировать сложные моменты – красивое, хрупкое и быстротечное мгновенье. Это непросто передать с помощью обычных рисунков. Какого бы качества не были фото, редактор iPhotor преобразит даже самое обычные, снятые мобильным или простым фотоаппаратом.

Фотография лучше всего способна передать то, о чем вам хотелось рассказать людям. Фоторедактор iPhotor поможет поделиться с близкими впечатлениями, чувствами, отразит ваше вдохновение.

Возможности Редактора онлайн

Изменение размера, поворот, обрезка

Это самые востребованные операции в фото — редакторе, позволяющие вращать на 90 градусов снимок влево, вправо, по вертикали, горизонтали. Обработка делается оперативно и легко. Для обрезки выбираются границы обрезания фото.

Данное меню позволяет регулировать яркость, ретушь лица, коррекцию теней, светлых участков фото и т.п. Здесь также можно изменить оттенок, насыщенность, увеличить резкость картинок. Изменяя настройки каждого инструмента, можно наблюдать за изменениями в режиме онлайн.

Текст, стикеры, рамки

Графический редактор iPhotor позволяет создавать модные картинки, с прикольными стикерами, оригинальными фото рамками, текстовыми подписями.

Фото — эффекты, фото фильтры

С помощью редактора iPhotor можно бесплатно превратить цветное изображение в черно-белое, или наоборот, сделать виньетирование, наложение фото на фото, эффект пикселизации.

Воспользуйтесь уникальными возможностями фото — редактора онлайн прямо сейчас, сделайте вашу жизнь в реальности и на фото ярче!

Онлайн редактор приукрасит самые дорогие моменты вашей жизни!

Конвертация форматов Microsoft Excel в XML

XML – это универсальный формат работы с данными. Его поддерживает множество программ, в том числе из сферы СУБД. Поэтому конвертация информации в XML важна именно с точки зрения взаимодействия и обмена данными между различными приложениями. Excel как раз входит в число программ, которые работают с таблицами, и даже может выполнять манипуляции с базами данных. Разберемся, как конвертировать файлы Эксель в XML.

Процедура конвертации

Конвертация данных в формат XML не такой уж простой процесс, так как в его ходе должна быть создана специальная схема (schema.xml). Впрочем, для преобразования информации в простейший файл данного формата достаточно иметь под рукой обычные инструменты сохранения в Excel, а вот для создания хорошо структурированного элемента придется основательно повозиться с составлением схемы и её подключением к документу.

Среди вариантов сохранения ищем наименование «Таблица XML 2003» или «XML-данные». Выбираем один из этих пунктов.

Таким образом, преобразование файла из формата Excel в XML будет совершено.

Инструменты разработчика

Конвертировать формат Excel в XML можно с помощью инструментов разработчика на вкладке программы. При этом, если пользователь все выполнит правильно, то на выходе он получит, в отличие от предыдущего способа, полноценный XML-файл, который корректно будет восприниматься сторонними приложениями. Но сразу нужно сказать, что не каждому новичку может хватить знаний и умений сразу научиться конвертировать данные таким способом.

• По умолчанию вкладка инструментов разработчика отключена. Поэтому, прежде всего, нужно её активировать. Переходим во вкладку «Файл» и кликаем по пункту «Параметры».
• В открывшемся окне параметров перемещаемся в подраздел «Настройка ленты». В правой части окна устанавливаем галочку около значения «Разработчик». После этого жмем на кнопку «OK», размещенную в нижней части окна. Вкладка инструментов разработчика теперь включена.

Как видим, открывающим и закрывающим тегом для документа в целом служит «data-set». В этой же роли для каждой строки выступает тег «record». Для схемы вполне будет достаточно, если мы возьмем всего две строки таблицы, а не будем переводить её всю вручную в XML. Название открывающего и закрывающего тега столбца может быть произвольным, но в данном случае для удобства мы предпочли просто перевести русскоязычные наименования колонок на английский язык. После того, как данные внесены, просто сохраняем их через функционал текстового редактора в любом месте на жестком диске в формате XML под названием «schema».

Запускается окно выбора источника. Переходим в директорию расположения схемы составленной ранее, выбираем её и кликаем по кнопке «Открыть».

После того, как элементы схемы появились в окне, перетаскиваем их с помощью курсора в соответствующие ячейки наименования столбцов таблицы.

Не могу сказать, что я разделял радость, как семья с главной страницы сайта.

С чего всё началось

На дворе был конец августа 2018 года. Предприятие, в котором я работал, переживало не лучшие времена, инвестор с Москвы выхода из этой ситуации не находил и я принял решение покинуть компанию. К сожалению, в родном селе не огромный выбор рабочих мест, но так удачно совпало, что в отделе образования администрации района освободилось место главного специалиста по информационным технологиям. Собеседование, сбор документов и “с понедельника можно работать”. Когда я узнал причину, почему уволился другой специалист, то не принял всерьез данный факт. И зря. На меня свалился груз под названием ЕГИССО.

Что же это такое?

ЕГИССО является информационной системой, позволяющей получать гражданам и органам власти актуальную информацию о мерах социальной поддержки, оказываемых из бюджетов всех уровней.

На примере бывшего отдела – ваш ребенок пошёл в садик и вы хотите получить выплату компенсации части родительской платы за содержание ребенка в детском саду. Пришли в отдел, оплатили, сделали заявление на начисление компенсации: сумму компенсации, личные данные ребенка и родителя, получившего компенсацию, вы можете посмотреть у себя в личном кабинете на сайте. Так же различные компенсации за детское питание в школах, путёвки в пришкольные летние лагеря, учет выплат для сирот и так далее.

Вот пример компенсации моего больничного после второй прививки от Covid.

Делалось это государством с целью контролировать, куда уходит бюджет и кому. Даже есть официальный документ с планом мероприятий по созданию – http://www.consultant.ru/document/cons_doc_LAW_223328/ Сервис в целом полезный (хотя судя по отзывам в интернете личный кабинет часто бывает недоступен), но каким путем данные попадают на сайт – это реальный ад.

Горькая реальность

Настал тот момент, когда пришлось прикоснуться к этому сервису. В отделе “так исторически сложилось”, что этим занимался предыдущий специалист, поэтому ты будешь продолжать это дело. На все мои возражения, что этого нет в должностной инструкции (доступ к сайту я настрою и ПО установлю нужное, но наполнять информацией базу – не ко мне), на доступ к персональным данным не обучен и в целом вы не по адресу, был только один ответ – нужно кому-то делать.

До этого проект ЕГИССО уже начал тестироваться в нескольких областях (ссылки на новости: раз, два, три, четыре). Я об этом сервисе вообще не имел представления, пока не начал работать на данной работе (поэтому поводу опрос ниже).

В нашей (Амурской) области тоже начиналось активное внедрение и к концу года надо было показать “активность”. Мы должны были предоставить в областное отделение ПФР цифры “уникальных пользователей”, которых мы добавим в данную систему. Сам себе задачу поставь, выполни и если данную цифру не выполнишь, то глава района (и дальше по наклонной) получит по шапке за неисполнение.

Ну что ж, сажусь считать, сколько у нас школ, сколько детей в них обучается с 1 по 4 класс (питание детей), к этим цифрам прибавляю детсадовцев, сирот – вот и получил цифру. Почему должен считать я, а не бухгалтеры – другой вопрос. Окей, посылаем письмо с данными и начинаем эти данные собирать.

А со сбором первоначальных данных две проблемы.

Первая проблема – законность. Из-за того, что данные нужны прямо и сейчас, то ни о каких бумажках с целью “разрешаю использовать персональные данные” никем не подписывались и не заполнялись. На вебинарах (я был на двух), которые проводились правительством области (соц защита) совместно с представителями ПФР, кроме “угроз”, что за невыполнение плана всем будем плохо, был и ответ на данный вопрос в стиле – “ну сейчас так, потом все уладим”. Хотел задать вопрос на этих вебинарах, почему IT специалист должен делать эту работу, но не дали.

Вторая проблема – люди. Вот серьезно. В нашем селе находится военная часть, где очень много военных, которые водят своих детей в садики. Месяц они ребенка водили, потом их переводят в другую часть и ребенок не ходит, но компенсацию он же получил за тот месяц. Где взять данные СНИЛС отца или матери – да нигде уже. Либо сначала компенсацию получала мать, данные которой у нас есть, а после стал получать отец, который номер СНИЛС не дал. Данные по компенсациям я забивал с мая 2018 года, поэтому таких “ходунов” накапливалось человек по 20 на район и что с ними нужно было делать – не понятно. “Ищите, ищите и ещё раз ищите”.

Немного про взаимоотношения с ПФР. Я, как человек, пришедший со стороны, в душе не знаю, что/куда/зачем и как в этой АИС делать. Пытаюсь им звонить, чтобы уточнить информацию и по сути каждый диалог начинался с того, что “предыдущий специалист, который начинал дело уволился” и объяснения, что оно мне всё это вообще не сдалось, никого не интересовали. Но что знали, то подсказывали и на этом спасибо – по шапке получить никто не хотел, да и пошла по районам текучка кадров из-за этой темы. Это я привел только пример с образованием, а ещё есть медицина, ветераны и т.д.

Техническая часть

А теперь перейдем к тому, к чему возникло ещё больше вопросов – как со всем этим работать.

Примерный план работы заключался в следующем:

• бухгалтер из 1С отправляет на печать сводку по каждой школе/садику с перечнем детей и родителей, получившим компенсацию
• эти данные через сайт заносятся, подписываются электронной подписью и забываются.

Проще некуда кажется с первого взгляда. Начнем с настройки рабочего места.

Так как это государственная АИС, то понятное дело работать оно будет только через что? Правильно – Internet Explorer. И не абы какой, а именно 11 версии. Из-за того, что на Windows 8 (не 8.1) рабочего ноутбука 11ая версия ну никак не ставилась, пришлось на VHD диск поставить Windows 8.1 и делать настройки там (а дома я развернул в Hyper-V на базе Windows 7 виртуальную машину и заливал данные на сайт уже через неё).

Так же нам нужно будет установить:

• КриптоПро CSP 4.0.9944.
• Crypto+DE 4.1.126. Эта программа у меня не работала корректно, поэтому готовые xml файлы я подписывал другой программой.
• Плагин Крипто Про ЭЦП Browser-plugin и Плагин для авторизации через портал Госуслуги IFCPlugin-x64.
• Корневые сертификаты ЕГИССО.

Заходим в качестве поставщика информации опять же под личным аккаунтом Госуслуг, который предварительно добавили в группу ответственных (без этого дальше на сайт просто не пустит). Если IE настроили верно, все сертификаты установили куда надо, то нам откроется кабинет поставщика информации.

Минимализм во всей красе (фото 2018 года)

Давно на самом сайте ЕГИССО и ПФР были хорошие инструкции по описанию этого сайта, но они куда-то делись и я нашел единственную ссылку, где всё это добро сохранилось. Я всё прочитал, но всё равно пришлось идти к бухгалтерам – что такое КБК, как оно формируется, что за цифры от меня хотят – вопросов было больше, чем ответов.

Картинка из руководства пользователя

Итак, у нас уже есть доступ к сайту, добавлены локальные МСЗ с их КБК и прочими непонятными мне числами. И мы уже готовы добавлять пользователей. Технически да, но кто в здравом уме будет добавлять по одному ребенку через сайт, если у меня их тысячи и нужно делать это не за один месяц. Если бы мне выделили только время под это дело, то так и можно было делать.

Какое видение было у правительства насчёт. этого метода заполнения сайта. Приходит условный Вася Петров производить оплату за месяц хождения в детский садик сына Сережи. Он берет деньги, свой СНИЛС и СНИЛС ребенка, платит в кассу, при нем же вычисляют компенсацию, эту компенсацию при нем же вбивают на сайте, выгружают, подписывают ЭП, загружают уже подписанные файлы обратно, дают на руки деньги – он радостный забирает их и идёт счастливый домой. Приходит домой, заходит на сайт и видит в личном кабинете, что уже рассчитали компенсацию. Идеально. В мечтах конечно же.

И так каждого получателя.

Когда тебе надо добавить максимум 15 ветеранов каждый месяц с их пенсиями – это нормально. Но когда у тебя тысячи детей/больных и других – это просто колоссальная трата времени работников. Но слава богу были альтернативы для занесения фактов МСЗ не через сайт.

Ассистент ЕГИССО

На моей работе использовали именно её, потому что ПФР по региону всем выдавало эту программу. Из плюсов – теперь пользователи и МСЗ хранятся централизованно, но каждый раз щелкать ребенка/родителя, писать сумму и вообще делать много щелчков мышью – не мой вариант да и от сайта почти толком не отличается. Но разработчикам приложениям выражаю благодарность – упростили своим приложением жизнь многим людям и поддерживали очень долго.

Почему выходило несколько версий программы. Правильно – потому что ПФР любил поменять формат XSD для файлов, из-за чего рушил логику генерирования и приходилось выпускать новый билд программы. А тем временем мои 1000 детей и взрослых ждали, когда их добавят на сайт.

Конвертор CSV

Я стал искать решение, как бы мне дело автоматизировать. В целом, можно было разобраться в XSD схемах, набросать какое-нибудь приложение, но когда это всё делать, ведь нужно показывать цифры прироста, да и другие дела тоже есть.

Пока время терпело потихоньку работал через Ассистент, но там, где детей было немного – в районе 30. Делал формально один садик, забивал “шаблончик” в программу, менял даты и суммы, выгружал полученные файлы из программы, подписывал их и выкладывал на сайт. Динамика шла, но до заветной цифры ещё было далеко.

И тут я наткнулся на чудесный форум (выражаю всем заинтересованным лицам моё уважение – ваш энтузиазм помог многим и мне в том числе) – Партизанский форум про ЕГИССО. В одной из тем я нашел то, что и искал. Опять видимо разработчики из уже другой области сделали конвертор из csv файла в xml нужного формата (ссылка на свежий пост).

Теперь я мог создавать шаблоны для каждой школы/садика, где основной набор в целом плюс/минус один и тот же, менять только даты выдачи компенсаций, их сумму – и всё это делая в Excel. Я сделал одну небольшую школу – это было очень просто, потому что стал просить приносить бухгалтеров выгрузки из 1С не в виде напечатанных листков, а в виде файлов excel. Сформировал небольшие excel’ные списки детей/родителей по каждой школе, чтобы методом ctrl+c/ctrl+v заполнять нужный шаблон. Да, приходилось ещё работать руками, но это было намного быстрее, чем тыкать каждый раз мышкой в программе или вносить каждую запись на сайте.

Дело пошло у меня в гору, до нормы по количеству уникальных пользователей за пару недель я довёл – к Новому году я успел и дело своё доделал. А в январе 2019 года я был уже на другом месте работы и в другом регионе страны, но это уже совсем другая история.

А что в итоге?

Совсем недавно как раз прошла новость – Упрощенный документооборот: регионы получили доступ к Единой госсистеме соцобеспечения. Исходя из текста новости – всё у нас прекрасно, эффективно, “модно, стильно, молодёжно”. Хотелось бы уточнить у СМИшников, да и у государства тоже, про покрытие высокоскоростным интернетом отдаленных регионов нашей страны и наличию хотя бы одного компьютера в семьях, что эти меры социальной поддержки получают (а многие из них как раз неблагополучные), но думаю вы и сами всё понимаете.

Судя по форуму и отзывам бывших сотрудников из бюджетной сферы (данные верны на 2019 год), смею предположить, что новых инструментов для работы так и не разработали. Всё это мне напомнило какой-то плохой стартап: идею придумали, бюджет выделили, а инструменты для конечных пользователей системы так и не довели до ума – пусть сами придумают (что в целом и сделали частные конторы со своими модулями для 1С). Не так я себе представлял Big Data и автоматизацию процессов. Если таким же образом и на сайт Госуслуг попадают данные, то мне становится страшно за государственные АИС. И получается, что “спасение утопающих – дело рук самих утопающих”.

Надеюсь, что своей заметкой я дал повод многим задуматься, как и какими жертвами проводится информатизация страны.

Только зарегистрированные пользователи могут участвовать в опросе. Войдите, пожалуйста.

А вы знали о существовании данного сервиса?

Проголосовали 172 пользователя.

Воздержались 7 пользователей.

Стоит ли продолжить тему про АИС в образовании?

Довольно таки интересно

Проголосовал 121 пользователь.

Воздержались 22 пользователя.

Время на прочтение

Продукты HFLabs в промышленных объемах обрабатывают данные: адреса, ФИО, реквизиты компаний и еще вагон всего. Естественно, тестировщики ежедневно с этими данными имеют дело: обновляют тест-кейсы, изучают результаты очистки. Часто заказчики дают «живую» базу, чтобы тестировщик настроил сервис под нее.

Первое, чему мы учим новых QA — сохранять данные в первозданном виде. Все по заветам: «Не навреди». В статье я расскажу, как аккуратно работать с CSV-файлами в Excel и Open Office. Советы помогут ничего не испортить, сохранить информацию после редактирования и в целом чувствовать себя увереннее.

Материал базовый, профессионалы совершенно точно заскучают.

Что такое CSV-файлы

Формат CSV используют, чтобы хранить таблицы в текстовых файлах. Данные очень часто упаковывают именно в таблицы, поэтому CSV-файлы очень популярны.

CSV-файл состоит из строк с данными и разделителей, которые обозначают границы столбцов

CSV расшифровывается как comma-separated values — «значения, разделенные запятыми». Но пусть название вас не обманет: разделителями столбцов в CSV-файле могут служить и точки с запятой, и знаки табуляции. Это все равно будет CSV-файл.

У CSV куча плюсов перед тем же форматом Excel: текстовые файлы просты как пуговица, открываются быстро, читаются на любом устройстве и в любой среде без дополнительных инструментов.

Из-за своих преимуществ CSV — сверхпопулярный формат обмена данными, хотя ему уже лет 40. CSV используют прикладные промышленные программы, в него выгружают данные из баз.

Одна беда — текстового редактора для работы с CSV мало. Еще ничего, если таблица простая: в первом поле ID одной длины, во втором дата одного формата, а в третьем какой-нибудь адрес. Но когда поля разной длины и их больше трех, начинаются мучения.

Следить за разделителями и столбцами — глаза сломаешь

Еще хуже с анализом данных — попробуй «Блокнотом» хотя бы сложить все числа в столбце. Я уж не говорю о красивых графиках.

Поэтому CSV-файлы анализируют и редактируют в Excel и аналогах: Open Office, LibreOffice и прочих.

Ветеранам, которые все же дочитали: ребята, мы знаем об анализе непосредственно в БД c помощью SQL, знаем о Tableau и Talend Open Studio. Это статья для начинающих, а на базовом уровне и небольшом объеме данных Excel с аналогами хватает.

Как Excel портит данные

Все бы ничего, но Excel, едва открыв CSV-файл, начинает свои лукавые выкрутасы. Он без спроса меняет данные так, что те приходят в негодность. Причем делает это совершенно незаметно. Из-за этого в свое время мы схватили ворох проблем.

Большинство казусов связано с тем, что программа без спроса преобразует строки с набором цифр в числа.

Округляет. Например, в исходной ячейке два телефона хранятся через запятую без пробелов: «5235834,5235835». Что сделает Excel? Лихо превратит номера́ в одно число и округлит до двух цифр после запятой: «5235834,52». Так мы потеряем второй телефон.

Приводит к экспоненциальной форме. Excel заботливо преобразует «123456789012345» в число «1,2E+15». Исходное значение потеряем напрочь.

Проблема актуальна для длинных, символов по пятнадцать, цифровых строк. Например, КЛАДР-кодов (это такой государственный идентификатор адресного объекта: го́рода, у́лицы, до́ма).

Удаляет лидирующие плюсы. Excel считает, что плюс в начале строки с цифрами — совершенно лишний символ. Мол, и так ясно, что число положительное, коль перед ним не стоит минус. Поэтому лидирующий плюс в номере «+74955235834» будет отброшен за ненадобностью — получится «74955235834». (В реальности номер пострадает еще сильнее, но для наглядности обойдусь плюсом).

Потеря плюса критична, например, если данные пойдут в стороннюю систему, а та при импорте жестко проверяет формат.

Разбивает по три цифры. Цифровую строку длиннее трех символов Excel, добрая душа, аккуратно разберет. Например, «8 495 5235834» превратит в «84 955 235 834».

Форматирование важно как минимум для телефонных номеров: пробелы отделяют коды страны и города от остального номера и друг от друга. Excel запросто нарушает правильное членение телефона.

Удаляет лидирующие нули. Строку «00523446» Excel превратит в «523446».
А в ИНН, например, первые две цифры — это код региона. Для Республики Алтай он начинается с нуля — «04». Без нуля смысл номера исказится, а проверку формата ИНН вообще не пройдет.

Меняет даты под локальные настройки. Excel с удовольствием исправит номер дома «1/2» на «01.фев». Потому что Windows подсказал, что в таком виде вам удобнее считывать даты.

Побеждаем порчу данных правильным импортом

Если серьезно, в бедах виноват не Excel целиком, а неочевидный способ импорта данных в программу.

По умолчанию Excel применяет к данным в загруженном CSV-файле тип «General» — общий. Из-за него программа распознает цифровые строки как числа. Такой порядок можно победить, используя встроенный инструмент импорта.

Выбираю CSV-файл с данными, открывается диалог. В диалоге кликаю на тип файла Delimited (с разделителями). Кодировка — та, что в файле, обычно определяется автоматом. Если первая строка файла — шапка, отмечаю «My Data Has Headers».

На третьем шаге выбираю формат полей, ради него все и затевалось. Для всех столбцов выставляю тип «Text». Кстати, если кликнуть на первую колонку, зажать шифт и кликнуть на последнюю, выделятся сразу все столбцы. Удобно.

Дальше Excel спросит, куда вставлять данные из CSV — можно просто нажать «OK», и данные появятся в открытом листе.

Перед импортом придется создать в Excel новый workbook

Но! Если я планирую добавлять данные в CSV через Excel, придется сделать еще кое-что.

После импорта нужно принудительно привести все-все ячейки на листе к формату «Text». Иначе новые поля приобретут все тот же тип «General».

• Нажимаю два раза Ctrl+A, Excel выбирает все ячейки на листе;
• кликаю правой кнопкой мыши;
• выбираю в контекстном меню «Format Cells»;
• в открывшемся диалоге выбираю слева тип данных «Text».

Чтобы выделить все ячейки, нужно нажать Ctrl+A два раза. Именно два, это не шутка, попробуйте

После этого, если повезет, Excel оставит исходные данные в покое. Но это не самая твердая гарантия, поэтому мы после сохранения обязательно проверяем файл через текстовый просмотрщик.

Open Office Calc

Для работы с CSV-файлами я использую именно Calc. Он не то чтобы совсем не считает цифровые данные строками, но хотя бы не применяет к ним переформатирование в соответствии с региональными настройками Windows. Да и импорт попроще.

Конечно, понадобится пакет Open Office (OO). При установке он предложит переназначить на себя файлы MS Office. Не рекомендую: хоть OO достаточно функционален, он не до конца понимает хитрое микрософтовское форматирование документов.

А вот назначить OO программой по умолчанию для CSV-файлов — вполне разумно. Сделать это можно после установки пакета.

Итак, запускаем импорт данных из CSV. После двойного клика на файле Open Office показывает диалог.

Заметьте, в OO не нужно создавать новый воркбук и принудительно запускать импорт, все само

• Кодировка — как в файле.
• «Разделитель» — точка с запятой. Естественно, если в файле разделителем выступает именно она.
• «Разделитель текста» — пустой (все то же, что в Excel).
• В разделе «Поля» кликаю в левый-верхний квадрат таблицы, подсвечиваются все колонки. Указываю тип «Текст».

Штука, которая испортила немало крови: если по ошибке выбрать несколько разделителей полей или не тот разделитесь текста, файл может правильно открыться, но неправильно сохраниться.

Помимо Calc у нас в HFLabs популярен libreOffice, особенно под «Линуксом». И то, и другое для CSV применяют активнее, чем Excel.

Проблемы при сохранении из Calc в. xlsx

Если сохраняете данные из Calc в экселевский формат .xlsx, имейте в виду — OO порой необъяснимо и масштабно теряет данные.

Белая пустошь, раскинувшаяся посередине, в оригинальном CSV-файле богато заполнена данными

Поэтому после сохранения я еще раз открываю файл и убеждаюсь, что данные на месте.

Если что-то потерялись, лечение — пересохранить из CSV в .xlsx. Или, если установлен Windows, импортнуть из CSV в Excel и сохранить оттуда.

После пересохранения обязательно еще раз проверяю, что все данные на месте и нет лишних пустых строк.

Если интересно работать с данными, посмотрите на наши вакансии. HFLabs почти всегда нужны аналитики, тестировщики, инженеры по внедрению, разработчики. Данными обеспечим так, что мало не покажется 🙂

Подготовка файла-шаблона со
сведениями о фактах назначения
МСЗ(П)

Основные ошибки при загрузке фактов
• 1. Расчетная сумма указывается не в
соответствии с периодичностью, указанной
в ЛМСЗ;
• 2. Не указываются даты окончания действия
факта назначения;
• 3. Записи дублируются, т.е. по одному и
тому же человеку за один и тот же период
загружено несколько записей.

Заполнение файла-шаблона по фактам версии 19.5 (42 графы)
Данные по получателю МСЗ, СНИЛС –
11 цифр (формат поля 00000000000),
пол М или Ж (большими буквами)
Код ОНМСЗ,
например
УОБР,
По каждому
получателю
«Fact»
Код ПИ
Идентификатор ЛМСЗ
Идентификатор
локальной категории
Эти данные нужно брать из CSV-файла соответствующей ЛМСЗ
Продолжение на следующем слайде
Паспортные данные
получателя можно не
заполнять

Продолжение шаблона
Данные по основанию
(например: ребенок, на
которого получателю МСЗ
выплачивается компенсация
род.платы) СНИЛС – 11 цифр
(формат поля 00000000000),
пол М или Ж (большими
буквами).
Данный блок заполняется в
случае наличия лица-основания,
если такового нет, то все
столбцы блока следует
оставить пустыми. (например:
пенсия, ЖКУ педагогам,
работникам культуры)
Паспортные
данные
ребенка
можно не
вводить
Дата
решения о
назначении
МСЗ,
Период назначения МСЗ, с
какой даты (DateStart) и
по какую дату
(DateFinish). В следующем
слайде – указания по
датам действия
Форма
представления
МСЗ в
соответствии с
формой
предоставления,
указанной в ЛМСЗ
(формат поля 00)
Критерий нуждаемости:
Нет – если не указаны критерии
нуждаемости в ЛМСЗ в реестре МСЗ во
вкладке «Категории получателей» по
выбранной категории;
Да – если указаны (например: доход
ниже прожиточного минимума)
Эти даты должны быть в пределах
действия ЛМСЗ, указанные в реестре МСЗ
Суммаэквивалент, если
в Монетизации
указано «Да», то
указать сумму
Единица
измерения
формат поля 00)
Признак
монетизации (Нет –
для денежной, Да для
других форм)
Сумма (для
денежной формы
предоставления)
Количество (для
остальных форм
предоставления)

Даты назначения МСЗ, если сумма меняется
Пенсия муниципальным служащим индексируется ежегодно.
Например: Иванов И.И. – принято решение о назначении ему муниципальной
пенсии 01.07.2017.
С 01.01.2019 размер пенсии = 27000 рублей
• decision_date – 01.07.2017
• dateStart – 01.01.2019
• dateFinish – 31.12.2019
• Amount – 27000,00
С 01.01.2020 размер пенсии = 30000 рублей
• decision_date – 01.07.2017
• dateStart – 01.01.2020
• dateFinish – 31.12.2020
• Amount – 30000,00
Списки получателей пенсии загружаем один раз в год в начале года, при этом
сумму указываем месячную, так как в ЛМСЗ периодичность по данной мере
указана – ежемесячная.
В течении года можно загружать сведения по новым получателям пенсии, по
которым решение было принято в текущем году.

Даты назначения МСЗ, если сумма не меняется
Например: ЖКУ педагогическим работникам или работникам культуры в
сельской местности в некоторых районах не индексируется, из года в
год платится одна и та же сумма (сумма не меняется)
Например: Иванова А.А. – принято решение о назначении ей
компенсационной выплаты за ЖКУ 10.08.2019 начиная с 01.09.2019 в
размере 1100 рублей.
• decision_date (дата решения) – 10.08.2019
• dateStart (дата начала действия) – 01.09.2019
• dateFinish (дата окончания дейтсвия) – (бессрочно, ничего не
указывать)
• Amount (сумма) – 1100,00
Если сумма назначения не меняется, то списки загружаем один раз с
датой начала действия факта назначения и дальше в течении года
загружать сведения только по новым получателям, т.е. по лицам,
которые поступили на работу в текущем году и решение по ним было
принято в текущем году. А если специалист уволился, то надо вносить
изменения в КПИ ЕГИССО, найти факт назначения по данному
гражданину и проставить дату окончания действия.
Сумму указываем месячную, если в ЛМСЗ периодичность по данной
мере указана – ежемесячная.

Сумма или количество (Amount)
Если в мере указан способ предоставления:
• денежный, то в данном поле указываем сумму
в рублях;
• Натуральный – в штуках, упаковках;
• Льгота – в штуках, процентах.
Размер зависит от периодичности, которая
указана в ЛМСЗ.
Например: если периодичность в ЛМСЗ:
месячная – сумма за месяц,
квартальная – сумма за квартал,
ежегодная – сумма за год,
единовременная – полная сумма

Форма предоставления (FormCode)
Код формы предоставления
• “01” – Денежная
• “02” – Натуральная
• “03” – Льготы
• “04” – Услуги
Форма предоставления обязательно должна
соответствовать форме предоставления,
указанной в ЛМСЗ (формат поля 00)

Единица измерения (measuryCode)
Код единицы измерения
• 01 Рубль
• 03 Штука
• 04 Упаковка
• 05 Процент
• 06 Международная единица биологической активности
• 07 Килограмм
• 08 Час
Если форма предоставления, то единица измерения:
– Денежная – 01 (Рубль),
– Натуральная – 03, 04 или 07 (штука, упаковка, килограмм),
– Льгота – 03, 05 (штука, процент),
– Услуги – 08 (час)
(формат поля 00)

Признак монетизации (monetization)
• Для льгот (03) и МСЗ в натуральной форме (02)
указывается «Да»
• Для МСЗ в денежной форме (01) и услуг (04)
указывается «Нет»
Если указывается «Да», то в графе
«equivalentAmount» указать сумму-эквивалент,
исходя из периодичности ЛМСЗ (если в ЛМСЗ
периодичность ежемесячная, то указать сумму
за месяц)
Да или Нет пишутся с заглавной буквы (первая
буква)

Заполнение шаблона, если имеется несколько оснований
для получателя МСЗ
После строки с типом Fact может быть указано от 0 до N строк с типом Reason.
Строка с таким типом содержит информацию о дополнительном лице,
являющемся основанием для назначения меры.
Например: семья с тремя и более детьми получает землю, тогда
по строке с типом Fact в графах по лицам-основаниям указывается первый член семьи,
остальные в следующих строках с типом Reason
(из справки о составе семьи).
По строке с типом «Reason» заполняются графы только по лицам-основаниям, все
остальные графы не заполняются.

Заполнение сведений по лицам-основаниям
• По лицам основаниям заполняются только графы
SNILS_reason, FamilyName_reason, Name_reason,
Patronymic_reason, Gender_reason,
BirthDate_reason, doctype_reason,
doc_Series_reason, doc_Number_reason,
doc_IssueDate_reason, doc_Issuer_reason. Красным
цветом выделены графы обязательные для
заполнения
• По графе RecType указывается Reason
• Больше никакие графы не заполняются!!!
• Строка с основаниями должна располагаться
строго под строкой со сведениями о получателе,
один из лиц-оснований указывается в строке по
получателю.

Питание в школах
указать дату начала и
окончания учебного года
Учащийся из многодетной семьи, поэтому в
основаниях указать состав семьи
03-льгота
03 – штука
указать сумму-эквивалент за месяц
(если в ЛМСЗ периодичность
ежемесячная, например: 80 рублей*30
учебных дней=2400)

В файле-шаблоне есть лист «Информация по заполнению», там
указаны наименования граф шаблона и описание и возможные
значения, обязательность поля

• Файл-шаблон с фактами назначения
необходимо сохранить файл в формате
EXCEL, затем CSV (разделители-запятые)
• CSV-файл пропустить через утилитуконвертер из CSV-файла в XML-файл

Загрузка данных о фактах
назначения МСЗ из CSV-файла в
ЕГИССО

При нажатии на кнопку «Выгрузить» сформируется
CSV-файл с данными о локальных мерах и
соответствующих им категориях из реестра ЛМСЗ с
идентификаторами, которые были присвоены им в
ЕГИССО

Здесь находятся CSV-файлы с
локальными мерами и категориями
и их идентификаторами

Содержимое CSV-файла
Для заполнения шаблона по фактам назначения нужны
идентификатор ЛМСЗ и идентификатор локальной категории,
например для меры «Компенсация части родительской платы»

ВНИМАНИЕ!!!
• При редактировании ЛМСЗ и отправке в
ЕГИССО изменяется идентификатор ЛМСЗ.
• Поэтому перед загрузкой файлов со
сведениями о фактах назначения
необходимо сделать новую выгрузку CSVфайла и в шаблоне указать актуальные
идентификаторы ЛМСЗ и категорий

Запустить утилиту-конвертер
Кликните мышкой по данному файлу.
Утилита запустится с помощью JAVA

Указать код поставщика
информации, первые 4 цифры
Если указана одна папка для исходных файлов и
файлов для сохранения, то в конце наименования
второй папки укажите , тогда будет доступна
кнопка «Запуск»

• В случае успешной проверки CSV-файла
сформируется XML-файл, в случае ошибок
будут указаны ошибки на экране «Журнал
обработки»;
• Для исправления ошибок используйте файл
формате EXCEL, т.к. при открытии CSVфайла теряются все форматы;
• Полученный XML-файл необходимо
подписать и загрузить в ЕГИССО через
раздел «Загрузка подписанных XMLфайлов»

Загрузка подписанных XML-файлов
Выбрать XML-файл с фактами назначения ЛМСЗ
Выбрать XML-файл, подписанный ЭЦП,
Здесь загрузить XML-файл с фактами назначения ЛМСЗ

Проверка результатов загрузки
По умолчанию устанавливается
последний месяц
Посмотреть
протокол загрузки
Нажать на эту кнопку, и в фильтре можно указать тип данных «Реестр фактов
назначения»
Результат загрузки

Протокол обработки файла
Посмотр детальной
информации, нажать
на эту кнопку

Информация по ошибке
Информация по ошибке

Исправление ошибок в шаблоне в случае выявления
ошибок при загрузке XML-файла в ЕГИССО
• Ошибки исправлять в XLS-файле;
• В файле для загрузки (в шаблоне) в
оставить только те записи, по которым
были исправлены ошибки. Все остальные
записи удалить!
• В противном случае все правильные
записи, которые были ранее загружены,
загрузятся повторно!!! Это приведет к
дублированию записей

Реестр фактов назначения
По умолчанию установлена
дата решения о назначении 3
последних месяца. Можно
удалить эти даты нажав на
значок с правого края.
Здесь можно указать СНИЛС
гражданина
Выбор нужной ЛМСЗ из выпадающего списка , нажать «Найти»

Пример задвоенных записей
Указали СНИЛС гражданина

Удаление дублирующихся записей
Выбираем из выпадающего
списка нужную причину
редактирования и нажимаем
«Сохранить»

Реестр изменений фактов назначений
Перейти в реестр изменений
Осталась только кнопка просмотра

Массовое изменение дат окончания в фактах назначения
Нажать на эту кнопку
выделятся все записи на
данной странице

Выбор причины редактирования
Из выпадающего списка выбираем нужную
причину

Указание даты окончания
Указываем дату окончания факта

Компенсация части родительской платы за содержание ребенка в
образовательных организациях, реализующих основную
общеобразовательную программу дошкольного образования
• В соответствии с утвержденным Минтрудом России
классификатором МСЗ данная мера имеет денежную форму
предоставления.
• В связи с этим, те районы, у которых в ЛМСЗ была указана форма
предоставления «Льгота» и факты загружались в процентах,
необходимо:
1. Закрыть данную меру, то есть указать дату окончания (например:
31.12.2019, если факты за 2020 год не вводили), при этом в коде
ЛМСЗ добавить слово «закрыта» (например: 0544_закрыта)
2. Закрыть факты назначения, то есть, если в реестре фактов
назначения по данной мере есть факты без даты окончания
действия (бессрочные), то в массовом режиме проставить дату
окончания равной дате окончания ЛМСЗ.
3. Добавить новую ЛМСЗ (например: с кодом 0544_денежная) и
заполнить ее в соответствии со стандартами.
4. Новые факты назначения вводить по новой ЛМСЗ, т.е. в шаблоне
указать идентификаторы этой меры.

• Районы, у которых в ЛМСЗ была указана
форма предоставления «Льгота», но факты НЕ
загружались:
1. Откорректировать ЛМСЗ в соответствии со
стандартами (форма предоставления
«Денежная» (в режиме полной
корректировки). При этом идентификатор
ЛМСЗ поменяется.
2. При вводе фактов назначения указать новый
идентификатор ЛМСЗ, форму предоставления
01-денежная, код единицы измерения 01рубли

Таблица по реестру актуальных ЛМСЗ района с указанием
основных показателей для формирования фактов

Спасибо за внимание!