Импорт данных из excel в sql

Введение

Итак, как я уже сказал, очень важную роль здесь играет конфигурация SQL сервера, в частности, какая версия сервера установлена, x86 или x64.

Если говорить о последних версиях Microsoft SQL Server 2016-2019, то они только x64 и устанавливаются на 64-разрядные версии Windows.

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

Для того чтобы быстро узнать, какая версия SQL Server установлена у Вас на компьютере, можете выполнить простой SQL запрос

   
   SELECT @@VERSION;

Обращение к файлу Excel и, соответственно, импорт данных в Microsoft SQL Server происходит с помощью специальных провайдеров (поставщиков). Для работы с Excel в Microsoft SQL Server обычно используются:

Jet.OLEDB.4.0
ACE.OLEDB.12.0

Во всех примерах ниже я буду посылать простой запрос SELECT на выборку данных из Excel файла, для того чтобы проверить доступ к данным в файле Excel. Чтобы осуществить импорт данных (загрузить данные в БД), Вы можете использовать любой удобный для Вас способ, например, конструкцию SELECT INTO или INSERT INTO.

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

Azure Data Factory

If you’re familiar with Azure Data Factory and don’t want to run the Copy Wizard, create a pipeline with a Copy activity that copies from the text file to SQL Server or to Azure SQL Database.

As described previously in the section, you have to export your Excel data as text before you can use Azure Data Factory to import it. Data Factory can’t read Excel files directly.

For more info about using these Data Factory sources and sinks, see the following topics:

File system
SQL Server
Azure SQL Database

To start learning how to copy data with Azure data factory, see the following topics:

Move data by using Copy Activity
Tutorial: Create a pipeline with Copy Activity using Azure portal

What is Microsoft Excel?

Image Source

Microsoft Excel is a spreadsheet application developed by Microsoft to organize and record data in a tabular format. Launched in 1985, Microsoft Excel is a widely used Spreadsheet software in businesses worldwide allowing you to organize and manipulate data through formulas in columns and rows. MS Excel is loaded with functionalities to perform several operations such as calculations, pivot tables, graph tools, etc.

Compatible with Microsoft Windows, macOS, Android, and iOS, MS Excel is included with the MS Office Suite. It is typically used by firms to record, organize and analyze financial data. MS Excel also offers VBA programming that can assist you in automating repetitive tasks.

Key Features of Microsoft Excel

Conditional Formatting: MS Excel allows you to apply formats to a cell or range of cells, and have that formatting change depending on the value of the cell or the value of a formula.
Built-in Formulae: MS Excel provides you with basic and advanced built-in formulae for sum, average, minimum, etc. You can use formula auditing to graph or trace the relationship between cells and formulas with blue arrows. You can track precedents (cells that provide data for a particular cell) or the cells that depend on the value of a particular cell.
Data Protection: Excel spreadsheets can be password protected on your laptop or PC. MS Excel also provides a robust system to recover data.
Data Sorting & Filtering: You can easily perform the basic data sorting and filtering operation on your data in excel. MS Excel also provides Advanced filtering options for complex criteria.

Solve your data replication problems with Hevo’s reliable, no-code, automated pipelines with 150+ connectors.
Get your free trial right away!

Why You Don’t Want to Use OPENROWSET with Excel

Some of you who have stumbled upon this blog post may wonder why you would even need PowerShell to get data from Excel to SQL Server.

Doesn’t T-SQL let you do this directly? I get it. I’m a T-SQL person and the older I get the fewer new things I want to learn if I don’t have to.

And indeed, it does seems to. If we start with a simple spreadsheet like this:

We can simply load it using the following command:

So, what’s not to like? It turns out, quite a bit.

A few key issues:

You need to have the Access driver installed on your server.
You need to create a linked server—and sometimes you’ll need to make changes directly to the registry to get it to work.
Once that’s done, you’ll have to make more setting changes to SQL Server (which your admin will love—not).
When you’ve finally got it working, you may then stumble upon this little piece of Microsoft documentation:

I know all this from first hand experience.

Summary – insert data from Excel to SQL Server

In this article, we’ve looked at 2 easy ways to insert data from Excel to SQL Server.

If you know how to use SQL Server Management Studio, the copy and paste feature is a great option when you need to quickly and easily import data from Excel to SQL Server. The process is simple and doesn’t require any special knowledge or tools, and can be used in tables with up to a few tens of thousands of rows of data. It can also cater for scenarios such as tables with an auto-incrementing identity key, or if you need to connect to SQL Server on a remote machine using a Remote Desktop Connection.

If you don’t have access to SQL Server Management Studio, you can use the SQL Spreads Excel Add-In to insert data from Excel to SQL. It’s quick and easy to use for non-technical users. For more advanced users, there are some cool features such as lookup columns, pivot options, and data validation which allow you to create robust data management solutions.

Download your free trial of SQL Spreads and get in touch with us if you have any questions.

Article by

Johannes Åkesson

Worked in the Business Intelligence industry for the last 15+ years.

Founder of SQL Spreads – the data management
solution to import, update and manage
SQL Server data from within Excel.

VBA Excel. «Копирование данных с выбранного листа открытой книги Excel в определенную таблицу заданной базы данных (БД) Access» Использование макросов (код VBA)

Откройте базу данных или объект базы данных.
На вкладке Файл выберите команду Сохранить как.
Выполните одно из указанных ниже действий. …
Выберите формат, который хотите использовать для новой копии.

В приложении Microsoft Excel есть команда импорта данных из базы данных Access. Ее можно использовать вместо команды экспорта Access, однако с помощью команды импорта в Excel можно импортировать только таблицы и запросы. Дополнительные сведения см. в разделе справки Excel Подключение к внешним данных и их импорт.

Как сделать базу данных в access из таблицы excel?

Из данной статьи вы узнаете, как создать базу данных из таблицы Microsoft Excel путем импорта данных прямо в Access (программа для работы с базами данных от Microsoft) или экспорта данных в формат, который подходит для работы с большинством баз данных. Microsoft Access является частью пакета программ Microsoft Office и доступен только на компьютерах с ОС Windows.

Метод 1 С помощью Microsoft Access

Найдите файл Excel, который хотите использовать в качестве источника.

Для этого нажмите на переключатель рядом со следующими опциями:

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

По умолчанию Excel создает книгу с тремя листами «Лист 1», «Лист 2» и «Лист 3». За раз можно перенести лишь один лист. Если у вас есть данные на всех трех листах, выполните передачу данных с одного листа, а затем вернитесь на вкладку «Внешние данные» и повторите процедуру для оставшихся листов.
Листы в Excel можно удалить, добавить и переименовать, при этом все внесенные изменения появятся в базе данных Access.

Если вы хотите импортировать все поля из листа, не внося каких-либо изменений, нажмите «Далее».

Если вы хотите изменить поле, нажмите на название столбца, который хотите изменить, а затем измените его название, тип данных и включите или выключите индексацию. Когда закончите, нажмите «Далее».
Если вы не хотите импортировать поле, поставьте галочку рядом с опцией «Не импортировать (пропустить) поле», а когда закончите, нажмите «Далее».

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

Введите название листа в поле «Импорт в таблицу». Оставьте как есть, чтобы выбрать имя по умолчанию.

Поставьте галочку рядом с опцией «Сохранить шаги импорта».

Метод 2 С помощью сторонней программы для работы с базами данных

Нажмите на выпадающее меню Тип Файла (Windows) или Формат файла (Mac).

Если вы работаете с приложением, основанном на работе с компьютером, выберите формат .csv (разделители — запятые).
Если вы работаете с приложением, основанном на работе с онлайн базами данных, выберите формат .xml.

Найдите в ней команду Импортировать. Как правило, она находится в выпадающем меню «Файл».

Найдите и выберите файл, который извлекли из Excel.

Следуйте инструкциям и вставьте базу данных в программу.

В пакете Microsoft Office есть специальная программа для создания базы данных и работы с ними – Access. Тем не менее, многие пользователи предпочитают использовать для этих целей более знакомое им приложение – Excel. Нужно отметить, что у этой программы имеется весь инструментарий для создания полноценной базы данных (БД). Давайте выясним, как это сделать.

OPENROWSET и связанные серверы

Важно!

В базе данных SQL Azure невозможно импортировать данные непосредственно из Excel. Сначала необходимо экспортировать данные в текстовый файл (CSV). Примеры см. в разделе Пример.

Примечание

Поставщик ACE (прежнее название — поставщик Jet), который подключается к источникам данных Excel, предназначен для интерактивного клиентского использования. Если поставщик ACE используется на сервере SQL Server, особенно в автоматизированных процессах или процессах, выполняющихся параллельно, вы можете получить непредвиденные результаты.

Распределенные запросы

Импортируйте данные напрямую из файлов Excel в SQL Server с помощью функции Transact-SQL OPENROWSET или OPENDATASOURCE. Такая операция называется распределенный запрос.

Важно!

Перед выполнением распределенного запроса необходимо включить параметр ad hoc distributed queries в конфигурации сервера, как показано в примере ниже. Дополнительные сведения см. в статье ad hoc distributed queries Server Configuration Option (Параметр конфигурации сервера “ad hoc distributed queries”).

sp_configure ‘show advanced options’, 1;RECONFIGURE;GOsp_configure ‘Ad Hoc Distributed Queries’, 1;RECONFIGURE;GO

В приведенном ниже примере кода данные импортируются из листа Excel Sheet1 в новую таблицу базы данных с помощью OPENROWSET.

USE ImportFromExcel;GOSELECT * INTO Data_dqFROM OPENROWSET(‘Microsoft.ACE.OLEDB.12.0’, ‘Excel 12.0; Database=C:TempData.xlsx’, );GO

Ниже приведен тот же пример с OPENDATASOURCE.

USE ImportFromExcel;GOSELECT * INTO Data_dqFROM OPENDATASOURCE(‘Microsoft.ACE.OLEDB.12.0’, ‘Data Source=C:TempData.xlsx;Extended Properties=Excel 12.0’)…;GO

Чтобы добавить импортированные данные в существующую таблицу, а не создавать новую, используйте синтаксис INSERT INTO … SELECT … FROM … вместо синтаксиса SELECT … INTO … FROM … из предыдущих примеров.

Для обращения к данным Excel без импорта используйте стандартный синтаксис SELECT … FROM ….

Дополнительные сведения о распределенных запросах см. в указанных ниже разделах.

Распределенные запросы (Распределенные запросы по-прежнему поддерживаются в SQL Server 2016, но документация по этой функции не обновлена.)
OPENROWSET
OPENDATASOURCE

Связанные серверы

Кроме того, можно настроить постоянное подключение от SQL Server к файлу Excel как к связанному серверу. В примере ниже данные импортируются из листа Excel Data на существующем связанном сервере EXCELLINK в новую таблицу базы данных SQL Server с именем Data_ls.

USE ImportFromExcel;GOSELECT * INTO Data_ls FROM EXCELLINK…;GO

Вы можете создать связанный сервер в SQL Server Management Studio или запустить системную хранимую процедуру sp_addlinkedserver, как показано в примере ниже.

DECLARE @RC intDECLARE @server nvarchar(128)DECLARE @srvproduct nvarchar(128)DECLARE @provider nvarchar(128)DECLARE @datasrc nvarchar(4000)DECLARE @location nvarchar(4000)DECLARE @provstr nvarchar(4000)DECLARE @catalog nvarchar(128)– Set parameter valuesSET @server = ‘EXCELLINK’SET @srvproduct = ‘Excel’SET @provider = ‘Microsoft.ACE.OLEDB.12.0’SET @datasrc = ‘C:TempData.xlsx’SET @provstr = ‘Excel 12.0’EXEC @RC = .. @server, @srvproduct, @provider,@datasrc, @location, @provstr, @catalog

Дополнительные сведения о связанных серверах см. в указанных ниже разделах.

Создание связанных серверов
OPENQUERY

Примеры и дополнительные сведения о связанных серверах и распределенных запросах см. указанных ниже разделах.

Использование Excel со связанными серверами SQL Server и распределенными запросами
Импорт данных из Excel в SQL Server

Background

Before I founded SQL Spreads (an Excel Add-In to Import and Update SQL Server data from within Excel), I worked as a Business Intelligence consultant for many years using Microsoft’s BI-tools, such as SQL Server, SSIS, Reporting Services, Excel, etc.

I’ve found that when working on different projects, I tend to snap up a number of great-to-know things that I can re-use over and over again. One of these things that I re-use in almost every project is the ability to copy and paste data from Excel into a table in SQL Server.

It’s a really simple and convenient way to quickly import data into a table via SQL Server Management Studio. For example, populating a new dimension table, adding some test data, or inputting any other data that you need to quickly get into a table in SQL Server.

