Страницы: Пред. 1 2 3 4 След.
RSS
Поиск в файле csv, не открывая его
 
Цитата
Hugo написал: objFile.WriteLine Split(strNextLine, ";")(11)
Но это если разделитель полей ;
Спасибо за разъяснение. Проверил на файле в 1,2 Гб. Все работает: и режет по 500 000 строк, и извлекает нужный столбец.

Цитата
PooHkrd написал: можно загрузить весь ваш массив в модель данных и вытаскивать нужную строку фильтром сводной таблицы
Готов попробовать. Но не знаю, что означает "загрузить весь массив в модель данных". Как это делается? Используется ли для этого что-то дополнительно, кроме Excel?
 
excel_and, какая версия Excel?
Ради интереса задайте путь к папке с файлами по 500 000 строк и попробуйте нажать Ctrl+Alt+F5 (желательно сначала проверить файлах на тридцати)
Разделитель - ; (точка с запятой)  
Изменено: Михаил Л - 18.05.2020 19:59:45
 
Цитата
excel_and написал:
(особенно, если будут использоваться штатные средства винды и стандартного офиса).
Здесь ребята справятся лучше меня.
Подумал о таком решении, можно реализовать, в виде exe или .xll-надстройки для Excel. Но это не штатные средства Excel и предложенные выше решения вполне рабочие.
Хотя, конечно, интересно за сколько секунд будет обрабатываться данные в .exe или xll.
Изменено: bedvit - 18.05.2020 19:56:57
«Бритва Оккама» или «Принцип Калашникова»?
 
Цитата
Михаил Л написал:
excel_and , какая версия Excel?Ради интереса задайте путь к папке с файлами по 500 000 строк и попробуйте нажать Ctrl+Alt+F5 (желательно сначала проверить файлах на тридцати)Разделитель - ; (точка с запятой)
Excel 2010. Не совсем понял, что нужно сделать с файлом. Если в ячейку A1 (по умолчанию указано "D:\temp\папка") ввести путь к папке поиска, я в ячейку A3 (по умолчанию указано "А") ввести идентификатор для поиска (он 100% присутствует в папке поиска), то по нажатию Ctrl+Alt+F5 у меня появляется сообщение "Сбой инициализации источника данных. Проверьте сервер или обратитесь к администратору базы данных. Убедитесь, что внешняя база данных доступна и вновь повторите операцию. При повторении сообщения создайте новый источник для подключения к базе данных".
Цитата
bedvit написал:
Подумал о таком  решении , можно реализовать, в виде exe или .xll-надстройки для Excel.
Да, это будет слишком сложно для меня :)
 
Цитата
excel_and написал:
Excel 2010
По ссылке описано где брать и как установить Power Query
 
Цитата
excel_and написал:
в ячейку A3 (по умолчанию указано "А") ввести идентификатор для поиска (он 100% присутствует в папке поиска)
Вводить в А3 не надо, только путь к папке в А1. После нажатия трех клавиш запрос в каждый столбец введет 12 столбец файлов. Пока посмотрим сколько времени надо
 
Цитата
Михаил Л написал:
описано где брать и как установить Power Query
но не написано что
Поддерживаются следующие версии Office:
Microsoft Office 2010 Professional Plus c Software Assurance
 
Цитата
Hugo написал:
Professional Plus c Software Assurance
Не знал я что и Excel 2010 разный бывает. Извиняюсь если кого ввел в заблуждение
 
Цитата
Михаил Л написал:
Пока посмотрим сколько времени надо
Много надо времени! Чтобы решать эту задачу через PQ очень желательно знать какие диапазоны искомых значений в каком файле находятся, тогда можно заранее знать в какой файл лезть. Но даже так, когда запрос полезет в файл на 30 гигов это будет что-то с чем-то.
И еще чтобы протащить такой массив офис обязательно должен быть 64битный. 32 бит такой объем не потянет от слова совсем.
Цитата
excel_and написал:
что означает "загрузить весь массив в модель данных". Как это делается?
Это означает использование надстройки Power Pivot. Но боюсь что в вашем случае (Эксель 2010) вряд ли оно потянет. Глючновата эта надстройка для той версии.
Но опять же если идея в том чтобы вытаскивать из огромного массива значения фильтрами, то можно скачать MS Power BI Desktop он бесплатный, и загрузить в модель данные с его помощью, а далее уже фильтрами вытягивать нужные значения в аналог экселевской сводной таблицы.
Вот горшок пустой, он предмет простой...
 
