Макрос excel как разбить большой текстовый файл
Перейти к содержимому

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

  • автор:

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

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

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

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

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

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

Sub ПримерИспользованияФункции_SplitTextFile() ИмяРазбиваемогоФайла$ = "C:\test\2011 04 17 12-32-30.csv" МаксимальноеКоличествоСтрокВфайле& = 3 Dim СписокИмёнФайлов As Collection Set СписокИмёнФайлов = SplitTextFile(ИмяРазбиваемогоФайла$, МаксимальноеКоличествоСтрокВфайле&, vbNewLine, False) For Each Файл In СписокИмёнФайлов Debug.Print "Создан файл: " & Файл Next End Sub

Результат работы примера (из окна 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:

Function SplitTextFile(ByVal filename$, ByVal MaxRowsCount&, ByVal Delimiter$, _ Optional ByVal DeleteSourceFile As Boolean = True) As Collection ' функция предназначена для разбивки текстового файла filename$ на несколько файлов ' меньшего размера - в каждом из которых будет не более MaxRowsCount& строк ' Разделение строк выполняется с использованием разделителя Delimiter$ ' Создаваемые файлы получают имена вида filename(1).txt, filename(2).txt и т.д. ' Если DeleteSourceFile = TRUE, - то исходный файл удаляется после разбивки ' Возвращает коллекцию имён созданных файлов ext$ = "." & Split(filename$, ".")(UBound(Split(filename$, "."))) Set fso = CreateObject("scripting.filesystemobject") Set ts = fso.OpenTextFile(filename, 1, True): txt = ts.ReadAll: ts.Close HeaderRow$ = Split(txt, Delimiter$, 2)(0) & Delimiter$ ' берем первую строку из файла как заголовок txt = Split(txt, Delimiter$, 2)(1) ' остаток текста - без строки заголовка ' удаляем разделители строк в конце текстовой строки (если таковые присутствуют) While txt Like "*" & Delimiter$: txt = Left(txt, Len(txt) - Len(Delimiter$)): Wend ' RowsCount = UBound(Split(txt, Delimiter$)) + 1 ' количество текстовых строк в файле FileIndex& = 1 ' индекс очередного создаваемого файла arr = Split(txt, Delimiter$): rc = 0: Set SplitTextFile = New Collection For i = LBound(arr) To UBound(arr) rc = rc + 1 NewTXT$ = NewTXT$ & arr(i) & Delimiter$ If rc >= MaxRowsCount& Or i = UBound(arr) Then ' набрали достаточно строк для записи в файл NewFilename$ = Mid(filename$, 1, Len(filename$) - Len(ext$)) & "(" & FileIndex & ")" & ext$ Set ts = fso.CreateTextFile(NewFilename$, True) ts.Write HeaderRow$ & NewTXT$: ts.Close SplitTextFile.Add NewFilename$ FileIndex& = FileIndex& + 1 rc = 0: NewTXT$ = "" End If Next i Set ts = Nothing: Set fso = Nothing If DeleteSourceFile Then Kill filename$ ' удаляем исходный файл, если DeleteSourceFile = TRUE End Function
  • 35635 просмотров

Комментарии

sadykovs, 12 Июн 2015 — 18:54. #1

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

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

Есть большой файл больше 27 000 строк. Как его разделить на такие же .xlsx файлы, но скажем по 1000 строк?

  • Вопрос задан более трёх лет назад
  • 25873 просмотра

Комментировать
Решения вопроса 0
Ответы на вопрос 3

honor8

Принципы быстродействия VBA в описании

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

Option Explicit ' Обязательное объявление переменных Option Base 1 ' Нижняя граница массива (по умолчанию) '123456789012345678901234567890123456h8nor@ya567890123456789012345678toster56789 Sub Border_Limit() Dim Limit As Integer, Count As Integer, SaveDir As String, SetTitle As Boolean Count = 1: Limit = 1000 ' Счётчик файлов; Количество строк SetTitle = False ' Если есть заголовок, заменить False на True SaveDir = ThisWorkbook.Path ' Или вписать полный путь для сохранения "C:\" ' Предполагается, что в колонке A нет пустых ячеек While Not IsEmpty(Cells(IIf(SetTitle, 2, 1), 1)) Rows("1:" & Limit).Copy Workbooks.Add xlWBATWorksheet ' Создать новую книгу: шаблон с 1 листом ActiveSheet.Paste: Cells(1, 1).Select ActiveWorkbook.SaveAs Filename:=SaveDir & "\Массив_" & Count & ".xlsx", _ FileFormat:=xlOpenXMLWorkbook ActiveWindow.Close Rows(IIf(SetTitle, 2, 1) & ":" & Limit).Delete Shift:=xlUp Count = Count + 1 Wend: MsgBox "Файл разбит на " & Count - 1 & " файл(ов). " End Sub

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

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

Ответ написан более трёх лет назад
Нравится 8 5 комментариев

Как сохранить ширину строк исходной таблицы? Также как сохранить заголовок во всех файлах? При выборе «2» заголовок не сохраняется.
Заранее спасибо

honor8

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

ReDim colWidth(Cells.SpecialCells(xlLastCell).Column) For Count = 1 To UBound(colWidth) ' Читаем ширину колонок colWidth(Count) = Cells(1, Count).ColumnWidth Next Count

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

' Пишем ширину колонок Cells(1, 1).Resize(1, UBound(colWidth)).ColumnWidth = colWidth

alcompstudio @alcompstudio

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

alcompstudio @alcompstudio

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

Option Explicit ' Обязательное объявление переменных Option Base 1 ' Нижняя граница массива (по умолчанию) '12345678901234567890123456789012345bopoh13@ya67890123456789012345678toster56789 Sub Border_Limit() Dim Limit As Integer, Count As Integer, SaveDir As String, SetTitle As Boolean Count = 1: Limit = 2001 ' Счётчик файлов; Количество строк SetTitle = True ' Если есть заголовок, заменить False на True SaveDir = "F:\ZeusCeramica\Веб-система\Руководитель\Спецификации" ' Или вписать полный путь для сохранения "F:\ZeusCeramica\Веб-система\Руководитель\Спецификации" или ThisWorkbook.Path ' Предполагается, что в колонке A нет пустых ячеек While Not IsEmpty(Cells(IIf(SetTitle, 2, 1), 1)) Rows("1:" & Limit).Copy Workbooks.Add xlWBATWorksheet ' Создать новую книгу: шаблон с 1 листом ActiveSheet.Paste: Cells(1, 1).Select '-------Оформляем полученные таблицы в умные---------- Dim a As Long 'Определяем количество строк a = Cells(1, 1).CurrentRegion.Rows.Count 'Создаем «умную» таблицу с сохранением первой строки заголовков ActiveSheet.ListObjects.Add(xlSrcRange, Range(Cells(1, 1), Cells(a, 17)), , xlYes).Name _ = "TableRange" ActiveWorkbook.SaveAs Filename:=SaveDir & "\BOM_test_" & Count & ".xlsx", _ FileFormat:=xlOpenXMLWorkbook ActiveWindow.Close Rows(IIf(SetTitle, 2, 1) & ":" & Limit).Delete Shift:=xlUp Count = Count + 1 Wend: MsgBox "Файл разбит на " & Count - 1 & " файл(ов). " End Sub

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

honor8

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

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

ActiveSheet.ListObjects.Add(, ActiveSheet.UsedRange, , xlYes).Name = "Table_1"

Для удаления единственной умной таблицы на активном листе используется метод:
ActiveSheet.ListObjects(1).Delete

Как разбить на части большой файл excel по строкам?

В файлике около 26тыс. строк. Очень тяжело работать в этом файле, как его можно разбить на 5 частей ?

  • Вопрос задан более года назад
  • 56 просмотров

2 комментария

Простой 2 комментария

Да как обычно — copy-paste. Или сделать 5 копий и поудалять из каждого лишнее.
anoriyuriy @anoriyuriy

Сами по себе 26 тыс. строк — это не причина тяжелой работы файла. Есть высокая вероятность, что ваш файл можно оптимизировать, а если он получен выгрузкой из 1С или любой другой программы, то простая вставка значениями всех данных в новый, специально созданный файл уже даст значительный прирост в производительности.
Лучше оптимизировать насколько возможно, так как рассогласованность данных обойдется вам, может быть, существенно дороже «прироста скорости работы» в отдельных пяти файлах

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

Добрый вечер!Можете мне подсказать, как разделить файл Excel(Прайс-лист)на несколько файлов?К примеру : есть файл Excel на 1000 строк, мне нужно 4 файла Excel по 250 строк.
Я не могу просто копировать и создавать файлы- это очень долго, у меня прайсы по 600000 строк, их надо делить на много частей, и он не один.Если Вам не сложно, можете ответить?
Если я не правильно создал тему, простите.Я первый раз на этом сайте создаю тему.

Добрый вечер!Можете мне подсказать, как разделить файл Excel(Прайс-лист)на несколько файлов?К примеру : есть файл Excel на 1000 строк, мне нужно 4 файла Excel по 250 строк.
Я не могу просто копировать и создавать файлы- это очень долго, у меня прайсы по 600000 строк, их надо делить на много частей, и он не один.Если Вам не сложно, можете ответить?
Если я не правильно создал тему, простите.Я первый раз на этом сайте создаю тему. Stepan096

Сообщение отредактировал Stepan096 — Среда, 20.11.2013, 00:23

Сообщение Добрый вечер!Можете мне подсказать, как разделить файл Excel(Прайс-лист)на несколько файлов?К примеру : есть файл Excel на 1000 строк, мне нужно 4 файла Excel по 250 строк.
Я не могу просто копировать и создавать файлы- это очень долго, у меня прайсы по 600000 строк, их надо делить на много частей, и он не один.Если Вам не сложно, можете ответить?
Если я не правильно создал тему, простите.Я первый раз на этом сайте создаю тему. Автор — Stepan096
Дата добавления — 20.11.2013 в 00:21

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *