Познавательное

Импорт текстовых файлов, содержащих более 65 536 записей VBA

  

Импорт файла, содержащего более 65 536 записей, с помощью мастера текстов завершится выдачей сообщения об ошибке Файл загружен не полностью (File not loaded completely) и загрузкой первых 65 536 строк. Импорт того же файла с помощью метода Workbooks. OpenText завершится загрузкой первых 65 536 строк без уведомления о внештатной ситуации. Проверьте содержимое ячейки А6553 6 после импорта файла. Если эта ячейка не пуста, вероятно, файл был загружен некорректно.
Импорт текстового файла, содержащего не более 98 304 записей
На самом деле, Excel позволяет импортировать текстовый файл, содержащий более 65 536 записей. В диалоговом окне Мастер текстов (импорт) — шаг 1 из 3 (Text Import Wizard — Step 1 of 3) есть счетчик Начать импорт со строки (Start Import At Row), являющийся аналогом параметра StartRow метода OpenText. Казалось бы, достаточно присвоить параметру StartRow значение 65 537 и продолжить импорт файла. Как бы не так! Попытка присвоить параметру StartRow значение, превышающее 32 767, завершится ошибкой времени выполнения 1004. Дело в том, что в ранних версиях Excel для хранения значения параметра StartRow использовалась переменная типа Integer, максимально допустимое значение которой составляло 32 767. Складывается впечатление, что Microsoft не проводила генеральную ревизию кода импорта текстовых файлов со времен, когда рабочий лист Excel был способен вмещать чуть более 16 ООО строк!
Рассмотрим задачу импорта текстового файла, содержащего сведения о складских запасах 35 магазинов розничной торговли. Известно, что перечень
наименований товаров в каждом из магазинов не превышает 2000 позиций. Следовательно, максимальное число записей импортируемого файла составляет 70 000. Ниже приведен код импорта текстового файла, содержащего не более 98 304 записей.

