Макрос excel как разбить большой текстовый файл

Разбиение текстового файла (в т.ч. CSV) на несколько файлов с заданным количеством строк

Создаваемые файлы получают имена вида filename(1).txt, filename(2).txt и т.д.

Если задан параметр функции DeleteSourceFile равным TRUE, — то исходный файл удаляется после разделения

Функция возвращает коллекцию, содержащую пути к сформированным файлам

В начало каждого создаваемого файла дописывается строка заголовка — первая строка из исходного файла

Пример использования функции SplitTextFile:

Результат работы примера (из окна Immediate редактора VBA)

Создан файл: C:\test\2011 04 17 12-32-30(1).csv
Создан файл: C:\test\2011 04 17 12-32-30(2).csv
Создан файл: C:\test\2011 04 17 12-32-30(3).csv

Код функции SplitTextFile:

• 34020 просмотров

Комментарии

Не удержался напишу) На ваш комментарий Дмитрию на счет больших файлов — больше всего понравилась софтина ASAP Utilities, функционал очень богатый, а для разбиения на файлы по строкам Sheets » Split the selected range into multiple worksheets..к вам забрел с тем же вопросом, пока в данной надстройке не нашел

Дмитрий, данный макрос не рассчитан на файлы размером 117мб
(он для маленьких файлов — до 1-10 Мб)
Для огромных файлов (как ваш) — совсем другие технологии обработки надо применять (построчное чтение),
чтобы быстрее всё работало, и памяти хватало на обработку.
Не пытайтесь найти такое у меня на сайте, — мои «универсальные» макросы не рассчитаны на файлы такого объема

Да, и если читать файл не ReadAll, а ReadLine, то строки корректно читаются при отладке. Но так как строк много, то в режиме отладки весь файл нет желания гонять, а как только работа начинается в обычном режиме, эксель виснет. Может это из-за большого числа строк?

Спасибо за ответ! Но не помогло. В окне отладчика вижу, что в txt появились символы ? вместо текста, а на строке txt = Split(txt, Delimiter$, 2)(1) вылетает ошибка subscript out of range. Насколько я понял — не видит разделителя и не может разбить строку на подстроки.

у вас файл в другой кодировке (unicode) — дополнительный параметр TRUE как раз для этого предназначен

Здравствуйте! Ваш код использую для обработки файлов. Но при обработке очередного файла в строке Set ts = fso.OpenTextFile(filename, 1, True): txt = ts.ReadAll: ts.Close не читается исходный файл. В txt передается «». Исходный файл csv. Подобных файлов (и по структуре и типу данных) обработано с десяток, было все отлично, а тут проблема. Не могу понять в чем причина. Может подскажете? Единственное отличие этот злополучный файл больше других — 117мб

Спасибо, попробую. Извините, что вопрос не по теме — не разобрался по порядку общения. Я тут в первый раз.

Марат, а какое отношение ваш вопрос имеет к теме статьи?
Задайте вопрос на форуме по Excel, — там подскажут.
Я бы использовал GetObject для получения доступа к файлу, если известен путь, по которому файл сохранён.

Здравствуйте Игорь, проблема такая — открыты два экселевских файла, но оба в своих родительских окнах, необходимо из одного файла перекинуть информацию в другой. Макрос, прикрепленный к одному из файлов, не видит книгу в другом родительском окне. Как его все-таки увидеть?

Галия, в данном случае в коде очень много чего менять надо.
Проще написать макрос «с нуля».

Оформляйте заказ на сайте (если готовы оплатить помощь),
или обратитесь на форумы по Excel (если хотите, чтобы вам помогли бесплатно)

Спасибо, очень помогли!
Подскажите, пожалуйста, еще немножко
Есть экселевский файл, в нем несколько столбцов. Необходимо, например, если значение в столбце А больше 0, записывать в первый файл, если больше 5, в следующий, т.е. кол-во строк в файлах мы сами не задаем, а определяем из условия. Как это реализовать? Как я понимаю, нужно поменять в этом месте
If rc>= MaxRowsCount& .
На что-то в этом роде
If Cells(i, «A») >0 «0» Then ..
дальше вот как с файлами работать не знай

Получатся текстовые файлы с расширением xls — Excel их поймёт.
чтобы XLSX получить, — это код заметно переделывать надо.

Здравствуйте! Скажите, пожалуйста, можно ли как-нибудь изменить макрос, чтоб новые файлы были с расширением .xlsx?

Как разделить .xlsx по строкам?

Если файл сохранён на диске, можно так:
1. Открываете книгу с данными на нужном листе
2. Заходите в VBA (Alt+F11)
3. Выбираете в меню Insert -> Module
4. Вставляете нижеприведённый код
5. Нажимаете F5 (не сохраняете исходный файл)

Никакие C++ запускать не надо.

Для пытливых умов: Отказ от Слияния в пользу шаблонов https://toster.ru/q/320942

• Facebook
• Вконтакте
• Twitter

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

Затем, после вставки данных перенести значения ширины колонок из массива:

Спасибо за решение, искал везде, ваш подошел идеально! Единственный вопрос — а как сделать, чтобы полученные таблицы-файлы были «упакованы» в умные таблицы на выходе? Я не силен в VBA, подскажете какой код и куда его вписать?

Добавил код, который добавляет умную таблицу к диапазону, но есть проблема. У меня файлы формируются из заранее подготовленной умной таблицы, т.е. она разбивается на части. И этот (ваш) код получается формирует файлы с не отформатированными диапазонами, а последний файл именно форматируется в умную таблицу (как бы унаследует формат из исходного файла). Т.е. не все сформированные файлы с умными таблицами получаются, а только последний. А мне нужно, чтобы все были оформлены в умные таблицы. Я добавил код, который добавляет формат в полученные файлы, вот такое у меня получилось:

Но в результате получается ошибка, т.к. система пытается последнюю таблицу, которая «умная» тоже повторно оформить.
Подскажете, как подправить?

kolyayolo, alcompstudio, на технических ресурсах принято выражать свою положительную оценку кнопкой «Нравится«, тем самым указывая на полезность материала.

Для создания умной таблицы для всего активного листа с именем «Table_1» используется следующий метод (четвёртый параметр указывает на наличие заголовков):

Макрос excel как разбить большой текстовый файл

Такая проблема: нужно срочно разделить огромный массив данных и сгруппировать его в новых файлах по фильтру. В ручную это делать очень долго и невыносимо.
Суть:
В рабочем файле десяток столбцов и полторы тысячи строк. Нужен макрос, который отсортирует по двум параметрам и сохранит результат отдельным файлам с названием из ячеек(сперва дата, затем договор).

А именно:
Фильтр по ячейке Дата 2 (J) и Договор (I).
Диапазон сохранения данных в новый файл со столбца A, по столбец I(включительно).
Как должен выглядеть финальный результат: создаются файлы с названием(к примеру) «08.06.2020 55.34-ПП (1).xlsx», «08.06.2020 55.34-ПП (2).xlsx», «09.06.2020 55.34-ПП (2).xlsx» и так далее, в которых содержится информация из диапазона A:I. В файле с названием «08.06.2020 55.34-ПП (1).xlsx» должны содержатся все строки где Дата 2 (J) и Договор (I) соответствуют тому, что в названии.

Важный момент: нельзя сохранить файл со слешем в названии из Договора (I), поэтому его необходимо заменять на точку.

Такая проблема: нужно срочно разделить огромный массив данных и сгруппировать его в новых файлах по фильтру. В ручную это делать очень долго и невыносимо.
Суть:
В рабочем файле десяток столбцов и полторы тысячи строк. Нужен макрос, который отсортирует по двум параметрам и сохранит результат отдельным файлам с названием из ячеек(сперва дата, затем договор).

А именно:
Фильтр по ячейке Дата 2 (J) и Договор (I).
Диапазон сохранения данных в новый файл со столбца A, по столбец I(включительно).
Как должен выглядеть финальный результат: создаются файлы с названием(к примеру) «08.06.2020 55.34-ПП (1).xlsx», «08.06.2020 55.34-ПП (2).xlsx», «09.06.2020 55.34-ПП (2).xlsx» и так далее, в которых содержится информация из диапазона A:I. В файле с названием «08.06.2020 55.34-ПП (1).xlsx» должны содержатся все строки где Дата 2 (J) и Договор (I) соответствуют тому, что в названии.

Важный момент: нельзя сохранить файл со слешем в названии из Договора (I), поэтому его необходимо заменять на точку.

exPower

Сообщение Добрый день!

