IT-инфраструктура для бизнеса и творчества
Разработка
NTA

Как обезличить конфиденциальную информацию в документах Excel?

Давайте рассматривается один из способов защиты данных при помощи Python и COM-соединения с файлом Excel.

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

Решить эту задачу нам поможет Python. Одним из вариантов получения доступа к данным документа, обычно это excel, является использование COM-соединения. Читатель наверняка спросит: «Почему не использовать замечательный модуль pandas?» Ответ на этот вопрос мы дадим позже. Для выбранного нами метода решения задачи, будем использовать модуль win32com. Открытие документа для чтения/записи происходит следующим образом:

import win32com.client as wclient path = r"C:\Users\...\data.xlsx" # путь к документу excel excel = wclient.Dispatch("Excel.Application") workbook = excel.Workbooks.Open(path)

Теперь можно использовать методы VBA (Visual Basic for Applications) из кода Python, обращаясь к объекту sheet. Перебрать все ячейки столбца, содержащего личные данные, позволяет метод Range():

r = sheet.UsedRange.Rows.Count # получаем количество строк for i, cell in enumerate(sheet.Range("B2:B{}".format(r))): # B – столбец в excel message = cell[0].value # получаем содержимое ячейки

В случае, если определённый столбец содержит только, например, номера банковских карт (БК) клиента, задача сильно упрощается. На практике встречаются ситуации, когда номер БК находится в тексте, и так для каждой ячейки столбца. Пример текста: «Клиент Иванов Иван Иванович осуществил денежный перевод на карту 1234 XXXX XXXX XXXX…» (в примере «X» соответствует отдельной цифре). Помимо номера БК в тексте может содержаться номер мобильного телефона и паспортные данные клиента. В таком случае для поиска личных данных придётся использовать регулярные выражения.

import re # импортируем модуль re для работы с регулярными выражениями query_storage = {'card': r'\b\d{4}\s?\d{4}\s?\d{4}\s?\d{4}\b'}

Согласно регулярному выражению, сохранённому в словаре query_storage, будет производиться поиск набора из 16 цифр, которые могут быть записаны без пробелов, либо разделены пробелами на группы из четырёх цифр. Разумеется, в тексте номер карты может встречаться не один раз, либо возможно появление номеров других карт, следовательно, необходимо получить позицию всех номеров БК:

for key in query_storage: reg_obj = re.compile(query_storage[key]) result = [[m.start(), m.end()] for m in reg_obj.finditer(text)]

В переменной result будет сохранён список позиций начала и конца всех найденных номеров БК, либо также других данных, если для них в словаре query_storage были записаны регулярные выражения. Зная «координаты» нужных данных, мы сможем заменить их в тексте, например, символами «*».

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

import win32com.client as wclient import re query_storage = {'card': r'\b\d{4}\s?\d{4}\s?\d{4}\s?\d{4}\b'} path = r"C:\Users\...\data.xlsx" def encrypt(text): changed = False for key in query_storage: reg_obj = re.compile(query_storage[key]) result = [[m.start(), m.end()] for m in reg_obj.finditer(text)] if len(result) != 0: changed = True for coord in result: fragment = text[coord[0]:coord[1]] if '*' in fragment: continue upd_fragment = str() for c in fragment: if c.isdigit(): upd_fragment += '*' else: upd_fragment += c text = text.replace(fragment, upd_fragment) return text, changed def main(): excel = wclient.Dispatch("Excel.Application") workbook = excel.Workbooks.Open(path) sheet = workbook.ActiveSheet for i, cell in enumerate(sheet.Range("B2:B{}".format(r))): message = cell[0].value message_encrypted, flag = encrypt(message) if flag: sheet.Range("B{}".format(i + 2)).value = message_encrypted workbook.Save() # после завершения работы с документом сохраняем его workbook.Close() # и закрываем excel.Quit() if __name__ == "__main__": main()

В строке, отвечающей за обновление текста ячейки (в теле условия if flag:) к переменной i, изначально равной 0, прибавляется 2: первой строке соответствует индекс 1, в обрабатываемой таблице столбцы имели заголовки, поэтому данные начинаются со строки с индексом 2.

Таким образом, после выполнения кода, приведённый выше фрагмент текста будет выглядеть следующим образом: «Клиент Иванов Иван Иванович осуществил денежный перевод на карту **** **** **** ****…». Добавив в словарь query_storage регулярные выражения для номера мобильного телефона и/или паспортных данных, мы сможем скрыть и их. Это поможет избежать утечки персональных данных клиентов.

Теперь вернёмся к pandas. Конечно, использовать этот модуль можно, но при обработке крупных файлов может возникнуть проблема нехватки оперативной памяти, потому что pandas загружает данные в ОЗУ, помимо этого загрузка объёмного файла, как и его последующее сохранение занимают длительное время, хотя обработка данных происходит очень быстро. Мы провели некоторые тесты с файлом размером около 40 Мб, содержащим 28500 строк, результаты представим в таблице.

Как видно, при использовании COM-объекта, время обработки значительно выше, чем у pandas, но при сохранении документа ситуация обратная, и из-за этого общее время работы pandas больше. Помимо этого, на практике мы столкнулись с ситуацией, когда при использовании движка’ xlsxwriter’ с pandas, при наличии web-ссылок, в тексте во время сохранения происходило преобразование всего текста в одну ссылку, и строки, содержащие эти ссылки, отбрасывались и не попадали в сохранённый файл из-за недопустимо большой длины ссылок. И ещё один момент – использование COM-объекта позволяет делать изменения в исходном файле, не создавая копии. В нашем случае перечисленные факторы и сыграли в пользу выбора COM-соединения для практического применения.

(function () { let cdnUrl = `https://specialsf378ef5-a.akamaihd.net/SelectelBranding/images/` let previousArticleNumber = null let currentArticleNumber = 0 let platform = 'Desktop' let articles = [ { name: 'camera', url: `${cdnUrl}CameraCat`, text: 'умную камеру для\u00A0наблюдения за\u00A0котиками', link: 'https://vc.ru/selectel/306690', num: 3 }, { name: 'chill', url: `${cdnUrl}ChillCat`, text: 'трекер, который подскажет, когда пора отдохнуть', link: 'https://vc.ru/promo/288561-eye-tracker', num: 1 }, { name: 'cloud', url: `${cdnUrl}CloudCat`, text: 'котика: даёшь ему «пять», а\u00A0он делает бэкап в облако', link: 'https://vc.ru/dev/294799-maneki-neko', num: 2 } ] let buttonCycle = document.querySelector('.button--cycle') let buttonChoose = document.querySelector('.button--choose') let buttonMobile = document.querySelector('.button--mobile') let textField = document.querySelector('.selectel-footer-subtitle') let imageAgent = document.querySelector('.image--agent') let banner = document.querySelector('.selectel-footer') buttonCycle.addEventListener('click', cycleClick) buttonChoose.addEventListener('click', () => sendEvent(`Promo ${articles[currentArticleNumber].num} Left`, 'Click')) buttonMobile.addEventListener('click', () => sendEvent(`Promo ${articles[currentArticleNumber].num} Left`, 'Click')) let media = window.matchMedia("(max-width: 570px)") media.addEventListener('change', matchMedia) function matchMedia() { if (media.matches) { platform = 'Mobile' } else { platform = 'Desktop' } update() } matchMedia() function cycleClick(event) { sendEvent(`Promo ${articles[currentArticleNumber].num} Right`, 'Click') if (event) { event.preventDefault() event.stopPropagation() } window.open('https://vc.ru/tag/selectelDIY', '_blank') //cycle(event) } function cycle(event) { // incrementArticleNumber() textField.innerHTML = generatedText() imageAgent.src = articles[currentArticleNumber].url + platform + '.svg?3' imageAgent.setAttribute("class", "") imageAgent.classList.add('image--agent', articles[currentArticleNumber].name) banner.href = articles[currentArticleNumber].link } function update() { banner.href = articles[currentArticleNumber].link imageAgent.src = articles[currentArticleNumber].url + platform + '.svg' textField.innerHTML = generatedText() } function incrementArticleNumber() { previousArticleNumber = currentArticleNumber if (currentArticleNumber >= articles.length - 1) { currentArticleNumber = 0 } else { currentArticleNumber++ } } const sendEvent = (label, action = 'Click') => { const value = `SelectelDIY — loc: Footer — ${label} — ${action}`; if (window.dataLayer !== undefined) { window.dataLayer.push({ event: 'data_event', data_description: value, }); } }; function generatedText() { let defaultText if (platform === 'Desktop') { defaultText = `Мы тут собрали %text%. Хотите научим?` } else { defaultText = `Мы тут собрали %text%.` } return defaultText.replace('%text%', articles[currentArticleNumber].text) } function getRandom(min, max) { min = Math.ceil(min) max = Math.floor(max) return Math.floor(Math.random() * (max - min + 1)) + min } (function create() { currentArticleNumber = getRandom(0, articles.length - 1) cycle() let page = document.querySelector('.page--entry') if (page) { function insertAfter() { let parents = page.querySelectorAll('[data-id="7"]') let referenceNode = parents[0] referenceNode.parentNode.insertBefore(banner, referenceNode.nextSibling); loaded() } setTimeout(() => insertAfter(), 0) } }()) function loaded() { banner.classList.add('loaded') } loadImages([ `${cdnUrl}CameraCatDesktop.svg`, `${cdnUrl}ChillCatDesktop.svg`, `${cdnUrl}CloudCatDesktop.svg`, `${cdnUrl}CameraCatMobile.svg`, `${cdnUrl}ChillCatMobile.svg`, `${cdnUrl}CloudCatMobile.svg?3`, ]) function loadImages(urls) { return Promise.all(urls.map(function (url) { return new Promise(function (resolve) { var img = document.createElement('img'); img.onload = resolve; img.onerror = resolve; img.src = url; }); })); } }())
0
0 комментариев
Популярные
По порядку
Читать все 0 комментариев
5 неожиданных сервисов Почты России, которые постоянно выручают меня в бизнесе

В прошлом году я открыл для себя совершенно удивительные фишки Почты России. С тех пор апка Почты у меня на главном экране в айфоне.

Обновление лэндинга для клуба маркетологов

Основная ценность любого клуба – комьюнити, поэтому критически важно было отладить процесс привлечения новой аудитории и удержания старой. В этой статье мы расскажем о первом пункте. До этого был только лендинг, который редко продвигался в платной рекламе, был ориентирован на пользователей уже знакомых с брендом Convert Monster.

Кнопка 112 - приложение с определением адреса и большой кнопкой вызова
ИТ-компании попросили правительство продлить льготную ставку 0% для держателей акций высокотехнологичных компаний Статьи редакции

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

Завод по производству идей. Как работают акселераторы, зачем они нужны стартапам и куда идти с идеей прямо сейчас

По данным Startup Genome, 9 из 10 стартапов терпят неудачу. Возможных причин «смерти» много: недостаточно протестированная гипотеза, неподтвержденная юнит-экономика, неверная стратегия или просто неудача в подходе к продажам.

Что Tele2 предлагает клиентам в «черную пятницу»

На главной распродаже года клиентов компании ждут сразу несколько интересных предложений: скидки на смартфоны, пакеты SMS и безлимитный трафик на YouTube, Яндекс.Карты, Яндекс.Навигатор.

Как за 150 тыс. руб. мы увеличили продажи на 67 млн. руб. в месяц

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

@АнтиспамБот — когда ты реальная заноза в з@днице, или как давали отпор «П0шлым_Дев4енкам»

Прошло меньше двух недель с того дня, как в статье на vc.ru я рассказал про @antispamname_bot, предназначенный для борьбы со спамом в никнейме юзеров телеграмма вида «PEАЛЬНЫЕ_ZНAКОМСТVА» и «ПОИСК_PAPTHЕRОV». В новой статье я поделюсь тем, как прошли эти 10 дней: как бота подключили огромные чаты медиа изданий с аудиторией 100 000+ человек, чаты…

Авито возглавила три рейтинга App Annie
Хочу кухню как у подруги: зачем в Циан сделали поиск квартир по фото

Рассказывает Юлия Зыкова, руководитель команды «Аудитория» в Циан.

Wildberries обязал покупателей оплачивать возвраты товара, если те получены или уже переданы в доставку
null