Обучающее видео различных тематик бесплатно!

Excel VBA: Работа с текстовыми файлами

Сказки на ночь

Работа с текстовыми файлами Excel VBA

  

Прежде чем формат XML окончательно утвердится в качестве стандартного формата файлов, некоторое время наряду с форматом XML широкое распространение будут иметь форматы CSV и ТХТ. В этой главе рассматривается импорт и экспорт текстовых файлов посредством VBA. Экспорт данных в текстовый файл может понадобиться для передачи информации в другую систему или создания HTML-файла.
Импорт данных из текстового файла
Рассмотрим три сценария импорта данных из текстовых файлов. Если в файле содержится менее 65 536 записей, его можно импортировать с помощью метода Workbooks .OpenText. Если в файле содержится более 65 536 записей, но менее 98 304 записей, его можно импортировать с помощью двух вызовов
метода Workbooks . OpenText. Если же в файле содержится более 98 304 записей, его придется импортировать путем последовательного считывания строк.
Импорт текстовых файлов, содержащих менее 65 536 записей
Существует два основных формата текстовых файлов. Первый формат предполагает разделение полей каждой записи с помощью некоторого символа, например, запятой, точки с запятой, знака табуляции и т.п. Второй формат подразумевает наличие фиксированной ширины полей каждой записи. Excel одинаково успешно справляется с файлами обоих форматов с помощью метода OpenText. При разработке программного кода рекомендуется записать макрос и использовать уже готовые фрагменты кода.
Импорт текстового файла с полями фиксированной ширины На рис. 18.1 показан пример текстового файла с полями фиксированной ширины.

Работа с текстовыми файлами Excel VBA

Рис. 18.1. При открытии текстового файла с полями фиксированной ширины необходимо указать точный размер полей

При открытии подобного файла необходимо указать точный размер его полей. Чтобы создать соответствующий программный код, воспользуемся
средством записи макросов. Начните запись макроса, выбрав в меню Excel команду Сервис^Макрос1^Начать запись (Tools^Macros^>Record New Macro). Выберите команду Файл^>Открыть (File^Open) и откройте нужный файл с помощью диалогового окна Открытие документа (Open). В диалоговом окне Мастер текстов (импорт) — шаг 1 из 3 (Text Import Wizard— Step 1 of 3) установите переключатель Фиксированной ширины (Fixed width) и щелкните на кнопке Далее (Next). На рис. 18.2 показан результат попытки автоматического определения ширины полей текстового файла.
Работа с текстовыми файлами Excel VBA

Рис. 18.2. Результат попытки автоматического определения ширины полей текстового файла. Очевидно, что Excel затрудняется определить ширину соприкасающихся полей

Excel затрудняется определить ширину соприкасающихся полей, таких как Дата и Заказчик. Добавьте требуемые линии, обозначающие конец поля, в области Образец разбора данных (Data preview) диалогового окна Мастер текстов (импорт) — шаг 2 из 3 (Text Import Wizard — Step 2 of 3). Чтобы добавить линию, щелкните в нужной позиции в области Образец разбора данных. Чтобы переместить линию, щелкните на ней и перетащите в требуемое место. Чтобы удалить линию, дважды щелкните на ней. Конечный результат определения ширины полей текстового файла показан на рис. 18.3. Обратите внимание на линейку, расположенную над областью Образец разбора данных. Каждое ее деление соответствует одному символу в текстовом файле. К примеру, поле Заказчик начинается с 25-й позиции и имеет длину 11 символов. По умолчанию Excel предполагает, что все поля текстового файла имеют формат Общий (General). Измените формат полей, требующих особой обработки. Для этого щелкните на столбце и выберите требуемый формат с помощью области Формат данных столбца (Column data format) диалогового окна Мастер текстов (импорт) — шаг 3 из 3 (Text Import Wizard — Step 3 of 3), как показано на рис. 18.4.
Работа с текстовыми файлами Excel VBA

Рис. 18.3. Образец разбора данных после добавления двух линий, обозначающих конец поля, и перемещения линии, разграничивающей поля Товар И Дата

Работа с текстовыми файлами Excel VBA

Рис. 18.4. Измените тип столбца Дата на МДГ (MDY). Откажитесь от импорта столбцов С-ть и Пр-ль, изменив их тип на Пропустить (Skip). Чтобы определить разделитель целой и дробной части, а также разделитель разрядов, щелкните на кнопке Подробнее (Advanced)