Такая проблема: нужно срочно разделить огромный массив данных и сгруппировать его в новых файлах по фильтру. В ручную это делать очень долго и невыносимо.
Суть:
В рабочем файле десяток столбцов и полторы тысячи строк. Нужен макрос, который отсортирует по двум параметрам и сохранит результат отдельным файлам с названием из ячеек(сперва дата, затем договор).

А именно:
Фильтр по ячейке Дата 2 (J) и Договор (I).
Диапазон сохранения данных в новый файл со столбца A, по столбец I(включительно).
Как должен выглядеть финальный результат: создаются файлы с названием(к примеру) «08.06.2020 55.34-ПП (1).xlsx», «08.06.2020 55.34-ПП (2).xlsx», «09.06.2020 55.34-ПП (2).xlsx» и так далее, в которых содержится информация из диапазона A:I. В файле с названием «08.06.2020 55.34-ПП (1).xlsx» должны содержатся все строки где Дата 2 (J) и Договор (I) соответствуют тому, что в названии.

Важный момент: нельзя сохранить файл со слешем в названии из Договора (I), поэтому его необходимо заменять на точку.

Автор — exPower
Дата добавления — 08.10.2020 в 13:02

Как разбить огромную электронную таблицу CSV Excel на отдельные файлы

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

Достижение предела количества строк в электронной таблице само по себе является довольно серьезной задачей. Текущий предел строки таблицы Excel составляет 1 048 576. Это верно — более 1 миллиона строк и столько же столбцов.

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

Что ж, я покажу вам, как разбить этот огромный CSV на легко управляемые куски.

(У вас нет большого CSV под рукой? Есть несколько довольно больших наборов данных CSV на data.gov . Я собираюсь использовать список сравнения больниц из 260 000 строк, чтобы вы могли использовать тот же файл.)

1. Используйте программу

Существует множество полезных программ для разветвления CSV. Я покажу вам два лучших.

CSV Chunker — это разветвитель CSV с открытым исходным кодом. Он может обрабатывать большие файлы, быстро разбивая их на куски по вашему выбору. Потребовалось около 3 секунд, чтобы разделить наш CSV для больниц сравнения на 106 блоков, каждый из которых содержит 2500 строк.

CSV Splitter — наш второй инструмент. Он предлагает почти такую ​​же функциональность CSV Chunker, хотя и с немного более гладким дизайном. CSV Splitter потребовалось около 4 секунд, чтобы разделить нашу CSV для больниц сравнения на 106, снова содержащую 2500 строк в каждой.

2. Используйте пакетный файл

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

Откройте новый текстовый документ. Скопируйте и вставьте следующее:

Во-первых, вам нужно изменить местоположение вашего входного файла. Во-вторых, вам нужно указать, сколько строк должны содержать ваши выходные файлы. Наконец, укажите местоположение выходного файла. Я оставил там свои пути к файлам, чтобы проиллюстрировать ситуацию. Кроме того, ваше выходное имя файла должно иметь суффикс «_ $ i.csv», соответствующий моему. В противном случае ваши выходные файлы не будут правильно нумерованы.

Когда вы отредактировали файл со своими собственными данными, нажмите CTRL + S, чтобы сохранить ваш скрипт.

Оригинальный сценарий можно найти здесь .

4. Загрузите его в модель данных

Наше окончательное решение фактически не разбивает большой CSV на более мелкие части. Видите ли, Хосе Баррето, член команды OneDrive, недавно проиллюстрировал, как именно вы можете загрузить большой файл CSV в Excel. В этом случае, большое значение превышает предел строки в 1 048 567 строк. В учебном пособии он объясняет, что вместо фактической загрузки файла Excel 2016 может создать ссылку на данные для файла CSV.

Данные из исходного CSV будут доступны пользователю в рамках модели данных. Баррето создал сводную таблицу, используя «до 8,5 миллионов строк без каких-либо проблем». Приведенное выше изображение взято из сообщения в блоге Баррето (см. Ссылку ниже), отображающего в Excel 2 миллиона строк. (Узнайте, как использовать сводную таблицу для анализа данных .)

Если это решение подходит вам, следуйте подробному руководству Хосе Баррето прямо здесь . Помните, что это не разделяет CSV, но это означает, что вы можете манипулировать своими данными в определенных инструментах Excel. Если вы обращаетесь к вашим данным для непосредственного взаимодействия с ними, стоит попробовать.

Сломай!

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

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