Как уменьшить файл csv

Язвы и грабли CSV и Excel: проблемы и решения

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

История этого формата насчитывает не менее 30 лет. Но даже сейчас, в эпоху повального использования XML, для выгрузки и загрузки больших объемов данных по-прежнему используют CSV. И, несмотря на то, что сам формат довольно неплохо описан в RFC, каждый его понимает по-своему.

Начнем с того, что форматом CSV на самом деле называют три разных текстовых формата, отличающихся символами-разделителями: собственно сам CSV (comma-separated values — значения, разделенные запятыми), TSV (tab-separated values — значения, разделенные табуляциями) и SCSV (semicolon separated values — значения, разделенные точкой с запятой). В жизни все три могут называться одним CSV, символ-разделитель в лучшем случае выбирается при экспорте или импорте, а чаще его просто «зашивают» внутрь кода. Это создает массу проблем в попытке разобраться.

Как иллюстрацию возьмем казалось бы тривиальную задачу: импортировать в Microsoft Outlook данные из таблицы в Microsoft Excel.

В Microsoft Excel есть средства экспорта в CSV, а в Microsoft Outlook — соответствующие средства импорта. Что могло быть проще — сделал файлик, «скормил» почтовой программе и — дело сделано? Как бы не так.

Создадим в Excel тестовую табличку:

… и попробуем экспортировать ее в три текстовых формата:

Какой вывод мы делаем из этого. То, что здесь Microsoft называет «CSV (разделители — запятые)», на самом деле является форматом с разделителями «точка с запятой». Формат у Microsoft — строго Windows-1251. Поэтому, если у вас в Excel есть Unicode-символы, они на выходе в CSV отобразятся в вопросительные знаки. Также то, что переводами строк является всегда пара символов, то, что Microsoft тупо берет в кавычки все, где видит точку с запятой. Также то, что если у вас нет Unicode-символов вообще, то можно сэкономить на объеме файла. Также то, что Unicode поддерживается только UTF-16, а не UTF-8, что было бы сильно логичнее.

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

Мы помним, что Microsoft Excel умеет работать с текстовыми файлами, импортировать данные из CSV, но в версии 2007 он делает это очень странно. Например, если просто открыть файл через меню, то он откроется без какого-либо распознавания формата, просто как текстовый файл, целиком помещенный в первую колонку. В случае, если сделать дабл-клик на CSV, Excel получает другую команду и импортирует CSV как надо, не задавая лишних вопросов. Третий вариант — вставка файла на текущий лист. В этом интерфейсе можно настраивать разделители, сразу же смотреть, что получилось. Но одно но: работает это плохо. Например, Excel при этом не понимает закавыченных переводов строк внутри полей.

Более того, одна и та же функция сохранения в CSV, вызванная через интерфейс и через макрос, работает по-разному. Вариант с макросом не смотрит в региональные настройки вообще.

Стандарта CSV как такового, к сожалению, нет, но, между тем, существует т.н. memo. Это RFC 4180 года, в котором описано все довольно толково. За неимением ничего большего, правильно придерживаться хотя бы RFC. Но для совместимости с Excel следует учесть его собенности.

Вот краткая выжимка рекомендаций RFC 4180 и мои комментарии в квадратных скобках:

Вот в нотации ABNF описание формата:

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

Пример валидного CSV, который можно использовать для тестов:

точно такой же SCSV:

Второй файлик, который по логике SCSV, экселом воспринимается и выходит вот что:

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

Есть эффективный способ, как заставить Excel не приводить типы, когда это нам не нужно. Но это будет CSV «специально для Excel». Делается это помещением знака «=» перед кавычками везде, где потенциально может возникнуть проблема с типами. Заодно убираем лишние пробелы.

И вот что случаеся, если мы открываем этот файлик в экселе:

Чтобы построить хороший и удобный импортер CSV, необходимо помнить о следующем:

Источник

Как уменьшить огромный файл excel

У меня есть небольшой и простой файл в *.XLS только с одним листом, на этом листе всего много ячеек с небольшим текстом по номеру. (размер файла 24 КБ)

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

У меня есть та же проблема в реальном файле с 300 листами и 1 изображением на каждом листе:размер файла 280Mb

8 ответов

