Как из storehouse выгрузить в excel
Помогите, пожалуйста, советом) А быть может и решением.
гигантская таблица с номенклатурой, в excel, которая обновляется по цене, скажем раз в квартал. Ответственный человек, также раз в квартал, берет ее и переносит данные вручную в Storehouse.
Номенклатура в excel и shouse по наименованию не совпадает.
Тоесть есть какие-то опознавательные знаки, по которым можно найти позицию и в excel и shouse, но наименования не идентичны.
Вопрос - как автоматизировать перенос данных из excel в shouse?
Пока в голове, на уровне дауна, идея - выгружать в xls таблицу номенклатуры с shouse, каким-то макаром сверять с исходной excel по общим "маркерам" в наименованиях и автоматом вносить в выгруженный shouse. затем заливать его обратно.
В том ли направлении смотрю? Может есть готовые решения или кто-то писал что-нибудь подобное "на коленке"?
Буду рад любой помощи, человек чуть ли не умирает от этих таблиц, настолько это муторно и долго.
MS, Libreoffice & Google docs
537 постов 13.4K подписчиков
Правила сообщества
2. Публиковать посты соответствующие тематике сообщества
3. Проявлять уважение к пользователям
4. Не допускается публикация постов с вопросами, ответы на которые легко найти с помощью любого поискового сайта.
По интересующим вопросам можно обратиться к автору поста схожей тематики, либо к пользователям в комментариях
Важно - сообщество призвано помочь, а не постебаться над постами авторов! Помните, не все обладают 100 процентными знаниями и навыками работы с Office. Хотя вы и можете написать, что вы знали об описываемом приёме раньше, пост неинтересный и т.п. и т.д., просьба воздержаться от подобных комментариев, вместо этого предложите способ лучше, либо дополните его своей полезной информацией и вам будут благодарны пользователи.
Утверждения вроде "пост - отстой", это оскорбление автора и будет наказываться баном.
Там вроде Interbase в качестве БД, я бы рекомендовал сделать нормальный ETL-процесс, для наименований завести словарь, первично состыковать имена по расстоянию Левенштейна с разбивкой по словам, на каждое изменение цены (загрузку) заводить документ, чтобы это прозрачно отрабатывалось в системе.
Попробуйте не создавать костыль, а поменять процесс полностью. @qawsed90 описал самый эффективный способ, дальше только использовать ИИ. И все равно нужно будет дорабатывать вручную.
Кто-то же обновляет первую таблицу? Может стоит внести в нее сразу номенклатурные коды и по ним вытаскивать изменившиеся цены?
Надо глядеть на объёмы и как происходит загрузка /выгрузка в Storehouse .
Только после этого можно понять обойдётся ли макросами это или надо что то более серьёзное .
Почту оставьте для связи.
Если действия человека по работе с таблицей хоть как то можно алгоритмизировать, то и макрос написать можно.
Дилерам написать, спросить, Карбис например, ЮЦЦ навряд ли пишет что то.
Экспорт и импорт
Вообще, подобную картинку видел в сербском интернете, оригинал не нашел, нарисовал свою. Хотя, для Сербии эта проблема по сравнению с Россией стоит далеко не так остро. Да, люди уезжают в страны Европы, приезжают строители из Турции и еще хз откуда. Но их не особо видно и слышно, да и приезжают они работать. С российскими масштабами не сравнить, мягко говоря.
А в России, судя по новостям, ситуация УЖЕ вышла из под контроля. Возникает два вопроса:
1 Неужели те, кто принимают такие решения, не думают о последствиях? Я понимаю, что они живут в другом мире, в котором они никогда не встретятся с мигрантами, но тем не менее.
2 А может им коренные жители России (я не написал "русские") не особо и нужны?
Ну и очень печально смотреть, как те (или их дети), кто еще 30 лет назад выгонял русских, грабил их, убивал, едут сюда. Они хотели независимости. Теперь нажрались своей независимости, живут в жопе (иначе они просто не умеют) и свою жопу тащат сюда.
Торговля вакцинами – Россия увеличила экспорт в 30 раз и стала одним из мировых лидеров. Сравнение с другими странами
До 2020 г. Россия не являлась значимым игроком на рынке вакцин. Доля страны в мировой торговле вакцинами в 2019 г. – менее 0,5%, экспорт г. не превышал 71 млн $, а импорт – 185 млн $.
Торговля вакцинами в мире до пандемии
Основными экспортерами вакцин для людей в 2019 г. являлись страны Европейского союза, США, Канада, Индия, на их долю приходилось 96% поставок в денежном выражении. География импорта вакцин была шире, поскольку потребителей кратно больше производителей, однако 47% импорта приходилось на США (35%) и Европейский союз.
Экспорт и импорт вакцин для людей по странам мира. Источник: расчет автора по данным ООН, ФТС России, статистических ведомств стран
Важная особенность рынка – разделение поставок между богатыми и бедными государствами, страны Европейского союза большую часть вакцин поставляли в США, Канаду и Великобританию. Вакцины в бедные страны Африки, Азии и Латинской Америки преимущественно импортировались из Индии. Ее экспортная доля в денежном выражении составляла только 3,4%, а в количестве – около 25%.
Торговля вакцинами в 2021 году
Экспорт вакцин для людей по странами мира в 2020-2021 гг. Источник: расчет автора по данным ООН, ФТС России, статистических ведомств стран
Торговля вакцинами в России
До пандемии Россия была чистым импортером вакцин, которые поставлялись из европейских стран и США, структура импорта не изменилась и в 2020-2021 гг. С начала 2021 г. Россия стала активно наращивать поставки вакцины от COVID-19 в другие страны, за 7 месяцев 2021 г. (г/г) общий экспорт вакцин увеличился почти в 30 раз с 22 млн $ до 590 млн $. К середине 2021 г. Россия вышла на 5 место среди крупнейших экспортеров.
Структура импорта и экпорта вакцин для людей в России по странам в 2020-2021 гг. Источник: расчет автора по данным ФТС России
Национальный доход не размножается делением или почему дорожает лес?
Россия – крупнейшая лесная держава мира. Однако, не так давно инфопространство всколыхнула инфрмация о серьезном подорожании леса на внутреннем рынке.
По данным Ассоциации деревянного домостроения, с апреля 2020-го по апрель 2021 г. доски для строительства подорожали с 11–13 тыс. руб. за 1 кубометр до 22–26 тыс. руб. Цены на внешних рынках превысили российские на 7–30% (в зависимости от типа продукции), что и повлекло рост цен для внутреннего рынка.
Давайте подумаем, а можно ли реально что-то сделать с этой проблемой?
Если отталкиваться от книжной экономической теории – конечно, существует определенная регулирующая связь между спросом и предложением. Если рассматривать Россию как закрытый обособленный рынок, то увеличение предложения способно снизить цену леса. Однако, это если мы представляем эту ситуацию как некоего «сферического коня в вакууме». В реальности, к сожалению, не все так однозначно.
Мы вынуждены учитывать реальное положение вещей. А здесь определяющие 3 фактора:
1. Для того, чтобы существенно повысить предложение леса – нужны инвестиции. Кто выступит инвесторами - частники, государство. По сути - не важно. Важно другое – инвестиции должны быть долгосрочными, тк первый результат не завтра. Для запуска необходимо урегулировать вопросы с государством (получить лицензию на вырубку, найти место под завод, заказать строительство, согласовать контракты, паспорта сделок, подождать изготовление оборудования и тд и тп). Только потом пойдет пред пусковой цикл, пусковой цикл и производств…И мы понимаем, что это не один год. Что сделать? Стимулировать привлекательность отрасли для частных инвестиций субсидиями, низкопроцентными кредитами. И, как мы понимаем, это прерогатива государства..
2. Россия является мировым игроком. Она входит в мировую экономическую систему. А существующее состояние мировой экономической системы говорит о том, что лес в во всем мире, особенно в странах Азии, стоит гораздо дороже, чем в России. И до тех пор, пока кубометр леса на экспорт будет стоить дороже, чем в России, производители будут стремиться сдать его не на внутренний рынок, а на экспорт. Какой вывод моно сделать? На первый взгляд простой - пока не насытится экспортный рынок, в России спрос не будет закрыт полностью. А что это означает? Несмотря на то, что производство увеличилось – предложение не увеличилось, тк все доппризводство идет на экспорт. Спрос растет, предложение не растет, а падает, следовательно - цены растут. Так же следует учитывать мировой тренд на сокращение вырубки лесов в мире, а это значит, что цены на мировом рынке будут только расти….Что сделать? Мне видится, что тут возможна только единственная управляющая роль государства
3. Цепочка перепродавцов. Вся наша экономическая система построена именно на такой модели…Кто-нибудь, где-нибудь в Сибири срубил лес, его купил посредник, отогнал на лесозаготовку. Комбинат обработал, но у него купил более крупный посредник и погнал в область и тд. . И без этого никуда. Цепочка посредников, в каждой из звеньев которой увеличивается цена…
Простых решений не существует, тк есть люди. Поможет некий комплексный план.. Однако, тут тоже надо понимать - если подорожание спорадическое , те вызвано сезонными причинами – это одно. Если это фундаментальное состояние отрасли, в которой не хватает переработчиков – это уже структурный кризис отрасли, то послаблений ждать не следует….
Мир достаточно велик, чтобы удовлетворить нужды любого человека, но слишком мал, чтобы удовлетворить людскую жадность. Махатма Ганди
Есть в IT-отрасли задачи, которые на фоне успехов в big data, machine learning, blockchain и прочих модных течений выглядят совершенно непривлекательно, но на протяжении десятков лет не перестают быть актуальными для целой армии разработчиков. Речь пойдёт о старой как мир задаче формирования и выгрузки Excel-документов, с которой сталкивался каждый, кто когда-либо писал приложения для бизнеса.
Какие возможности построения файлов Excel существуют в принципе?
- VBA-макросы. В наше время по соображениям безопасности идея использовать макросы чаще всего не подходит.
- Автоматизация Excel внешней программой через API. Требует наличия Excel на одной машине с программой, генерирующей Excel-отчёты. Во времена, когда клиенты были толстыми и писались в виде десктопных приложений Windows, такой способ годился (хотя не отличался скоростью и надёжностью), в нынешних реалиях это с трудом достижимый случай.
- Генерация XML-Excel-файла напрямую. Как известно, Excel поддерживает XML-формат сохранения документа, который потенциально можно сгенерировать/модифицировать с помощью любого средства работы с XML. Этот файл можно сохранить с расширением .xls, и хотя он, строго говоря, при этом не является xls-файлом, Excel его хорошо открывает. Такой подход довольно популярен, но к недостаткам следует отнести то, что всякое решение, основанное на прямом редактировании XML-Excel-формата, является одноразовым «хаком», лишенным общности.
- Наконец, возможна генерация Excel-файлов с использованием open source библиотек, из которых особо известна Apache POI. Разработчики Apache POI проделали титанический труд по reverse engineering бинарных форматов документов MS Office, и продолжают на протяжении многих лет поддерживать и развивать эту библиотеку. Результат этого reverse engineering-а, например, используется в Open Office для реализации сохранения документов в форматах, совместимых с MS Office.
Но у прямого использования Apache POI есть и недостатки. Во-первых, это Java-библиотека, и если ваше приложение написано не на одном из JVM-языков, вы ей вряд ли сможете воспользоваться. Во-вторых, это низкоуровневая библиотека, работающая с такими понятиями, как «ячейка», «колонка», «шрифт». Поэтому «в лоб» написанная процедура генерации документа быстро превращается в обильную «лапшу» трудночитаемого кода, где отсутствует разделение на модель данных и представление, трудно вносить изменения и вообще — боль и стыд. И прекрасный повод делегировать задачу самому неопытному программисту – пусть ковыряется.
Но всё может быть совершенно иначе. Проект Xylophone под лицензией LGPL, построенный на базе Apache POI, основан на идее, которая имеет примерно 15-летнюю историю. В проектах, где я участвовал, он использовался в комбинации с самыми разными платформами и языками – а счёт разновидностей форм, сделанных с его помощью в самых разнообразных проектах, идёт, наверное, уже на тысячи. Это Java-проект, который может работать как в качестве утилиты командной строки, так и в качестве библиотеки (если у вас код на JVM-языке — вы можете подключить её как Maven-зависимость).
Xylophone реализует принцип отделения модели данных от их представления. В процедуре выгрузки необходимо сформировать данные в формате XML (не беспокоясь о ячейках, шрифтах и разделительных линиях), а Xylophone, при помощи Excel-шаблона и дескриптора, описывающего порядок обхода вашего XML-файла с данными, сформирует результат, как показано на диаграмме:
Шаблон документа (xls/xlsx template) выглядит примерно следующим образом:
Как правило, заготовку такого шаблона предоставляет сам заказчик. Вовлечённый заказчик с удовольствием принимает участие в создании шаблона: начиная с выбора нужной формы из «Консультанта» или придумывания собственной с нуля, и заканчивая размерами шрифтов и ширинами разделительных линий. Преимущество шаблона в том, что мелкие правки в него легко вносить уже тогда, когда отчёт полностью разработан.
Когда «оформительская» работа выполнена, разработчику остаётся
- Создать процедуру выгрузки необходимых данных в формате XML.
- Создать дескриптор, описывающий порядок обхода элементов XML-файла и копирования фрагментов шаблона в результирующий отчёт
- Обеспечить привязку ячеек шаблона к элементам XML-файла с помощью XPath-выражений.
Если бы в форме, которую мы создаём, не было повторяющихся элементов с разным количеством (таких, как строки накладной, которых разное количество у разных накладных), то дескриптор выглядел бы следующим образом:
Здесь root – название корневого элемента нашего XML-файла с данными, а диапазон A1:Z100 – это прямоугольный диапазон ячеек из шаблона, который будет скопирован в результат. При этом, как можно видеть из предыдущей иллюстрации, подстановочные поля, значения которых заменяются на данные из XML-файла, имеют формат ~ (тильда, фигурная скобка, XPath-выражение относительно текущего элемента XML, закрывающая фигурная скобка).
Что делать, если в отчёте нам нужны повторяющиеся элементы? Естественным образом их можно представить в виде элементов XML-файла с данными, а помочь проитерировать по ним нужным образом помогает дескриптор. Повторение элементов в отчёте может иметь как вертикальное направление (когда мы вставляем строки накладной, например), так и горизонтальное (когда мы вставляем столбцы аналитического отчёта). При этом мы можем пользоваться вложенностью элементов XML, чтобы отразить сколь угодно глубокую вложенность повторяющихся элементов отчёта, как показано на диаграмме:
Красными квадратиками отмечены ячейки, которые будут являться левым верхним углом очередного прямоугольного фрагмента, который пристыковывает генератор отчёта.
Есть и ещё один возможный вариант повторяющихся элементов: листы в книге Excel. Возможность организовать такую итерацию тоже имеется.
Рассмотрим чуть более сложный пример. Допустим, нам надо получить сводный отчёт наподобие следующего:
Пусть диапазон лет для выгрузки выбирает пользователь, поэтому в этом отчёте динамически создаваемыми являются как строки, так и столбцы. XML-представление данных для такого отчёта может выглядеть следующим образом:
Мы вольны выбирать названия тэгов по своему вкусу, структура также может быть произвольной, но с оглядкой на простоту конвертации в отчёт. Например, выводимые на лист значения я обычно записываю в атрибуты, потому что это упрощает XPath-выражения (удобно, когда они имеют вид @имяатрибута ).
Шаблон такого отчёта будет выглядеть так (сравните XPath-выражения с именами атрибутов соответствующих тэгов):
Теперь наступает самая интересная часть: создание дескриптора. Т. к. это практически полностью динамически собираемый отчёт, дескриптор довольно сложен, на практике (когда у нас есть только «шапка» документа, его строки и «подвал») всё обычно гораздо проще. Вот какой в данном случае необходим дескриптор:
Полностью элементы дескриптора описаны в документации. Вкратце, основные элементы дескриптора означают следующее:
- element — переход в режим чтения элемента XML-файла. Может или являться корневым элементом дескриптора, или находиться внутри iteration . С помощью атрибута name могут быть заданы разнообразные фильтры для элементов, например
- name="foo" — элементы с именем тэга foo
- name="*" — все элементы
- name="tagname[@attribute='value']" — элементы с определённым именем и значением атрибута
- name="(before)" , name="(after)" — «виртуальные» элементы, предшествующие итерации и закрывающие итерацию.
- mode="horizontal" — режим вывода по горизонтали (по умолчанию — vertical)
- index=0 — ограничить итерацию только самым первым встреченным элементом
- sourcesheet —лист книги шаблона, с которого берётся диапазон вывода. Если не указывать, то применяется текущий (последний использованный) лист.
- range – диапазон шаблона, копируемый в результирующий документ, например “A1:M10”, или “5:6”, или “C:C”. (Применение диапазонов строк типа “5:6” в режиме вывода horizontal и диапазонов столбцов типа “C:C” в режиме вывода vertical приведёт к ошибке).
- worksheet – если определён, то в файле вывода создаётся новый лист и позиция вывода смещается в ячейку A1 этого листа. Значение этого атрибута, равное константе или XPath-выражению, подставляется в имя нового листа.
Ну что же, настало время скачать Xylophone и запустить формирование отчёта.
Возьмите архив с bintray или Maven Central (NB: на момент прочтения этой статьи возможно наличие более свежих версий). В папке /bin находится shell-скрипт, при запуске которого без параметров вы увидите подсказку о параметрах командной строки. Для получения результата нам надо «скормить» ксилофону все приготовленные ранее ингредиенты:
Открываем файл report.xlsx и убеждаемся, что получилось именно то, что нам нужно:Так как библиотека ru.curs:xylophone доступна на Maven Central под лицензией LGPL, её можно без проблем использовать в программах на любом JVM-языке. Пожалуй, самый компактный полностью рабочий пример получается на языке Groovy, код в комментариях не нуждается:
У класса XML2Spreadsheet есть несколько перегруженных вариантов статического метода process , но все они сводятся к передаче всё тех же «ингредиентов», необходимых для подготовки отчёта.Важная опция, о которой я до сих пор не упомянул — это возможность выбора между DOM и SAX парсерами на этапе разбора файла с XML-данными. Как известно, DOM-парсер загружает весь файл в память целиком, строит его объектное представление и даёт возможность обходить его содержимое произвольным образом (в том числе повторно возвращаясь в один и тот же элемент). SAX-парсер никогда не помещает файл с данными целиком в память, вместо этого обрабатывает его как «поток» элементов, не давая возможности вернуться к элементу повторно.
Использование SAX-режима в Xylophone (через параметр командной строки -sax или установкой в true параметра useSax метода XML2Spreadsheet.process ) бывает критически полезно в случаях, когда необходимо генерировать очень большие файлы. За счёт скорости и экономичности к ресурсам SAX-парсера скорость генерации файлов возрастает многократно. Это даётся ценой некоторых небольших ограничений на дескриптор (описано в документации), но в большинстве случаев отчёты удовлетворяют этим ограничениям, поэтому я бы рекомендовал использование SAX-режима везде, где это возможно.
Надеюсь, что способ выгрузки в Excel через Xylophone вам понравился и сэкономит много времени и нервов — как сэкономил нам.
В холдинге имеются файловые базы 1С на каждую отдельную компанию. Сейчас сбор отчетности производится следующим образом:
1) специалист фин отдела заходит в каждую базу и собирает необходимую информацию о движении денежных средств, карточку 20,25,26 счетов
2) копирует информацию в Excel
3) обрабатывает вручную в едином шаблоне в Excel, чтобы видеть консолидированную информацию по группе компаний.Как хочется делать: нажимать кнопку в Excel и получать данные из 1С автоматически.
Базы 1С не на SQL, а с обычным файловым доступом.
1С: Консолидацию не пробовали, но она требует очень долгой настройки, потому что виды бизнеса разные и разные учетные политики у организаций.По кнопке "Выгрузить в Excel" создаете ТабличныйДокумент и заполняете нужными данными. Потом вызываете у него метод Записать и указываете в параметрах путь для сохранения и формат в котором нужно сохранить.
За адекватную цену сделаю для вас эту задачу.Поскольку баз много, лучше без кнопок.
Банальная обработка которая будет запускаться из батника поочередно в каждой базе выгружая информацию в нужные поля листа Excel.АртемЪ: а как быть с тем, что базы могут быть закрыты? Точнее они итак закрыты. Хочется открыть файл Excel, нажать кнопку "Получить данные" и обработка сама вытащит из баз все данные. Как я понимаю, базы все равно нужно открывать.
Из батника вызываете 1cv8.exe указав в качестве параметров режим Enterprise и имя обработки.
Она откроется в фоновом режиме, выполнит обработку и закроется.Константин Нагибович: доступ есть к ним, они на удаленном сервере лежат. Я имею в виду, что в них никто не работает в этом время, т.е. программа не запущена.
АртемЪ: получается в батнике нужно будет имя пользователя и пароль прописать? И схема дальше получается: батник запустил обработку, обработка собрала нужные данные в файл и куда то положила, сводный Excel файл забрал этот файл. Так?
гигантская таблица с номенклатурой, в excel, которая обновляется по цене, скажем раз в квартал… Ответственный человек, также раз в квартал, берет ее и переносит данные вручную в Storehouse.
Номенклатура в excel и shouse по наименованию не совпадает…
Тоесть есть какие-то опознавательные знаки, по которым можно найти позицию и в excel и shouse, но наименования не идентичны…
Вопрос — как автоматизировать перенос данных из excel в shouse?
Пока в голове, на уровне дауна, идея — выгружать в xls таблицу номенклатуры с shouse, каким-то макаром сверять с исходной excel по общим «маркерам» в наименованиях и автоматом вносить в выгруженный shouse…. затем заливать его обратно…
В том ли направлении смотрю? Может есть готовые решения или кто-то писал что-нибудь подобное «на коленке»?
Буду рад любой помощи, человек чуть ли не умирает от этих таблиц, настолько это муторно и долго.
Там вроде Interbase в качестве БД, я бы рекомендовала сделать нормальный ETL-процесс, для наименований завести словарь, первично состыковать имена по расстоянию Левенштейна с разбивкой по словам, на каждое изменение цены (загрузку) заводить документ, чтобы это прозрачно отрабатывалось в системе.
как это технически реализовать?) можешь показать в какую сторону копать?
Если самому такую штуку проектировать, то надо бы, наверное, так:
1) загрузить Excel файл в таблицу Interbase внешней самописно утилитой
2) провести проверку значений — где они должны быть обязательны, где допустимы только цифры, где должны быть только индивидуальные значения
3) первичный Data Cleansing
4) из поля Наименование сделать список, разбить его по словам, собрать хэши (типа soundex) и ссылки наименований на слова, и собрать такую же информацию по наименованиям в базе, и собрать релевантность для каждой пары наименований из файла и базы, низко-релевантные пары можно сразу же отсеивать, потом профильтровать похожие «конкурирующие пары» по алгоритму расстояния Левенштейна, оставив только 1 с наименьшим расстоянием.
Ну или сопоставить 1 раз список наименований в файле и базе вручную.
5) Создать документ, кторый меняет эти цены и заполнить его, преобразуя наименование по получившемуся словарю в айдишки номенклатуры, с новой ценой ессесно.
6) Вторичный Data Cleansing
7) документ «провести» в системе
Вот как-то так. С самим StoreHouse особо не знаком, но имел дело когда-то с их кипером и 1С, не думаю, что общая концепция сильно изменилась.
очень хорошо написано, спасибо, буду пробовать разбираться как это сделать)
Если есть время и желание сделать самому, то п.4 будет весьма интересным)
Между полем наименование и отдельными словами надо будет реализовать связь меногие-ко-многим, слова из словаря очистить от «стоп-слов» — вроде «и» и т.п. общеупотребимых слов и союзов, скорее всего и от символов, а сами слова привести к одному регистру.
С Soundex тоже будет интересно, т.к. оригинальный алгоритм адаптирован под английские слова, но есть так называемый «восточно-европейский soundex» или Daitch-Mokotoff Soundex, единственно этот алгоритм тоже надо будет адаптировать под кириллицу (если она используется) — простая транслитерация должна подойти. По хэшам сопоставлять отдельные слова значительно проще. Мерой релевантности в данном случае будет количество слов в единичном наименовании из, скажем, файла, которые нашли похожие слова в одном и том же наименовании из базы, по отношению к общему количеству слов в наименовании из файла. Это считается простым агрегирующим запросом.
Но важно понимать, что даже единица не может означать точного совпадения наименований и нужна еще проверка по расстоянию Левенштейна (или даже лучше — расстояние Дамерау-Левенштейна). Реализации как DM Soundex, так и расстояния Дамерау-Левенштейна есть в сети.
Первичный Data Cleansing включает в себя по возможности исправление «косяков» в данных, которые могут быть исправлены автоматически, и принятие решения о судьбе данных, которые не могут быть исправлены — удалить или оставить на усмотрение оператора.
Вторичный Data Cleansing должен включать контроль за сопоставлением наименований, обработку дублирующих записей, возникших по многим причинам — когда одному наименованию номенклатуры в файле сопоставляются несколько в базе и наоборот, контроль прочих условий (например, если цена не может снижаться) и т.п.
Из сохранения результатов SQL-запроса в формате Excel или CSV я узнал, что лучше всего использовать мастер импорта и экспорта SQL, а не пытаться щелкнуть правой кнопкой мыши результаты и сохранить их.
Вы всегда можете щелкнуть правой кнопкой мыши и выбрать «Сохранить результаты как», однако полученный CSV-файл не будет содержать заголовков столбцов! Это королевская боль, если у вас есть 50 столбцов.
Экспорт данных SQL в Excel
Чтобы правильно экспортировать данные SQL, щелкните правой кнопкой мыши базу данных (не таблицу) и выберите Задачи, Экспорт данных.
Далее вам нужно выбрать источник данных. Если вы щелкнули правой кнопкой мыши на имени базы данных, то все должно появиться автоматически.
Теперь вам нужно выбрать пункт назначения. Идите вперед и выберите Microsoft Excel из выпадающего списка. После этого вам нужно будет нажать кнопку «Обзор» и выбрать местоположение для выходного файла Excel. Также не забудьте установить флажок «У первой строки есть имена столбцов».
Затем вы можете либо экспортировать всю таблицу / представление в Excel, либо написать собственный запрос. В моем случае я напишу конкретный запрос для данных, которые я хочу экспортировать.
Введите свой запрос и затем нажмите Parse, чтобы убедиться, что все правильно.
Наконец, нажмите Далее, а затем нажмите Готово. Ваши данные SQL будут выведены в файл Excel, и он будет также включать все заголовки столбцов! Наслаждайтесь!
Мало пользователей, да и начинающих программистов, которые знают о возможности Excel подключаться к внешним источникам, и в частности к SQL серверу, для загрузки данных из этих источников. Эта возможность достаточно полезна, поэтому сегодня мы займемся ее рассмотрением.
Функционал Excel получения данных из внешних источников значительно упростит выгрузку данных с SQL сервера, так как Вам не придется просить об этом программиста, к тому же данные попадают сразу в Excel. Для этого достаточно один раз настроить подключение и в случае необходимости получать данные в Excel из любых таблиц и представлений Views, из базы настроенной в источнике, естественно таких источников может быть много, например, если у Вас несколько баз данных.
Задача для получения данных в Excel
И для того чтобы более понятно рассмотреть данную возможность, мы это будем делать как обычно на примере. Другими словами допустим, что нам надо выгрузить данные, одной таблицы, из базы SQL сервера, средствами Excel, т.е. без помощи вспомогательных инструментов, таких как Management Studio SQL сервера.
Примечание! Все действия мы будем делать, используя Excel 2010. SQL сервер у нас будет MS Sql 2008.
И для начала разберем исходные данные, допустим, есть база test, а в ней таблица test_table, данные которой нам нужно получить, для примера будут следующими:
Эти данные располагаются в таблице test_table базы test, их я получил с помощью простого SQL запроса select, который я выполнил в окне запросов Management Studio. И если Вы программист SQL сервера, то Вы можете выгрузить эти данные в Excel путем простого копирования (данные не большие), или используя средство импорта и экспорта MS Sql 2008. Но сейчас речь идет о том, чтобы простые пользователи могли выгружать эти данные.
Заметка! Если Вас интересует SQL и T-SQL, рекомендую посмотреть мои видеокурсы по T-SQL, с помощью которых Вы «с нуля» научитесь работать с SQL и программировать с использованием языка T-SQL в Microsoft SQL Server.
Настройка Excel для получения данных с SQL сервера
Настройка, делается достаточно просто, но требует определенных навыков и консультации администратора SQL сервера. Вы, конечно, можете попросить программиста настроить Excel на работу или сделать это сами, просто спросив пару пунктов, а каких мы сейчас узнаем.
И первое что нам нужно сделать, это конечно открыть Excel 2010. Затем перейти на вкладку «Данные» и нажать на кнопку «Из других источников» и выбрать «С сервера SQL Server»
Затем у Вас откроется окно «Мастер подключения данных» в котором Вам необходимо, указать на каком сервере располагается база данных и вариант проверки подлинности. Вот именно это Вам придется узнать у администратора баз данных, а если Вы и есть администратор, то заполняйте поля и жмите «Далее».
- Имя сервера – это адрес Вашего сервера, здесь можно указывать как ip адрес так и DNS имя, в моем случае сервер расположен на этом же компьютере поэтому я и указал localhost;
- Учетные данные – т.е. это логин и пароль подключения к серверу, здесь возможно два варианта, первый это когда в сети Вашей организации развернута Active directory (Служба каталогов или домен), то в этом случае можно указать, что использовать те данные, под которыми Вы загрузили компьютер, т.е. доступы доменной учетки, и в этом случае никаких паролей здесь вводить не надо, единственное замечание что и на MSSql сервере должна стоять такая настройка по проверки подлинности. У меня именно так и настроено, поэтому я и выбрал этот пункт. А второй вариант, это когда администратор сам заводит учетные данные на SQL сервере и выдает их Вам, и в этом случае он должен их Вам предоставить.
Далее необходимо выбрать базу, к которой подключаться, в нашем примере это база test. Также это подключение можно настроить сразу на работу с определенной таблицей или представлением, список таблиц и представлений у Вас будет отображен, давайте мы сделаем именно так и настроем подключение сразу на нашу таблицу test_table. Если Вы не хотите этого, а хотите чтобы Вы подключались к базе и потом выбирали нужную таблицу, то не ставьте галочку напротив пункта «Подключаться к определенной таблице», а как я уже сказал, мы поставим эту галочку и жмем «Далее».
В следующем окне нам предложат задать имя файла подключения, название и описание, я например, написал вот так:
После того как Вы нажмете «Готово» у Вас откроется окно импорта этих данных, где можно указать в какие ячейки копировать данные, я например, по стандарту выгружу данные, начиная с первой ячейки, и жмем «ОК»:
В итоге у меня загрузятся из базы вот такие данные:
Т.е. в точности как в базе. Теперь когда, например, изменились данные в этой таблице, и Вы хотите выгрузить их повторно Вам не нужно повторять все заново, достаточно в excel перейти на вкладку «Данные» нажать кнопку «Существующие подключения» и выбрать соответствующее, т.е. то которое Вы только что создали.
Вот собственно и все, как мне кажется все достаточно просто.
Таким способом получать данные в Excel из базы SQL сервера очень удобно и главное быстро, надеюсь, Вам пригодятся эти знания полученные в сегодняшнем уроке. Удачи!
Как перенести или экспортировать данные SQL Server 2005 в Excel
У меня есть простой SQL-запрос "Select", и я хотел бы сбросить результаты в файл Excel. Я могу только сохранить как .csv и преобразование в .xls создает супер уродливый выход. В любом случае, насколько я могу судить (используя Google), это не кажется таким прямым. Любая помощь будет очень признательна.
используйте "внешние данные" из Excel. Он может использовать соединение ODBC для извлечения данных из внешнего источника: Data/Get External Data/New Database Query
таким образом, даже если данные в базе данных изменяются, вы можете легко обновить.
SSIS-это без проблем для таких вещей, как это, и очень прямолинейно (и это именно то, для чего он предназначен).
- щелкните правой кнопкой мыши базу данных в SQL Management Studio
- перейти к задачам, а затем экспортировать данные, вы увидите простой в использовании мастер.
- ваша база данных будет источником, вы можете ввести свой SQL-запрос
- выберите Excel в качестве цели
- запустите его в конце мастера
Если вы вы также можете сохранить пакет служб SSIS (есть опция в конце мастера), чтобы вы могли сделать это по расписанию или что-то еще (и даже открыть и изменить, чтобы добавить больше функциональности, если это необходимо).
Я нашел простой способ экспортировать результаты запросов из SQL Server Management Studio 2005 в Excel.
1) Выберите пункт меню запрос -> параметры запроса.
2) Установите флажок в результатах — > сетка ->включить заголовки столбцов при копировании или сохранении результатов.
после этого, когда вы выбираете все и копируете результаты запроса, вы можете вставить их в Excel, и заголовки столбцов будут присутствовать.
это, безусловно, лучший пост для экспорта в excel из SQL:
процитировать пользователя madhivanan ,
Помимо использования DTS и мастера экспорта, мы также можем использовать этот запрос для экспорта данных из SQL Server2000 в Excel
создайте файл Excel с именем testing, имеющий заголовки, такие же, как у столбцов таблицы и используйте эти запросы
1 экспорт данных в существующий файл EXCEL из таблицы SQL Server
2 экспорт данных из Excel в новую таблицу SQL Server
3 экспорт данных из Excel в существующую таблицу SQL Server
4 Если вы не хотите создавать файл EXCEL заранее и хотите экспортировать в него данные, используйте
(теперь вы можете найти файл с данными в табличном формате)
5 для экспорта данных в новый файл EXCEL с заголовком (имена столбцов), создайте следующую процедуру
после создания процедуры выполните ее, указав имя базы данных, имя таблицы и путь к файлу:
его whomping 29 страниц, но это потому, что другие показывают различные другие способы, а также люди, задающие вопросы, как и этот, о том, как это сделать.
полностью следуйте этой теме и посмотрите на различные вопросы, которые люди задали, и как они решаются. Я взял довольно много знаний, просто просматривая его и использовал его части, чтобы получить ожидаемые результаты.
обновить отдельные ячейки
член Также там Питер Ларсон публикует следующее: Мне кажется, здесь чего-то не хватает. Здорово иметь возможность экспортировать и импортировать файлы Excel, но как насчет обновления отдельных ячеек? Или ряд клеток?
это принцип того, как вы управляете этим
вы также можете добавьте формулы в Excel, используя это:
экспорт с именами столбцов с помощью T-SQL
член Младен Прайдич также имеет запись в блоге о том, как это сделать здесь
Если вы ищете специальные элементы, а не то, что вы бы поместили в SSIS. Изнутри SSMS просто выделите сетку результатов, скопируйте, затем вставьте в excel, это не элегантно, но работает. Затем вы можете сохранить как родной .формате xls, а не .csv
вот видео, которое покажет вам, шаг за шагом, как экспортировать данные в Excel. Это отличное решение для "одноразовых" проблем, когда вам нужно экспортировать в Excel:
ОтчетыЧитайте также: