Skip to content

Commit fd29cc3

Browse files
PRMerger3PRMerger3
authored
Merge pull request #20841 from MashaMSFT/202111_fresh
refreshed articles for q2 health project
2 parents bae8465 + 5f1395a commit fd29cc3

3 files changed

Lines changed: 52 additions & 43 deletions

File tree

‎docs/integration-services/import-export-data/start-the-sql-server-import-and-export-wizard.md‎

Lines changed: 13 additions & 10 deletions
Original file line numberDiff line numberDiff line change
@@ -15,7 +15,7 @@ author: chugugrace
1515
ms.author: chugu
1616
ms.reviewer: ""
1717
ms.custom: ""
18-
ms.date: 11/18/2019
18+
ms.date: 12/18/2021
1919
---
2020

2121
# Start the SQL Server Import and Export Wizard
@@ -52,7 +52,7 @@ If you want to run the wizard, but you don't have [!INCLUDE[msCoName](../../incl
5252

5353
Run the 64-bit version of the wizard unless you know that your data source requires a 32-bit data provider.
5454

55-
![Start wizard Start](../../integration-services/import-export-data/media/start-wizard-start-64.png)
55+
:::image type="content" source="../../integration-services/import-export-data/media/start-wizard-start-64.png" alt-text="Start wizard Start":::
5656

5757
## Command prompt
5858

@@ -68,7 +68,7 @@ In a Command Prompt window, run **DTSWizard.exe** from one of the following loca
6868

6969
Run the 64-bit version of the wizard unless you know that your data source requires a 32-bit data provider.
7070

71-
![Start wizard cmd](../../integration-services/import-export-data/media/start-wizard-cmd.png)
71+
:::image type="content" source="../../integration-services/import-export-data/media/start-wizard-cmd.png" alt-text="Start wizard cmd":::
7272

7373
## SQL Server Management Studio (SSMS)
7474

@@ -88,7 +88,7 @@ Run the 64-bit version of the wizard unless you know that your data source requi
8888

8989
- **Export Data**
9090

91-
![Start wizard SSMS](../../integration-services/import-export-data/media/start-wizard-ssms.jpg)
91+
:::image type="content" source="../../integration-services/import-export-data/media/start-wizard-ssms.jpg" alt-text="Start wizard SSMS":::
9292

9393
If you don't have SQL Server installed, or you have SQL Server but don't have SQL Server Management Studio installed, see [Download SQL Server Management Studio (SSMS)](../../ssms/download-sql-server-management-studio-ssms.md).
9494

@@ -100,13 +100,13 @@ If you don't have SQL Server installed, or you have SQL Server but don't have SQ
100100

101101
- On the **Project** menu, click **SSIS Import and Export Wizard**.
102102

103-
![Start wizard Project](../../integration-services/import-export-data/media/start-wizard-project.png)
103+
:::image type="content" source="../../integration-services/import-export-data/media/start-wizard-project.png" alt-text="Start wizard Project":::
104104

105105
\- or -
106106

107107
- In Solution Explorer, right-click the **SSIS Packages** folder, and then click **SSIS Import and Export Wizard**.
108108

109-
![Start wizard Packages](../../integration-services/import-export-data/media/start-wizard-packages.png)
109+
:::image type="content" source="../../integration-services/import-export-data/media/start-wizard-packages.png" alt-text="Start wizard Packages":::
110110

111111
If you don't have Visual Studio installed, or you have Visual Studio but don't have SQL Server Data Tools installed, see [Download SQL Server Data Tools (SSDT)](../../ssdt/download-sql-server-data-tools-ssdt.md).
112112

@@ -129,14 +129,17 @@ Here are some other basic tasks.
129129

130130
- **See a quick example of how the wizard works.**
131131

132-
- **If you prefer to see screen shots.** Look at this simple example on a single page - [Get started with this simple example of the Import and Export Wizard](../../integration-services/import-export-data/get-started-with-this-simple-example-of-the-import-and-export-wizard.md).
132+
- **If you prefer to see screenshots.** Look at this simple example on a single page - [Get started with this simple example of the Import and Export Wizard](../../integration-services/import-export-data/get-started-with-this-simple-example-of-the-import-and-export-wizard.md).
133133

134134
- **If you prefer to watch a video.** Watch this four-minute video from YouTube that demonstrates the wizard and explains clearly and simply how to export data to Excel - [Using the SQL Server Import and Export Wizard to Export to Excel](https://go.microsoft.com/fwlink/?linkid=829049).
135135

136136
- **Learn more about how the wizard works.**
137+
- **Learn more about the wizard.** If you're looking for an overview of the wizard, see [Import and Export Data with the SQL Server Import and Export Wizard](../../integration-services/import-export-data/import-and-export-data-with-the-sql-server-import-and-export-wizard.md).
138+
- **Learn about the steps in the wizard.** If you're looking for info about the steps in the wizard, see [Steps in the SQL Server Import and Export Wizard](../../integration-services/import-export-data/steps-in-the-sql-server-import-and-export-wizard.md). There's also a separate page of documentation for each page of the wizard.
139+
- **Learn how to connect to data sources and destinations.** If you're looking for info about how to connect to your data, select the page you want from the list here - [Connect to data sources with the SQL Server Import and Export Wizard](../../integration-services/import-export-data/connect-to-data-sources-with-the-sql-server-import-and-export-wizard.md). There's a separate page of documentation for each of several commonly used data sources.
137140

138-
- **Learn more about the wizard.** If you're looking for an overview of the wizard, see [Import and Export Data with the SQL Server Import and Export Wizard](../../integration-services/import-export-data/import-and-export-data-with-the-sql-server-import-and-export-wizard.md).
139141

140-
- **Learn about the steps in the wizard.** If you're looking for info about the steps in the wizard, see [Steps in the SQL Server Import and Export Wizard](../../integration-services/import-export-data/steps-in-the-sql-server-import-and-export-wizard.md). There's also a separate page of documentation for each page of the wizard.
142+
## Next steps
141143

142-
- **Learn how to connect to data sources and destinations.** If you're looking for info about how to connect to your data, select the page you want from the list here - [Connect to data sources with the SQL Server Import and Export Wizard](../../integration-services/import-export-data/connect-to-data-sources-with-the-sql-server-import-and-export-wizard.md). There's a separate page of documentation for each of several commonly used data sources.
144+
- [Import and Export Data with the SQL Server Import and Export Wizard](../../integration-services/import-export-data/import-and-export-data-with-the-sql-server-import-and-export-wizard.md)
145+
- [Get started with this simple example of the Import and Export Wizard](../../integration-services/import-export-data/get-started-with-this-simple-example-of-the-import-and-export-wizard.md)

‎docs/relational-databases/import-export/import-data-from-excel-to-sql.md‎

Lines changed: 33 additions & 28 deletions
Original file line numberDiff line numberDiff line change
@@ -2,7 +2,7 @@
22
title: "Import data from Excel to SQL | Microsoft Docs"
33
description: This article describes methods to import data from Excel to SQL Server or Azure SQL Database. Some use a single step, others require an intermediate text file.
44
ms.custom: sqlfreshmay19
5-
ms.date: "09/30/2019"
5+
ms.date: "12/12/2021"
66
ms.prod: sql
77
ms.prod_service: "database-engine, sql-database"
88
ms.reviewer: ""
@@ -16,10 +16,14 @@ monikerRange: "=azuresqldb-current||>=sql-server-2016||>=sql-server-linux-2017||
1616

1717
[!INCLUDE [SQL Server Azure SQL Database](../../includes/applies-to-version/sql-asdb.md)]
1818

19-
There are several ways to import data from Excel files to SQL Server or to Azure SQL Database. Some methods let you import data in a single step directly from Excel files; other methods require you to export your Excel data as text (CSV file) before you can import it. This article summarizes the frequently used methods and provides links for more detailed information.
19+
There are several ways to import data from Excel files to SQL Server or to Azure SQL Database. Some methods let you import data in a single step directly from Excel files; other methods require you to export your Excel data as text (CSV file) before you can import it.
20+
21+
This article summarizes the frequently used methods and provides links for more detailed information. A complete description of complex tools and services like SSIS or Azure Data Factory is beyond the scope of this article. To learn more about the solution that interests you, follow the provided links.
2022

2123
## List of methods
2224

25+
There are a number of methods to import data from Excel. You may need to install [SQL Server Management Studio (SSMS)](../../ssms/download-sql-server-management-studio-ssms.md) to use some of these tools.
26+
2327
You can use the following tools to import data from Excel:
2428

2529
| Export to text first (SQL Server and SQL Database) | Directly from Excel (SQL Server on-premises only) |
@@ -33,52 +37,50 @@ You can use the following tools to import data from Excel:
3337

3438
If you want to import multiple worksheets from an Excel workbook, you typically have to run any of these tools once for each sheet.
3539

36-
A complete description of complex tools and services like SSIS or Azure Data Factory is beyond the scope of this list. To learn more about the solution that interests you, follow the provided links.
37-
3840
> [!IMPORTANT]
39-
> For detailed info about connecting to Excel files, and about limitations and known issues for loading data from or to Excel files, see [Load data from or to Excel with SQL Server Integration Services (SSIS)](../../integration-services/load-data-to-from-excel-with-ssis.md).
41+
> To learn more, see [limitations and known issues for loading data](../../integration-services/load-data-to-from-excel-with-ssis.md#issues-types) to or from Excel files.
4042
41-
If you don't have SQL Server installed, or you have SQL Server but don't have SQL Server Management Studio installed, see [Download SQL Server Management Studio (SSMS)](../../ssms/download-sql-server-management-studio-ssms.md).
4243

43-
## <a name="wiz"></a> SQL Server Import and Export Wizard
4444

45-
Import data directly from Excel files by stepping through the pages of the SQL Server Import and Export Wizard. Optionally, save the settings as a SQL Server Integration Services (SSIS) package that you can customize and reuse later.
45+
## <a name="wiz"></a> Import and Export Wizard
46+
47+
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.
4648

4749
1. In [!INCLUDE[ssManStudioFull](../../includes/ssmanstudiofull-md.md)], connect to an instance of the [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)] [!INCLUDE[ssDE](../../includes/ssde-md.md)].
4850

4951
2. Expand **Databases**.
5052
3. Right-click a database.
5153
4. Point to **Tasks**.
52-
5. Click one of the following options.
54+
5. Choose to **Import Data** or **Export Data**:
5355

54-
- **Import Data**
55-
- **Export Data**
56+
:::image type="content" source="../../integration-services/import-export-data/media/start-wizard-ssms.jpg" alt-text="Start wizard SSMS":::
5657

57-
![Start wizard SSMS](../../integration-services/import-export-data/media/start-wizard-ssms.jpg)
58+
This launches the wizard:
5859

59-
![Connect to an Excel data source](media/excel-connection.png)
60+
:::image type="content" source="media/excel-connection.png" alt-text="Connect to an Excel data source":::
6061

61-
For an example of using the wizard to import from Excel to SQL Server, see [Get started with this simple example of the Import and Export Wizard](../../integration-services/import-export-data/get-started-with-this-simple-example-of-the-import-and-export-wizard.md).
62+
To learn more, review:
6263

63-
To learn about other ways to launch the Import and Export wizard, see [Start the SQL Server Import and Export Wizard](../../integration-services/import-export-data/start-the-sql-server-import-and-export-wizard.md).
64+
- [Start the SQL Server Import and Export Wizard](../../integration-services/import-export-data/start-the-sql-server-import-and-export-wizard.md)
65+
- [Get started with this simple example of the Import and Export Wizard](../../integration-services/import-export-data/get-started-with-this-simple-example-of-the-import-and-export-wizard.md)
6466

65-
## <a name="ssis"></a> SQL Server Integration Services (SSIS)
6667

67-
If you're familiar with SSIS and don't want to run the SQL Server Import and Export Wizard, create an SSIS package that uses the Excel Source and the SQL Server Destination in the data flow.
68+
## <a name="ssis"></a> Integration Services (SSIS)
6869

69-
For more info about these SSIS components, see the following topics:
70+
If you're familiar with SQL Server Integration Services (SSIS) and don't want to run the SQL Server Import and Export Wizard, create an SSIS package that uses the Excel Source and the SQL Server Destination in the data flow.
7071

72+
To learn more, review:
7173
- [Excel Source](../../integration-services/data-flow/excel-source.md)
7274
- [SQL Server Destination](../../integration-services/data-flow/sql-server-destination.md)
7375

7476
To start learning how to build SSIS packages, see the tutorial [How to Create an ETL Package](../../integration-services/ssis-how-to-create-an-etl-package.md).
7577

76-
![Components in the data flow](media/excel-to-sql-data-flow.png)
78+
:::image type="content" source="media/excel-to-sql-data-flow.png" alt-text="Components in the data flow":::
7779

7880
## <a name="openrowset"></a> OPENROWSET and linked servers
7981

8082
> [!IMPORTANT]
81-
> In Azure SQL Database, you cannot import directly from Excel. You must first export the data to a text (CSV) file. For examples, see [Example](import-bulk-data-by-using-bulk-insert-or-openrowset-bulk-sql-server.md).
83+
> In Azure SQL Database, you cannot import directly from Excel. You must first [export the data to a text (CSV) file](import-bulk-data-by-using-bulk-insert-or-openrowset-bulk-sql-server.md).
8284
8385
> [!NOTE]
8486
> The ACE provider (formerly the Jet provider) that connects to Excel data sources is intended for interactive client-side use. If you use the ACE provider on SQL Server, especially in automated processes or processes running in parallel, you may see unexpected results.
@@ -88,7 +90,7 @@ To start learning how to build SSIS packages, see the tutorial [How to Create an
8890
Import data directly into SQL Server from Excel files by using the Transact-SQL `OPENROWSET` or `OPENDATASOURCE` function. This usage is called a *distributed query*.
8991

9092
> [!IMPORTANT]
91-
> In Azure SQL Database, you cannot import directly from Excel. You must first export the data to a test (CSV) file. For examples, see [Example](import-bulk-data-by-using-bulk-insert-or-openrowset-bulk-sql-server.md).
93+
> In Azure SQL Database, you cannot import directly from Excel. You must first [export the data to a text (CSV) file](import-bulk-data-by-using-bulk-insert-or-openrowset-bulk-sql-server.md).
9294
9395
Before you can run a distributed query, you have to enable the `ad hoc distributed queries` server configuration option, as shown in the following example. For more info, see [ad hoc distributed queries Server Configuration Option](../../database-engine/configure-windows/ad-hoc-distributed-queries-server-configuration-option.md).
9496

@@ -129,9 +131,9 @@ To query the Excel data without importing it, just use the standard `SELECT ...
129131

130132
For more info about distributed queries, see the following topics:
131133

132-
- [Distributed Queries](/previous-versions/sql/sql-server-2008-r2/ms188721(v=sql.105)) (Distributed queries are still supported in SQL Server 2016, but the documentation for this feature has not been updated.)
134+
- [Distributed Queries](/previous-versions/sql/sql-server-2008-r2/ms188721(v=sql.105)) (Distributed queries are still supported in SQL Server 2019, but the documentation for this feature has not been updated.)
133135
- [OPENROWSET](../../t-sql/functions/openrowset-transact-sql.md)
134-
- [OPENDATASOURCE](../../t-sql/functions/openquery-transact-sql.md)
136+
- [OPENDATASOURCE](../../t-sql/functions/opendatasource-transact-sql.md)
135137

136138
### Linked servers
137139

@@ -238,9 +240,9 @@ For more info about BCP, see the following topics:
238240
- [bcp Utility](../../tools/bcp-utility.md)
239241
- [Prepare Data for Bulk Export or Import](../../relational-databases/import-export/prepare-data-for-bulk-export-or-import-sql-server.md)
240242

241-
## <a name="adf-wiz"></a> Copy Wizard (Azure Data Factory)
243+
## <a name="adf-wiz"></a> Copy Wizard (ADF)
242244

243-
Import data saved as text files by stepping through the pages of the Azure Data Factory Copy Wizard.
245+
Import data saved as text files by stepping through the pages of the Azure Data Factory (ADF) Copy Wizard.
244246

245247
As described previously in the [Prerequisite](#prereq) 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.
246248

@@ -306,7 +308,7 @@ Msg 7438, Level 16, State 1, Line 3
306308
The 32-bit OLE DB provider "Microsoft.ACE.OLEDB.12.0" cannot be loaded in-process on a 64-bit SQL Server.
307309
```
308310

309-
### The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" reported an error. The provider did not give any information about the error
311+
### The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" reported an error.
310312

311313
### Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)"
312314

@@ -324,6 +326,9 @@ Msg 7303, Level 16, State 1, Line 3
324326
Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".
325327
```
326328

327-
## See Also
329+
## Next steps
328330

329-
[Import data from Excel or export data to Excel with SQL Server Integration Services (SSIS)](../../integration-services/load-data-to-from-excel-with-ssis.md)
331+
- [Get started with this simple example of the Import and Export Wizard](../../integration-services/import-export-data/get-started-with-this-simple-example-of-the-import-and-export-wizard.md)
332+
- [Import data from Excel or export data to Excel with SQL Server Integration Services (SSIS)](../../integration-services/load-data-to-from-excel-with-ssis.md)
333+
- [bcp Utility](../../tools/bcp-utility.md)
334+
- [Move data by using Copy Activity](/azure/data-factory/data-factory-data-movement-activities)

0 commit comments

Comments
 (0)