Я написал файл VBA, чтобы добавить инструмент для очистки этих аномально больших файлов. Этот скрипт очищает все столбцы и строки после последних ячеек, используемых для сброса последних ячеек ([Ctrl]+[End]), а также обеспечивает сжатие изображений.

Я разрабатываю дополнения с автоматической установкой (просто запустите его с включенным макросом), чтобы включить в контекстное меню много новых кнопок:

это основано на КБ Microsoft office 2003 и ответ PP. с улучшением личных данных:

решение > вы можете скачать мой *.файл xlam ToolsKit

Я меняю формат файла на *.XLSX-файл это изменение сжимает мой файл и уменьшает размер файла на 15%

Я много работал в Excel и нашел следующие 3 пункта очень полезными

найдите, есть ли ячейки, которые, по-видимому, не содержат никаких данных, но Excel считает, что у них есть данные

вы можете найти это, используя следующее свойство на листе

если этот диапазон больше, чем ячейки, по которым у вас есть данные, удалите остальные строки / столбцы

вы будете удивлены, увидев, сколько места он может бесплатно

формат XLSM должен сделать Excel совместимым с Open XML, но есть очень мало случаев, когда мы фактически используем формат XML Excel. Это уменьшает размер почти на 50%, если не больше

оптимальный способ хранения информации

например, если вам нужно сохранить цену акций около 10 лет, и вам нужно сохранить Open, High, Low, Close для акции, это приведет к (252*10) * (4) используемые ячейки

вместо использования отдельных столбцов для Open, High,Low, Close сохраните их в одном столбце с разделителем полей Открыть: Высокий:Низкий: Закрыть

вы можете легко написать функцию для извлечения информации из одного столбца, когда захотите, но она освободит почти 2/3 пространства, которое вы в настоящее время занимаете

У меня был файл excel размером 24 Мб, благодаря более чем 100 изображениям внутри. Я уменьшил размер до менее чем 5MB следующими шагами: 1. Выделил каждую картинку, вырезал ее (CTRL X) и вставил в специальный режим с помощью опции Alt E S Bitmap 2. Чтобы найти, какое растровое изображение все еще было большим, нужно выбрать один из файлов на листе, затем сделать CTRL A. Это выберет все изображения. 3. Дважды щелкните по любому изображению, и сверху появится опция сброса изображения. 4. Нажмите на reset picture и все изображения, которые все еще большой. 5. Сделайте CTRL Z (отменить) и теперь снова вставьте эти изображения баланса в растровое изображение (*.BMP), как Шаг 1.

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

Источник

Как преобразовать файлы Excel в формат CSV

CSV (Comma Separated Values) – распространённый формат для хранения табличных данных (числовых и текстовых) в виде простого текста. Этот формат файлов популярен и живуч благодаря тому, что огромное количество программ и приложений понимают CSV, хотя бы как альтернативный вариант файлового формата для импорта / экспорта. Более того, формат CSV позволяет пользователю заглянуть в файл и немедленно найти проблему с данными, если таковая имеется, изменить разделитель CSV, правила цитирования и так далее. Это возможно потому, что CSV – это простой текст, и даже не очень опытный пользователь, сможет легко его понять без специальной подготовки.

В этой статье мы изучим быстрые и эффективные способы экспорта данных из Excel в CSV и узнаем, как преобразовать файл Excel в CSV, сохранив без искажений все специальные и иностранные символы. Описанные в статье приёмы работают во всех версиях Excel 2013, 2010 и 2007.

Как преобразовать файл Excel в CSV

Если требуется экспортировать файл Excel в какое-либо другое приложение, например, в адресную книгу Outlook или в базу данных Access, предварительно преобразуйте лист Excel в файл CSV, а затем импортируйте файл .csv в другое приложение. Ниже дано пошаговое руководство, как экспортировать рабочую книгу Excel в формат CSV при помощи инструмента Excel – «Сохранить как».

Замечание: Все упомянутые форматы сохраняют только активный лист Excel.

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

Экспортируем из Excel в CSV с кодировкой UTF-8 или UTF-16

Если на листе Excel содержатся какие-либо специальные или иностранные символы (тильда, ударение и подобные) или иероглифы, то преобразование листа Excel в CSV описанным выше способом не сработает.