But what if you want to insert data from Excel to SQL without using Management Studio? What if there was a way to do this directly from Excel? This is where the SQL Spreads Excel Add-In that I’ve been working with over the last few years comes in. It makes your Excel to SQL Server import tasks much easier to do!

In this article, I’m therefore going to explain how to insert data from Excel to SQL Server using these 2 easy methods:

Copy and paste from Excel to SQL tables via SQL Server Management Studio
Insert directly from Excel to SQL tables using SQL Spreads

What Could Possibly Go Wrong?

After months of struggling with OPENROWSET and bulk imports, I was thrilled when I finally got the above approach working. Now I use it every day.

But this is technology, so nothing is perfect. Things can go wrong.

Let’s look at some of them.

Problem #1: Write-SQLTableData Doesn’t (Always) Look at Your Column Names

When we used the -force option, we saw that Write-SQLTableData used the names in the first row of our spreadsheet in order to create the table. So, you might guess that it actually cares about those column names. Unfortunately, you would be wrong.

Let’s take our basic spreadsheet and swap the last name and first name columns:

In our previous example, FirstName was column B and LastName was column A.

So, let’s bring this into our excel.Example1 table. (The syntax is the same as above so I won’t repeat it.)

When we check the data, we see the following:

The first three rows are from our first example. Those make sense.

But the last three records went in by column order, not by column name. So “Jacobson” is now a first name.

Basically, once it finds a table it can use, Write-SQLTableData doesn’t look at your columns.

Problem #2: The Number of Excel Columns Doesn’t Match the Number of Columns in Your Table

What if we don’t have enough columns in our spreadsheet? Following the logic above, as long as the columns in your table accept null values, you’ll be fine:

We load this into our excel.Example1 table (which has three columns), and we see that the last column is left null:

What if we have too many columns (as below)? Any guesses?

In this case, Write-SQLTableData gives us an error message:

“The given ColumnMapping does not match up with any column in the source or destination.”

Problem #3: Variable Type Checking (the Most Annoying Thing about Write-SQLTableData)

When we first created our table, we saw that Write-SQLTableData guessed at the types we wanted for each column.

In general, I make all my load table columns into varchar(max) and then deal with type issues in T-SQL. So, you’d think that variable typing wouldn’t be an issue in this stage. But you’d be wrong (again).

Here’s a small spreadsheet where the third record has a different type of ClientID:

The table we’re using is set to VARCHAR(MAX) for all columns:

When we load the file, however, we receive the following error:

“Write-SqlTableData : Input string was not in a correct format. Couldn’t store <XX> in ClientID Column. Expected type is Double.”

What? Write-SQLTableData is trying to be helpful—it wants your data to be consistent even if the SQL Table itself doesn’t really care.

So, since the column starts out as double, it should remain as double. (Note that the type is driven by Excel. The field may look like an integer to you, but that’s just formatting in Excel.)

There are a couple of ways to solve this problem.

First, we can remove the comma from the beginning of the command. So, instead of this command:

We use this one:

And it actually works:

However, this can really, really slow down processing when we have a large file—as I noted earlier.

Another solution depends on how much you know about the “bad” value.

In general, (but only in general), report data is consistent. So, it’s not so much an issue of bad data in a column, as a superfluous row or two at the end. Like this:

The first thing we can do is make sure every ClientID is actually a number before we pass it.

Remembering back to the example on selected columns, we basically take the array of objects we receive from Import-Excel and test the ClientID for each one before we move the object into an array that we’ll actually import.

The key line above is where we check that the client id is indeed the correct type.

This method works fine if we feel pretty confident about the issues we are going to have.

However, my general rule is to bring everything thing into SQL Server and deal with the issues there. So, I have another option.

PowerShell doesn’t care if you specify variable types. I’m not going to get into whether that’s a good or bad thing. For our purposes, it just is, and you can’t change that behavior.

However, when preparing our array for import, we CAN specify types for each property. In our case, we want everything to be a string:

If we look at SQL Server, we see that even “Total Records” was loaded:

Of course, we’ll have to handle that “Total Records” entry in SQL Server.

Retrieve Data from SQL Server

To insert data into SQL Server, you will first need to retrieve data from the SQL Server table you want to add to. This links the Excel spreadsheet to the SQL Server table selected: After you retrieve data, any changes you make to the data are highlighted in red.