https://www.microsoft.com/ru-ru/download/details.aspx?id=39379
там же и
Поддерживаются следующие версии Office:
Microsoft Office 2010 Professional Plus c Software Assurance
Microsoft Office 2013
По вопросам из тем форума, личку не читаю.
 
MS Power BI ~~ Qlik, что я уже упоминал на той ещё странице в #6
Если не влезет всё - то думаю можно в модель загрузить только эти артикулы с координатами (имя файла, номер строки), а далее уже вытягивать всю строку чем-то другим.
Получится типа словаря :)
 
Цитата
Михаил Л написал:
Вводить в А3 не надо, только путь к папке в А1. После нажатия трех клавиш запрос в каждый столбец введет 12 столбец файлов. Пока посмотрим сколько времени надо
Установил поверх своего офиса (x86) Power Query (тоже x86) по вашей ссылке.
Решил просто попробовать на одном файле размером 1,2 Гб. Ввел путь к папке с этим единственным файлом, нажал Ctrl+Alt+5. Ждал 5 минут. Появилось сообщение "Данные, возвращаемые запросом, не умещаются на листе". Подтвердил ОК и отобразились данные из 12 столбца (насколько это поддерживает эксель). Смутило, что символы (кроме цифр), в большинстве своем, отображаются в виде крякозябр. Дело в кодировке. Но сам эксель (не через запуск в Power Query) символы отображает корректно. Вот пример того, что вижу.
Решил попробовать на папке, где находятся 10 файлов по 500 000 строк, полученные из файла в 1,2 Гб. Ждал 15 минут, пока откроются 10 столбцов, содержащих 12-ый столбец каждого из файлов в папке. Опять отображаются крякозябры. Действительно, компьютер подвисает очень прилично. Но памяти чуть свободной остается (из 4 ГБ оперативки где-то 20% свободны), а вот процессор все время загружен на 100%. При этом никаких активных процессов, которые расходуют прилично память, на ПК не запущено.
Итог по процессу: все работает, только расходует много памяти и отображает крякозябры. Но я так понял, что Power Query гораздо шустрее обрабатывает тяжелые файлы в сравнении с эксель. Думаю, что фильтры и формулы в Power Query будут работать так же быстро. Но для этого нужно знать синтаксис PQ.
Цитата
PooHkrd написал:
Но опять же если идея в том чтобы вытаскивать из огромного массива значения фильтрами, то можно скачать MS Power BI Desktop он бесплатный, и загрузить в модель данные с его помощью, а далее уже фильтрами вытягивать нужные значения в аналог экселевской сводной таблицы
Да, все верно. Именно такая идея была изначально. И это самый оптимальный вариант. Тут вопрос в производительности, т.е. насколько шустро MS Power BI Desktop будет обрабатывать все это тяжелое добро. В принципе, на домашнем ПК можно и попробовать. Но софт ведь тоже требует каких-то специфических знаний команд и т.п.?
Цитата
Hugo написал:
MS Power BI ~~ Qlik, что я уже упоминал на той ещё странице в #6Если не влезет всё - то думаю можно в модель загрузить только эти артикулы с координатами (имя файла, номер строки), а далее уже вытягивать всю строку чем-то другим.Получится типа словаря
Да, так конечно удобно. Но вот реализовать я сам не смогу (скачать и установить могу все что угодно) :)
Изменено: excel_and - 19.05.2020 02:12:56
 
Цитата
excel_and написал:
10 файлов по 500 000 строк, полученные из файла в 1,2 Гб. Ждал 15 минут, пока откроются 10 столбцов
Да, это не вариант.
 