Дело в том, что команда Сохранить как > CSV (Save as > CSV) исказит все символы, кроме ASCII (American Standard Code for Information Interchange). И если на листе Excel есть парные кавычки или длинные тире (перенесённые в Excel, например, из документа Word при копировании / вставке текста) – такие символы также будут искромсаны.

Простое решение – сохранить лист Excel как текстовый файл Unicode(.txt), и затем преобразовать его в CSV. Таким образом все символы, не входящие в ASCII, останутся в целости и сохранности.

Прежде чем двинуться дальше, позвольте мне кратко пояснить главные отличия между кодировками UTF-8 и UTF-16, чтобы в каждом индивидуальном случае Вы могли выбрать подходящий формат:

Как преобразовать файл Excel в CSV UTF-8

Предположим, у нас есть лист Excel с иностранными символами, в нашем примере – это японские имена.

Чтобы экспортировать этот лист Excel в файл CSV, сохранив при этом все иероглифы, сделаем следующее:

Замечание: Не все простые текстовые редакторы полностью поддерживают символы Юникод, поэтому некоторые из них могут отображаться как прямоугольники. В большинстве случаев, это никак не повлияет на итоговый файл, и можно просто не обращать на это внимание или выбрать более продвинутый редактор, например, Notepad++.

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

В Блокноте результат будет приблизительно вот такой:

Замечание: Если Ваш файл предназначен для использования за пределами Excel и формат UTF-8 является обязательным требованием, то не совершайте на листе никаких изменений и не сохраняйте его снова в Excel, поскольку это может привести к проблемам с чтением кодировки. Если какая-то часть данных не отображается в Excel, откройте тот же файл в Блокноте и в нём внесите исправления в данные. Не забудьте снова сохранить файл в формате UTF-8.

Как преобразовать файл Excel в CSV UTF-16

Экспортировать в файл CSV UTF-16 намного быстрее и проще, чем в UTF-8. Дело в том, что Excel автоматически применяет формат UTF-16 при сохранении файла как Текст Юникод (Unicode Text).

Для этого сохраняем файл при помощи инструмента Сохранить как (Save as) в Excel и затем в Проводнике Windows изменяем расширение созданного файла на .csv. Готово!

Если нужен файл CSV с запятой или точкой с запятой в качестве разделителя, замените все символы табуляции на запятые или точки с запятой соответственно в Блокноте или в любом другом текстовом редакторе на свой выбор (ранее в этой статье есть подробная инструкция, как это делается).

Другие способы преобразования файлов Excel в CSV

Описанные выше способы экспорта данных из Excel в CSV (UTF-8 и UTF-16) универсальны, т.е. подойдут для работы с любыми специальными символами и в любой версии Excel от 2003 до 2013.

Существует множество других способов преобразования данных из формата Excel в CSV. В отличие от показанных выше решений, эти способы не будут давать в результате чистый UTF-8 файл (это не касается OpenOffice, который умеет экспортировать файлы Excel в несколько вариантов кодировки UTF). Но в большинстве случаев получившийся файл будет содержать правильный набор символов, который далее можно безболезненно преобразовать в формат UTF-8 при помощи любого текстового редактора.

Преобразуем файл Excel в CSV при помощи Таблиц Google

Как оказалось, можно очень просто преобразовать файл Excel в CSV при помощи Таблиц Google. При условии, что на Вашем компьютере уже установлен Google Drive, выполните следующие 5 простых шагов:

Совет: Если файл Excel относительно небольшой, то для экономии времени можно перенести из него данные в таблицу Google при помощи копирования / вставки.

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

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

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

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

Сохраняем файл Excel как CSV при помощи OpenOffice

OpenOffice – это пакет приложений с открытым исходным кодом, включает в себя приложение для работы с таблицами, которое отлично справляется с задачей экспорта данных из формата Excel в CSV. На самом деле, это приложение предоставляет доступ к большему числу параметров при преобразовании таблиц в файлы CSV (кодировка, разделители и так далее), чем Excel и Google Sheets вместе взятые.

Просто открываем файл Excel в OpenOffice Calc, нажимаем Файл > Сохранить как (File > Save as) и выбираем тип файла Текст CSV (Text CSV).

На следующем шаге предлагается выбрать значения параметров Кодировка (Character sets) и Разделитель поля (Field delimiter). Разумеется, если мы хотим создать файл CSV UTF-8 с запятыми в качестве разделителей, то выбираем UTF-8 и вписываем запятую (,) в соответствующих полях. Параметр Разделитель текста (Text delimiter) обычно оставляют без изменения – кавычки («). Далее нажимаем ОК.

Таким же образом для быстрого и безболезненного преобразования из Excel в CSV можно использовать ещё одно приложение – LibreOffice. Согласитесь, было бы здорово, если бы Microsoft Excel предоставил возможность так же настраивать параметры при создании файлов CSV.

В этой статье я рассказал об известных мне способах преобразования файлов Excel в CSV. Если Вам знакомы более эффективные методы экспорта из Excel в CSV, расскажите об этом в комментариях. Благодарю за внимание!

Источник

Как создать CSV файл через таблицу Excel

Возможность создания CSV файла из таблицы Excel позволяет преобразовывать документы в нужный формат для загрузки в любые системы, например, outlook, Google Analytics, Яндекс.Метрику и многое другое.

Простой способ создания CSV через таблицу в Excel

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

Для превращения файла в CSV потребуется нажать на кнопку «Файл» (левый-верхний угол).

Далее выбираем пункт «Сохранить как» и нажимаем на «Обзор».

Теперь выбираем нужный формат, например, стандартный CSV.

Все готово, мы получили нужный формат.

Проверив его в блокноте можно увидеть, что разделители стоят «;» (точка с запятой).

Ручной метод превращения таблицы Эксель в CSV-файл

Если требуется создать кастомный файл (с собственными разделителями, например, |). Для этого прописываем конструкцию между каждым элементом:

Далее таким образом соединяем все элементы.

Растягиваем формулу до конца документа.

Выделяем нужный столбец (новый) и правым кликом мыши по нему выводим меню, где нажимаем на «Копировать».

Создаем текстовый документ новый (блокнот), кликнув правой клавишей мыши в нужной папке.

Открываем его и вводим новое поле.

После в левом-верхнем углу кликаем на «Файл»-«Сохранить как».

Выбираем нужный тип кодировки и сохраняем документ.

Важно! Лучше всего выбирать UTF-8, что позволит работать в дальнейшем с кириллицей.

Теперь опять кликаем правой клавишей мыши на файл и нажимаем на «Переименовать».

Стираем разрешение «.txt» и прописываем вместо него «.csv». При необходимости меняем название.

Все готово, мы получили нужный формат.

Для чего это нужно?

Создание CSV-файла потребуется для:

Также можно ознакомиться с инструкцией по открытию CSV файла.

Источник

Как открыть CSV в Excel: пошаговая инструкция

Умение открывать CSV файлы в Excel позволит анализировать и использовать документы с любым разделителем при работе с таблицами.

Простой способ открыть CSV в Excel (Способ №1)

В примере будет использовать пример файла CVS, который разделен «,» (запятой). Можно скачать ниже:

Чтобы использовать его потребуется кликнуть правой клавишей мыши по документу и в выпавшем меню выбрать «Открыть с помощью»-«Microsoft Excel».

В итоге получаем следующий вид.

Чтобы превратить его в табличный открываем вкладку «Данные» и выбираем «Текст по столбцам».

Далее выбираем «С разделителями».

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

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

Важно! Если разделителем является иной знак (не точка с запятой и не запятая) то необходимо использовать «Другой» и ввести нужный символ.

Файл превратился в нужный формат.

Открытие CSV сразу с разделением столбцов в Эксель (Способ №2)

В нужном листе кликаем на «Данные»-«Получение внешних данных» и в выпавшем меню выбираем «Из текста».

Далее выбираем нужный нам файл.

Теперь аналогично способу №2 выбираем с разделителями.

Указываем нужный символ.

Через вкладку «Файл» (Способ №3)

Кликаем на кнопку «Файл» (левый-верхний угол).

В открывшемся меню нажимаем на «Открыть»-«Компьютер»-«Обзор».

Открываем нужную папку и документ (в графе нужного типа файла можно поставить «Все файлы»).

Далее проходим всю процедуру описанную в Способе 1 и 2 или же нажимаем на «Готово».

Источник