Например, чтобы изменить тип столбца Дата, щелкните на нем и установите переключатель Дата (Date) в области Формат данных столбца. Укажите формат
даты (например, "день-месяц-год" или "месяц-день-год") с помощью раскрывающегося списка, расположенного справа от переключателя Дата. Чтобы отказаться от импорта определенного столбца, щелкните на нем и установите переключатель Пропустить столбец (Do not import column (skip)). Пропуск столбцов может пригодиться при импортировании текстового файла, содержащего нежелательную для разглашения информацию (например, себестоимость товара и прибыль, получаемая от его продажи). Иногда текстовый файл с полями фиксированной ширины содержит также символыразделители. Установите переключатель Пропустить столбец для всех столбцов, содержащих символы-разделители, как показано на рис. 18.5.
Работа с текстовыми файлами Excel VBA

Рис. 18.5. Переключатель Пропустить столбец рекомендуется установить для всех столбцов текстового файла с полями фиксированной ширины, содержащих символы-разделители

Полям, содержащим алфавитные символы, можно назначить тип Общий. Назначьте тип Текст (Text) числовым полям, значения которых необходимо импортировать как текст. Примером таких полей является поле почтового индекса и поле номера банковского счета (оба поля могут содержать значения с
ведущими нулями, например, 01234).
Внимание
Excel воспринимает формулу, введенную в столбец текстового формата, как обычную строку. Чтобы ввести формулу, формат столбца придется изменить на Общий (General).

Ниже приведен код импорта текстового файла с полями фиксированной ширины, сгенерированный средством записи макросов.
Workbooks.OpenText Filename:="sales.prn",  Origin:=1251, _
StartRow:=l,  DataType:=xlFixedWidth,  Fieldlnfо:=Array( _
Array(0,  2), Array(8,  1), Array(17,  3), Array(25,  1), _
Array(36,  1), Array(46,  1), Array(56,  9),  Array(61,  9)), _
TrailingMinusNumbers:=True

Значение параметра Fieldlnfo представляет массив двухэлементных массивов, определяющих позицию первого символа поля (с отсчетом от нуля) и его тип.
Тип поля представлен числом, соответствующим одной из констант Excel xlColumnDataType (табл. 18.1). Например, массив Array (0 , 1) определяет поле общего формата, отстающее от начала строки на 0 символов (поле Регион), массив Array (8, 1) — поле общего формата, отстающее от начала строки на 8 символов (поле Товар), а массив Array (11, 3) — поле даты в формате "месяц-день-год", отстающее от начала строки на 17 символов (поле Дата).
Работа с текстовыми файлами Excel VBA

Ввиду относительной сложности кодирования параметра Fieldlnfo рекомендуется записать макрос и скопировать автоматически сгенерированный фрагмент кода.
Внимание
Впервые параметр TrailingMinusNumbers был представлен в Excel 2002. Не используйте его в макросах, которые могут выполняться в более ранних версиях Excel, например, Excel 97 или Excel 2000, так как это приведет к возникновению ошибки компиляции. Отсутствие параметра Trail ingMinusNumbers не кажется
на результате выполнения макроса в новых версиях Excel.

Импорт текстового файла с символами-разделителями
На рис. 18.6 показан текстовый файл с разделителями-запятыми. При открытии подобного файла в Excel укажите используемый символразделитель и, при необходимости, способ обработки полей. В рассматриваемом примере значение третьего поля должно быть интерпретировано как дата
в формате "месяц-день-год".
Работа с текстовыми файлами Excel VBA

Рис 18.6. При открытии текстового файла с символами-разделителями укажите используемый символ-разделитель (в данном случае это запятая) и, при необходимости, способ обработки полей

Внимание
При открытии текстового файла с расширением . csv, содержащего разделители запятые, средство записи макросов Excel создаст код, вызывающий метод Workbooks. Open. Чтобы иметь возможность указать способ обработки полей, измените расширение файла на . txt.

Начните запись макроса, выбрав в меню Excel команду Сервис^Макрос1^Начать запись (ToolsOMacros1^Record New Macro). Выберите команду Файл ^Открыть (File^Open) и откройте нужный файл с помощью диалогового окна Открытие документа (Open). В диалоговом окне Мастер текстов (импорт) — шаг 1 из 3 (Text Import Wizard— Step 1 of 3) установите переключатель С разделителями (Delimited) и щелкните на кнопке Далее (Next). Первоначальный результат автоматического разбора данных, показанный в области Образец разбора данных (Data preview) диалогового окна Мастер текстов (импорт) — шаг 2 из 3 (Text Import Wizard— Step 2 of 3), будет выглядеть крайне непривлекательно. Это вызвано тем, что Excel по умолчанию использует в качестве символа-разделителя знак табуляции (рис. 18.7).
Работа с текстовыми файлами Excel VBA

Рис. 18.7. Результат первоначального разбора данных выглядит как чьято неудавшаяся шутка. А все из-за того, что Excel по умолчанию использует в качестве символа-разделителя знак табуляции

Сбросьте флажок Знак табуляции (Tab) и установите флажок, соответствующий требуемому символу-разделителю (в данном случае это флажок Запятая
(Comma)). Результат повторного разбора данных показан на рис. 18.8.
Работа с текстовыми файлами Excel VBA

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

Задайте формат полей, требующих особой обработки, с помощью диалогового окна Мастер текстов (импорт) — шаг 3 из 3 (Text Import Wizard — Step 3 of 3). Ниже приведен код импорта текстового файла с символамиразделителями, сгенерированный средством записи макросов.
Workbooks.OpenText Filename:="sales.csv.txt",  Origin:=1251, _
StartRow:=l,  DataType:=xlDelimited,  TextQualifier:= _
xlDoubleQuote,  ConsecutiveDelimiter:=False,  Tab:=False, _
Semicolon:=False,  Comma:=True,  Space:=False,  Other:=False, _
FieldInfo:=Array(Array(1,  1), Array(2,  1), Array(3,  3), _
Array(4,  1), Array(5,  1), Array(6,  1), Array(7,  1), _
Array(8,  1)),  Trai1ingMinusNumbers:=True

Несмотря на то что этот код длиннее кода импорта текстового файла с полями фиксированной ширины, он менее сложен для восприятия. Параметр Fieldlnfo представляет массив двухэлементных массивов, определяющих порядковый номер поля (с отсчетом от единицы) и его тип (см. табл. 18.1). Например, массив Array (2, 1) определяет второе поле как поле общего формата, а массив Array(3, 3) — третье поле как поле даты в формате "месяц-день-год". Длина кода обусловлена явным заданием значений всех параметров, определяющих символы-разделители. Поскольку по умолчанию значение этих параметров равно False, код можно оптимизировать, как показано ниже:
Workbooks.OpenText Filename:="sales.csv.txt",  Origin:=1251, _
StartRow:=l,  DataType:=xlDelimited,  Comma:=True, _
Fieldlnfo:=Array(Array(1,  1), Array(2,  1), Array(3,  3), _
Array(4,  1), Array(5,  1), Array(6,  1), Array(7,  1), Array(8,  1))

Чтобы сделать код более удобочитаемым, числовые значения, определяющие тип поля, можно заменить соответствующими константами:
Workbooks.OpenText Filename:="sales.csv.txt",  Origin:=12 51, _
StartRow: =1,  DataType: =xlDelimiteci,  Comma: =True, _
Fieldlnfo:=Array(Array(1,  xlGeneralFormat), Array(2, _
xlGeneralFormat), Array(3,  xlMDYFormat), Array(4, _
xlGeneralFormat), Array(5, xlGeneralFormat), Array(6, _
xlGeneralFormat), Array(7, xlGeneralFormat), Array(8, _
xlGeneralFormat))

Внимание
Значения параметров, определяющих символы-разделители, остаются действительными на протяжении всего сеанса работы с Excel. При вставке текстовых данных, содержащихся в буфере обмена, Excel осуществит их разбор в соответствии со значениями параметров, заданными во время последнего вызова метода OpenText. К примеру, название фирмы заказчика "ABC, Inc." будет разбито на две части ("ABC" и "Inc.") и помещено в два соседних столбца, если в качестве символа-разделителя использовалась запятая.

Excel позволяет использовать любой символ-разделитель, а не только символ табуляции, запятую, точку с запятой и пробел. Чтобы указать на необходимость использования в качестве сим вол а-разделителя знака перенаправления (|), установите значение параметра Other метода OpenText равным
True, а значение параметра OtherChar равным " ", как показано далее:
Workbooks.OpenText Filename:="sales.csv.txt",  Origin:=12 51, _
StartRow:=1,  DataType:=xlDelimited,  Other:=True,  OtherChar:="|" _
Fieldlnfo:=Array(Array(1,  1), Array(2,  1), Array(3,  3), _
Array(4,  1), Array(5,  1), Array(6,  1), Array(7,  1), Array(8, 1))
 
Уважаемый посетитель, Вы зашли на сайт как незарегистрированный пользователь. Мы рекомендуем Вам зарегистрироваться либо войти на сайт под своим именем.


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

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

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