Подключение внешних данных в excel. запрос на выборку данных (формулы) в excel

11 ответов

есть много прекрасных способов сделать это, которые другие уже предложили. Следуя вдоль «получить данные Excel через SQL track», вот некоторые указатели.

Excel имеет «мастер подключения к данным», который позволяет импортировать или связать из другого источника данных или даже в том же файле Excel.

в составе Microsoft Office (и ОС) есть два интересующих поставщика: старый » Microsoft.Реактивный.OLEDB», и последний «Microsoft.ТУЗ.Для oledb». Ищите их при настройке соединения (например, с помощью мастера подключения к данным).

после подключения к книге Excel рабочий лист или диапазон эквивалентны таблице или представлению. Имя таблицы рабочего листа — это имя рабочего листа со знаком доллара ( » $ » ), добавленным к нему, и окруженным квадратными скобками («»); диапазона, это просто имя диапазона. Чтобы указать неназванный диапазон ячеек в качестве источника записей, добавьте стандартные обозначения строк/столбцов Excel в конец имени листа в квадратных скобках.

собственный SQL будет (более или менее) SQL Microsoft Access. (В прошлом это называлось JET SQL; однако Access SQL эволюционировал, и я считаю, что JET является устаревшим old tech.)

пример чтения рабочего листа: выберите * из

пример, чтение диапазона: выберите * из Миранж!—3—>

пример чтения неназванного диапазона ячеек: выберите * из

есть много много много книг и веб-сайтов, доступных, чтобы помочь вам работать через детали.

по умолчанию предполагается, что первая строка источника данных Excel содержит заголовки столбцов, которые могут использоваться как имена полей. Если это не так, вы должны включить этот параметр выкл., или ваша первая строка данных «исчезает» для использования в качестве имен полей. Это делается путем добавления необязательного параметра HDR= в расширенные свойства строки подключения. Значение по умолчанию, которое не нужно указывать, равно HDR=Yes. Если у вас нет заголовков столбцов, вам нужно указать HDR=No; поставщик называет ваши поля F1, F2 и т. д.

предупреждение об указании листов: поставщик предполагает, что ваша таблица данных начинается с самой верхней, самой левой, непустой ячейки на указанном листе. Другими словами, ваша таблица данных может начинаться в строке 3, столбце C без проблем. Однако нельзя, например, ввести заголовок листа выше и слева от данных в ячейке A1.

предупреждение об указании диапазонов: когда вы указываете лист в качестве источника записей, поставщик добавляет новые записи ниже существующих записей на листе, насколько позволяет пространство. При указании диапазона (именованного или неназванного) Jet также добавляет новые записи ниже существующих записи в диапазоне, как позволяет пространство. Однако при запросе исходного диапазона результирующий набор записей не включает вновь добавленные записи вне диапазона.

типы данных (стоит попробовать) для CREATE TABLE: Short, Long, Single, Double, Currency, DateTime, Bit, Byte, GUID, BigBinary, LongBinary, VarBinary, LongText, VarChar, Decimal.

Как объединить две и более Excel таблиц с помощью надстройки «SQL запросы»

Вы можете объединить несколько таблиц Excel в одну, если у них есть общее ключевое поле. Предположим, вам нужно объединить несколько таблиц по общему столбцу «КодТовара»:

Нажмите «Выполнить SQL» на вкладке XLTools > Выберите поля, которые нужно включить в объединённую таблицу. По мере выбора полей, автоматически генерируется запрос SELECT и LEFT JOIN.
Укажите, куда необходимо поместить результат запроса: на новый или существующий лист.
Нажмите «Выполнить» > Готово! Объединённая таблица появится в считанные секунды.

Появились вопросы или предложения? Оставьте комментарий ниже.

Возможные проблемы при выгрузке и загрузке данных из 1С в Excel

Иногда бывает так, что простая кнопка «Сохранить» не активна и не получается сохранить документ. В этом случае нужно активизировать саму форму, кликнув по ней в любом месте. Теперь кнопка станет активной. Другие ошибки, особенно при использовании программирования различных методов, чаще всего связаны с:

неправильным указанием пути к файлу;
попытками создать файл ненадлежащего имени;
Опечатки в коде скриптов.

