Всем доброго времени суток. Прошу помощи у знатоков Access. Начал применять в работе Access для хранения огромного массива данных вытащенного с 1с 7.7 (на данный момент это итоги двух лет по продажам в количестве практически 1,5млн. строк), с последующим обращением к этому файлу PowerPivot в Excel (но это уже лирика). Так как с Access совершенно не знаком прошу помощи у тех кто с ним работает: В приложении архив в нём есть файл Access и маленький ескселевский док. Суть проблемы такова: В Access есть две таблицы одна локальная, подписана как "прошлые периоды" в эту таблицу я вливаю уже итоговые сверенные данные за прошлые месяцы и вторая таблица связанная "Текущий месяц" которая ссылается на приложенный файл Ексель. Как мне получить с этих двух (локальной и связанной) таблиц одну локальную которая будет содержать данные с обеих таблиц? В планах файл ексель за текущий период обновлять ежедневно и пока это оперативный файл, не вносить его стационарно в локальную таблицу (а лишь каким то образом чтоб локальная подтягивала его как ссылку вконце своих данных и ежедневно обновлялась), а уже по факту месяца заливаю данные в локалку, а новый месяц начинаю заново подтягивать с ексельки ежедневно.
Если вдруг что то неправильно расписал или что то непонятно, то прошу прощение, добъясню, просто с Access совсем не знаком.
Заранее благодарю за помощь.
update: обратил внимание, что PowerPivot в Excel не воспринимает с Access связанных таблиц, исключительно локальные. Предполагаю что в Access нужен запрос/макрос, который бы с двух таблиц делал одну локальную, при повторном запуске опять таки удалял эту таблицу и формировал заново с двух таблиц 1.
Всем доброго времени суток. Прошу помощи у знатоков Access. Начал применять в работе Access для хранения огромного массива данных вытащенного с 1с 7.7 (на данный момент это итоги двух лет по продажам в количестве практически 1,5млн. строк), с последующим обращением к этому файлу PowerPivot в Excel (но это уже лирика). Так как с Access совершенно не знаком прошу помощи у тех кто с ним работает: В приложении архив в нём есть файл Access и маленький ескселевский док. Суть проблемы такова: В Access есть две таблицы одна локальная, подписана как "прошлые периоды" в эту таблицу я вливаю уже итоговые сверенные данные за прошлые месяцы и вторая таблица связанная "Текущий месяц" которая ссылается на приложенный файл Ексель. Как мне получить с этих двух (локальной и связанной) таблиц одну локальную которая будет содержать данные с обеих таблиц? В планах файл ексель за текущий период обновлять ежедневно и пока это оперативный файл, не вносить его стационарно в локальную таблицу (а лишь каким то образом чтоб локальная подтягивала его как ссылку вконце своих данных и ежедневно обновлялась), а уже по факту месяца заливаю данные в локалку, а новый месяц начинаю заново подтягивать с ексельки ежедневно.
Если вдруг что то неправильно расписал или что то непонятно, то прошу прощение, добъясню, просто с Access совсем не знаком.
Заранее благодарю за помощь.
update: обратил внимание, что PowerPivot в Excel не воспринимает с Access связанных таблиц, исключительно локальные. Предполагаю что в Access нужен запрос/макрос, который бы с двух таблиц делал одну локальную, при повторном запуске опять таки удалял эту таблицу и формировал заново с двух таблиц 1.DJ_Marker_MC
Ааа!!! ура!!! так долго писал, а поломав еще немного после этого голову решил свою задачу сам всего навсего простеньким запросом при чем прямиком с PowerPivot на Access таким образом достаточно зайти в Access обновить данные связанного файла, а потом смело шагать в ексель, запустив запрос всё обновляется))) моей радости нет границ)))
SELECT * FROM [Прошлые Периоды] UNION SELECT * FROM [Текущий Месяц]
Ааа!!! ура!!! так долго писал, а поломав еще немного после этого голову решил свою задачу сам всего навсего простеньким запросом при чем прямиком с PowerPivot на Access таким образом достаточно зайти в Access обновить данные связанного файла, а потом смело шагать в ексель, запустив запрос всё обновляется))) моей радости нет границ)))
SELECT * FROM [Прошлые Периоды] UNION SELECT * FROM [Текущий Месяц]DJ_Marker_MC
Я бы в вашем случае для надежности написал UNION ALL. Разница между операторами UNION и UNION ALL заключается в том, что первый выкидывает из окончательного результата повторяющиеся строки (т.е. делает неявный DISTINCT). Если не считаться с этим положением, то иногда можно потерять строки и не знать истинной причины этой потери.
Т.е. так рекомендую: SELECT * FROM [Прошлые Периоды] UNION ALL SELECT * FROM [Текущий Месяц]
Я бы в вашем случае для надежности написал UNION ALL. Разница между операторами UNION и UNION ALL заключается в том, что первый выкидывает из окончательного результата повторяющиеся строки (т.е. делает неявный DISTINCT). Если не считаться с этим положением, то иногда можно потерять строки и не знать истинной причины этой потери.
Т.е. так рекомендую: SELECT * FROM [Прошлые Периоды] UNION ALL SELECT * FROM [Текущий Месяц]Gustav
Gustav, По логике то идентичных строк у меня в файлах быть не должно, так как изначально 1с их сжимает, но буду иметь ввиду и пропишу именно то что посоветовали Вы. Большое спасибо.
Gustav, По логике то идентичных строк у меня в файлах быть не должно, так как изначально 1с их сжимает, но буду иметь ввиду и пропишу именно то что посоветовали Вы. Большое спасибо.DJ_Marker_MC
Добрый день. Появилась необходимость поднять эту тему снова. Коллеги, подскажите пожалуйста, как обратиться из PowerPivot через sql-запрос к двум разным файлам?
На данный момент используется обращение к таблице Access? к двум таблицам внутри неё с помощью
SELECT * FROM [Base2014] UNION ALL SELECT * FROM [Now]
Сейчас же хотелось бы в этом запросе указать на файлы с которых нужно тянуть данные.
SELECT * FROM [Base2014] - хочу так и оставить тянуть из access
а вот UNION ALL SELECT * FROM [Now] - хочу тянуть с файла Excel (а в идеале вообще из .txt) чтоб не затягивать постоянно в access, а только после этого в PowerPivot
Заранее спасибо.
Добрый день. Появилась необходимость поднять эту тему снова. Коллеги, подскажите пожалуйста, как обратиться из PowerPivot через sql-запрос к двум разным файлам?
На данный момент используется обращение к таблице Access? к двум таблицам внутри неё с помощью
SELECT * FROM [Base2014] UNION ALL SELECT * FROM [Now]
Сейчас же хотелось бы в этом запросе указать на файлы с которых нужно тянуть данные.
SELECT * FROM [Base2014] - хочу так и оставить тянуть из access
а вот UNION ALL SELECT * FROM [Now] - хочу тянуть с файла Excel (а в идеале вообще из .txt) чтоб не затягивать постоянно в access, а только после этого в PowerPivot
_Boroda_, сейчас оно так и реализовано. UNION ALL SELECT * FROM [Now] это как раз тянет со второй таблицы в аксе, которая является связанной с Ексель файлом.
Выходит связка: Ексель 1 - Аксе (две таблицы: локальная и связанная) - Ексель 2
Когда я обновляю ексель 2 в него втягиваются данные локальной таблицы аксе и со связанной. И мне не нравится что происходит своеобразный сквозной запрос от ексель 2 к ексель 1 через аксе, вместо того чтоб тянуть напрямую.
anvg, спасибо за наводку, я конечно попробую и обязательно почитаю что к чему, но вот с "И опишите структуру файла в schema.ini" пока совсем не ясно))) плюс я втягивать его буду не в аксес, а в PowerPivot (это настройка для Ексель)
_Boroda_, сейчас оно так и реализовано. UNION ALL SELECT * FROM [Now] это как раз тянет со второй таблицы в аксе, которая является связанной с Ексель файлом.
Выходит связка: Ексель 1 - Аксе (две таблицы: локальная и связанная) - Ексель 2
Когда я обновляю ексель 2 в него втягиваются данные локальной таблицы аксе и со связанной. И мне не нравится что происходит своеобразный сквозной запрос от ексель 2 к ексель 1 через аксе, вместо того чтоб тянуть напрямую.
anvg, спасибо за наводку, я конечно попробую и обязательно почитаю что к чему, но вот с "И опишите структуру файла в schema.ini" пока совсем не ясно))) плюс я втягивать его буду не в аксес, а в PowerPivot (это настройка для Ексель)DJ_Marker_MC
anvg, Спасибо, после не долгих поисков удалось разобраться с schema.ini и данные все таки полетели туда куда нужно. Единственный вопрос. Мне в schema.ini нужно перечислять ВСЕ столбцы которые есть в моем txt или только те которые я тяну с помощью запроса? Просто через запрос я вытаскиваю на столбцов 10 меньше чем есть в файле, вот сам запрос:
UNION ALL SELECT [Год], [Месяц], [Регион], [Менеджер], [КодК], [Клиент], [Город], [ТГ1], [ТГ2], [ТГ3], [ТГ4], [КодП], [Производитель], [Бренд], [КодТ], [ТМЦ], [Кво], [Сумма], [Доход] FROM [Text;DataBase=D:\Мои документы\01 - Отчеты и Анализы\02 - Анализ Продаж\Исходники\txt].[Киев.txt]
мне schema.ini нужно именно эти столбцы вписать или все что есть в файле?
Заранее благодарю за ответ.
anvg, Спасибо, после не долгих поисков удалось разобраться с schema.ini и данные все таки полетели туда куда нужно. Единственный вопрос. Мне в schema.ini нужно перечислять ВСЕ столбцы которые есть в моем txt или только те которые я тяну с помощью запроса? Просто через запрос я вытаскиваю на столбцов 10 меньше чем есть в файле, вот сам запрос:
UNION ALL SELECT [Год], [Месяц], [Регион], [Менеджер], [КодК], [Клиент], [Город], [ТГ1], [ТГ2], [ТГ3], [ТГ4], [КодП], [Производитель], [Бренд], [КодТ], [ТМЦ], [Кво], [Сумма], [Доход] FROM [Text;DataBase=D:\Мои документы\01 - Отчеты и Анализы\02 - Анализ Продаж\Исходники\txt].[Киев.txt]
мне schema.ini нужно именно эти столбцы вписать или все что есть в файле?
Мне в schema.ini нужно перечислять ВСЕ столбцы которые есть в моем txt или только те которые я тяну с помощью запроса?
Позволю себе несколько пожурить. Тест для файла на 4-5 столбцов и на 2-3 строчки не сложно же написать и проверить? И убедиться, что пропуск определения столбцов в schema.ini, Col2=F1 Text Col4=F2 Long увы, не работает. Требуется указать все столбцы. А вот имена для требуемых столбцов можно задать собственные в особых случаях закавычивая, например Col5="Производитель товара" Text По поводу же PowerPivot. Если он как и сводные таблицы поддерживает подключения WorkbookConnection, то вполне его можно заставить работать и со связанными таблицами. Успехов.
Добрый вечер
Цитата
Мне в schema.ini нужно перечислять ВСЕ столбцы которые есть в моем txt или только те которые я тяну с помощью запроса?
Позволю себе несколько пожурить. Тест для файла на 4-5 столбцов и на 2-3 строчки не сложно же написать и проверить? И убедиться, что пропуск определения столбцов в schema.ini, Col2=F1 Text Col4=F2 Long увы, не работает. Требуется указать все столбцы. А вот имена для требуемых столбцов можно задать собственные в особых случаях закавычивая, например Col5="Производитель товара" Text По поводу же PowerPivot. Если он как и сводные таблицы поддерживает подключения WorkbookConnection, то вполне его можно заставить работать и со связанными таблицами. Успехов.anvg
anvg, Спасибо за наставление на путь истинный)))) Сделал пример на пару столбцов и строк, потестил, со всем что нужно на данный момент разобрался.
anvg, Спасибо за наставление на путь истинный)))) Сделал пример на пару столбцов и строк, потестил, со всем что нужно на данный момент разобрался.DJ_Marker_MC
Коллеги, добрый день. Продолжаю воевать с запросами из PowerPivot к Access и txt Чуть выше anvg направил в правильное русло для написания запроса который тянет данные сперва с Access, а потом дополняется полученные данные, значениями из файла к txt Для этого было предложено создать файл schema.ini с описанием столбцов txt файла и соответственно сам запрос получил вид:
SELECT ...большой перечень столбцов FROM [Base2014]
UNION ALL SELECT ...большой перечень столбцов FROM [NOW] [Text;DataBase=C:\txt].[Primer.txt]
Первая часть запроса тянет из Access. вторая из txt, но при получении данных в PowerPivot с txt столкнулся с проблемой Двойных кавычек в получаемых данных к примеру, имя клиента: ООО "ГАЗПРОМ" Столбец "Имя клиента" по счету 4, так вот первые три столбца этой строки заполняются полностью, имя клиента отображается ООО_(пробел), а название начиная с кавычек и все столбцы что идут после имени клиенты - пустые. Хотя если этот же txt втянуть например в Excel или Access то данные попадают корректно не взирая на двойные кавычки.
Помогите пожалуйста понять где что не так. Я знаю что по правилам нужно прилаживать файл (и сам часто указываю пользователям на это, которые задают вопрос без файла), но тут идет связка разных файлов с прописанными путями к ним, поэтому если кто знает примерные пути решения проблемы дайте знать, буду дополнять недостающей информацией.
Заранее всем спасибо.
Коллеги, добрый день. Продолжаю воевать с запросами из PowerPivot к Access и txt Чуть выше anvg направил в правильное русло для написания запроса который тянет данные сперва с Access, а потом дополняется полученные данные, значениями из файла к txt Для этого было предложено создать файл schema.ini с описанием столбцов txt файла и соответственно сам запрос получил вид:
SELECT ...большой перечень столбцов FROM [Base2014]
UNION ALL SELECT ...большой перечень столбцов FROM [NOW] [Text;DataBase=C:\txt].[Primer.txt]
Первая часть запроса тянет из Access. вторая из txt, но при получении данных в PowerPivot с txt столкнулся с проблемой Двойных кавычек в получаемых данных к примеру, имя клиента: ООО "ГАЗПРОМ" Столбец "Имя клиента" по счету 4, так вот первые три столбца этой строки заполняются полностью, имя клиента отображается ООО_(пробел), а название начиная с кавычек и все столбцы что идут после имени клиенты - пустые. Хотя если этот же txt втянуть например в Excel или Access то данные попадают корректно не взирая на двойные кавычки.
Помогите пожалуйста понять где что не так. Я знаю что по правилам нужно прилаживать файл (и сам часто указываю пользователям на это, которые задают вопрос без файла), но тут идет связка разных файлов с прописанными путями к ним, поэтому если кто знает примерные пути решения проблемы дайте знать, буду дополнять недостающей информацией.
Доброе время суток Попробуйте указать нестандартный ограничитель текста, например, ' а не " TextDelimited=' или, возможно, пустой TextDelimited= в schema.ini как описано тут Успехов.
Доброе время суток Попробуйте указать нестандартный ограничитель текста, например, ' а не " TextDelimited=' или, возможно, пустой TextDelimited= в schema.ini как описано тут Успехов.anvg
Сообщение отредактировал Serge_007 - Вторник, 17.06.2014, 21:34
anvg, в текстовом файле у меня разделитель столбцов, это Format=TABDelimited, соответственно и в фале txt разделитель столбцов табуляция, поэтому не понимаю вообще как может возникать такой трабл. А ограничителя текста у меня в txt совсем, попробую завтра вставить на работе, посмотрю что выдаст. Пока что спасибо.
anvg, в текстовом файле у меня разделитель столбцов, это Format=TABDelimited, соответственно и в фале txt разделитель столбцов табуляция, поэтому не понимаю вообще как может возникать такой трабл. А ограничителя текста у меня в txt совсем, попробую завтра вставить на работе, посмотрю что выдаст. Пока что спасибо.DJ_Marker_MC
anvg, Большое спасибо в очередной раз. Наводку с TextDelimited= уловил, но сперва на практике до конца не понял к чему оно было нужно. После полутора часа поисков в инете на эту тематику, дошел умом к тому, что все таки нашел путь решения проблемы. Другими словами TextDelimited нужно было указать такой символ который точно не присутствует в тексте. У меня им оказался знак - | schema.ini принял вид:
[Otgruzka.txt] ColNameHeader=True MaxScanRows=10 Format=TABDelimited TextDelimiter=| CharacterSet=ANSI ....дальше большая туча никому не нужных тут моих столбцов вида: Col1="Год" Text и т.д. [offtop]Когда получаешь не готовое решение, а наводку на то как должно быть, а потом все таки находишь правильный путь решения - это КАЙФ!!![/offtop]
anvg, Большое спасибо в очередной раз. Наводку с TextDelimited= уловил, но сперва на практике до конца не понял к чему оно было нужно. После полутора часа поисков в инете на эту тематику, дошел умом к тому, что все таки нашел путь решения проблемы. Другими словами TextDelimited нужно было указать такой символ который точно не присутствует в тексте. У меня им оказался знак - | schema.ini принял вид:
[Otgruzka.txt] ColNameHeader=True MaxScanRows=10 Format=TABDelimited TextDelimiter=| CharacterSet=ANSI ....дальше большая туча никому не нужных тут моих столбцов вида: Col1="Год" Text и т.д. [offtop]Когда получаешь не готовое решение, а наводку на то как должно быть, а потом все таки находишь правильный путь решения - это КАЙФ!!![/offtop]DJ_Marker_MC
Сообщение отредактировал DJ_Marker_MC - Среда, 18.06.2014, 12:33
Другими словами TextDelimited нужно было указать такой символ который точно не присутствует в тексте.
Извиняюсь, это собственно и подразумевал. Рад, что у вас всё получилось. [offtop]Писал с планшета, вот и не осилил много букв при вводе[/offtop] P. S. Немного поковырялся, можно отключить поиск ограничителей текста так TextDelimiter=none
Цитата
Другими словами TextDelimited нужно было указать такой символ который точно не присутствует в тексте.
Извиняюсь, это собственно и подразумевал. Рад, что у вас всё получилось. [offtop]Писал с планшета, вот и не осилил много букв при вводе[/offtop] P. S. Немного поковырялся, можно отключить поиск ограничителей текста так TextDelimiter=noneanvg
Сообщение отредактировал anvg - Среда, 18.06.2014, 14:30
anvg, Протестил на живом файле TextDelimiter=none - тоже всё правильно выдаёт. Я так понимаю этот вариант более надежный, поскольку исключает даже непостижимые здравым смыслом которые может ввести пользователь символы в строке.
anvg, Протестил на живом файле TextDelimiter=none - тоже всё правильно выдаёт. Я так понимаю этот вариант более надежный, поскольку исключает даже непостижимые здравым смыслом которые может ввести пользователь символы в строке.DJ_Marker_MC
Да, собственно, получается, что значение none - это эквивалент варианту "нет" в диалогах импорта текстового файла в Excel и Access в раскрывающемся списке "Ограничитель строк". То есть будут использоваться все символы межу указанными разделителями столбцов. Единственное, будут обрезаться пробелы и справа и слева. Указание ограничителя текста позволит оставить пробелы слева - справа будут обрезаны в любом случае, хотя, вряд ли это так важно. И ограничители теста (при условии наличия их в самом текстовом файле) позволяют исключить применение разделителя столбцов, если такой разделитель находится между ограничителями текста). Для примера. Пусть разделитель столбцов запятая, тогда запятая в строке с ограничителями теста "г. Орёл, ул. Ленина, 65" будет включена в содержимое и этот текст будет интерпретирован как значение одного столбца. То же без ограничителей, естественно, будет воспринято как содержимое трёх столбцов. Так что следует учитывать и такую особенность. В вашем случае это не важно, так как у вас разделитель столбцов табуляция, но всё же...
DJ_Marker_MC,
Цитата
исключает даже непостижимые здравым
Да, собственно, получается, что значение none - это эквивалент варианту "нет" в диалогах импорта текстового файла в Excel и Access в раскрывающемся списке "Ограничитель строк". То есть будут использоваться все символы межу указанными разделителями столбцов. Единственное, будут обрезаться пробелы и справа и слева. Указание ограничителя текста позволит оставить пробелы слева - справа будут обрезаны в любом случае, хотя, вряд ли это так важно. И ограничители теста (при условии наличия их в самом текстовом файле) позволяют исключить применение разделителя столбцов, если такой разделитель находится между ограничителями текста). Для примера. Пусть разделитель столбцов запятая, тогда запятая в строке с ограничителями теста "г. Орёл, ул. Ленина, 65" будет включена в содержимое и этот текст будет интерпретирован как значение одного столбца. То же без ограничителей, естественно, будет воспринято как содержимое трёх столбцов. Так что следует учитывать и такую особенность. В вашем случае это не важно, так как у вас разделитель столбцов табуляция, но всё же...anvg
Сообщение отредактировал anvg - Пятница, 20.06.2014, 09:49
Вход
Регистрация
Напомнить пароль
Автосумма
Часто используемые
ВПР
ЕСЛИ
СУММЕСЛИ
Финансовые
Логические
Текстовые
Даты и времени 
Ссылки и массивы 
Математические
Другие функции
Проверка свойств и значений
Баз данных
Математические
Обработки массивов
Обработки строк
Преобразования типов данных 
Загрузки данных
Работы с файлами
Обработки системных параметров
Обработки цвета
Работы с датами и временем
Преобразования в системы счисления
Финансовые
ура!!! так долго писал, а поломав еще немного после этого голову решил свою задачу сам всего навсего простеньким запросом при чем прямиком с PowerPivot на Access таким образом достаточно зайти в Access обновить данные связанного файла, а потом смело шагать в ексель, запустив запрос всё обновляется))) моей радости нет границ))) 