Sub ImportFileO
Dim WBO As Workbook
Dim WBC As Workbook
Set WBO = ActiveWorkbook
ChDir ThisWorkbook.Path
ThisFile =  "inventory.txt"
Workbooks.OpenText Filename:=ThisFile
Set WBC = ActiveWorkbook
WBO.Worksheets("Данные").Cells.Clear
Cells.Copy WBO.Worksheets("Данные").Range(''A1")
WBC.Close False
If WBO.Worksheets("Данные") .Cells (65536,  1) .Value <>  "" Then
'  Файл содержит более  6553 6 записей.
'   Импортировать  заново,  начиная со строки 327 67.
Workbooks.OpenText Filename:=ThisFile,  StartRow:=32767
Set WBC = ActiveWorkbook
'  Некоторые  строки   (а именно строки 32767-65536)
'   будут скопированы повторно.
'  Первые 32770 строк нужно удалить.
RowsToSkip = Application.Rows.Count + 1 - 32767
Cells(1,  1).Resize(RowsToSkip,  1).EntireRow.Delete
FinalRow = Cells(65536,  1).End(xlUp).Row
Cells(1,  1).Resize(FinalRow,  1).Copy _
WBO.Worksheets("Данные").Range("AA1")
WBC.Close False
End If
End Sub

Импорт текстового файла построчно
Изменим условие задачи, увеличив число розничных магазинов до 50. При этом максимальный размер импортируемого файла возрастет до 100 ООО записей и
его придется загружать построчно. Импорт текстового файла, содержащего менее 65 536 записей, построчно Откройте файл для чтения с помощью выражения Input As #1. Чтобы считать строку файла в переменную, воспользуйтесь выражением Line Input #1, имя_переменной. Следующий макрос открывает файл inventory, txt, считывает первые 10 строк, помещая их содержимое в ячейки рабочего листа, после чего закрывает файл.
Sub Import10()
ChDir ThisWorkbook.Path
ThisFile = "inventory.txt"
Open ThisFile For Input As #1
For i = 1 To 10
Line Input #1, Data
Cells(i,  1).Value = Data
Next i
Close #1
End Sub

Чтобы считать все содержимое файла, воспользуйтесь циклом Do. . .While и функцией EOF. Значение выражения EOF (1) позволяет определить, был ли достигнут конец файла с номером #1. Следующий макрос построчно импортирует содержимое файла inventory.txt (чтобы обеспечить корректное выполнение макроса ImportAll, файл inventory.txt не должен содержать более 65 536 записей).
Sub ImportAll()
ThisFile = "inventory.txt"
Open ThisFile For Input As
Ctr = о
Do
Line Input #1, Data
Ctr = Ctr + 1
Worksheets("Данные").Cells(Ctr,  1).Value = Data
Loop While EOF(l)  = False
Close #1
Worksheets("Данные").Select
End Sub

Результат импорта текстового файла описанным выше способом показан на рис. 18.9.
Импорт текстовых файлов, содержащих более 65 536 записей VBA

Рис. 18.9. Построчное считывание текстового файла требует последующей обработки данных на рабочем листе

Очевидным недостатком такого подхода является помещение содержимого каждой строки файла в ячейку столбца А. Чтобы провести разбор мпортированных данных, воспользуйтесь методом TextToColumns. Параметры метода TextToColumns практически идентичны параметрам метода OpenText.
Cells(l,  1).Resize(Ctr,  1).TextToColumns Destinations _
Range("Al"),  DataType:=xlDelimited,  comma:=True,  FieldInfo:= _
Array(Array(1,  xlGeneralFormat),  Array(2,  xlGeneralFormat), _
Array(3,  xlGeneralFormat), Array(4,  xlMDYFormat))

Вместо жесткой привязки к конкретному номеру файла рекомендуется использовать функцию FreeFile. Функция FreeFile возвращает целое число, представляющее номер файла, доступный для использования в выражении Open. Ниже приведен полный код макроса, считывающего текстовый файл с
менее чем 65 536 записями, построчно.
Sub ImportAllAndParse()
ThisFile =  "inventory.txt"
FileNumber = FreeFile
Open ThisFile For Input As #FileNumber
Ctr = 0
Do
Line Input #FileNumber, Data
Ctr = Ctr + 1
Worksheets("Данные").Cells(Ctr,  1).Value = Data
Loop While EOF(FileNumber)  = False
Close #FileNumber
Worksheets("Данные").Select
'  Провести разбор данных.
Cells(l,  1).Resize(Ctr,  1).TextToColumns Destination:= _
Range("Al"),  DataType:=xlDelimited,  comma:=True,  Fieldlnfo:= _
Array(Array(1,  xlGeneralFormat),  Array(2,  xlGeneralFormat), _
Array(3,  xlGeneralFormat), Array(4,  xlMDYFormat))
End Sub

Импорт текстового файла, содержащего более 65 536 записей, построчно
Для построчного импорта текстового файла, содержащего более 65 536 записей, можно использовать уже знакомое выражение Line Input. Считаем часть файла в ячейки Al: А65534, продолжив считывание оставшихся строк, начиная с ячейки АА2 (первая строка отводится для размещения заголовков столбцов). Если и этого окажется недостаточно, продолжим считывание, начиная с ячейки ВА2, СА2 и т.д. Наличие двух пустых строк в конце каждого столбца с данными обеспечивает корректное определение номера последней строки с помощью выражения Range ("А65536 ") . End (xlUp) . Row.
Sub ReadLargeFile()
ThisFile = "inventory.txt"
FileNumber = FreeFile
Open ThisFile For Input As #FileNumber
NextRow = 1
NextCol = 1
'  Добавлено для увеличения скорости выполнения макроса.
Application.ScreenUpdating = False
Application.StatusBar =  "Считывание данных из текстового файла"
Do While Not EOF(FileNumber)
Line Input #FileNumber, Data
Cells(NextRow, NextCol).Value = Data
NextRow = NextRow + 1
If NextRow = 6553 5 Then
'  Провести разбор данных.
Application.StatusBar =  "Разбор данных"
Range(Cells(1, NextCol),  Cells(65536, _
NextCol)).TextToColumns Destination:=Cells(1, NextCol),
DataType:=xlDelimited,  comma:=True,  Fieldlnfo:=Array(Array(1, _
xlGeneralFormat), Array(2, xlGeneralFormat), Array(3, _
xlGeneralFormat), Array(4,  xlMDYFormat))
1  Скопировать  заголовки столбцов.
If NextCol > 1 Then
Range("Al:Dln).Copy Destination:=CelIs(1, NextCol)
End If
'   Начать  считывание  следующего набора данных.
NextCol = NextCol + 26
NextRow = 2
Application.StatusBar =  Считывание  следующего _
набора данных"
End If
Loop
Close #FileNumber
'   Разбор последнего набора данных.
FinalRow = NextRow - 1
If FinalRow = 1 Then
'  Обработка файла,   содержащего ровно  65534 строки.
NextCol = NextCol - 26
Else
Application.StatusBar =  "Разбор последнего набора данных"
Range(Cells(2, NextCol),  Cells(FinalRow, _
NextCol)).TextToColumns Destination:=Cells(2,  NextCol), _
DataType:=xlDelimited,  comma:=True,  Fieldlnfo:=Array(Array(1, _
xlGeneralFormat), Array(2, xlGeneralFormat), Array(3, _
xlGeneralFormat), Array(4,  xlMDYFormat))
If NextCol > 1 Then
Range("Al:Dl").Copy Destination:=Cells(1, NextCol)
End If
End If
Cells(l,  NextCol).Select
DataSets =  (NextCol - 1) /26+1
Application.StatusBar = False
Application.ScreenUpdating = True
End Sub

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


Дата: 18 марта 2014   |   Опубликовал: Admin   |   Просмотров: 1143   |   Комментарии (0)

Уважаемый , статьи ниже возможно будут интересны вам:

Посетители, находящиеся в группе Гости, не могут оставлять комментарии к данной публикации.