Click the From SQL Server button on the CData ribbon. The Data Selection wizard is displayed.
In the Table or View menu, select the Orders table.
In the Maximum Rows menu, select the number of rows you want to retrieve. If you want to insert rows, you need to retrieve only one row. The Query box will then display the SQL query that corresponds to your request.
In the Sheet Name box, enter the name for the sheet that will be populated. By default the add-in will create a new sheet with the name of the table.

Импорт данных из 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...;

Import and Export Wizard

Import data directly from Excel files by using the SQL Server Import and Export Wizard. You also have the option to save the settings as a SQL Server Integration Services (SSIS) package that you can customize and reuse later.

In , connect to an instance of the .
Expand Databases.
Right-click a database.
Point to Tasks.
Choose to Import Data or Export Data:

:::image type=»content» source=»../../integration-services/import-export-data/media/start-wizard-ssms.jpg» alt-text=»Start wizard SSMS»:::

This launches the wizard:

:::image type=»content» source=»media/excel-connection.png» alt-text=»Connect to an Excel data source»:::

To learn more, review:

Start the SQL Server Import and Export Wizard
Get started with this simple example of the Import and Export Wizard

SQL запросы: возможности SQL для создания запросов в Excel и напрямую к таблицам Excel

Порой таблицы Excel постепенно разрастаются настолько, что с ними становится неудобно работать. Поиск дубликатов, группировка, сложная сортировка, объединение нескольких таблиц в одну, т.д. — превращаются в действительно трудоёмкие задачи. Теоретически эти задачи можно легко решить с помощью языка запросов SQL… если бы только можно было составлять запросы напрямую к данным Excel.

Надстройка XLTools «SQL запросы» расширит Excel возможностями языка структурированных запросов:

Создание запросов SQL в интерфейсе Excel и напрямую к Excel таблицам
Автогенерация запросов SELECT и JOIN
Доступны JOIN, ORDER BY, DISTINCT, GROUP BY, SUM и другие операторы SQLite
Создание запросов в интуитивном редакторе с подстветкой синтаксиса
Обращение к любым таблицам Excel из дерева данных

Подходит для: Microsoft Excel 2019 – 2010, desktop Office 365 (32-бит и 64-бит).

Скачать надстройку XLTools

Как превратить данные Excel в реляционную базу данных и подготовить их к работе с SQL запросами

По умолчанию Excel воспринимает данные как простые диапазоны. Но SQL применим только к реляционным базам данных. Поэтому, прежде чем создать запрос, преобразуйте диапазоны Excel в таблицу (именованный диапазон с применением стиля таблицы):

Выделите диапазон данных > На вкладке «Главная» нажмите «Форматировать как таблицу» > Примените стиль таблицы.
Выберите эту таблицу > Откройте вкладку «Конструктор» > Напечатайте имя таблицы.
Напр., «КодТовара».
Повторите эти шаги для каждого диапазона, который планируете использовать в запросах.
«КодТовара», «ЦенаРозн», «ОбъемПродаж», т.д.
Готово, теперь эти таблицы будут служить реляционной базой данных и готовы к SQL запросам.

Как создать и выполнить запрос SQL SELECT к таблицам Excel

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

Нажмите кнопку «Выполнить SQL» на вкладке XLTools > Откроется окно редактора.

В левой части окна находится дерево данных со всеми доступными таблицами Excel.
Нажатием на узлы открываются/сворачиваются поля таблицы (столбцы).

Выберите целые талицы или конкретные поля.
По мере выбора полей, в правой части редактора автоматически генерируется запрос SELECT.

Обратите внимание: редактор запросов SQL автоматически подсвечивает систаксис.

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

Нажмите кнопку «Выполнить» > Готово!

Операторы Left Join, Order By, Group By, Distinct и другие SQLite команды в Excel

XLTools использует стандарт SQLite. Пользователи, владеющие языком SQLite, могут создавать самые разнообразные запросы:

LEFT JOIN – объединить две и более таблиц по общему ключевому столбцу
ORDER BY – сортировка данных в выдаче запроса
DISTINCT – удаление дубликатов из результата запроса
GROUP BY – группировка данных в выдаче запроса
SUM, COUNT, MIN, MAX, AVG и другие операторы

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

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

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

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

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

Other tools and techniques

There are some other ways to import an Excel file to a table in SQL Server. Here are some of the other methods.

SQL Server Import Wizard – a wizard-based import tool inside SQL Server Management Studio. Ideal for one-time imports when you have an Excel document that you need to import into a table in SQL Server. The pros include flexibility and lots of settings to finetune the import. The biggest drawback is that you need to run through a dozen Wizard dialogs with lots of settings each time you need to import the data. More info about the SQL Server Import Wizard is available here.
SSIS – this is the oil tanker for moving data between different sources. You can do almost any task you like, but you will need to put in lots of time to get started, and it will take still more time to maintain and change the solution down the line. The pros include good versatility and plenty of available features; the main con is the time you will have to put in to learn the tool. More info about SSIS is available here.
The BCP utility – a command line-based tool that offers a huge number of settings – if you are a coder, this is the tool to use. More info about the BCP utility is available here.

Восстановление файла MDF в SQL Server без LDF с помощью SQL Server Managment Studio

Выполните все указанные шаги, чтобы успешно прикрепить файл .mdf в SQL Server.

Откройте SQL Server Managment Studio.
В обозревателе объектов щелкните правой кнопкой мыши базу данных и выберите «Присоединить».
Теперь открыто окно «Присоединить базу данных», нажмите кнопку «Добавить».
Просмотрите расположение файла MDF, затем выберите файл и нажмите кнопку ОК.
Теперь вы можете увидеть детали базы данных. Чтобы прикрепить файл MDF без файла LDF, вы должны выбрать файл LDF, затем нажать кнопку «Удалить», а затем нажать кнопку «ОК».

SQL Server создаст файл LDF при прикреплении файла MDF.

Теперь вам нужно проверить базу данных в папке базы данных.

Прикрепите или восстановите файл MDF в SQL Server с помощью сценария T-SQL

Чтобы прикрепить файл MDF в SQL Server с помощью T-SQL, вы выполнили следующий сценарий T-SQL —

Используя вышеуказанные решения, вы можете легко прикрепить файл MDF в SQL Server, но иногда из-за некоторых ошибок пользователь не может восстановить MDF в SQL Server. Некоторые ошибки обсуждаются ниже —

Учитывайте запросы пользователя —

Решение — Вы получаете сообщение об ошибке 5123 от SQL Server, потому что есть проблемы с разрешениями в вашем MDF или файле базы данных. Из-за этих проблем вы не можете прикрепить файл MDF в SQL Server или не можете восстановить базу данных из файла MDF в SQL Server. Чтобы решить эту ошибку, вы должны изменить разрешение в качестве владельца файла MDF, а затем прикрепить файл на сервере SQL, следуя приведенным выше решениям.

Решение — Эта ошибка возникает, когда информация заголовка файла MDF повреждена, и база данных становится недоступной, поэтому для решения этих проблем вам необходимо восстановить файл MDF. Чтобы удалить ошибку 5172, вам необходимо использовать Recovery Tool.

Скачать инструмент —

После восстановления файла MDF с помощью программного обеспечения вы можете подключить MDF в SQL Server. Это программное обеспечение позволяет пользователю восстанавливать удаленные объекты базы данных SQL, а также удаленные записи таблицы SQL. Пользователь может легко восстановить как первичные, так и вторичные файлы с помощью этого программного обеспечения. Кроме того, это программное обеспечение поддерживает Microsoft SQL Server 2019/2017/2016/2014/2012 и более раннюю версию.

Выполните указанные ниже шаги, чтобы восстановить базу данных только из файла MDF.

Установить и Пробег приложение и нажмите Добавлять файл.

2. Щелкните на Открыть и просмотреть файл MDF из вашей системы. Далее Выберите Версия SQL Server и Расширенный режим сканирования. (Пользователь также может проверить pпросмотреть удаленный объектs вариант.)

3. Предварительный просмотр объектов базы данных SQL SQL Стол, хранимая процедура, функции, взгляды, индексы и т.д. (Это программное обеспечение показывает удаленные записи таблицы SQL красным цветом.)

4. Щелкните на Кнопка экспорта и заполнить требуемые детали для восстановления базы данных из файла MDF.

Вывод

Используя SQL Server Management Studio, сценарий TSQL, вы можете восстановить базу данных из файла MDF без необходимости использования файла LDF. В случае, если вы получаете какое-либо сообщение об ошибке от SQL Server при прикреплении файла MDF, то сначала вам нужно разрешить его, а затем применить указанные шаги для прикрепления .mdf в SQL Server.