Чем открыть большой csv файл
Редактируем CSV-файлы, чтобы не сломать данные
Продукты 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» — общий. Из-за него программа распознает цифровые строки как числа. Такой порядок можно победить, используя встроенный инструмент импорта.
Запускаю встроенный в Excel механизм импорта. В меню это «Data → Get External Data → From Text».
Выбираю CSV-файл с данными, открывается диалог. В диалоге кликаю на тип файла Delimited (с разделителями). Кодировка — та, что в файле, обычно определяется автоматом. Если первая строка файла — шапка, отмечаю «My Data Has Headers».
Перехожу ко второму шагу диалога. Выбираю разделитель полей (обычно это точка с запятой — semicolon). Отключаю «Treat consecutive delimiters as one», а «Text qualifier» выставляю в «
На третьем шаге выбираю формат полей, ради него все и затевалось. Для всех столбцов выставляю тип «Text». Кстати, если кликнуть на первую колонку, зажать шифт и кликнуть на последнюю, выделятся сразу все столбцы. Удобно.
Дальше Excel спросит, куда вставлять данные из CSV — можно просто нажать «OK», и данные появятся в открытом листе.
Перед импортом придется создать в Excel новый workbook
Но! Если я планирую добавлять данные в CSV через Excel, придется сделать еще кое-что.
После импорта нужно принудительно привести все-все ячейки на листе к формату «Text». Иначе новые поля приобретут все тот же тип «General».
После этого, если повезет, Excel оставит исходные данные в покое. Но это не самая твердая гарантия, поэтому мы после сохранения обязательно проверяем файл через текстовый просмотрщик.
Альтернатива: Open Office Calc
Для работы с CSV-файлами я использую именно Calc. Он не то чтобы совсем не считает цифровые данные строками, но хотя бы не применяет к ним переформатирование в соответствии с региональными настройками Windows. Да и импорт попроще.
Конечно, понадобится пакет Open Office (OO). При установке он предложит переназначить на себя файлы MS Office. Не рекомендую: хоть OO достаточно функционален, он не до конца понимает хитрое микрософтовское форматирование документов.
А вот назначить OO программой по умолчанию для CSV-файлов — вполне разумно. Сделать это можно после установки пакета.
Итак, запускаем импорт данных из CSV. После двойного клика на файле Open Office показывает диалог.
Заметьте, в OO не нужно создавать новый воркбук и принудительно запускать импорт, все само
Помимо Calc у нас в HFLabs популярен libreOffice, особенно под «Линуксом». И то, и другое для CSV применяют активнее, чем Excel.
Белая пустошь, раскинувшаяся посередине, в оригинальном CSV-файле богато заполнена данными
Поэтому после сохранения я еще раз открываю файл и убеждаюсь, что данные на месте.
После пересохранения обязательно еще раз проверяю, что все данные на месте и нет лишних пустых строк.
Если интересно работать с данными, посмотрите на наши вакансии. HFLabs почти всегда нужны аналитики, тестировщики, инженеры по внедрению, разработчики. Данными обеспечим так, что мало не покажется 🙂
Что делать, если набор данных слишком большой для сетки Excel
Если вы открыли в Excel файл с большим набором данных, например текстовый (TXT) или CSV-файл, возможно, вы увидели предупреждение: «Этот набор данных слишком большой для сетки Excel. Если вы сохраните эту книгу, вы потеряете данные, которые не были загружены.Это означает, что набор данных превышает количество строк или столбцов, доступных в Excel, поэтому некоторые данные не были загружены.
Чтобы не потерять данные, необходимо соблюдать дополнительные меры предосторожности.
Откройте файл в Excel для ПК,используя get Data (Получить данные). Если у вас есть приложение Excel для ПК, вы можете загрузить полный набор данных и проанализировать его с помощью таблиц с помощью Power Query.
Не сохранения файла в Excel. Если сохранить файл через исходный, вы потеряете все данные, которые не были загружены. Помните, что набор данных также неполный.
Сохранение усеченной копии. Если вам нужно сохранить файл, перейдите в папку > «Сохранить копию». Затем введите другое имя, которое будет ясно, что это усеченная копия исходного файла.
Открытие набора данных, превышаго ограничения Excel на сетку
С помощью Excel для ПК можно импортировать файл с помощью средства «Получить данные», чтобы загрузить все данные. Данные будут отображаться не больше, чем количество строк и столбцов в Excel, однако полный набор данных уже создан, и вы сможете анализировать его без потери данных.
Откройте пустую книгу в Excel.
На вкладке «Данные» > «Из текста/CSV> найдите файл и выберите «Импорт». В диалоговом окне предварительного просмотра выберите «Загрузить в. « > отчета.
После загрузки используйте список полей для у упорядочений полей в pivotTable. Для сведения данных в ней будет работать весь набор данных.
Вы также можете отсортировать данные в pivotTable илиотфильтроватьданные в ее.
Ограничения форматов файлов Excel
Чтобы предотвратить ограничение в Excel, не забудьте использовать для этого формат XLSX вместо XLS. Если вы знаете, что набор данных превышает ограничение XLSX, используйте альтернативные обходные пути для открытия и просмотра всех данных.
Совет: Убедитесь, что при открытие набора данных в Excel были импортироваться все данные. Вы можете проверить количество строк или столбцов в исходных файлах, а затем подтвердить их совпадение в Excel. Для этого вы можете выбрать всю строку или столбец и просмотреть количество в строке состояния в нижней части Excel.
Help! CSV объемом в 3,5 Гб
если пользуешь винду:libreoffice + файл подкачки сделай гигов эдак 5-6
Должно помочь, но будет дико тормозить
Еще может помочь CSV Splitter любой на выбор.
Просто разбей на файлы поменьше и все
Макросы VBA для Excel: выделение значений цветом (PERSONAL.xlsb, переменные, цикл For Each, всплывающие окна, заливка ячеек цветом)
Вопреки сказанному в конце прошлого поста, решил второй пост писать про Excel, потому что на макросы для него спрос намного выше, судя по всему. Пример будет практически малополезный, но зато попроще.
Сразу договоримся вот о чем: у меня стоит Office 2016 на домашнем ноуте и 2019 на рабочем компьютере. Они почти не отличается интерфейсом. Я прошу разрешить мне не расписывать, где что нажимать в других версиях, чтобы не раздувать посты еще сильнее. Если у вас что-то не будет получаться, просто спросите в комментариях, и я вам помогу.
Посты я теперь буду называть так, как этот, чтобы было понятно, о чем он конкретно и какие темы VBA в нем разбираются.
Но сначала кое-что важное:
Материалы данного поста созданы непрофессиональным программистом.
Я не претендую на гордое звание преподавателя, коуча или сенсея.
Я буду показывать решения, которые просто будут работать.
Критика и советы горячо приветствуются.
При, по крайней мере, написании поста ни один настоящий программист не пострадал.
1. Что такое VBA? Зачем писать на нем макросы и что нужно, чтобы они работали?
Об этом писал в первом посте. В вашего позволения, повторяться не буду.
2. Мне прислали книгу в Excel, где там макросы?
Если VBA установлен в ваш эксель, то все манипуляции с макросами нужно проводить во вкладке «Разработчик».
Если вы открыли Excel, а вкладки нет, ее нужно включить. Для этого нажмите A, C, вверх, B, вверх, B, A, вниз откройте настройки («Параметры» в самом низу, если нажать «Файл» сверху слева). Слева в списке выберите «Настроить ленту». У вас будет два списка с набором команд и вкладок. В правом найдите строчку «Разработчик» и включите напротив нее галочку, вот так:
Кстати, неплохо бы еще прогуляться в «Центр управления безопасностью», нажать кнопку «Параметры центра управления безопасностью…», затем выбрать «Настройки макросов» и выбрать подходящий вам пункт. У меня лично так:
Не хочу вас пугать, но буду на VBA можно написать и вредоносную программу, которая запустится сразу после открытия файла, поэтому включать все макросы по умолчанию стоит только на ваш страх и риск, если вы часто ими пользуетесь и вам сильно надоели всплывающие окошки с предупреждениями. Лично я на всякий случай выключаю поддержку макросов перед тем, как открыть файл, который мне кто-то малознакомый присылает. За пять лет проблем не было.
Итак, если вы все сделали правильно, у вас появится такая вкладка:
Кнопка с подписью «Visual Basic» откроет редактор проектов VBA, который почти такой же, как в прошлом посте про AutoCAD.
Через кнопку «Макросы» можно увидеть и запустить макросы, которые сейчас доступны и загружены в Excel.
Еще есть кнопка поменьше, «Запись макроса». Нажав на нее, можно сделать макрос без программирования. Можете попробовать сами, я объяснять подробно не буду, потому что лично мне кажется, что написанные таким образом макросы подходят только для имитации бурной деятельности разовых и крошечных задач. Нам эта кнопка пригодится чуть ниже для кое-чего другого.
3. Как написать свой макрос для Excel?
В редакторе проектов VBA вы увидите объекты листов, книги, а также можете добавлять свои модули и формы.
Опять-таки, пока я советую добавлять новые модули, а не писать их в книгах или листах. Со временем нам попадется ситуация, когда это будет необходимо. Если вообще будет.
В общем, добавляем модуль, чтобы потом писать туда код:
Решить такую проблему помогает специальная книга PERSONAL.xlsb, которая в скрытом режиме открывается автоматически вместе с самим Excel. В ней можно хранить макросы, и тогда они всегда будут доступны. На предыдущем изображении ее как раз видно в редакторе VBA.
У вас ее, скорее всего нет.
Чтобы она появилась, нужно три раза позвать Битлджуса сделать макрос через «Запись макроса». Нажимаете эту кнопку, потом обязательно в списке «Сохранить в» выбираете «Личная книга макросов», что-нибудь делаете (например, выделяете ячейку, пишете в нее что-нибдуь) и нажимаете «Остановить запись». Потом нажимаете кнопку «Остановить запись», она будет на месте кнопки «Запись макроса».
Если вы теперь откроете редактор VBA (нажав кнопку «Visual Basic»), то в списке проектов увидите книгу PERSONAL.xlsb, а в ней модуль с кодом того, что вы делали, пока записывали макрос. Советую удалить его, но я вам не командир.
Теперь мы можем писать туда свой код, и он будет работать во всех книгах, если мы его запустим через кнопку «Макросы».
Давайте прикинем задачу.
Например, мы владеем приютом для кошек, и мы отслеживаем вес наших подопечных. Все же любят котиков? В целях демонстрации на еженедельном собрании мы хотели бы эффектно и быстро подстветить красным цветом тех котеек, кому пора на диету. Представим данные в таком виде:
Пока давайте заранее решим, что максимальная снаряженная масса котиков – 5 кг. Тогда код будет такой:
Работает он так: выделяете какой-то диапазон ячеек (или одну), запускаете макрос. Те ячейки, в которых написано число больше 5 заливаются красным.
Давайте разбирать код подробно.
В самой первой строке:
мы с помощью ключевого слова Sub объявляем процедуру (процедура просто выполняет какой-то список команд по порядку и завершает работу) findLargeValues без параметров, потому что после ее названия идут пустые круглые скобки ().
Дальше идут строки:
Dim maxWeight As Integer
здесь мы используем ключевое слово Dim, чтобы объявить переменную и через другое обязательное ключевое слово As задаем ей тип Integer (целое число).
Но нам мало объявить машине, что теперь будет такая переменная такого типа, VBA по умолчанию придаст ей значение 0 (это так для Integer и Double, например). Поэтому в следующей строке мы присваиваем ей значение 5, с помощью простого знака равенства.
процедуру мы объявляем через Sub
функцию (которая выполняет команды, но потом еще возвращает какое-то значение) через Function
все переменные через Dim.
Далее идет цикл For Each … In … Next.
Я не случайно уже второй раз использую его в примерах, потому что он очень часто оказывается полезен, поэтому лучше понять, как он работает, как можно раньше.
Я понимаю, что это непросто, но давайте представим, что вы – Гринч Санта Клаус.
Вся ваша ежегодная работа будет описываться циклом:
Для Каждого ребенка В спискеХороших
На языке VBA это будет выглядеть так:
For Each child In niceList
Этот цикл берет массив или коллекцию (простыми словами – какой-то набор чего-то), перебирает по очереди каждый ее элемент, к каждому из которых мы можем обращаться через переменную, чтобы производить с ним какие-то действия.
В примере с Сантой, коллекцией будет niceList, переменной – child. Циклю For Each … In … Next будет брать каждого child, который содержится в niceList, и вызывать для него метод makeAGift (читай – дарить подарок ему).
В макросе, который мы пишем, коллекцией послужит Selection. В Excel Selection – это коллекция того, что выделено на экране. Но может быть выделен диапазон ячеек, изображение и еще много чего. Мы этот момент сегодня рискованно опускаем, и считаем, что выделяется диапазон ячеек с числами. Переменную мы назвали cell, просто потому что это будет понятно.
Дальше идет условный оператор, If Then Else End if.
Я уже разобрал его в первом посте, но давайте еще раз.
— это что-то, что можно подать машине как Boolean, как величину True или False (Да или Нет).
Если = True, то выполняется код1. В противном случае выполняется код2.
Если Else вообще нету, то код1 выполняется, если = True, а в противном случае вообще ничего не происходит в программа просто идет по строчкам дальше, что там написано после End if.
Что мы имеем в нем:
cell – это та самая переменная из цикла For Each … in … Next. Она олицетворяет одну отдельно взятую ячейку из выделенного диапазона.
Через cell.Value мы получаем доступ к тому, что написано в этой ячейке, к ее значению.
Функция Val(“строка”) извлекает из строки число, если оно там вообще есть. Мы применяем ее, чтобы обезопасить себя от ситуации, когда в Excel e ячейки cell значание будет, например «5а». В этом случае функция Val(cell.Value) вернет нам просто число 5.
Далее все это выражение просто сравнивается с нашей переменной maxWeight.
Таким образом, все это выражение Val(cell.Value) > maxWeight будет True, если в ячейке cell будет написано число больше 5, и будет False, если оно будет меньше или равно 5.
Ну и дальше мы видим, что в случае True, то есть когда вес нашего отдельно взятого кота, который записан в cell, больше 5 кг, выполняется «код1»:
Он обращается к свойству Interior, которое есть у ячейки cell (а точнее, у всех ячеек и диапазонов ячеек в Excel, у каждой со своим значением). У этого свойства, в свою очередь, есть своё свойство Color, которому мы присвоили значение vbRed. Иными словами, назначает ей красный цвет заливки.
В случае False в условии происходит то же самое, только цвет присваивается xlNone, то есть «никакой», «без заливки».
Цвета, начинающиеся с vb… «вшиты» в сам Visual Basic, они бывают
xlNone – это специальный для Excel цвет, который обозначает отсутствие цвета. Кроме как в экселе работать код с его использованием нигде больше не будет.
Если вам хочется повыёживаться использовать какой-то другой цвет, то можно написать
cell.Interior.Color = RGB(rr, gg, bb),
а вместо rr, gg и bb подставить число от 0 до 255, обозначающее интенсивность красного, зеленого и синего соответственно.
Последняя строка в нашем коде End Sub обозначает, что все, что процедура кончилась.
Результат его выполнения будет такой:
Такой вот нехитрый макрос.
Но давайте добавим в него чуть-чуть универсальности и интерактивности.
Давайте очень легко и просто сделаем так, чтобы максимальное значение веса можно было менять прямо перед выполнением макроса.
Изменим код вот так
То есть, вместо скучной пятерки вы получаем число от пользователя через метод InputBox:
Вообще, этот метод в полной форме выглядит так:
InputBox(prompt, [ title ], [ default ], [ xpos ], [ ypos ], [ helpfile, context ])
prompt – обязательный параметр, сообщение в самом окошке, значение String (строка, пишется обязательно в кавычках);
[title] – необязательный параметр, заголовок этого окошка, тоже значение String;
[default] – необязательный, то, что по умолчанию уже будет написано, можете писать туда любое число или String;
[ xpos ], [ ypos ] – необязательные, положение окна на экране от верхнего левого угла, любое число;
Мы, как вы заметили, использовали только первые три параметра.
Если бы вы, например, хотели указать только prompt, title и xpos, ypos, то нужно было бы писать так:
InputBox(“Сообщение”,”Заголовок”,,100,150), то есть ставить запятую, перед тем местом, где должен был быть default, а потом еще одну, перед xpos. А после ypos просто закрывать скобку.
На этом, пожалуй, остановимся. Всем спасибо за внимание, желаю успехов в освоении VBA.
Буду рад видеть в комментариях ваши мнения, просьбы и советы.