Чем sql лучше excel
У меня есть запрос, который возвращает очень большой набор данных. Я не могу скопировать и вставить его в Excel, что я обычно делаю. Я проводил некоторые исследования о том, как экспортировать напрямую в лист Excel. Я запускаю SQL SERVER 2008 на сервере под управлением Microsoft Server 2003. Я пытаюсь использовать поставщик данных Microsoft.Jet.OLEDB.4.0 и Excel 2007. Я собрал небольшой фрагмент кода, который выглядит так, как я видел в примерах.
Msgstr "Неверный синтаксис рядом с ключевым словом SELECT".
У кого-нибудь есть идеи о том, как это сделать или, возможно, лучший подход?
Я не знаю, если это то, что вы ищете, но вы можете экспортировать результаты в Excel следующим образом:
В области результатов щелкните верхнюю левую ячейку, чтобы выделить все записи, затем щелкните правой кнопкой мыши верхнюю левую ячейку и выберите «Сохранить результаты как». Одним из вариантов экспорта является CSV.
Вы можете сделать это тоже:
Наконец, вы можете изучить использование SSIS (заменено DTS) для экспорта данных. Вот ссылка на учебник:
Если вам просто нужно экспортировать в Excel, вы можете использовать мастер экспорта данных. Щелкните правой кнопкой мыши базу данных, Задачи-> Экспорт данных.
У меня была похожая проблема, но с изюминкой - перечисленные выше решения работали, когда набор результатов был из одного запроса, но в моей ситуации у меня было несколько отдельных запросов select, для которых мне нужно было экспортировать результаты в Excel. Ниже приведен пример для иллюстрации, хотя я мог бы сделать предложение name in .
Мастер позволял мне экспортировать результат из одного запроса в Excel, но не все результаты из разных запросов в этом случае.
Когда я исследовал, я обнаружил, что мы можем отключить результаты в сетке и включить результаты в текст. Итак, нажмите Ctrl + T, затем выполните все операторы. Это должно показать результаты в виде текстового файла в окне вывода. Вы можете манипулировать текстом в формате с разделителями табуляции для импорта в Excel.
Вы также можете нажать Ctrl + Shift + F, чтобы экспортировать результаты в файл - он экспортируется как файл .rpt, который можно открыть с помощью текстового редактора и управлять им для импорта в Excel.
Надеюсь, что это поможет другим людям, имеющим аналогичную проблему.
Сначала создайте свой модельный класс.
Затем установите пакет EPPlus Nuget . (Я использовал версию 4.0.5, вероятно, будет работать и для других версий.)
Класс create ExcelExportHelper , который будет содержать логику для преобразования набора данных в строки Excel. Этот класс не имеет зависимостей с вашим классом модели или набором данных.
Теперь добавьте этот метод, где вы хотите создать файл Excel, вероятно, для метода в контроллере. Вы также можете передать параметры для вашей хранимой процедуры. Обратите внимание, что тип возвращаемого значения метода FileContentResult . Независимо от того, какой запрос вы выполняете, важно то, что вы должны иметь результаты в List .
Я задумался использовать SQLite для хранения данных. Но не знаю какую выгоду я для себя получу от перехода на SQLite (заместо Excel). Просто я думаю, стоит ли изучать SQL и переходить на SQLite или нет.
Какие выгоды и какие недостатки я получу от перехода на SQLite?
- Вопрос задан более трёх лет назад
- 1452 просмотра
Простой 1 комментарий
Переводить ваше приложение на использование БД или нет - должно быть виднее вам. Но вам не виднее, так как вы не умеете ими пользоваться. И это плохо, программист без знания SQL - не программист вовсе. Поэтому SQL нужно выучить однозначно, а потом на основе полученных знаний принять решение.
если excel вы используете только для себя и вас он устраивает, то преимущества sqlite вам ни к чему.
1. Excel - платный продукт, который есть не везде. SQLite - бесплатный движок, доступен под любой язык программирования, есть огромное количество бесплатных программ, которые могут с ним работать. Также можно на любой языке написать простенькую визуализацию данных из sqlite
2. Excel - электронная таблица. SQLite - база данных. Это разные продукты. По сути надо сравнивать SQLite и Access. Просто Excel достаточно мощная программа, в которой средства, чтобы справляться с рядом не слишком сложных задач, которые пора бы уже ложить в базу данных.
3. Для работы с SQLite нужно писать запросы, Excel более распространен и популярен - почти любой может в экселе посмотреть данные и что-то с ними сделать при помощи мышки.
А насчет конкретно вас - никто не скажет, ибо структура данных, требования к быстродействию и вообще - знаете только вы. Просто подучите SQL и освойте SQLite, чтобы решить надо оно вам или нет.
Не то, что бы sqlite хорош для бигдаты, но переход на взрослые субд сильно проще.
Плюс часть бизнес-логики иногда сильно удобнее перенести в SQL
x67, для бигдаты sql вообще не хорош.
Но я не солгашусь, что sqlite это не "взрослая субд). Sqlite - это своя отдельная ниша - отличная локальная однопользовательская база без дополнительных серверов. Например, даже конфиги в ней хранить удобно.
Saboteur, это почему же не хорош?
Sqlite - хорошо развитый проект, но относительно других субд по возможностям ее нельзя назвать взрослой из-за ограниченности возможностей. В этом ничего плохого нет, потому что это как вы правильно сказали, нишевый продукт.
x67, потому что для бигдата нужно либо хорошо масштабируемая база данных, либо nosql база данных, которая изначально заточена под большой объем.
СУБД прекрасно позволяют работать с огромными массивами информации. С масштабированием есть нюансы. Но очень немногим при работе с бигдатой нужно обработать десять террабоайт информации на распределенном кластере.
x67, Не понимаю вашей категоричности.
Я сказал ЛИБО масштабируемая база данных ЛИБО nosql, который заточен под большой объем.
Для маленького объема нет смысла ставить nosql - любая стандартная база данных умеет хранить обычную таблицу из пар ключ-значение и справится с нагрузкой. nosql - это решение в первую очередь для производительности на больших объемах.
И да, bigdata - это не десять терабайт, это обычно гораздо больше, либо вы неправильно понимаете этот термин, который изначально предполагает громадные объемы информации, а не просто "большую базу данных"
NoSQL часто и наверное чаще используются по другим причинам, не все в скорость упирается.
x67, именно в скорость.
nosql это банально база данных, которая хранит всего два столбца - ключ и значение.
С этим справится ЛЮБАЯ стандартная sql база, которая чуть ли не на любом хостинге есть по дефолту.
Так зачем заморачиваться и ставить что-то нестандартное?
Самые известные носкл базы - mongoDB (от слова huMongous - огромный) и Hadoop - изначально спроектирован для распределенных вычислений.
Да, конечно можно юзать mongo для небольшой документоориентированной базы. Но проблема в том, что SQL гораздо популярнее nosql, а значит писать на mongoDB мелочь - выйдет дороже в поддержке.
p.s. Кроме гугла есть огромное количество компаний в ентерпрайзе, у которых миллионы клиентов. У гугла - миллиарды. И многим нужна бигдата. Ну и если что, могу прислать свою фотку из офиса Гугла в mountain view.
Как по мне, основное преимущество баз данных перед электронными таблицами - возможность быстрой обработки огромного объёма данных.
Откройте эксель-файл в несколько сот мегабайт - и насладитесь тормозами и тупняками экселя. А что, если из этого файла выдернуть сотню записей, удовлетворяющих определённым критериям? Что если это нужно делать 100 раз в секунду по запросам? А если результат должен быть возвращён не за минуту, а за секунду, иначе клиент не захочет ждать, пока программа вернёт ему то, что нужно?
Эксель и другие электронные таблицы для такого явно не годятся. В то время, как СУБД могут с лёгкостью обрабатывать гигабайты и даже терабайты данных (естественно, при условии, что структура БД, индексы и запросы составлены грамотно, иначе - быстрой обработки не видать, преимуществ перед обычным файлами при неграмотном подходе никаких не будет).
Ну и навскидку - не припомню ни одной программы, которая хранит данные в Excel. В то время, как в sqlite - хранят все современные браузеры (firefox, chrome и их производные, за Edge не скажу), а также множество прикладных программ, тут список только известных.
Плюсы организации ИС на основе EXCEL просто не счесть, поэтому перечислим только некоторые:
1 - Скорость и простота разработки схемы хранения данных
2 - простота администрирования. например для бэкапа достаточно скопировать файлик в архив
3 - Поддержка ODBC для внешних репортингов
4 - возможность распределённого хранения данных
5 - МОЩНЫЕ средства ОЛАП анализа
6 - встроенный язык программирования
7 - Дешевизна специалистов/программистов
8 - Хранение данных производиться в разряженной таблице
9 - есть объектный доступ
А - ещё множество плюсов
Кстати я ни в коем случае не агитирую за отказ от обычных СУБД. Лично я убежден, что подавляющее большинство существующих сегодня бизнес-задач лучше реализуется именно в в них и именно поэтому они стали столь популярными. Просто в России почему-то решили, что обычные СУБД - лучший выбор для всех случаев, что в корне неверно (примеров использования EXCEL крупнейшими западными компаниями множество, да и по всему миру насчитываются миллионы пользователей EXCEL).
Кстати я забыл упомянуть, что EXCEL отлично поддерживает XML, HTML, CVS , TXT , DOC. и другие перспективные стандарты/технологии
минус
безопасность по понятиям SQL отсутсвует полностью
интересно как будут работать несколько человек с одним листом?
для одноразовой залипухи Excel в самый раз, а если нужно обеспечить нагрузку на приложение, то для таких задач он вооще не катит.
типа не предназначен
Конечно, тока надежности по сравнению с СУБД вооще нет
Нагрузку на приложение EXCEL как раз даст такую, что и не снилось.
Cовременный EXCEL имеет тот же уровень надежности, что нам может обеспечить РСУБД (особенно MS SQL, Sybase, Oracle) и даже ООП СУБД .
Спасибо за поддержку!
2 алл
Фирма 1с не даром выбрала для себя движок EXCEL для генерации отчётов. Правда для усложнения администрирования (и соответственно повышения расценок) в качестве хранилища используется устаревший ДБФ. "А при использовании SQL версии функционала вообще меньше" (это почти цитата из форума)
поэтому вопрос: Кто и как понимает термин Табличные Процессоры?
я не стану выносить это в отдельный топик?
Строка - сущность а зачем вам сущностей больше чем 65535*255=2^24
2 олл
Но давайте не придираться к словам, а отвечать на вопросы вот Вам задали вопрос , отвечайте
В своей прошлой статье я рассказывал про программные возможности языка SQL и обещал поделиться кейсом по созданию автоматизированного отчета на основе стека технологий MS SQL Server и Power BI.
Почему именно эти технологии?
За время работы аналитиком, я перепробовал различные варианты сбора отчетности. Начиная с ручной выгрузки данных из кабинетов рекламных систем, с последующим сведением в Excel, и заканчивая созданием специальных отчетов в Google Analytics или дашбордов в Data Studio.
Но ни один из вариантов не был идеальным и каждый имел свои недостатки. Все изменилось, когда я открыл для себя Power BI.
Но и Power BI сам по себе не идеален и без грамотного использования будет работать медленно и неэффективно. Приведу два примера:
Вышеописанные проблемы привели меня к мысли о загрузке всех данных сначала в базу, моделировании отчета при помощи SQL и только потом их визуализации в Power BI.
Переходим к делу
Для примера возьмем задачу по автоматизации отчета по эффективности контекстной рекламы.
К данному отчету заказчиком предъявляются следующие требования:
- Отчет должен содержать исторические данные по вчерашний день;
- Отчет должен обновляться ежедневно в автоматизированном режиме;
- Помимо Power BI, должна быть возможность подключения к отчету через Excel.
Также отчет должен содержать следующие параметры и показатели:
Естественно, все данные должны быть предварительно загружены в хранилище, но это тема отдельного поста и обычно этим занимаются data-инженеры. Мы же с вами аналитики и используем те данные, которые для нас любезно сложили в DWH (хранилище данных).
В моем случае DWH работает на базе MS SQL Server и содержит следующие таблицы:
Для работы нам потребуется установить:
Опущу совсем уж базовые вещи, такие как регистрация аккаунтов и установка программ, с этим вы без проблем справитесь и сами.
Готовим данные
Создаем таблицу
Для того чтобы создать отчет, нам необходимо свести данные по расходам, сеансам и заказам в одной таблице. Для этого напишем SQL-запрос, в котором объединим таблицы по следующим ключам:
- date ;
- sourceMedium ;
- campaign .
Кстати, никакой сквозной аналитики у вас никогда не получится, если вы не умеете грамотно размечать рекламу utm-метками. О том как правильно ставить метки, читайте в одном из уроков бесплатного онлайн-курса «Digital-аналитика для новичков».
Но вернемся к задаче и после некоторых манипуляций с SQL получим вот такой скрипт:
Запустим его и порадуемся получившемуся результату:
Создаем таблицу
Скрипт работает и выдает отчет, в принципе его уже можно использовать для автоматизации вставив в Power BI при помощи встроенного коннектора. Но не советую так делать, потому что если данных в отчете будет много, например заказчик захочет посмотреть как работали рекламные кампании в течение года, на выполнение скрипта может уйти несколько часов.
Гораздо более правильным решением будет создать промежуточную таблицу в базе данных и докладывать туда ежедневно данные за прошедшие сутки. Что мы и сделаем:
Таблица будет иметь следующую структуру (подробнее о типах данных):
При сохранении таблицы укажем название:
И теперь, чтобы получить все данные из нее, достаточно выполнить простой SELECT :
Создаем хранимую процедуру
Отлично! Настало время автоматизации 😉
А поможет нам в этом функционал хранимых процедур (подробнее рассказывал о них тут).
Засучим рукава и обернем наш скрипт в код процедуры:
Теперь протестируем и вручную вызовем процедуру:
Осталось настроить ежедневное обновление.
Настраиваем расписание
Зайдем в агент и добавим новое задание:
Укажем название и придумаем описание:
Далее создадим новый шаг, в котором будем вызывать процедуру с данными за прошедшие сутки (обратите внимание, объявление переменных с датами из нашего скрипта мы перенесли в расписание и немного изменили):
Настраиваем время запуска, периодичность и сохраняем:
Теперь данные автоматически будут поступать в отчет ежедневно в 9 утра.
Визуализируем данные
Данные готовы, обновление настроено, самое время приступить к визуализации.
Останавливаться на том как установить Power BI и как им пользоваться не буду, так как этой теме посвящен целый урок нашего курса.
Создаем отчет
Заходим в desktop-версию Power BI и открываем коннектор к SQL Server:
Вводим данные для подключения к серверу, название базы данных и наш короткий SQL-запрос к ранее созданной табличке:
И это все! Никаких сложных моделей в Power BI строить не нужно, так как мы уже это сделали на стороне SQL-запроса.
Наиболее правильным считаю подход, когда инструмент визуализации используется именно для этой самой визуализации и еще для создания рассчитываемых показателей (например, CPC, CPO, ROMI). Используйте эти рекомендации и ваши отчеты будут летать.
После того как будет готов дизайн отчета, его нужно загрузить в облако Microsoft:
Настраиваем расписание
Отчет опубликован! Остался финальный шаг, для этого переходим в веб-версию Power BI и настраиваем расписание обновления.
Но перед этим не забываем поставить на компьютер, с которого будет происходить обновление, локальный шлюз Power BI (а лучше всего завести под это дело отдельную виртуальную машину):
Важно так подгадать расписание, чтобы оно запускалось в тот момент, когда на стороне SQL Server уже отработает наша процедура и положит в табличку свежие данные. Плюс нужно заложить небольшой запас времени, на возможные проблемы с сервером при его перегрузке:
А как же Excel?
Иногда заказчики могут попросить загрузить данные в Excel для более детального анализа.
После чего останется только указать SQL-запрос и сохранить:
С этого момента данные из нашей таблицы на сервере станут доступны в Excel.
В итоге мы получили автообновляемую отчетность, без привлечения каких-то гигантских ресурсов разработки и без особых денежных затрат.
Читайте также: