Excel/PowerQuery/VBA, Анализ данных

Выгрузка информации из текстового файла по определенному параметру.

Время прочтения: 2 мин.

Сегодня большинство пользователей Excel умеют загружать данные из текстовых файлов с помощью встроенного мастера загрузки, но это только один из этапов решения задачи. Дальше необходимо отфильтровать нужные записи и вот здесь и начинаются трудности: Excel «зависает», не может справиться с большим объемом данных. Бывалые IT-спецы посоветуют использовать SQL и другие средства работы с базами данных, но ….есть другой несложный метод.

Итак, необходимо решить следующую задачу:

Исходный текстовый файл с разделителями «|» содержит данные обо всех операциях клиентов банка: в офисах банка, в банкоматах, в он-лайн сервисах, в магазинах и проч.

Необходимо выбрать операции, совершенные только в офисах банка. Данные операции в текстовом файле отмечены признаком «Офис банка» построчно.

Решение:

  1. Создаем новый файл Excel, в нем создаем лист «Результат», сюда будут записываться строки из файла, удовлетворяющие нашему критерию.
  2. Не забываем сохранить файл с поддержкой макросов.
  3. Открываем редактор VBA (Alt+F11).
  4. Создаем процедуру.
Sub ЗагрузкаТекста()
    Dim Stroka, Razdel, a() As String 'объявляем некоторые переменные
       
    Filename$ = GetFilePath() 'текстовый файл выбираем в стандартном окне Windows 
    If Filename$ = "" Then Exit Sub
    
    'очистка таблицы перед загрузкой
    last_row = Worksheets("Результат").UsedRange.Rows.Count
    Worksheets("результат").Activate
    Worksheets("результат").Rows("1:" & last_row).Delete Shift:=xlUp
    
    Razdel = "|" 'задаем формат разделителя полей в текстовом файле
    k = 1 'счетчик записей в результирующей таблице
    Open Filename$ For Input As #1 ' открываем файл для чтения строк
    Do While Not EOF(1) 'цикл по строкам файла
        Input #1, Stroka 'считывание текущей строки
        If InStr(Stroka, "|Офис банка|") > 0 Then 'проверяем строку на предмет вхождения подстроки "Офис банка"
            'разделяем строку на поля и записываем в ячейки на листе "Результат"
            a = Split(Stroka, Razdel)
            k = k + 1
            For i = 1 To UBound(a)
      Worksheets("Результат").Cells(k, i) = Trim(a(i))
            Next i
        End If
    Loop    
    Close #1 'закрываем текстовый файл
    MsgBox ("ГОТОВО!")
End Sub

Ниже приведен код функции выбора файла GetFilePath

Function GetFilePath(Optional ByVal Title As String = "Выберите файл для обработки", _
                     Optional ByVal InitialPath As String = "c:\", _
                     Optional ByVal FilterDescription As String = "файлы txt", _
                     Optional ByVal FilterExtention As String = "*.txt") As String
    On Error Resume Next
    With Application.FileDialog(msoFileDialogOpen)
        .ButtonName = "Выбрать": .Title = Title:
        .InitialFileName = GetSetting(Application.Name, "GetFilePath", "folder", InitialPath)
        .Filters.Clear: .Filters.Add FilterDescription, FilterExtention
        If .Show <> -1 Then Exit Function
        GetFilePath = .SelectedItems(1)
        folder$ = Left(.SelectedItems(1), InStrRev(.SelectedItems(1), "\"))
        SaveSetting Application.Name, "GetFilePath", "folder", folder$
    End With
 
End Function
Советуем почитать