Хотя ODBC – это программный интерфейс для баз данных, использование ODBC и предустановленного Microsoft Text ODBC driver открывает интересные возможности для обработки текстовых файлов (таких как txt и csv), благодаря тому, что SQL запросы типа SELECT могут быть применены к содержимому txt и csv файлов, как если бы это были таблицы базы данных!
Применение SELECT запросов для обработки данных предоставляет ряд преимуществ, которые доступны из командной строки и не требуют программирования!
- обработка данных с учётом их типа — Integer, Date, Char и др.
- сортировка строк выражением ORDER BY и группировка GROUP BY
- выборка строк по условиям с поддержкой AND, OR и LIKE
- агрегатные функции — минимальной, максимальное, среднее и суммарное значения (MIN, MAX, AVG, SUM)
- выборка неповторяющихся значений выражением SELECT DISTINCT
В этой статье:
- Строка подключения Microsoft Text Driver
- Файл Schema.ini для описания формата txt/csv
- Примеры SQL запросов к txt/csv файлам
Строка подключения Microsoft Text Driver
Driver={Microsoft Text driver (*.txt; *.csv)};DBQ=C:\ProgramData\My Program\Data\;DriverId=27;MaxBufferSize=2048;PageTimeout=5
Параметр DBQ
Единственный обязательный параметер, он задаёт драйверу путь к файлам. Путь может содержать пробелы:
DBQ=C:\ProgramData\My Program\Data\;
Для подключения к текущему каталогу достаточно указать просто точку (.) вместо пути:
DBQ=.
Строка запуска программы nhdbt:
Подключение к текущему каталогу и выполнение одного SQL запроса:
nhdbt -cs:"driver={Microsoft Text driver (*.txt; *.csv)};dbq=." -q:"select count(*) from products;"
Подключение к текущему каталогу и выполнение SQL запросов из файла test.sql:
nhdbt -cs:"driver={Microsoft Text driver (*.txt; *.csv)};dbq=." -qf:test.sql
Файл Schema.ini для Microsoft Text Driver
Файл Schema.ini file необходим, чтобы драйвер правильно интерпретировал содержимое текстовых файлов.
Schema.ini File (Text File Driver)
When the Text driver is used, the format of the text file is determined by using a schema information file. The schema information file is always named Schema.ini and always kept in the same directory as the text data source. The schema information file provides the IISAM with information about the general format of the file, the column name and data type information, and several other data characteristics. A Schema.ini file is always required for accessing fixed-length data. You should use a Schema.ini file when your text table contains DateTime, Currency, or Decimal data, or any time that you want more control over the handling of the data in the table.
Пример Schema.ini для apache_access.csv:
[apache_access.csv] Format=TabDelimited ColNameHeader=false CharacterSet=ANSI Col1=IPAddress char width 15 Col2=AccessTime date Col3=Request char width 250 Col4=Code Integer
Как только файл Schema.ini сохранён в папке, Microsoft Text драйвер может работать с файлами.
Формат полей даты и времени DateTime
Формат для даты и времени не описан в официальной документации Schema.ini File (Text File Driver), поэтому всегда вызывает вопросы.
По умолчанию, используется пользовательский короткий формат.
Например, для English US это dd/MM/yyyy HH:mm, также принимается dd/MM/yyyy HH:mm:ss. Поэтому обработка дат следующего лог файла будет работать только с региональными настройками English US:
70.69.152.165 02/22/2017 22:13:23 GET /blog/index.php HTTP/1.1... 128.196.108.201 02/22/2017 23:21:23 GET /favicon.ico HTTP/1.1... 147.106.118.104 02/22/2017 15:27:00 GET /company/ HTTP/1.1...
При других настройках, например, европейских или российских, считывание даты не произойдёт из-за несовпадения формата.
Формат даты и времени изменяется параметром DateTimeFormat. Заданный формат применяется ко всем полям даты и времени в эотм файле:
[apache_access.csv] ... DateTimeFormat=mm/dd/yyyy hh:nn:ss ...
Обратите внимание, обозначение формата не совпадает с форматом, принятым в Windows:
- nn используется вместо mm для обозначения минут
- AM/PM не поддерживается, время всегда в 24-часовом формате
- регистр не имеет значения
| Формат US | DateTimeFormat=mm/dd/yyyy hh:nn:ss |
| Европейский формат | DateTimeFormat=dd/mm/yyyy hh:nn:ss |
| Российский формат | DateTimeFormat=dd.mm.yyyy hh:nn:ss |
| Формат SQL | DateTimeFormat=yyyy-mm-dd hh:nn:ss |
Если формат считываемых данных не соответствует настройкам, драйвер использует значение NULL для этого поля.
Примеры
Ниже приведены примеры выполнения SQL запросов применительно к csv файлу с Tab-разделителями:
70.69.152.165 2017-02-22 19:39:27 GET /_media/logo_my_v2.png HTTP/1.1 304... 147.106.118.104 2017-02-22 18:00:37 GET /_css/master.1334356838.css HTTP/1.1 200... 65.98.119.36 2017-02-22 16:32:25 GET /_js/master.js HTTP/1.1 200...
Используется Schema.ini (необязательно прописывать все столбцы):
[apache_access.csv] Format=TabDelimited ColNameHeader=false CharacterSet=ANSI DateTimeFormat=yyyy-mm-dd hh:nn:ss Col1=IPAddress char width 15 Col2=AccessTime date Col3=Request char width 250 Col4=Code Integer
Программа nhdbt используется для тестирования. Все файлы можно скачать по ссылке внизу страницы.
Определить количество строк
SELECT COUNT возвращает число строк в файле-таблице.
Как определить число всех строк в файле:
select count(*) from apache_access.csv
Expr1000
-----------
9946
Как определить число строк, сгруппированное по коду (поле Code):
select Code,count(Code) from apache_access.csv group by Code order by Code
Code Expr1001
----------- -----------
200 7059
302 407
304 1525
401 224
403 5
404 527
500 75
503 124
Определение суммарных значений значение
Как определить минимальное и максимально значение поля по всему файлу:
select min(AccessTime),max(AccessTime) from apache_access.csv Expr1000 Expr1001 ------------------- ------------------- 2017-02-22 15:00:22 2017-02-22 23:33:31
Функции AVG и SUM тоже поддерживаются (для Integer).
Считать неповторяющиеся значения
Как получить только различные значения IP адресов с сортировкой:
select distinct(IPAddress) from apache_access.csv order by IPAddress asc IPAddress --------------- 101.92.120.16 128.196.108.201 14.47.165.89 147.106.118.104 161.71.8.142 19.174.45.8 221.125.19.252 30.75.225.192 34.87.4.6 46.139.54.184 5.35.225.115 52.87.131.109 65.98.119.36 70.69.152.165 78.235.33.64
Поиск по текстовым значениям
Как выделить пользователей Windows:
select distinct(UserAgent) from apache_access.csv where UserAgent like '%Windows%' UserAgent ------------------------------------------------------------------------------------------------------------ Mozilla/5.0 (Windows NT 6.1; Win64; x64; rv:23.0) Gecko/20131011 Firefox/23.0 Mozilla/5.0 (Windows NT 6.2) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/28.0.1467.0 Safari/537.36 Mozilla/5.0 (compatible; MSIE 10.0; Windows NT 6.1; Trident/4.0; InfoPath.2; SV1; .NET CLR 2.0.50727; WOW64)
Как выделить использующих Opera:
select distinct(UserAgent) from apache_access.csv where UserAgent like '%Opera%' UserAgent ------------------------------------------------------------------------------------- Opera/9.80 (iPad; Opera Mini/7.1.32694/27.1407; U; en) Presto/2.8.119 Version/11.10 Opera/9.80 (iPhone; Opera Mini/7.1.32694/27.1407; U; en) Presto/2.8.119 Version/11.10
Ограничения
Строка с разделителями может содержать одно или несколько значений, с разделителями между ними: запятая, tab или другой символ. Выбранный разделитель используется во всём файле.
Таблицы могут включать максимум 255 столбцов. Названия полей ограничиваются 64 символами, ширина поля ограничена 32 766 символами. Размер записи ограничен 65,000 байтами.
Текстовый файл может быть открыт только для одного пользователя. Несколько пользователей не поддерживается.
Для txt и csv файлов драйвер не поддерживает кодировки Unicode, такие как UTF-8 или UCS2, можно задавать или ANSI, или OEM.
Файлы для загрузки
Все файлы, включая log файл, Schema.ini, SQL запросы, test.cmd и nhdbt.exe доступны для загрузки с Google Drive:
processing-text-files-with-microsoft-text-driver.zip
Узнать больше
Статьи о Microsoft Text driver на Microsoft:
Schema.ini File (Text File Driver)
Text File Format (Text File Driver)
nhdbt – бесплатная программа для Windows, выполняющая SQL запросы из командной строки.