Цитата
excel_and написал:
поверх своего офиса (x86) Power Query (тоже x86)
Этим инструментам, при таких объемах данных сильно помогает x64 и доп. память. даже если исходные данные уместятся в памяти то на прочие операции её будет не хватать. Ну а в целом, если файлы статичны, и в крайнем случае дополняются, то обрабатывать такие объемы текстовых файлов плохая идея. Но загрузить это в базу идея хорошая но не простая. Именно из-за объемов. Access -  2ГБ, SQL express 10ГБ,  далее
уже платный SQL Standard или свободные MongoDB, PostgreSQL, но все это не для пары кликов.

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

Фактически имеем в три таблицы.
1 Справочник имен файлов (ID,Filepath)
2 Значения (ID,Column12Value)
3 номера стroк (ID, AValueID, FileID, Rownumber)
Изменено: БМВ - 19.05.2020 08:24:19
По вопросам из тем форума, личку не читаю.
 
Приложите небольшой пример csv-файла, что ищем и какой результат возвращаем. Будет время сделаю вам библиотеку, думаю, будет быстрее всего, здесь предложенного. У вас сколько ОЗУ, какой разрядности система, в каком формате/кодировке csv файл?
Изменено: bedvit - 19.05.2020 11:12:22
«Бритва Оккама» или «Принцип Калашникова»?
 
Доброе время суток.
Покрутил. Исходно csv-файлы в папке csv files содержали по миллиону строк. Общий объём более 5 Гбайт. VBA через SQL (да In с 20 вхождениями - не лучшее решение, согласен) - выполнялся 766 секунд. Power Query версия 56 секунд.
Если кого заинтересует, то архив распаковывать в папку C:\Path
 
Андрей VG, а если по словам ТС все значения в 12 столбце уникальные, то можно перебирать файлы List.Generate'ом и остановиться в случае нахождения полного результата. По идее должно быть еще быстрее.
Вот горшок пустой, он предмет простой...
 
Добрый день, коллеги! Андрей, напоминаю результат из #8. На мой взгляд, этот показатель принципиально не улучшить, если только не записывать в SQL базу (о чем писал Михаил). На мой взгляд, SQLite c этим прекрасно справится (для нее все есть готовое).
Владимир
 
QLIK после загрузки модели может выгрузить данные в свои оптимизированные файлы, которые и места занимают поменьше, и грузятся быстрее.
Мы делали в два захода - сперва одним приложением тянули всё что есть в 1С в эти файлы (попутно приводя к нужному виду), затем уже разными другими из этих чистых оптимизированных данных выводили данные в визуализации кому что нужно.
Это для информации, как можно реализовывать в принципе.
P.S. цитата с одной темы Телеграма по Qlik
- Интересный факт - 20 гб. csv файлов при перегонке в qvd весят 700 мб)
- Так это известное поведение, у меня в проекте 17 тыс текстовых файлоа по 0.5-3 мб в QVD было 150 мегабайт))
Изменено: Hugo - 19.05.2020 15:46:20
 
Цитата
БМВ написал:
А имеющиеся инструменты - ну да, поддерживаю идею индексирования файлов по 12му столбцу.
Да, этот вариант работает гарантированно.https://shtr
Цитата
bedvit написал:
Приложите небольшой пример csv-файла, что ищем и какой результат возвращаем. Будет время сделаю вам библиотеку, думаю, будет быстрее всего, здесь предложенного. У вас сколько ОЗУ, какой разрядности система, в каком формате/кодировке csv файл?
Спасибо. Приложу чуть позже. Или напишу в ЛС. ОЗУ - 4 Гб, система win 8.1 x64, кодировка ANSI.
Цитата
Андрей VG написал:
Если кого заинтересует, то архив распаковывать в папку C:\Path
А как мне проверить ваш способ? В папку "C:\Path\csv files" закинуть свои тяжелые файлы и потом запустить query.xlsb? Так сделал, но ничего не получилось :)
sokol92
Hugo
Я встречал базы с еще большим числом данных, но они и весили гораздо меньше (как описывает Hugo) и поиск в них происходит очень шустро. Это базы в системе Cronos. Может, стоит перегнать все в эту базу? Никто не сталкивался с перегонкой в Cronos?
Изменено: excel_and - 19.05.2020 16:33:34
 
Цитата
sokol92 написал:
напоминаю результат из #8
Добрый вечер, Владимир.
Ну, можно ещё и регулярками попробовать, правда мне так и не стала понятна цель задачи - простое подтверждение того, что есть такой код в каком-нибудь из файлов?
Цитата
PooHkrd написал:
По идее должно быть еще быстрее.
Ну, в лучшем случае может быть раза в два. Но не факт - визуально по прогрессу больше всего времени занимает загрузка файла, а не собственно нахождение следующего значения. Тестить лениво. Пусть будет в два раза быстрее :)
Цитата
excel_and написал:
А как мне проверить ваш способ?
Адаптировать тот или другой код под структуру ваших файлов, естественно. Вы же постеснялись выполнить не одну такую просьбу
Цитата
bedvit написал:
Приложите небольшой пример csv-файла
Следовательно был сделан вывод - вы считаете себя вполне самодостаточным разработчиком, чтобы довести решение до требуемого самостоятельно :)
 
Цитата
Андрей VG написал:
Адаптировать тот или другой код под структуру ваших файлов, естественно. Вы же постеснялись выполнить не одну такую просьбу
Постеснялся от непонимания того, как это работает, и хватит ли моего уровня для адаптации предложенного решения :)
 
Цитата
Андрей VG написал:
Следовательно был сделан вывод - вы считаете себя вполне самодостаточным разработчиком, чтобы довести решение до требуемого самостоятельно
Андрей, привет! Не понял твою мысль :)
excel_and, скиньте файл, я вам сделаю простое решение - функцию в Excel, возвращающую нужную часть строки или всю строку, по первому совпадению из указанного csv.
«Бритва Оккама» или «Принцип Калашникова»?
 
В итоге можно сделать примерно как я и описывал в начале -
- при открытии файла макрос просматривает все эти небольшие с одним столбцом, и собирает словарь с артикулами, у каждого точный адрес.
И держит это в памяти.
- нужно найти ASD123FGH456 - спрашиваем у словаря - он выдаёт адрес в функцию, которая сразу идёт куда послали, и берёт что указали. и показывает. Можно это всё оформить в форме, или выводить на лист - смотря по задаче.
Т.к. эти монстры уже разбиты на небольшие файлы - тормоза будут только при открытии этого инструмента, но это можно и потерпеть один раз утром, пока кофе то да сё...
Главное чтоб хватило памяти под словарь... Нужно пробовать.
 
Цитата
bedvit написал:
excel_and , скиньте файл, я вам сделаю простое решение - функцию в Excel, возвращающую нужную часть строки или всю строку, по первому совпадению из указанного csv.
Во вложении - пример СТРУКТУРЫ csv-файлов, в КОТОРЫХ нужно искать заданные совпадения (предположим строку "ASD123FGH456", она как раз присутствует в примере). Из-за огромного размера нескольких файлов пришлось разбивать их на части, по 500 000 строк (с помощью скрипта, который посоветовал Hugo), что на выходе дало много csv. Соответственно поиск по совпадению должен производиться во всех этих csv, которые можно положить в одну папку. Это, как уже обсуждалось, можно сделать при помощи консольной утилиты find, которая входит в состав Windows. Правда find работает не очень быстро, но все же работает.

Идея с индексом (будем это называть так) заключалась в том, чтобы создать ОТДЕЛЬНЫЙ ФАЙЛ ИЛИ НЕСКОЛЬКО (ввиду ограничений Excel на число строк, а их будет около 130 млн.!!!), в котором бы присутствовали заданные идентификаторы (извлеченный из всех csv 12-ый столбец (СТОЛБЕЦ L) только с идентификаторами). Если поиск нужного идентификатора в ИНДЕКСНОМ ФАЙЛЕ (ФАЙЛАХ) находит совпадение, то уже тогда целесообразно производить поиск, как указано выше. Т.е. чтобы сэкономить время на поиск во всех csv, сперва нужно убедиться, что сам идентификатор присутствует в ИНДЕКСНОМ ФАЙЛЕ/ФАЙЛАХ со всеми извлеченными заранее из всех csv идентификаторами. И уже потом выводить номер строки со ссылкой на соответствующий csv из папки (или всю строку полностью, желательно также со ссылкой на исходный файл).

