Страницы: 1
RSS
Импорт очень большого текстового файла (>10 млн строк, и объем около 2 ГБ)
 
Добрый день!

Подскажите, пожалуйста как оптимально по скорости/памяти решить следующую задачу:  

Имеются ежемесячные выгрузки из внешней системы, на которую я никак повлиять не могу.
Выгрузки представляют из себя текстовые файлы с разделителямя (зачисления).
Размер файла 1,5 - 2 ГБ и количество строк более 10 млн. Каждая строка = 1 зачисление , около 30 полей, из которых мне нужно только 5. (ФИО, Сумма, Дата операции, Дата рождения, тип зачисления) . Для одного клиента может быть несколько  зачислений (строчек).

Есть справочник клиентов (текстовый файл ~4 млн.записей), в котором хранятся только ФИО + Дата рождения

Мне необходимо из первого файла выбрать клиентов которые отсутствуют в справочнике и удовлетворяют определенным условиям. Таких ежемесячно бывает примерно 30-40 тыс. ФИО + ДР отобранных добавляем в справочник.

Соответственно,  чтобы решить данную задачу мне сейчас необходимо понять следующее:
1. Как правильно с точки зрения скорости/памяти считать текстовый файл в массив если я не знаю количество строчек в нем.Мне нужно будет в какой-то момент менять размерность массива, как это сделать лучше?

2. Прежде чем сравнивать массивы, по-любому придется их сортировать по полю ФИО. Подскажите пожалуйста самый быстрый алгоритм, который не использует вспомогательные массивы. Не хочется делать дополнительные копии массива в памяти.    
 
А точно это должен быть Excel? Тут пора применять Access...
F1 творит чудеса
 
в любом случае, я бы посоветовала через ADO+SQL - количество строк знать не надо, обращение к txt c разделителем - как  к столбцам, отбор сделать нужной SQL командой... примеры примерно такие:
Точное считывание с текстового файла
ADO в Excel и большой размер файла
Цитата
tvit написал:
1. ... считать текстовый файл ...?
2. ... сортировать по полю ФИО. ...
- и уже в SQL-команду можно вставить нужные условия отбора WHERE, и Сортировку по нужному полю ORDER BY
P.S.(согласна с Максимом)
пример:
Использование Access для обработки больших текстовых файлов #17
Изменено: JeyCi - 26.08.2015 18:09:51
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
 
Доброе время суток
Цитата
Размер файла 1,5 - 2 ГБ и количество строк более 10 млн. Каждая строка = 1 зачисление , около 30 полей, из которых мне нужно только 5. (ФИО, Сумма, Дата операции, Дата рождения, тип зачисления) . Для одного клиента может быть несколько  зачислений (строчек).
Есть справочник клиентов (текстовый файл ~4 млн.записей), в котором хранятся только ФИО + Дата рождения
Такой объём явно выгружен из какой-то базы данных, что-то с трудом вериться, что кто-то в блокноте ведёт эти записи. Стоит ли тогда "огород городить" с выгрузкой и анализом через сравнение массивов, или даже, как предлагают, связывать текстовые данные в Access для построения запросов? Может сразу в этой же базе SQL-запросами получать требуемое?
 
Думаю вполне можно и кодом работу сделать - сперва читаем справочник в словарь, можно всё в массив, или построчно прочитать.
Затем читаем построчно гигабайты, проверяем свои определённые условия и затем наличие в словаре - если нужно отобрать, то отбираем в коллекцию или другой словарь (так не нужно думать о размерности).
В итоге собранное дописываем в справочник, реализация по вкусу (можно циклом по одному, можно сразу всё выгрузить - думаю разница на общем фоне не будет существенной).
 
Спасибо всем ответившим, только я другие вопросы задавал. :-)
Через SQL задача уже реализована и работает, проблема в том, что это нужно передать другому человеку в другой город, у которого есть только Excel и Word

To HUGO: Вариант с заменой массивов словарем рабочий,  вопрос только на сколько это будет медленнее по скорости чем с массивами? И еще вопрос, помню как то работал со словарем и там был глюк, который я так и не смог обойти: При проверки наличия значения в словаре оно почему-то самопроизвольно добавлялось в этот словарь, я так и не смог до конца разобраться в проблеме посему так происходит?

PS На выгружаемый файл из внешней системы я никак повлиять не могу, считайте что мне его робот по почте присылает :-)
 
А версия Excel какая?
 
