Страницы: 1 2 След.
RSS
Excel: Использование Access для обработки больших текстовых файлов
 
Добрый день!   Помогите пожалуйста в решении следующей задачи (см. вложение)

   Дано:
   Лист ФИО  от 60 до 500 тыс. строк (выделено голубым цветом), он заполняется вручную, копированием из другого Excel файла
   Необходимо заполнить столбики D и E (выделено желтым и зеленым) на основании листов Телефоны и Карты.
   Для маленького числа строк задача уже решена (см. вложение), но листы Телефоны и Карты иногда могут быть более 1 млн строк
     
   Задачу планируется решать с Помощью Access, но проблема в том, что человек, который будет работать с этим файлом, не знает что такое Access.
    Поэтому импорт текстового файла в Access хотелось бы сделать через Эксель, чтобы пользователь нажал кнопку, выбрал нужный файл, а дальше все само загрузилось в Access. Затем нажал 2-ю кнопку, заполнились нужные поля.
   Задача периодическая, раз в неделю-две.
   Кто может подсказать как это правильно сделать?
 
Цитата
tvit пишет:
Задачу планируется решать с Помощью Access.......
Поэтому импорт текстового файла в Access хотелось бы сделать через Эксель.....
И в чём, собственно, проблема? Вы не можете сделать эти две кнопки?
Изменено: Johny - 01.04.2014 16:06:16
There is no knowledge that is not power
 
И Вам добрый день
Цитата
tvit пишет:
чтобы пользователь нажал кнопку, выбрал нужный файл, а дальше все само загрузилось в Access. Затем нажал 2-ю кнопку, заполнились нужные поля.
запишите макрорекордером два макроса и повесьте их на Ваши кнопки
Согласие есть продукт при полном непротивлении сторон
 
зачем аксесс?
считать файл в массив - результат вывести на лист.
 
Паспорт штука "не правильная" клиент может сменить паспорт и зарегить еще телефон :)))
если в текстовом файле структура правильная, то аксес тут совсем не надо, можно автоматизацией(ADO) решить все вопросы
 
Посмотрите надстройку (для 2010 и выше) PowePivot
Согласие есть продукт при полном непротивлении сторон
 
Цитата
Johny пишет:
И в чём, собственно, проблема? Вы не можете сделать эти две кнопки?
Именно! Я уже лет 10 с VBA не использовал, да и что знал все забыл. Если заполнение ячеек поищу в инете, думаю найду аналогичные примеры, то как быть с открытием текстового файла и экспорта его в access используя excel понятия не имею как делать.
 
Цитата
Sanja пишет: запишите макрорекордером
А эксель запишет код команд Акцесса?
 
Цитата
Dima S пишет: считать файл в массив - результат вывести на лист.
Хорошая идея, если никто не ответит на мой вопрос примером кода, то буду думать в этом направлении.
Единственная проблема , что есть еще аналогичная задача, где нужно каждую неделю накапливать данные, тогда массивы не помогут.
 
Цитата
B.Key пишет: Паспорт штука "не правильная" клиент может сменить паспорт и зарегить еще телефон ))
если в текстовом файле структура правильная, то аксес тут совсем не надо, можно автоматизацией(ADO) решить все вопросы
Все верно, но в рамках моей  задачи (а она намного шире чем я изложил) эти параметры постоянные. Просто на базе этого кода остальное думаю уже сам сделаю.
Структура текстового файла правильная, она выгружается из Автоматизированной системы.
НА счет АДО, Можно пример кода , что вы имете ввиду?
 
Скрытый текст
Вот пример из моего файла.
Function goleador(sstr) - получает параметром строку запроса, строку подключения и тип вывода результата: 1 - выдает массив заголовков полей, 2 - массив данных без заголовков, 3 - массив данных с заголовками.

Sub вигрузон2 содержит строки подключения и строки запроса для excel, access и mssql.
Изменено: Dima S - 01.04.2014 19:22:48
 
Цитата
Sanja пишет:
Посмотрите надстройку (для 2010 и выше) PowePivot
Как я понимаю, если я буду использовать эту надстройку, то и тот кто будет пользоваться моим файлам, тоже должен будет ее установить, а вот с этим все проблематично.
 
нужны реальные файлы txt (с яблоками и бананами). строк на 20-30, цифры замените, код помогу написать.
(без образцов ну ни как у меня не получиться)
 
Прошу прощения, в старом файле часть столбиков забыл удалить, они на решение задачи не влияют, но вносят путаницу. Сейчас перевыложил исходный файл, и два текстовых файла.
 
Цитата
Заголовков точно нет в txt ?
-----------------------
Скрытый текст
Изменено: B.Key - 01.04.2014 21:37:32
 
Заголовков нет, если тебе удобнее с заголовками, напиши с ними, я потом под себя переделаю
 
пример импорта в аксесс из текстового файла с созданием спецификации импорта тхт файла
вам необходимо создать файл аксеса с
'[PFONE]-таблица куда импортируем
'NUMPASPORT,NAMEKLIENT,PFONE - наименование полей куда импортируем

Скрытый текст
 
Думаю можно просто один раз вручную/мастером подключить к Access эти оба/все файлы как внешние данные, и составить запрос по этим данным, выводящий нужную таблицу.
Тогда достаточно в любой момент подменить эти файлы новыми (оставив полное имя), затем открыть эту базу и выполнить запрос. Результат можно экспортировать в Эксель, возможно макросом/кнопкой, детали не изучал.
Изменено: Hugo - 02.04.2014 18:56:12
 
Цитата
B.Key пишет: пример импорта в аксесс из текстового файла с созданием спецификации импорта тхт файла
Спасибо, завтра на работе попробую
 
Цитата
Hugo пишет: Думаю можно просто один раз вручную/мастером подключить к Access эти оба/все файлы как внешние данные, и составить запрос по этим данным, выводящий нужную таблицу.
Красивое решение, не знал что так можно. Начал пробовать возникло два вопроса:
1. Путь к файлам абсолютный, мне нужно будет отсылать базу со всеми файлами другому человеку, который будет потом с ней работать, файлы он подменить сможет, а вот остальное врядли, поэтому хотелось бы указать относительные пути к текстовым фалам, это как то можно сделать?
2. Насколько я понимаю, Акцесс с внешними таблицами должен работать медленнее, чем со своими, поэтому вопрос, если в файлах будет по 500 тыс. строк. этот вариант на сколько будет работать быстрее, чем если бы я просто ручками скопировал эти текстовые файлы в листы Excel?
 
Ну эти вопросы уже на другом форуме нужно спрашивать.  :)  

Насчёт путей - положите всё в c:\телефоны\ или в x:\телефоны\ или другую какую совместную букву можно подыскать для сетевого диска, который можно сделать из любой папки. Если вообще там важен диск, не знаю - может там важен путь относительно базы?
Зато можно легко обновлять-менять данные, хоть вручную, хоть любым простым скриптом.

По скорости - думаю макрос, обрабатывающий то, что Вы показали (т.е. файл экселя и тексты, как вот они там есть) будет работать быстрее SQL-запросов и Access.
Изменено: Hugo - 02.04.2014 21:50:03
 
Протестировал по скорости варианты для текстового файла «Карты» 1000000 строк, на листе «ФИО» 500000:
1. Текстовый, чтение разбиение по vbCrLf, запись значения «ID Клиента» и индекса строки данных в массиве в словарь, чтение в массив «ID Клиента» с листа «ФИО» и поиск по словарю для определения есть карта, нет карты с записью результата в массив и сохранение на лист, заняло 110 секунд.
2. Результат на новый лист как ADO-запрос к листу «ФИО» и текстовому файлу «Карты» около 75 секунд.
3. Результат на новый лист как ADO-запрос к листу «ФИО» и таблице в базе Access около 68 секунд. Стоит учесть, что загрузка данных из текстового файла «Карты» в таблицу Access требует около 30 секунд.
4. Если бы данные с листа «ФИО» были бы тоже таблицей Access, то запись результата на новый лист около 15 секунд.
5. Если база и таблица «ФИО» в базе SQLite, то около 7 секунд, правда, загрузка данных в базу около 120 секунд.
В общем получается самый выгодный вариант №2.
Хотя не совсем понятны такие моменты. Связь листа ФИО всегда предполагается с новыми текстовыми файлами или задача грузить их в базу данных предполагает добавление не существующих в ней данных?
 
Я думал так делать - сперва читаем в словарь xls, запоминаем где какой паспорт., создаём массив под результат. Т.к. там данных немного - будет быстро.
Затем при чтении текста (если большой, то можно и построчно - вероятно будет быстрее, памяти меньше займёт) проверяем наличие прочтённого в словаре, если нашлось - ставим метку в массив.
Затем читаем-проверяем второй txt.
Ну и выгрузка результата.
Думаю будет быстрее чес второй результат, ну и не нужно читать текст как таблицу в Access.

Как делать базу с накоплением - думаю другая задача.
Можно просто тексты склеивать  :)
Изменено: Hugo - 03.04.2014 09:47:10
 
Игорь
Цитата
сперва читаем в словарь xls... данных немного - будет быстро
Цитата
Лист ФИО от 60 до 500 тыс. строк (выделено голубым цветом)
Если посчитать число операций на поисковый просмотр, то для 500000 ФИО на 1000000 в КАРТЫ
LOG(500000;2)*1000000=18931568 хуже, чем
=LOG(1000000;2)*500000=9965784 (хотя, конечно, словарь с ростом размера тормозить начинает не линейно).
Цитата
Можно просто тексты склеивать
Что-то мне подсказывает, что там обновлённая выгрузка одной и той же базы. Сколько пустопорожнего просмотра будет при склеивании? ;)
 
Я не математик - но кажется, что так и так сравнивать придётся одно с другим. Только зачем загонять миллион в словарь (что займёт минуту), если можно сразу каждое значения сверять со словарём поменьше?
Ну а если выгрузка из одной базы - то конечно зачем склеивать, берём просто последнюю.
 
Цитата
anvg пишет: Протестировал по скорости варианты ...
Спасибо за проделанную работу!
А можешь исходники со своими изысканиями можно вместе с тестовыми файлами мне е-майл: tvit@mail.ru кинуть, может кроме 5-го?
Когда я задавал вопрос про время, меня совсем не секунды интересовали :-) Я вставлял каждый файл на листы эксель, а если он был больше миллиона, то приходилось делить и на два листа, а затем протягивал формулы по всем строчкам листа ФИО, у меня счет шел на часы (i5, 4ГБ - ОЗУ), поэтому любой твой вариант получается супер-мега быстрым :-)
 
Цитата
Hugo пишет: Можно просто тексты склеивать
Тексты склеивать не вариант, мне проще проверку на дупликаты сделать.
 
Цитата
Hugo пишет:
Я не математик - но кажется, что так и так сравнивать придётся одно с другим. Только зачем загонять миллион в словарь (что займёт минуту), если можно сразу каждое значения сверять со словарём поменьше?
Ну а если выгрузка из одной базы - то конечно зачем склеивать, берём просто последнюю.
Сами понимаете что задача для форума сформулирована в упрощенном виде. Действительно 1 справочник будет коммулятивным и обновляться 1 раз в квартал перезаписыванием первого. А второй будет раз в 1-2 недели добавлять информацию к старой. При этом сам файл ФИО будет тоже каждый раз заново формироваться, там будут совсем другие строчки, старых практически ни одной не останется.
 
tvit:
1. Умеете не вставлять целые сообщения в цитаты? Отбирать только нужное? Следовать пункту правил и здравому смыслу?! Править уже надоело.
2. Нигде в сообщениях не обнаружил ни одного обращения к Вам на "ты"...
 
Цитата
А второй будет раз в 1-2 недели добавлять информацию к старой
дописать можно и в текстовый файл  :)  
перелинкуйте файлы в аксесе через диспетчер связанных таблиц. закройте аксес
откройте файл екселя, обновите таблицу
---------------------------
зы... это не самый быстрый вариант но один из самых простых
зы..зы да забыл сказать в ёкселе список фио формируете на одном листе, а результат на втором
Изменено: B.Key - 03.04.2014 21:40:00
Страницы: 1 2 След.
Наверх