Таким образом, стало понятно, как из 1С выгрузить документы в Excel и как произвести импорт из Excel.В экспорте и импорте табличных файлов из Excel в 1C и наоборот нет ничего очень сложного. Существует множество методов это сделать и каждый из них больше подходит для своих ситуаций.

https://www.youtube.com/watch?v=O6agy1bsVfA

Загрузка номенклатуры (с реквизитами, свойствами, несколькими единицами измерений и их коэффициентами, объемами, весами и штрихкодами), прайсов, счетов, накладных, УПД и прочих товарных документов из Excel для Управление торговлей 10.3

Загружает номенклатуру, единицы измерения (вес и объем), штрихкоды (поддерживается одновременная загрузка нескольких единиц и штрихкодов), реквизиты и свойства номенклатуры, номера ГТД и серии. Загрузка производится во все документы в которых поддерживается механизм подбора номенклатуры (Заказ покупателя, Поступление товаров и услуг, Установка цен номенклатуры и многие другие). Ищет номенклатуру по наименованию, артикулу, штрихкоду и аналогичным полям в номенклатуре контрагентов. Работает в УТ 10.3 (весьма вероятно будет работать в КА 1 и УПП). Для работы необходима платформа не ниже 8.3.6. Наличие установленного Excel на компьютере не обязательно. Не загружает изображения! Не работает в БП, УТ 11, Рознице 2, УНФ и прочих новых конфигурациях!

3 стартмани

07.10.2018
19177
69

1c.pro.fun

ORA-01019 подключение к Oracle из Excel

Я установил Oracle 10g Express Edition. При попытке проверить соединение я получаю сообщение об ошибке «Ошибка при попытке получить текст для ошибки ORA-01019».

У меня недавно произошла следующая ошибка.

Мне удалось решить проблему, просто изменив строку подключения.

кто-то изменил / обновил компоненты на коробке.

«ORA-01019 не удалось выделить память на стороне пользователя

Причина. Распределитель памяти на стороне пользователя возвратил ошибку.

Действие: Увеличьте размер кучи процесса или переключитесь на старый набор вызовов.»

Продолжение из комментариев:

Не могли бы вы попробовать этот код?

закрыть конец соединения

(Это не помещалось в поле для комментариев)

Вам нужен как минимум один водитель. Драйвер оракула лучше, но Microsoft Driver тоже будет работать.

Давайте сначала попробуем создать строку подключения. Щелкните правой кнопкой мыши на рабочем столе и создайте новый файл.txt. Теперь переименуйте ваш текстовый файл в нечто.udl Дважды щелкните файл udl. Перейдите к «Поставщику» и выберите Microsoft OLEDB Provider для Oracle. Затем нажмите на следующий. В поле имени сервера вы вводите свое имя TNS. Затем введите имя пользователя и пароль и вставьте V в поле «Разрешить сохранение пароля» (оно нам понадобится). Нажмите на тестовое соединение. Убедитесь, что это работает.

Если это работает, нажмите ОК. Теперь откройте файл UDL с помощью текстового редактора. Вы увидите нечто похожее на:

Скопируйте эту часть в строку подключения:

Теперь ваша строка подключения должна выглядеть так:

ситуацией переноса ( к онвертирования) данных из Excel в БД Oracle,

готов поделиться таким нехитрым опытом.

Итак , нужно было залить данные Excel по кадровому составу некой фирмы приблизительно такого содержания .

Да , тут нужны еще вот какие пояснения. База данных Oracle для случая нашего примера была не слишком большой по размеру, не слишком сложной и доступна для ограниченного количества пользователей. Поэтому, в контексте нашего примера, на компьютере была установлена облегченная версия Oracle Database 11g Release 2 Express Edition

Следующим вопросом был выбор оболочки для собственно управления этой самой облегченной версией. К счастью, и тут нашлось решение в виде Oracle SQL Developer

Прим. Процедура скачки упомянутых выше продуктов вполне обычная — заводите учетку на сайте http://www.oracle.com/ и качайте себе на здоровье. Правда, скорость отдачи ну очень медленная. Гораздо проще и быстрее забрать эти вещи с территории известных зон. Там они есть

Однако, ‘вернемся к нашим баранам’ — то бишь загрузки Excel-данных в Oracle. Предположим, что вы вполне успешно проинсталлировали Oracle Express Edition и Oracle SQL Developer ( строго говоря , это тема отдельного разговора). Переходим к непосредственно к работе с последним.