Доброе время суток
Цитата
Через SQL задача уже реализована и работает
Цитата
проблема в том, что это нужно передать другому человеку в другой город
Не совсем понимаю суть проблемы, почему нужно другому человеку отправлять исходные данные, чтобы он у себя в Excel их обрабатывал? А почему нельзя отправить уже реализованный результат?
Почему у вас ошибка со словарём - надо смотреть ваш код. Проверка наличия ключа в словаре выполняется Dictionary.Exists(KeyValue) - словарь это пара ключ/значение.
 
Цитата
tvit написал:
на сколько это будет медленнее по скорости чем с массивами?
это будет быстрее. в разы, скорее - в десятки раз.
Цитата
tvit написал:
работал со словарем и там был глюк, который я так и не смог обойти: При проверки наличия значения в словаре оно почему-то самопроизвольно добавлялось в этот словарь
это не глюк.
для проверки следует использовать Exists, любое другое обращение к несуществующему элементу - добавляет его.
это штатное поведение словаря.
фрилансер Excel, VBA - контакты в профиле
"Совершенствоваться не обязательно. Выживание — дело добровольное." Э.Деминг
 
Цитата
StepanWolkoff написал: А версия Excel какая?
2010
 
Получается если считывать в Коллекцию
Цитата
Андрей VG написал: почему нужно другому человеку отправлять исходные данные, чтобы он у себя в Excel их обрабатывал?
Данные никто никому не отправляет, другой человек сам их выгрузит из той же внешней системы

Цитата
Андрей VG написал: Проверка наличия ключа в словаре выполняется Dictionary.Exists(KeyValue) - словарь это пара ключ/значение.
Сейчас уже не вспомню, возможно я действительно проверял более "хитрым" способом (присваивание внутри on error)
 
Цитата
другой человек сам их выгрузит из той же внешней системы
Вы правда не назвали, что это за система. Но всё равно не понимаю, если человек имеет к ней доступ, то кто мешает ему получать (в том числе и обновляемые) данные, используя SQL, в книгу Excel?
Например, для MS SQL Server
Код
    Const connStr As String = "ODBC;Driver={SQL Server Native Client 11.0};Server=(localdb)\mssqllocaldb;Database=SampleDb;Trusted_Connection=yes;"
    Dim pLO As ListObject, pSheet As Worksheet
    Set pSheet = ThisWorkbook.Worksheets.Add
    Set pLO = pSheet.ListObjects.Add(xlSrcExternal, connStr, True, xlYes, pSheet.Range("A1"))
    With pLO.QueryTable
        .CommandType = xlCmdSql
        .CommandText = "Select * From Production.Products"
        .Refresh
    End With

Естественно, CommandText может быть более сложным SQL запросом, чем в приведённом примере. Можно это же сделать и без программирования, задействовав MS Query.
Изменено: Андрей VG - 27.08.2015 09:47:06
 
Я думаю что SQL будет работать намного медленнее кода на словаре и массивах.
На работе простая выборка по одному полю из 12 тысяч записей работает секунд наверное 40 на 2007 ацессе - она же ранее на 2003 отрабатывала не раздражая... хотя тоже не пулей. Не работаю регулярно с ацесс, и не вникал, т.е. это не моя каждодневная рабочая задача, но удивляет такая медлительность...
 
Попробуйте посмотреть в сторону надстроек Power Pivot или Power Query. Последняя надстройка (PQ) вообще творит чудеса. Может сделать всё что угодно и как угодно без макросов. Поддерживаются следующие версии Office:
    • Microsoft Office 2010 Professional Plus c Software Assurance
    • Microsoft Office 2013
 
Поддерживаю Dmitryktm, поэтому и был вопрос про версию.
Из личного опыта - скармливал одновременно три таблицы (текстовых файла) 21млн, 7млн и 300тыс.
Тут главное оперативной памяти не жалеть)))
 
Dmitryktm, спасибо, но это не вариант. Я не администратор своей машины, и тот человек кому я передаю программу тоже ничего не сможет установить.

Сейчас пробую вариант с коллекциями. Excel занял 1.2 ГБ оперативки. пока работает, Но я на 5 млн пробовал. В след месяцах (декабре и мае) могут быть двойные зачисления, тогда не знаю что будет  
 
Кнопка цитирования не для ответа
 
Игорь, зря вы так по поводу SQL. Решил, коль целый день делать нечего с имитировать (упрощённо) задачу ТС. Создал два тестовых файла.
1. Users таблица клиентов ([UserName] ФИО - 16 символьное поле; [UserBirthday] Дата ФИО - дата).
2. UserData таблица покупок ([UserName] ФИО - 16 символьное поле; [DateBuy] Дата Покупки - дата; [Ammount] Сумма - плавающее).
Код был следующий
Скрытый текст

В Users 2 000 000 записей, в UserData 15 409 218 (часть записей для 500 000 ФИО отсутствует в Users). Собственно, задача отобрать в UserData ФИО, которых нет в Users (естественно, без повторений, так как в UserData могут быть сведения об нескольких покупках одного ФИО). В коде выше добавлены процедурой AddNotIn в UserData.

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

Подождал некоторое время (минут 15) прервал.

Далее, загнал в Access, проиндексировал обе таблицы по полю UserName, далее следующим кодом получил результат за 65 секунд
Скрытый текст

Ну, и тоже самое для этих же данных, помещённых в MS SQL LocalDb (естественно с индексами по UserName) - результат получен за 13 секунд
Скрытый текст

Думаю, для столь объёмных данных выводы очевидны. Не забывайте использовать в базах данных индексы и строить запросы так, чтобы эти индексы задействовались.
 
Split(pStream.ReadAll, vbCrLf) может быть долго на большом файле, и памяти займёт много. Может лучше читать файл построчно - по времени может чуть дольше (а может и нет), но память должна экономиться.
For i = 1 To UBound(strOut) - там в файле есть строка заголовка?
Ключи можно выгрузить на лист сразу, без допмассива, используя Application.Transpose(notInDict.keys) - если правда Application.Transpose такой объём потянет.

"загнал в Access, проиндексировал обе таблицы по полю UserName" - это время не учитывалось?

Но вообще если SQL отработал намного быстрее - то это отлично. Вариант на словаре вроде оптимально написан, но думаю основное время теряется на чтении файлов в массив, и на этих Left$().
Кстати, где эта процедура в SQL? :)
У словаря есть одно преимущество - там всё понятно что когда и как происходит :)
 
Цитата
Может лучше читать файл построчно - по времени может чуть дольше (а может и нет), но память должна экономиться.
Был и такой вариант - разницы особой не вижу, этот показался, что будет быстрее, по идее в Excel 2010 64bit на ПК с 8Гб памяти поместится должен.
Цитата
For i = 1 To UBound(strOut) - там в файле есть строка заголовка?
Есть, но slit же индексирует начало массива с 0, хотя согласен For i = LBound(strOut) + 1 - правильнее.
Цитата
если правда Application.Transpose такой объём потянет.
Увы, не потянет - результат почти полмиллиона строк.
Цитата
"загнал в Access, проиндексировал обе таблицы по полю UserName" - это время не учитывалось?
Нет, не учитывалось. Я всё пытаюсь довести мысль до tvit, что не логично выгружать данные из базы, чтобы потом кустарным способом делать тоже самое. Правда, не смотря на то, что ТС тут мимо пробегает, что не так и что не устраивает НАГЛО не сообщает. Ни тебе спасибо, ни пожалуйста.
Цитата
Вариант на словаре вроде оптимально написан, но думаю основное время теряется на чтении файлов в массив, и на этих Left$(). Кстати, где эта процедура в SQL?
Так она выполняется утилитой импорта, а как писал выше - это время не учитывалось.
Цитата
У словаря есть одно преимущество - там всё понятно что когда и как происходит
С другой стороны SQL и базы для того разрабатывались, чтобы думать над реализаций логики получения данных в требуемом виде, а не над особенностями хранения и выбора лучшего алгоритма получения (хотя и тут присутствуют варианты и свои рекомендации для более быстрого выполнения);)

P. S. Прогнал ещё на SQLite - 22 секунды - хорош.
Изменено: Андрей VG - 27.08.2015 17:08:44
 
Андрей VG, добрый вечер. А можно Вас попросить любопытства ради попробовать вместо DISTINCT группировку? Будет ли быстрее?

ЗЫ: а можно в Access еще проиндексировать много полей, чтобы вообще быстро было :D
Изменено: Smiley - 27.08.2015 17:11:20
Учусь программировать :)
 
Smiley, добрый вечер.
Да почти так же 62 сек. Прогнал и чисто текстовый вариант с написанием schema.ini
Код
.CommandText = "Select Distinct tud.UserName From [data.csv] as tud Left Join [user.csv] as tu On (tud.UserName=tu.UserName) Where tu.UserName Is Null"
вышло 105 секунд.
Индексировать - тоже можно нарваться - не только же выборки есть, но и вставки, обновления, а это всё пересчёт индексов - может и притормозить. Из всех протестированных сегодня баз данных - Access самый тугодумный;)
Изменено: Андрей VG - 27.08.2015 18:05:37
 
Цитата
Андрей VG написал:
Индексировать - тоже можно нарваться - не только же выборки есть, но и вставки, обновления, а это всё пересчёт индексов - может и притормозить
ну я поэтому и поставил смайлик :)
Учусь программировать :)
 
Реализовал свою идею через Словари. Скорость приемлемая, за исключением последнего шага. В котором я пытаюсь сохранить Словарь в файл. На меленьких файлах скорость приемлемая, но когда размер справочника 4 млн строк, а размер занимаемой памяти Excel около 1 Гб, сохранение идет очень долго - в минуту не более 1000 строк  :
Код
Public Sub ClosePensSprav(FileName As String)
Dim txtFile As TextStream
Dim TmpString As String
Dim Ndx As Long
Dim lLowVal As Long
Dim lHighVal As Long
Dim lStep As Long

    If Not (FSO.FileExists(FileName)) Then FSO.CreateTextFile (FileName)
    Set txtFile = FSO.OpenTextFile(FileName, ForWriting)
    
    lHighVal = PensSprav.Count
    lStep = CLng(lHighVal / 100)
    
    For Ndx = 0 To PensSprav.Count - 1

        lLowVal = Ndx
        If lLowVal Mod lStep = 0 Then Application.StatusBar = "Сохранение справочника:" & FSO.GetFileName(FileName) & ": " & sObrStr(lLowVal, lHighVal) & sSuff(CInt(lLowVal / lHighVal * 100))

        TmpString = PensSprav.Keys(Ndx) & "|" & PensSprav.Items(Ndx)
        txtFile.WriteLine (TmpString)
        DoEvents
    Next Ndx
    
    txtFile.Close
    Application.StatusBar = False
    Set txtFile = Nothing
    Set PensSprav = Nothing

End Sub
 
Также заметил что размер памяти при закрытии файлов и очистки вспомогательных словарей, уменьшается не сильно. Объем моего справочника 4.5 млн строк в среднем по 45 символов каждая, итого не более 200 Мб, а Эксель занимает около 1 ГБ, не пойму почему
 
Прошу прощения что сразу не отвечал на вопросы. У меня Интернет на отдельной машине. Поэтому форум нет возможности читать регулярно.
На SQL Задача уже реализована давно и работает с очень быстрой скоростью, но мне нужно именно на Excel.  
 
Доброе время суток
tvit, а у меня в #22 именно на Excel, с использованием движка от Access. Если Excel 32битный для SQL операций с текстовыми файлами можно задействовать Jet "движок" Access с XP по Win7 штатно стоит в системе (только в этом случае для них лучше прописать schema.ini). Что с 8 и 10 версией не знаю.

P. S. А не поделитесь - сколько времени у вас уходит на поиск отсутствующих?
Изменено: Андрей VG - 31.08.2015 16:36:39
 
Цитата
Андрей VG написал:
P. S. А не поделитесь - сколько времени у вас уходит на поиск отсутствующих?
Завтра на работе запущу и отчитаюсь
 
to Андрей VG

Построчное считывание файла и занесение во временный словарь: 2 476 710 за 5 минут 47 секунд
Обновление пустого справочника 1 390 342 строчек за 4 мин 45 сек

Построчное считывание 2 файла: 2 025 113 за 4 мин 34 сек
Обновление справочника: 1 222 422 строчек за 7:38

При попытке сохранить словарь 2.5 млн строчек в файл, комп умирает. Сохраняется не более 1000 строк в минуту, как бы это ускорить?
 
Доброе время суток
Попробуйте открыть текстовый файл справочника на добавление записей, и добавлять только отсутствующие в нём записи, а не создавать его с ноля.
Код
Set txtFile = FSO.OpenTextFile(FileName, ForAppending)

Хотя не понимаю, почему вы не хотите использовать Jet Access Engine?
В заведомо проигрышных условиях. Виртуальная машина Windows 7 32bit  (хостовая машина Windows 7 64bit на ноутбуке с двуядерным Intel i7 2011 года выпуска), установленная на внешний ноутбучный HDD, подключенный через USB2.0. По примеру, исходная структура описанная в #18 (2000000 исходных).  Добавлялось к справочнику пользователей два поля ФИО, ДатаРождения (добавлено 500000 записей). И на таких условиях время выполнения 188 секунд - 3 минуты 8 секунд. Может всё же не городить огород? Не стандартные разделители полей в текстовых файлах легко описываются в schema.ini. Да и весь код был в vbscript.
Вы бы уж описали задачу чуть поподробнее и приложили бы пример файлов.
Изменено: Андрей VG - 01.09.2015 19:33:26
Страницы: 1
Наверх