Если при реализации функции будет возможен поиск с использованием подстановочных символов "*" (заменяет любое число символов) или "?" (заменяет один символ), это будет самое идеальное решение. Это объясняется тем, что на основе анализа выявлены идентификаторы, где одинаковые по написанию символы на латинице и на кириллице создают проблему при ТОЧНОМ поиске идентификатора. Например "ASD123FGH456" (в латинице) может визуально быть похожим на "АSD123FGН456" (в кириллице). Вопрос с поиском всегда бы заканчивался удачно, если бы строка для поиска могла бы быть задана в виде "?SD123FG?456".

Заранее спасибо.
Изменено: excel_and - 19.05.2020 19:17:08
 
Цитата
bedvit написал:
Не понял твою мысль
Привет, Виталий.
Я вашу цитату привёл как пример на запрос файла-примера. И вот, не прошло двух суток... А остальное укоризна ТСу.
Загнал в SQLite, проиндексировал. Power Query даже с ленивым In загружает за менее секунды (дольше ширину столбцов подгоняет).
Код
let
    Source = Odbc.Query("DRIVER=SQLite3 ODBC Driver;Database=C:\Path\SQLite\demoSQLite.sqlite;LongNames=0;Timeout=1000;NoTXN=0;SyncPragma=NORMAL;StepAPI=0;", "Select * From [col12Table] Where [Column12] In ('abc000000003','bcd000000003','cde000010003','def000000003','efg000080003','fgh000000003','ghi000000003','hij000000003','ijk000000003','jkl000000003','klm000000003','lmn000000003','mno000000003','nop000000004','opr000000003','prs000000003','rst000000003','stu000000003','tuv000000003','uvw000000003');")
in
    Source

Даже такая наглая конструкция в VBA вполне себе пристойно ведёт - вызов для 2500 значений за 6 секунд.
Код
Public Function SqlLookup(ByVal byKey As String, Optional ByVal ColumnId As Long = 0)
    Const connStr = "DRIVER=SQLite3 ODBC Driver;Database=C:\Path\SQLite\demoSQLite.sqlite;LongNames=0;Timeout=1000;NoTXN=0;SyncPragma=NORMAL;StepAPI=0;"
    Const baseSql = "Select * From [col12Table] Where [Column12] = '$1'"
    Dim pConn As Object, pRSet As Object, result
    Set pConn = CreateObject("ADODB.Connection")
    pConn.Open connStr
    Set pRSet = CreateObject("ADODB.Recordset")
    pRSet.CursorLocation = 3
    pRSet.Open Replace$(baseSql, "$1", byKey), pConn
    If pRSet.RecordCount > 0 Then
        result = pRSet(ColumnId).Value
    Else
        result = CVErr(XlCVError.xlErrNull)
    End If
    pRSet.Close
    pConn.Close
    SqlLookup = result
End Function
 
Вопрос по латиница/кириллица - в самих csv тоже может быть такая пересортица? По логике там обязано всё быть ровно. Но если вдруг не так - то при запросе одного номера "ASD123FGH456" нет особых проблем запросить у словаря все 4 варианта, и даже вывести все 4 строки в худшем случае...
Изменено: Hugo - 19.05.2020 20:01:02
 
Андрей, спасибо за очередное интересное исследование!
Владимир
 
Цитата
Hugo написал:
в самих csv тоже может быть такая пересортица? По логике там обязано всё быть ровно
И в исходных файлах, и в "индексном" файле все одинаково, по-другому быть не может. Просто когда стандартным способом (поиск по точному значению в "индексах" при помощи find или notepad++) чего-то не находил, сравнивал с индексом Архивариуса и он при расширенных опциях выдавал значения, идентичные визуально, но с разными кодами символов ASCII.
Изменено: excel_and - 19.05.2020 21:44:37
 
Я говорил про то, что и в исходных csv может ли путаться раскладка. Если путается - это бардак-с Кто-то данные не чистит в базе...
Если делать как я описал - это можно игнорировать. Т.е. пишем в словарь всё как есть, ищем в словаре все варианты, извлекаем все строки...
А там уже пользователь пускай сам репу чешет - а какая строка правильная, если вдруг их 4 и все разные?
Изменено: Hugo - 19.05.2020 21:50:13
Страницы: Пред. 1 2 3 4 След.
Читают тему (гостей: 1)
Наверх