Прим. Имейте в виду — если у вас на компьютере установлен Антивирус Касперского , то есть вероятность при запуске sqldeveloper’ a получить такое сообщение :

Выход из создавшейся коллизии заключается в разрешении в настройках Касперского активности для модулей sqldeveloper ‘а. Или уж совсем лобовое решение — просто на время отключить защиту( но это совсем в крайнем случае)

Тут для начала вам придется создать соединение с правами пользователя SYSTEM (по умолчанию пароль adm). Исходная диспозиция имеет приблизительно сл. вид :

Комментарий к рисунку слева. Командой

CREATE USER CHIEF IDENTIFIED BY boss создаем пользователя CHIEF с паролем boss. Такой пользователь нам понадобиться, чтобы создать, в терминах Oracle схему для работы с данными.

Идем далее. Командой CONNECT CHIEF/boss попробуем установить соединение :

Нет, ошибка — потерпели неудачу. Но ничего страшного . Командой GRANT CREATE SESSION TO CHIEF разрешим пользователю CHIEF устанавливать соединения :

Excel Data Export to SQL Server using SaveToDB Add-In

The SaveToDB add-in allows saving data changes from Excel to databases.

You can save data using the Save button at the ribbon or from VBA macros.

The simplest scenario is saving changes to a single target table. It works without coding.
Moreover, you can load data from tables, views, or stored procedures.

If you need to save the data to multiple normalized tables, you have to use stored procedures
for INSERT, UPDATE, and DELETE operations. It is not so hard.

Procedure TestExportUsingSaveToDB

The macro saves data changes of the active table to a database and reloads the data.

Sub TestExportUsingSaveToDB()

    Dim addIn As COMAddIn
    Dim addInObj As Object

    Set addIn = Application.COMAddIns("SaveToDB")
    Set addInObj = addIn.Object

    addInObj.Save

End Sub

Code comments:

The SaveToDB add-in makes a lot of work behind the scene.

It saves table metadata, a copy of loaded data, and data changes on hidden sheets.
You can even close the workbook. When the Save action is called, it builds and sends
INSERT, UPDATE and DELETE statements (or specified stored procedures) to a database.

You can learn hidden sheets using the SaveToDB, Options, Developer Options tab,
and generated SQL commands using the SaveToDB, Save, View Save Changes SQL menu item.

Особенности выгрузки и загрузки данных из 1С в Excel

Сравнительно недавно в программе «1С: Бухгалтерия» появилась возможность манипулировать данными 1С, загрузка из Excel которых ранее производилась лишь внешними способами. Это позволило производить загрузку и выгрузку документов без изменений конфигурации и использования сторонних обработок. Все процедуры по обороту файлов стали настолько просты, что опытные специалисты сразу же полюбили этот функционал, а новые освоили его очень быстро.

Сама по себе выгрузка в MS Excel может потребоваться в некоторых ситуациях, например, при копировании готовой таблицы в абсолютно пустую базу данных. Также это применяется при необходимости работать с базами без использования средств 1С. С выходом новой «1С: Бухгалтерия 8.3 ред. 3.0» отпала нужда применять сторонние средства для выгрузки, так как платформа получила все необходимые для этих целей механизмы. Несмотря на это, старые способы остались, и о них будет рассказано далее.

Вид MXL-файла в программе

Особенность загрузок и выгрузок из 1С состоит в том, что программа работает со следующими типами файлов сохранений для документов и счетов:

.mxl — специальный файл сохранений, разработанный для 1С программ. Используется ими для сохранения таблиц. Его можно сразу подгружать в другую базу данных 1С на другом компьютере;
.xls — файл таблиц данных MS Excel, ставший стандартным для этой программы и других табличных редакторов;
.pdf — файл-документ, который разработан компанией Adobe для своего редактора Adobe Reader;
.html — документ в виде веб-страницы, которая может быть просмотрена в любом браузере или размещена на сайте;
.txt — стандартный для многих систем текстовый файл.

У каждого из этих форматов есть свои особенности, влияющие на сохранение и отображение сведений и данных из регистров. Наиболее близким к стандартному файлу сохранений .mxl от 1С становится файл Excel .xls, так как только при выгрузке в этих двух форматах возможно отображение в таблице исходной иерархии, наличие возможности сворачивать и разворачивать группы. Остальные форматы предоставляют информацию простыми списками.

Важно! Microsoft Excel выбран не случайно. Когда-то он заменил собой бумажные списки и таблицы, ручное ведение бухгалтерии в офисах

Позже он отошел на второй план в этой области после появления 1С. Несмотря на это, Excel был и остается одним из наиболее популярных табличных процессоров с огромным функционалом и собственным языком создания макросов VBA от Microsoft. Программа и ныне часто используется для импортирования и экспортирования данных из различных систем учета.

Наиболее простым способом выгрузить все таблицы из программ 1С в формат «Экселя» является простое сохранение в .xls. Для начала нужно открыть нужную базу в Бухгалтерии, а затем выбрать «Операции» — «Справочники» — «Номенклатура», но иногда нужно сохранить отдельную часть документа. При большом объеме таблиц каждая группа выгружается в отдельный файл для повышения удобства работы.

Выгрузка номенклатуры

Дальнейшие действия таковы:

Нажать ПКМ на любой строчке и выбрать «Вывести список», чтобы открыть меню установок;
Пометить галочками поля, которые нужно выгрузить в Excel;
В строке «Выводить в…» выбрать текстовый или табличный документ и нажать Энтер;
Будет открыта таблица, на которую нужно нажать в произвольном месте ЛКМ, чтобы выделить ее полностью;
Перейти в Файл — Сохранить как;
Указать нужный формат и дать название будущему документу.

Загрузка же таблиц из Excel в Бухгалтерию производится таким же способом, но есть один нюанс: она должна производиться только профессионалом, который сможет исправить все ошибки, которые возникают при импорте больших объемов данных.

Важно! Перед началом любой загрузки и выгрузки следует во избежание недоразумений и ошибок совершать резервное и архивное копирование базы данных и документов. Может произойти так, что ошибка не позволит вернуть целостность файлу

Теперь понятно, что такое работа с Excel, как в 1С сохранить данные в Excel, как произвести в 1С чтение и импорт из Excel. Осталось лишь ознакомиться с доступными для этих действий способами.

Выгружаем результаты запроса из MS SQL Server в Excel

Д овольно часто пользователи MS SQL Server сталкиваются с задачей конвертации данных из базы данных формата MS SQL Server в формат Excel. Результаты запроса необходимо каким-то образом интерпретировать , и понятное дело , лучше это делать в представлении данных в наиболее популярном Excel’ е . Если знать последовательность действий для выполнения этой задачи конвертации , то можно убедиться в том , что сделать это совсем не сложно.

В качестве примера , предположим , что нужно отконвертировать в Excel данные справочника товаров — табличка Tovary нашей учебной базы OOO_RogaKopyta.

Для выполнения первого шага становимся на нужную нам таблицу в панели Обозревателя объектов ( в Management Studio, понятное дело) и по правой кнопке в контекстном меню выбираем Выбрать первые строк :

Прим. Как изменить количество строк для выборки по правой кнопке можно посмотреть здесь . Ну и кроме того, подобным образом мы моделируем получение результатов какой-то выборки. Совершенно очевидно , что вместо приведенного запроса типа Select Top 1000 может быть абсолютно любой другой запрос , который возвращает хотя бы одну строчку .

Первый случай

разработчик может отключить сохранение из списков, у отдельных форм
обычно этот типовой функционал расширяется по кнопке, либо вызов более удобного отчета
у вас может отсутствовать право «Вывода», уберет возможность копирования в буфер обмена или печать документа, соответственно и сохранить в эксель в этом случае не получится
возможность вывода списков доступно и в конфигураторе (например в момент отладки можно выгрузить таблицу значений)

Скачать последнюю версию компонентов подключения для Excel можно на следующей странице: Распространяемый пакет ядра СУБД Microsoft Access 2016. Последняя версия компонентов позволяет открывать файлы, созданные в более ранних версиях Excel.

В статье:

Технические характеристики → Полезное видео → Общие данные → Таблица размеров → Полезные советы → Как правильно рассчитать → Сыпучие продукты → Натяжные потолки и технологии→ Мера жидких продуктов

Экспорт данных SQL в Excel с заголовками столбцов

Сколько раз вам приходилось писать быстрый запрос в SQL Management Studio для кого-то, а затем экспортировать данные в Excel? Экспорт данных из SQL Server — это довольно длительный процесс, состоящий из нескольких этапов, но если вы сделаете это несколько раз, это неплохо.

Из сохранения результатов SQL-запроса в формате Excel или CSV я узнал, что лучше всего использовать мастер импорта и экспорта SQL, а не пытаться щелкнуть правой кнопкой мыши результаты и сохранить их.

Вы всегда можете щелкнуть правой кнопкой мыши и выбрать «Сохранить результаты как», однако полученный CSV-файл не будет содержать заголовков столбцов! Это королевская боль, если у вас есть 50 столбцов.

Экспорт данных SQL в Excel

Чтобы правильно экспортировать данные SQL, щелкните правой кнопкой мыши базу данных (не таблицу) и выберите Задачи, Экспорт данных.

Далее вам нужно выбрать источник данных. Если вы щелкнули правой кнопкой мыши на имени базы данных, то все должно появиться автоматически.

Теперь вам нужно выбрать пункт назначения. Идите вперед и выберите Microsoft Excel из выпадающего списка. После этого вам нужно будет нажать кнопку «Обзор» и выбрать местоположение для выходного файла Excel. Также не забудьте установить флажок «У первой строки есть имена столбцов».

Затем вы можете либо экспортировать всю таблицу / представление в Excel, либо написать собственный запрос. В моем случае я напишу конкретный запрос для данных, которые я хочу экспортировать.

Введите свой запрос и затем нажмите Parse, чтобы убедиться, что все правильно.

Наконец, нажмите Далее, а затем нажмите Готово. Ваши данные SQL будут выведены в файл Excel, и он будет также включать все заголовки столбцов! Наслаждайтесь!

Ошибки

В некоторых случаях пользователь может видеть в Excel ошибки, которые бывают следующих видов:

#ДЕЛ/О! – результат деления на число >;
#Н/Д – введены недопустимые данные;
#ЗНАЧ! – использование неправильного вида аргумента в функции;
#ЧИСЛО! – неверное числовое значение;
#ССЫЛКА! – удалена ячейка, на которую ссылалась формула;
#ИМЯ? – неправильное имя в формуле;
#ПУСТО! – неправильно указан адрес дапазона.

Подключение к внешним данным

Вы можете получить доступ к внешним источникам через вкладку Данные, группу Получить и преобразовать данные. Подключения к данным хранятся вместе с книгой, и вы можете просмотреть их, выбрав пункт Данные –> Запросы и подключения.

Подключение к данным может быть отключено на вашем компьютере. Для подключения данных пройдите по меню Файл –> Параметры –> Центр управления безопасностью –> Параметры центра управления безопасностью –> Внешнее содержимое. Установите переключатель на одну из опций: включить все подключения к данным (не рекомендуется) или запрос на подключение к данным.

Настройка доступа к внешним данным; чтобы увеличить изображение кликните на нем правой кнопкой мыши и выберите Открыть картинку в новой вкладке

Подробнее о подключении к внешним источникам данных см. Кен Пульс и Мигель Эскобар. Язык М для Power Query. При использовании таблиц, подключенных к данным можно переставлять и удалять столбцы, не изменяя запрос. Excel продолжает сопоставлять запрошенные данные с правильными столбцами. Однако ширина столбцов обычно автоматически устанавливается при обновлении. Чтобы запретить Excel автоматически устанавливать ширину столбцов Таблицы при обновлении, щелкните правой кнопкой мыши в любом месте Таблицы и пройдите по меню Конструктор –> Данные из внешней таблицы –> Свойства, а затем снимите флажок Задать ширину столбца.

Свойства Таблицы, подключенной к внешним данным

Подключение к базе данных

Для подключения к базе данных SQL Server выберите Данные –> Получить данные –> Из базы данных –> Из базы данных SQL Server. Появится мастер подключения к данным, предлагающий элементы управления для указания имени сервера и типа входа, который будет использоваться для открытия соединения. Обратитесь к своему администратору SQL Server или ИТ-администратору, чтобы узнать, как ввести учетные данные для входа.

Подключение к базе данных SQL Server

При импорте данных в книгу Excel их можно загрузить в модель данных, предоставив доступ к ним другим инструментам анализа, таким как Power Pivot.

Существует много различных типов доступных источников данных, и иногда шаблоны соединений по умолчанию, представленные Excel, не работают.

Импорт данных из Excel 2003 (файл xls) в Microsoft SQL Server x86

Шаг 1 – Проверяем наличие провайдера Microsoft.Jet.OLEDB.4.0 на SQL Server

Первое, с чего нам нужно начать, это проверить, зарегистрирован ли провайдер Microsoft.Jet.OLEDB.4.0 на SQL Server, так как в данном случае необходимо использовать именно этот провайдер. Это можно сделать с помощью следующей SQL инструкции

   
   EXEC sp_enum_oledb_providers;

В результирующем наборе данных должна присутствовать строка с Microsoft.Jet.OLEDB.4.0. Если такого провайдера нет, то скорей всего в системе нет установленного Excel 2003 и, соответственно, его нужно установить.

Особенностью распределённых запросов и работы со связанным серверами Excel в x86 версиях SQL Server является то, что независимо от имени какой учетной записи посылается SQL запрос к Excel, эта учетная запись должна иметь права на запись во временный каталог той учетной записи, под которой работает сама служба SQL Server.Так как поставщик OLE DB создает временный файл во время запроса во временном каталоге SQL Server, используя учетные данные пользователя, выполняющего запрос.

Таким образом, если служба SQL Server работает от имени или локальной, или сетевой службы, необходимо дать соответствующие права на временный каталог этих служб всем пользователям, которые будут посылать распределенные запросы и обращаться к связанному серверу Excel (если сервер работает от имени пользователя, который посылает SQL запросы, то такие права давать не требуется, они у него уже есть).

Это можно сделать с помощью встроенной утилиты командной строки icacls.

Например, для локальной службы команда будет выглядеть следующим образом.

   
   icacls C:\Windows\ServiceProfiles\LocalService\AppData\Local\Temp /grant UserName:(R,W)

Для сетевой службы

   
   icacls C:\Windows\ServiceProfiles\NetworkService\AppData\Local\Temp /grant UserName:(R,W)

Вместо UserName укажите имя пользователя, который посылает запрос.

Шаг 3 – Включаем распределенные запросы на SQL Server

По умолчанию возможность использования распределённых запросов, в частности функций OPENDATASOURCE и OPENROWSET, в Microsoft SQL Server запрещена, поэтому данную возможность нужно сначала включить.

Она включается с помощью системной хранимой процедуры sp_configure, которая отвечает за системные параметры сервера. Нам необходимо параметру Ad Hoc Distributed Queries присвоить значение 1, для этого выполняем следующую SQL инструкцию.

   
   sp_configure 'show advanced options', 1;
   RECONFIGURE;
   GO
   sp_configure 'Ad Hoc Distributed Queries', 1;
   RECONFIGURE;
   GO

Шаг 4 – Выполняем SQL запрос, обращение к файлу Excel

Ниже я приведу несколько вариантов обращения к файлу Excel (TestExcel.xls).

OPENROWSET

   
   SELECT * FROM OPENROWSET 
   ( 
    'Microsoft.Jet.OLEDB.4.0', 
    'Excel 8.0; Database=D:\TestExcel.xls', 
    'SELECT * FROM ' 
   );

OPENDATASOURCE

   
   SELECT * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0', 
                                'Data Source=D:\TestExcel.xls; 
                                 Extended Properties=Excel 8.0')...;

Linked Server

   
   --Создание связанного сервера 
   EXEC sp_addlinkedserver @server = 'TEST_EXCEL', 
                           @srvproduct = 'Excel', 
                           @provider = 'Microsoft.Jet.OLEDB.4.0', 
                           @datasrc = 'D:\TestExcel.xls', 
                           @provstr = 'Excel 8.0;IMEX=1;HDR=YES;';   
   
   --Настройки безопасности (авторизации) 
   EXEC dbo.sp_addlinkedsrvlogin @rmtsrvname='TEST_EXCEL', 
                                 @useself= 'False', 
                                 @locallogin=NULL, 
                                 @rmtuser=NULL, 
                                 @rmtpassword=NULL;   
   
   --Обращение к связанному серверу 
   SELECT * FROM OPENQUERY (TEST_EXCEL, 'SELECT * FROM '); 
   --или 
   SELECT * FROM TEST_EXCEL...;

Automate Your SQL Data Update In Excel With The GETPIVOTDATA Function

You’re in the right place if you want to learn how to update your great-looking dashboard automatically.

After this tutorial, never again you will have to painfully recreate pivot tables every week only to get results that you can format in a beautiful way.

Now, when new data will be added to your data set, you’ll refresh the pivot table, and your great-looking dashboard will automatically get new data from the pivot table, with a perfectly formatted report, ready to share and print.

#1 Build Your Final Dashboard Structure

On a new worksheet, build your perfectly-looking report with no value inside.

Here is my initial pivot table:

And here is how I would rather present my data to my team:

#2.1 Try A Dashboard With Breakable Formulas

What I initially thought I would do (and it would work as long as I didn’t change anything) is that I would just hit an equal sign and then come over to the pivot table, left click in cell, hit enter:

And there you go, you have the dollar value that matches here:

Then, I could take my mouse, go into the lower right hand corner of the cell that I’ve selected until it becomes a plus sign, hold my left mouse key, and drag it down. I’d have all of these values coming up:

If that’s what I wanted, and I’d never change this pivot table, then I’d be fine, and just leave it like that. But, because this pivot table is dynamic, it’s going to break these formulas.

#2.2 Build a Robust Dashboard That Will Automatically Update

Luckily, we don’t have to worry as there’s a way to get around these broken formulas, and it’s precisely what we’re going to explain here. You’ll learn how to specify the value you want to return from the pivot table using the function GETPIVOTDATA.

I have to admit I was a bit puzzled by the function syntax the first time I was trying to determine the arguments I should use. FYI, here is the official syntax:

GETPIVOTDATA(data_field, pivot_table, , …)

One solution you could use, which is the easiest one but that I do NOT recommend to implement as it ends up taking way too much time, is to type =GETPIVOTDATA and click on the cell you want to retrieve data from. You will get the correct data but it won’t let you reuse the formula for other cell, forcing you to manually enter each cell data. Here the formula Excel automatically proposed to me when I typed =GETPIVOTDATA and selected the corresponding cell in the pivot table:

=GETPIVOTDATA(«revenue»,Sheet1!$A$1,»product»,»Career Transition Coaching»,»quarter»,»Q1″)

The problem here is that, if I take my mouse, go into the lower right hand corner of the cell that I’ve selected until it becomes a plus sign, hold my left mouse key, and drag it down. I’d have all of these values coming up:

Mmm… not very interesting. Why is that? Because I didn’t lock the field and item that are used by the function.

I had to go through a bunch of elliptical tutorials and felt no one was really clear. So I’d like to make it easy for you, and I believe that a graph is definitely the most simple way to understand how to write you GETPIVOTDATA function. Here is how:

So here is the syntax detailed description for you to fully grasp it:

The function syntax by the book:

GETPIVOTDATA(data_field, pivot_table, , …)

The function used in our example, where it returns the quarterly revenue per product from a pivot table:

GETPIVOTDATA(«revenue»,Sheet1!$A$1,»product»,$A3,»quarter»,B$2)

The GETPIVOTDATA function syntax has the following arguments:

‍

Now that we wrote the function in a way that will allow us to work faster, let’s click on the cell with the GETPIVOTDATA function, go into the lower right hand corner of the cell until it becomes a plus sign, hold my left mouse key, and drag it down. I’d have all of these values coming up:

Yes! That’s what I wanted! . I do the same dragging it towards the right and I’m done:

Does your GETPIVOTDATA return the #REF! error value?

It might be for one of these reasons:

The pivot_table argument is not a range in which a PivotTable is found.
The arguments do not describe a visible field.
The arguments include a report filter in which the filtered data is not displayed.

You can add a chart if you like:

#3 Refresh Your SQL to Excel Dashboard In 1 Click

Now, let’s test the automation of our table and ensure it updates if we change the data in our SQL database. So here is the initial data of my pivot table and the associated great-looking dashboard and chart:

Now, in my SQL database, I’ll boost the “Confidence Coaching” revenue in Q4 to $10,200. Let’s see how easy it is to automatically update the data in Excel. I click on Refresh all:

And boom, everything is refreshed in one click :

You can keep your dashboard data up to date by refreshing the link to its source. Each time you refresh the connection, you see the most recent data, including anything that’s new or has been deleted.

‍

Подключение к базе данных Oracle с помощью Excel

Я пытаюсь подключиться к базе данных Oracle на нашем сервере из листа Excel, но у меня возникают проблемы с пониманием, почему.

Я в настоящее время как 32-разрядные и 64-разрядные Oracle 12c установлены в разных ORACLE_HOME с и имеют 32-разрядную установку Excel на моем 64-разрядном компьютере.

Я пытаюсь подключиться к базе данных Oracle в Excel с использованием нового запроса мастера.

Когда я щелкаю из базы данных Oracle Я получаю следующее сообщение об ошибке / сообщении.

Я могу продолжить с моим текущим поставщиком, но когда я на самом деле пытаюсь подключиться к базе данных, я получаю это:

То , что я не понимаю, почему я получаю эту ошибку , когда у меня есть и 32-разрядные и 64-разрядные версии Oracle 12c , установленная на моем компьютере. Оба включены в моем PATH переменном (32-битном первый), и я также включал конкретные ORACLE_HOME и TNS_ADMIN указать на мою 32-битной установку , так как мой Excel является 32-битным.

Я очень хочу, возможность запроса из базы данных с помощью Excel, но эта проблема путает меня к смерти.

РЕДАКТИРОВАТЬ

Я также только что добавил драйвера ODBC от моей Oracle Home’S в исходном инструмент администратора ODBC Data.

Это в настоящее время в состоянии соединиться с помощью этого инструмента.

Но когда я пытаюсь подключиться к идти через ODBC Connection мастер, я знаю , получаю это:

Как установить 32bit и 64bit Oracle Client?

Импорт данных из Excel 2007 и выше (файл xlsx) в Microsoft SQL Server x86

Шаг 1 – Проверяем наличие провайдера Microsoft.ACE.OLEDB.12.0 на SQL Server

Точно так же, как и в предыдущем примере, сначала проверяем, установлен ли у нас необходимый нам провайдер, в данном случае нам нужен Microsoft.ACE.OLEDB.12.0.

Шаг 2 – Установка провайдера Microsoft.ACE.OLEDB.12.0 (32-bit)

Если провайдера нет, то его необходимо установить.

Вот ссылка на скачивание провайдера

Выберите и скачайте файл, соответствующий архитектуре x86 (т.е. в названии без x64).

В данном случае также даем права на временный каталог локальной или сетевой службы всем пользователям, которые будут посылать SQL запросы к файлу Excel.

Используем все ту же утилиту командной строки icacls.

Для локальной службы

Вместо UserName укажите имя пользователя, который посылает запрос.

Шаг 4 – Включаем распределенные запросы на SQL Server

Включаем возможность использования OPENDATASOURCE и OPENROWSET на Microsoft SQL Server, повторюсь, что по умолчанию данная возможность отключена.

Шаг 5 – Настройка провайдера Microsoft.ACE.OLEDB.12.0

В данном случае дополнительно потребуется настроить провайдер Microsoft.ACE.OLEDB.12.0. Для этого включим следующие параметры провайдера (для отключения укажите 0 вместо 1).

Если не включать данные параметры, то, скорей всего, появится ошибка примерно следующего содержания

Сохранение в Excel программным путем

Вариант 2: использование объекта Excel.Aplication

Данная возможность доступна в среде Windows, желательно исполнение данного кода на стороне клиента, либо требуется дополнительная настройка серверной части.

Далее, простой код для записи данных в Excel (специально не усложняем, ничего лишнего. Явно к 1с относится только 2 строка, остальное это использование методов и свойств объекта Excel.Application, документация по которому доступна на сайте микрософт):

Шаг 6: Проверьте свои настройки и ВПЕРЕД!

Это значение «ха – ха» никогда не войдет в качестве значения HIRE_DATE – если только вы не храните ДАТЫ в VARCHAR2 , — и если вы делаете это, то делаете это НЕПРАВИЛЬНО. Всегда храните ДАТЫ в формате DATE !

About Marko Zivkovic

Marko aka “Zivko” is a senior software analyst from Nis, Serbia focusing on SQL Server and MySQL as well as client technologies like SSMS, Visual Studio, and VSCode. He has extensive experience with quality assurance, issue escalation/resolution, and product evangelism.

He is a prolific author of authoritative content related to SQL Server including a number of “platinum” articles (top 1% in terms of popularity and engagement). His writing covers a range of topics on MySQL and SQL Server including remote/linked servers, import/export, LocalDB, SSMS, and more.

In his part-time, Zivko likes basketball, foosball (table-soccer), and rock music.

See more about Marko at LinkedIn

View all posts by Marko Zivkovic