{"id":1901,"date":"2014-11-05T00:00:00","date_gmt":"2014-11-05T00:00:00","guid":{"rendered":"https:\/\/test.simple-talk.com\/uncategorized\/questions-about-using-tsql-to-import-excel-data-you-were-too-shy-to-ask\/"},"modified":"2021-09-29T16:21:33","modified_gmt":"2021-09-29T16:21:33","slug":"questions-about-using-tsql-to-import-excel-data-you-were-too-shy-to-ask","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/t-sql-programming-sql-server\/questions-about-using-tsql-to-import-excel-data-you-were-too-shy-to-ask\/","title":{"rendered":"Questions About Using TSQL to Import Excel Data You Were Too Shy to Ask"},"content":{"rendered":"<div id=\"pretty\">\n<ul>\n<li><a href=\"#first\">&#8220;How do I write an ad hoc query to retrieve data from an Excel spreadsheet?&#8221;<\/a>  <\/li>\n<li><a href=\"#second\">&#8220;How do I retrieve data from an Excel spreadsheet that does not include a header row?&#8221;<\/a>  <\/li>\n<li><a href=\"#third\">&#8220;How do I import data from an Excel workbook directly into a new table?&#8221;<\/a>  <\/li>\n<li><a href=\"#fourth\">&#8220;When I try to retrieve data from an Excel worksheet, I receive an error saying that the Ad Hoc Distributed Queries component is turned off. How do I enable that component?&#8221;<\/a>  <\/li>\n<li><a href=\"#fifth\">&#8220;I&#8217;m importing data from an Excel spreadsheet. One of its columns contains mostly numeric data with a few alphanumeric values mixed in. When I run my query, SQL Server replaces the alphanumeric values with NULL. How do I preserve the alphanumeric values?&#8221;<\/a>  <\/li>\n<li><a href=\"#sixth\">&#8220;How do I import data from an Excel worksheet directly into an existing table in my SQL Server database?&#8221;<\/a>  <\/li>\n<li><a href=\"#seventh\">&#8220;Can I import Excel data directly into a temporary table?&#8221;<\/a>  <\/li>\n<li><a href=\"#eighth\">&#8220;How do I limit the data I retrieve from an Excel spreadsheet to specific rows and columns?&#8221;<\/a>  <\/li>\n<li><a href=\"#ninth\">&#8220;How do I use a linked server to import data from an Excel worksheet?&#8221;<\/a>  <\/li>\n<li><a href=\"#tenth\">&#8220;I&#8217;m using a linked server to import data from an Excel spreadsheet. Is there a way to limit the columns and rows I return when I query the data?&#8221;<\/a>  <\/li>\n<li><a href=\"#eleventh\">&#8220;I&#8217;ve created a linked server that connects to an Excel workbook file and need to be able to view a list of spreadsheets within the workbook. Is there a way to retrieve this information?&#8221;<\/a>  <\/li>\n<li><a href=\"#twelveth\">&#8220;How do I import Excel data through a linked server if the spreadsheet contains no header columns?&#8221;<\/a>  <\/li>\n<li><a href=\"#thirteenth\">&#8220;How do I join data in a SQL Server table with data from an Excel workbook?&#8221;<\/a>  <\/li>\n<li><a href=\"#fourteenth\">&#8220;When I try to import Excel data into SQL Server, I keep running into errors related to the OLE DB provider. I&#8217;ve received errors about not being able to initialize the provider, not being able to create an instance of the provider, and not being able to install a 64-bit version of the provider. Can you help?&#8221;<\/a> <\/li>\n<\/ul>\n<h3 id=\"first\">&#8220;How do I write an ad hoc TSQL query to retrieve data from an Excel spreadsheet?&#8221;<\/h3>\n<p>The simplest way to query Excel data is to use the <b><code>OPENROWSET<\/code><\/b> function to connect directly to the Excel file. The <b><code>OPENROWSET<\/code><\/b> function is a SQL Server built-in rowset function (nondeterministic) that lets you connect to an OLE DB data source. The function serves as a one-time alternative to creating a linked server.<\/p>\n<p>The <b><code>OPENROWSET<\/code><\/b> function returns data in a tabular format, which means we can use the function in a query&#8217;s <b><code>FROM<\/code><\/b> clause as we would any table expression. For example, the following <b><code>SELECT<\/code><\/b> statement retrieves the data on the <b><code>vEmployee<\/code><\/b> spreadsheet in the <b><code>EmployeeData1.xmls<\/code><\/b> file:<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">SELECT * \nFROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',\n&#160; 'Excel 12.0 Xml;\n&#160;&#160; Database=C:\\DataFiles\\EmployeeData1.xlsx',\n&#160;&#160; [vEmployee$]);&#160;\n<\/pre>\n<p>When calling the <b><code>OPENROWSET<\/code><\/b> function to retrieve Excel data, we must pass several arguments into the function. The first is the name of the provider, in this case, <b><code>Microsoft.ACE.OLEDB.12.0<\/code><\/b>. We can choose from one of two providers:<\/p>\n<ul>\n<li><b><code>Microsoft.Jet.OLEDB.4.0<\/code>:<\/b> Use on SQL Server 32-bit editions for Excel 2003 files (or earlier).  <\/li>\n<li><b><code>Microsoft.ACE.OLEDB.12.0<\/code>:<\/b> Use on SQL Server 32-bit editions for Excel 2007 files (or later) or on SQL Server 64-bit editions for any Excel files.<\/li>\n<\/ul>\n<p>The second <b><code>OPENROWSET<\/code><\/b> argument defines the provider string and includes multiple parts, divided by semi-colons. The provider string in the example above is divided into two parts. The first one specifies the file type, in this case, <b><code>Excel<\/code><\/b> <b><code>12.0<\/code><\/b> <b><code>Xml<\/code><\/b>. Here&#8217;s the current breakdown on file types:<\/p>\n<ul>\n<li>For Excel &#8217;97-2003 (.xls) files, use <b><code>Excel<\/code><\/b> <b><code>8.0<\/code><\/b>.  <\/li>\n<li>For Excel 2007-2010 (.xlsx) files, use <b><code>Excel<\/code><\/b> <b><code>12.0<\/code><\/b> <b><code>Xml<\/code><\/b>.  <\/li>\n<li>For Excel 2007-2010 macro-enabled (.xlsm) files, use <b><code>Excel<\/code><\/b> <b><code>12.0<\/code><\/b> <b><code>Macro<\/code><\/b>.  <\/li>\n<li>For Excel 2007-2010 non-XML binary (.xlsb) files, use <b><code>Excel<\/code><\/b> <b><code>12.0<\/code><\/b>.<\/li>\n<\/ul>\n<p>The second part of the argument is the <b><code>Database<\/code><\/b> option, which provides the file&#8217;s path and file name (<b><code>C:\\DataFiles\\EmployeeData1.xlsx<\/code><\/b>). This of course will vary depending on the file&#8217;s location. Be aware, however, that when accessing files on a remote server, the permissions issue can get tricky. You might find it easier to copy the file to the local system before importing the data into SQL Server.<\/p>\n<p>The third argument we pass into the <b><code>OPENROWSET<\/code><\/b> function is the name of the spreadsheet we want to access. Notice that we need to add a dollar sign (<b><code>$<\/code><\/b>) to the end of the name and enclose the name in brackets, as in <b><code>[vEmployee$]<\/code><\/b>. (The spreadsheet received this name because the data comes from the <b><code>vEmployee<\/code><\/b> view in the <b><code>AdventureWorks2014<\/code><\/b> sample database.)<\/p>\n<p>That&#8217;s all there is to retrieving data from an Excel spreadsheet. The query above returns 290 rows of employee data. We can also use the <b><code>OPENDATASOURCE<\/code><\/b> function to achieve the same results. The <b><code>OPENDATASOURCE<\/code><\/b> function is another SQL Server rowset function that can be used in place of a linked server, as shown in the following example:<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">SELECT * \nFROM OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0',\n&#160; 'Data Source=C:\\DataFiles\\EmployeeData1.xlsx;\n&#160;&#160; Extended Properties=Excel 12.0 Xml')...[vEmployee$];\n<\/pre>\n<p>As you can see, the arguments we pass into the <b><code>OPENDATASOURCE<\/code><\/b> function are structured a bit differently from those passed into the <b><code>OPENROWSET<\/code><\/b> function, but the results are the same. Although the <b><code>OPENDATASOURCE<\/code><\/b> and <b><code>OPENROWSET<\/code><\/b> functions serve different purposes, when used to retrieve Excel data in this way, they basically work the same way.<\/p>\n<h3 id=\"second\">&#8220;How do I retrieve data from an Excel spreadsheet that does not include a header row?&#8221;<\/h3>\n<p>By default, when you retrieve data from an Excel file, SQL Server assumes that the first row in the spreadsheet is a header that contains column names. However, you can include an option in your provider string that determines whether or not the first row should be treated as a header. That option, <b><code>HDR<\/code><\/b>, can be set to <b><code>YES<\/code><\/b> or <b><code>NO<\/code><\/b>, with <b><code>YES<\/code><\/b> being the default. The following <b><code>SELECT<\/code><\/b> statement specifies the default value:<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">SELECT * \nFROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',\n&#160; 'Excel 12.0 Xml; HDR=YES;\n&#160;&#160; Database=C:\\DataFiles\\EmployeeData1.xlsx',\n&#160;&#160; [vEmployee$]);\n&#160;\n<\/pre>\n<p>The statement returns the same 290 rows that were returned in the previous examples. However, if we want to specify that no header row exists, we can change the setting to <b><code>NO<\/code><\/b>:<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">SELECT * \nFROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',\n&#160; 'Excel 12.0 Xml; HDR=NO;\n&#160;&#160; Database=C:\\DataFiles\\EmployeeData1.xlsx',\n&#160;&#160; [vEmployee$]);\n&#160;\n<\/pre>\n<p>When using the <b><code>HDR<\/code><\/b> option, you have to be careful that you don&#8217;t inadvertently assume there&#8217;s no header row when there actually is, or vice versa. In this case, we assumed incorrectly. The following table shows part of the results returned by the <b><code>SELECT<\/code><\/b> statement:<\/p>\n<table>\n<tbody>\n<tr>\n<td valign=\"top\">\n<p><b>F1<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p><b>F2<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p><b>F3<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p><b>F4<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p><b>F5<\/b><\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p>NULL<\/p>\n<\/td>\n<td valign=\"top\">\n<p>FirstName<\/p>\n<\/td>\n<td valign=\"top\">\n<p>MiddleName<\/p>\n<\/td>\n<td valign=\"top\">\n<p>LastName<\/p>\n<\/td>\n<td valign=\"top\">\n<p>JobTitle<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p>259<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Ben<\/p>\n<\/td>\n<td valign=\"top\">\n<p>T<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Miller<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Buyer<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p>278<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Garrett<\/p>\n<\/td>\n<td valign=\"top\">\n<p>R<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Vargas<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Sales Representative<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p>204<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Gabe<\/p>\n<\/td>\n<td valign=\"top\">\n<p>B<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Mares<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Production Technician &#8211; WC40<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p>78<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Reuben<\/p>\n<\/td>\n<td valign=\"top\">\n<p>H<\/p>\n<\/td>\n<td valign=\"top\">\n<p>D&#8217;sa<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Production Supervisor &#8211; WC40<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p>255<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Gordon<\/p>\n<\/td>\n<td valign=\"top\">\n<p>L<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Hee<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Buyer<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p>66<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Karan<\/p>\n<\/td>\n<td valign=\"top\">\n<p>R<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Khanna<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Production Technician &#8211; WC60<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p>270<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Fran&#195;&#167;ois<\/p>\n<\/td>\n<td valign=\"top\">\n<p>P<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Ajenstat<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Database Administrator<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p>22<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Sariya<\/p>\n<\/td>\n<td valign=\"top\">\n<p>E<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Harnpadoungsataya<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Marketing Specialist<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p>161<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Kirk<\/p>\n<\/td>\n<td valign=\"top\">\n<p>J<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Koenigsbauer<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Production Technician &#8211; WC45<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p>124<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Kim<\/p>\n<\/td>\n<td valign=\"top\">\n<p>T<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Ralls<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Stocker<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p>10<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Michael<\/p>\n<\/td>\n<td valign=\"top\">\n<p>NULL<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Raheem<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Research and Development Manager<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p>248<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Mike<\/p>\n<\/td>\n<td valign=\"top\">\n<p>K<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Seamans<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Accountant<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p>175<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Reed<\/p>\n<\/td>\n<td valign=\"top\">\n<p>T<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Koch<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Production Technician &#8211; WC30<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p>155<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Fadi<\/p>\n<\/td>\n<td valign=\"top\">\n<p>K<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Fakhouri<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Production Technician &#8211; WC20<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p>144<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Paul<\/p>\n<\/td>\n<td valign=\"top\">\n<p>R<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Singh<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Production Technician &#8211; WC20<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p>192<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Brenda<\/p>\n<\/td>\n<td valign=\"top\">\n<p>M<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Diaz<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Production Supervisor &#8211; WC40<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p>&#160;The problem, as you can see, is that the column names contained in the spreadsheet are treated like any other row of data and show up here as the first row. Notice, too, that SQL Server automatically uses the default Excel column names, <b><code>F1<\/code><\/b>, <b><code>F2<\/code><\/b>, <b><code>F3<\/code><\/b>, etc. Now let&#8217;s retrieve data from a spreadsheet that actually is header-less:<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">SELECT * \nFROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',\n&#160; 'Excel 12.0 Xml; HDR=NO;\n&#160;&#160; Database=C:\\DataFiles\\EmployeeData2.xlsx',\n&#160;&#160; [vEmployee$]);\n<\/pre>\n<p>This time around, we&#8217;re retrieving data from the <b><code>EmployeeData2.xmlx<\/code><\/b> file, which lists the employee data without the column names. The following table shows part of the results now returned by our statement:<\/p>\n<table>\n<tbody>\n<tr>\n<td valign=\"top\">\n<p><b>F1<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p><b>F2<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p><b>F3<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p><b>F4<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p><b>F5<\/b><\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p>259<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Ben<\/p>\n<\/td>\n<td valign=\"top\">\n<p>T<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Miller<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Buyer<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p>278<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Garrett<\/p>\n<\/td>\n<td valign=\"top\">\n<p>R<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Vargas<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Sales Representative<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p>204<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Gabe<\/p>\n<\/td>\n<td valign=\"top\">\n<p>B<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Mares<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Production Technician &#8211; WC40<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p>78<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Reuben<\/p>\n<\/td>\n<td valign=\"top\">\n<p>H<\/p>\n<\/td>\n<td valign=\"top\">\n<p>D&#8217;sa<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Production Supervisor &#8211; WC40<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p>255<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Gordon<\/p>\n<\/td>\n<td valign=\"top\">\n<p>L<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Hee<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Buyer<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p>66<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Karan<\/p>\n<\/td>\n<td valign=\"top\">\n<p>R<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Khanna<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Production Technician &#8211; WC60<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p>270<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Fran&#195;&#167;ois<\/p>\n<\/td>\n<td valign=\"top\">\n<p>P<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Ajenstat<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Database Administrator<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p>22<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Sariya<\/p>\n<\/td>\n<td valign=\"top\">\n<p>E<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Harnpadoungsataya<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Marketing Specialist<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p>161<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Kirk<\/p>\n<\/td>\n<td valign=\"top\">\n<p>J<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Koenigsbauer<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Production Technician &#8211; WC45<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p>124<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Kim<\/p>\n<\/td>\n<td valign=\"top\">\n<p>T<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Ralls<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Stocker<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p>10<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Michael<\/p>\n<\/td>\n<td valign=\"top\">\n<p>NULL<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Raheem<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Research and Development Manager<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p>248<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Mike<\/p>\n<\/td>\n<td valign=\"top\">\n<p>K<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Seamans<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Accountant<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p>175<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Reed<\/p>\n<\/td>\n<td valign=\"top\">\n<p>T<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Koch<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Production Technician &#8211; WC30<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p>155<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Fadi<\/p>\n<\/td>\n<td valign=\"top\">\n<p>K<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Fakhouri<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Production Technician &#8211; WC20<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p>144<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Paul<\/p>\n<\/td>\n<td valign=\"top\">\n<p>R<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Singh<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Production Technician &#8211; WC20<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p>192<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Brenda<\/p>\n<\/td>\n<td valign=\"top\">\n<p>M<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Diaz<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Production Supervisor &#8211; WC40<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p>166<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Jack<\/p>\n<\/td>\n<td valign=\"top\">\n<p>S<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Richins<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Production Supervisor &#8211; WC30<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p>&#160;Now let&#8217;s retrieve data from the same file, only this time around we&#8217;ll set the <b><code>HDR<\/code><\/b> option to <b><code>YES<\/code><\/b>, just to mix things up a bit:<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">SELECT * \nFROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',\n&#160; 'Excel 12.0 Xml; HDR=YES;\n&#160;&#160; Database=C:\\DataFiles\\EmployeeData2.xlsx',\n&#160;&#160; [vEmployee$]);\n<\/pre>\n<p>Not surprisingly, this can skewer our results even more, with the first row of data treated as column names, as shown in the following table:<\/p>\n<table>\n<tbody>\n<tr>\n<td valign=\"top\">\n<p><b>259<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p><b>Ben<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p><b>T<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p><b>Miller<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p><b>Buyer<\/b><\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p>278<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Garrett<\/p>\n<\/td>\n<td valign=\"top\">\n<p>R<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Vargas<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Sales Representative<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p>204<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Gabe<\/p>\n<\/td>\n<td valign=\"top\">\n<p>B<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Mares<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Production Technician &#8211; WC40<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p>78<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Reuben<\/p>\n<\/td>\n<td valign=\"top\">\n<p>H<\/p>\n<\/td>\n<td valign=\"top\">\n<p>D&#8217;sa<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Production Supervisor &#8211; WC40<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p>255<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Gordon<\/p>\n<\/td>\n<td valign=\"top\">\n<p>L<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Hee<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Buyer<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p>66<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Karan<\/p>\n<\/td>\n<td valign=\"top\">\n<p>R<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Khanna<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Production Technician &#8211; WC60<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p>270<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Fran&#195;&#167;ois<\/p>\n<\/td>\n<td valign=\"top\">\n<p>P<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Ajenstat<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Database Administrator<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p>22<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Sariya<\/p>\n<\/td>\n<td valign=\"top\">\n<p>E<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Harnpadoungsataya<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Marketing Specialist<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p>161<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Kirk<\/p>\n<\/td>\n<td valign=\"top\">\n<p>J<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Koenigsbauer<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Production Technician &#8211; WC45<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p>124<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Kim<\/p>\n<\/td>\n<td valign=\"top\">\n<p>T<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Ralls<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Stocker<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p>10<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Michael<\/p>\n<\/td>\n<td valign=\"top\">\n<p>NULL<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Raheem<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Research and Development Manager<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p>248<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Mike<\/p>\n<\/td>\n<td valign=\"top\">\n<p>K<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Seamans<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Accountant<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p>175<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Reed<\/p>\n<\/td>\n<td valign=\"top\">\n<p>T<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Koch<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Production Technician &#8211; WC30<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p>155<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Fadi<\/p>\n<\/td>\n<td valign=\"top\">\n<p>K<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Fakhouri<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Production Technician &#8211; WC20<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p>144<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Paul<\/p>\n<\/td>\n<td valign=\"top\">\n<p>R<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Singh<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Production Technician &#8211; WC20<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p>192<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Brenda<\/p>\n<\/td>\n<td valign=\"top\">\n<p>M<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Diaz<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Production Supervisor &#8211; WC40<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p>166<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Jack<\/p>\n<\/td>\n<td valign=\"top\">\n<p>S<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Richins<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Production Supervisor &#8211; WC30<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p>112<\/p>\n<\/td>\n<td valign=\"top\">\n<p>John<\/p>\n<\/td>\n<td valign=\"top\">\n<p>P<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Evans<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Production Technician &#8211; WC50<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p>&#160;What all this points to is that, when retrieving data from an Excel spreadsheet, it helps to know what the data looks; otherwise, you can end up with all sorts of unusual results.<\/p>\n<h3 id=\"third\">&#8220;How do I import data from an Excel workbook directly into a new table?&#8221;<\/h3>\n<p>Once you&#8217;ve gotten down how to use the <b><code>OPENROWSET<\/code><\/b> function to retrieve data from an Excel spreadsheet, it&#8217;s a relatively easy to slip that data into a new table. (Refer to the previous two questions if you have any questions about using the function.) The simplest way to create the table on the fly is to use a <b><code>SELECT...INTO<\/code><\/b> statement to create the table when you import the data, as shown in the following example:<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">SELECT * INTO EmployeeInfo1\nFROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',\n&#160; 'Excel 12.0 Xml; HDR=YES;\n&#160;&#160; Database=C:\\DataFiles\\EmployeeData1.xlsx',\n&#160;&#160; [vEmployee$]);\n<\/pre>\n<p>All we&#8217;ve done here is specify that the database engine should create the <b><code>EmployeeInfo1<\/code><\/b> table when we run the query. The data from the Excel spreadsheet will then be inserted into the new table.<\/p>\n<h3 id=\"fourth\">&#8220;When I try to retrieve data from an Excel worksheet, I receive an error saying that the Ad Hoc Distributed Queries component is turned off. How do I enable that component?&#8221;<\/h3>\n<p>When you first try to retrieve data from an Excel spreadsheet, there&#8217;s a good chance you&#8217;ll run into this error; however, the issue is easily addressed. Let&#8217;s look at the problem in action. Suppose we try to run the following <b><code>SELECT<\/code><\/b> statement:<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">SELECT * \nFROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',\n&#160; 'Excel 12.0 Xml;\n&#160;&#160; Database=C:\\DataFiles\\EmployeeData1.xlsx',\n&#160;&#160; [vEmployee$]);\n<\/pre>\n<p>Nothing new here. As we saw in the previous example, we&#8217;re simply trying to retrieve the data from the <b><code>EmployeeData1.xlsx<\/code><\/b> spreadsheet. However, instead of returning the actual data, we get the following error:<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">Msg 15281, Level 16, State 1, Line 181\nSQL Server blocked access to STATEMENT 'OpenRowset\/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure. For more information about enabling 'Ad Hoc Distributed Queries', search for 'Ad Hoc Distributed Queries' in SQL Server Books Online.\n<\/pre>\n<p>Unlike a lot of SQL Server error messages, this one is actually useful. All we need to do is use the <b><code>sp_configure<\/code><\/b> system stored procedure to enable the specified option. If you don&#8217;t have the necessary permissions, then get an administrator to do it. The only trick is to first enable the <b><code>Show<\/code><\/b> <b><code>Advanced<\/code><\/b> <b><code>Options<\/code><\/b> setting and then set the <b><code>Ad<\/code><\/b> <b><code>Hoc<\/code><\/b> <b><code>Distributed<\/code><\/b> <b><code>Queries<\/code><\/b> option, as shown in the following T-SQL script:<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">EXEC sp_configure 'Show Advanced Options', 1;\nRECONFIGURE;\nGO\n&#160;\nEXEC sp_configure 'Ad Hoc Distributed Queries', 1;\nRECONFIGURE;\nGO\n<\/pre>\n<p>In each case, we&#8217;re setting the option to <b><code>1<\/code><\/b> to enable that option. Notice too that we must run a <b><code>RECONFIGURE<\/code><\/b> statement each time we set an option. When the statements run, you should see results similar to the following:<\/p>\n<pre>Configuration option 'show advanced options' changed from 0 to 1. Run the RECONFIGURE statement to install.\nConfiguration option 'Ad Hoc Distributed Queries' changed from 0 to 1. Run the RECONFIGURE statement to install.\n<\/pre>\n<p>Once the options have been enabled, we should be able to run our <b><code>SELECT<\/code><\/b> statement and retrieve the Excel data.<\/p>\n<h3 id=\"fifth\">&#8220;I&#8217;m importing data from an Excel spreadsheet. One of its columns contains mostly numeric data with a few alphanumeric values mixed in. When I run my query, SQL Server replaces the alphanumeric values with NULL. How do I preserve the alphanumeric values?&#8221;<\/h3>\n<p>When you retrieve data from an Excel spreadsheet, the OLE DB provider guesses at a column&#8217;s data type by sampling rows and going with the majority. If that column contains mostly numeric data, chances are, you&#8217;ll end up with a numeric column, in which case, your alphanumeric stragglers are returned as <b><code>NULL<\/code><\/b> values<\/p>\n<p>For example, suppose we start with the following <b><code>SELECT...INTO<\/code><\/b> statement:<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">SELECT * INTO EmployeeInfo2\nFROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',\n&#160; 'Excel 12.0 Xml; HDR=YES;\n&#160;&#160; Database=C:\\DataFiles\\EmployeeData1.xlsx',\n&#160;&#160; [vEmployee$]);\n<\/pre>\n<p>All we&#8217;re doing is importing the Excel data directly into the new table, <b><code>EmployeeInfo2<\/code><\/b>. Now let&#8217;s go into Table Designer in SQL Server Management Studio (SSMS) to look at the data types assigned to the columns. To get there, right click the table in <b><code>Object<\/code><\/b> <b><code>Explorer<\/code><\/b> and then click <b><code>Design<\/code><\/b>. What pops up should look similar to the screenshot shown in Figure 1.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/2090-clip_image002.gif\" width=\"384\" height=\"342\" alt=\"2090-clip_image002.gif\" \/><\/p>\n<p class=\"caption\">Figure 1: Data types assigned to the columns in the <b><code>EmployeeInfo2<\/code><\/b><b> table<\/b><\/p>\n<p>First off, notice that all the columns have been configured with either the <b><code>float<\/code><\/b> or <b><code>nvarchar(255)<\/code><\/b> data type. It does not matter that the numeric values contain no decimals or that the character data is all much shorter than 255 characters. This is what the provider does.<\/p>\n<p>Of particular note is the <b><code>PostalCode<\/code><\/b> column. In the Excel spreadsheet, several employees come from Canada and England, both of which use postal codes based on alphanumeric values, such as <b><code>T2P<\/code><\/b> <b><code>2G8<\/code><\/b>. The other 287 employees live in places with numeric postal codes. Because of how the OLE DB provider works, when we import the data into the <b><code>EmployeeInfo2<\/code><\/b> table, the alphanumeric postal codes are replaced with <b><code>NULL<\/code><\/b> values. <\/p>\n<p>Of course, losing data is never a good sign, but we can get around this problem by adding the <b><code>IMEX<\/code><\/b> option to our provider string and setting its value to <b><code>1<\/code><\/b>, as shown in the following <b><code>SELECT...INTO<\/code><\/b> statement:<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">SELECT * INTO EmployeeInfo3\nFROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',\n&#160; 'Excel 12.0 Xml; HDR=YES; IMEX=1;\n&#160;&#160; Database=C:\\DataFiles\\EmployeeData1.xlsx',\n&#160;&#160; [vEmployee$]);\n<\/pre>\n<p>When we include the <b><code>IMEX<\/code><\/b> option and set it to <b><code>1<\/code><\/b>, the OLE DB provider will import our problem column as intermixed data, thus avoiding the <b><code>NULL<\/code><\/b> replacements. We can now return to Table Designer (shown in Figure 2), where we&#8217;ll see that the <b><code>PostalCode<\/code><\/b> column is configured with the <b><code>nvarchar<\/code><\/b> data type.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/2090-clip_image004.gif\" width=\"377\" height=\"336\" alt=\"2090-clip_image004.gif\" \/><\/p>\n<p class=\"caption\">Figure 2: Data types assigned to the columns in the <b><code>EmployeeInfo3<\/code><\/b> table<\/p>\n<p>If we were to retrieve data from the <b><code>EmployeeInfo3<\/code><\/b> table, we&#8217;d find that the alphanumeric postal codes have been preserved.<\/p>\n<h3 id=\"sixth\">&#8220;How do I import data from an Excel worksheet directly into an existing table in my SQL Server database?&#8221;<\/h3>\n<p>To import Excel data directly into an existing table, you can use an <b><code>INSERT...SELECT<\/code><\/b> statement along with the <b><code>OPENROWSET<\/code><\/b> function, like we&#8217;ve seen it used in previous examples. To demonstrate how this works, let&#8217;s start by creating the following table:<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">IF OBJECT_ID('EmployeeInfo4', 'U') IS NOT NULL\nDROP TABLE EmployeeInfo4;\nGO\n&#160;\nCREATE TABLE EmployeeInfo4\n(\n&#160; BusinessEntityID INT NOT NULL,\n&#160; FirstName NVARCHAR(50) NOT NULL,\n&#160; MiddleName NVARCHAR(50) NULL,\n&#160; LastName NVARCHAR(50) NOT NULL,\n&#160; JobTitle NVARCHAR(50) NOT NULL,\n&#160; PhoneNumber NVARCHAR(25) NULL,\n&#160; PhoneNumberType NVARCHAR(50) NULL,\n&#160; EmailAddress NVARCHAR(50) NULL,\n&#160; EmailPromotion INT NOT NULL,\n&#160; AddressLine1 NVARCHAR(60) NOT NULL,\n&#160; City NVARCHAR(30) NOT NULL,\n&#160; StateProvinceName NVARCHAR(50) NOT NULL,\n&#160; PostalCode NVARCHAR(15) NOT NULL,\n&#160; CountryRegionName NVARCHAR(50) NOT NULL\n)\n<\/pre>\n<p>Once we have our table, we can run the following <b><code>INSERT...SELECT<\/code><\/b> statement to import the data:<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">INSERT INTO EmployeeInfo4\nSELECT *\nFROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',\n&#160; 'Excel 12.0 Xml; HDR=YES; IMEX=1;\n&#160;&#160; Database=C:\\DataFiles\\EmployeeData1.xlsx',\n&#160;&#160; [vEmployee$]);\n<\/pre>\n<p>Assuming that we have a spreadsheet whose data is relatively compatible with the target columns, the data should be implicitly converted, where necessary, and inserted into the table with no problem. However, if the data is not compatible, we&#8217;ll run into problems. For example, suppose we had created the table with a <b><code>JobTitle<\/code><\/b> column configured as <b><code>nvarchar(5)<\/code><\/b>. If we then ran the <b><code>INSERT...SELECT<\/code><\/b> statement, we&#8217;d receive the following error:<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">Msg 8152, Level 16, State 13, Line 274\nString or binary data would be truncated.\nThe statement has been terminated.\n<\/pre>\n<p>Again, you must be mindful when importing Excel data. In some cases, you might have to work a bit to get the data into the format you need.<\/p>\n<h3 id=\"seventh\">&#8220;Can I import Excel data directly into a temporary table?&#8221;<\/h3>\n<p>Yes, you can import the data into a temporary table just like you can a regular table. In fact, you might find that using a temporary table is a good way to handle a spreadsheet that cannot be easily imported into your existing table. In such cases, you can stage the data in a temporary table in order to transform the data before inserting it into its final destination.<\/p>\n<p>Let&#8217;s look at a simple example of how this might work. Suppose you have a spreadsheet that looks something like the one shown in Figure 3.<\/p>\n<p class=\"illustration\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/2090-clip_image006.gif\" width=\"624\" height=\"343\" alt=\"2090-clip_image006.gif\" \/><\/p>\n<p class=\"caption\">Figure 3: A partial view of the <code>vEmployee<\/code> spreadsheet of the <code>EmployeeData3.xlsx<\/code> file<\/p>\n<p>Notice that we have extra rows at the top, a spreadsheet title (<b><code>AdventureWorks<\/code><\/b> <b><code>Employees<\/code><\/b>), and three cells merged into one (<b><code>FULL<\/code><\/b> <b><code>NAME<\/code><\/b>). The actual headings don&#8217;t come until line 5 and only after that do we get to the actual data.<\/p>\n<p>We can start by pulling the data into a temporary table, as shown in the following <b><code>SELECT...INTO<\/code><\/b> statement:<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">SELECT * INTO #EmployeeInfo5\nFROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',\n&#160; 'Excel 12.0 Xml; HDR=YES; IMEX=1;\n&#160;&#160; Database=C:\\DataFiles\\EmployeeData3.xlsx',\n&#160;&#160; [vEmployee$]);\n<\/pre>\n<p>At this point, if we were to retrieve the data in <b><code>#EmployeeInfo5<\/code><\/b>, we would see an unusual mix of headers, as shown in the following table:<\/p>\n<table>\n<tbody>\n<tr>\n<td valign=\"top\">\n<p><b>AdventureWorks Employees<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p><b>F2<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p><b>F3<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p><b>F4<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p><b>F5<\/b><\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p>NULL<\/p>\n<\/td>\n<td valign=\"top\">\n<p>NULL<\/p>\n<\/td>\n<td valign=\"top\">\n<p>NULL<\/p>\n<\/td>\n<td valign=\"top\">\n<p>NULL<\/p>\n<\/td>\n<td valign=\"top\">\n<p>NULL<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p>NULL<\/p>\n<\/td>\n<td valign=\"top\">\n<p>FULL NAME<\/p>\n<\/td>\n<td valign=\"top\">\n<p>NULL<\/p>\n<\/td>\n<td valign=\"top\">\n<p>NULL<\/p>\n<\/td>\n<td valign=\"top\">\n<p>NULL<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p>BusinessEntityID<\/p>\n<\/td>\n<td valign=\"top\">\n<p>FirstName<\/p>\n<\/td>\n<td valign=\"top\">\n<p>MiddleName<\/p>\n<\/td>\n<td valign=\"top\">\n<p>LastName<\/p>\n<\/td>\n<td valign=\"top\">\n<p>JobTitle<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p>259<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Ben<\/p>\n<\/td>\n<td valign=\"top\">\n<p>T<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Miller<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Buyer<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p>278<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Garrett<\/p>\n<\/td>\n<td valign=\"top\">\n<p>R<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Vargas<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Sales Representative<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p>204<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Gabe<\/p>\n<\/td>\n<td valign=\"top\">\n<p>B<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Mares<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Production Technician &#8211; WC40<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p>78<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Reuben<\/p>\n<\/td>\n<td valign=\"top\">\n<p>H<\/p>\n<\/td>\n<td valign=\"top\">\n<p>D&#8217;sa<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Production Supervisor &#8211; WC40<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p>255<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Gordon<\/p>\n<\/td>\n<td valign=\"top\">\n<p>L<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Hee<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Buyer<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p>66<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Karan<\/p>\n<\/td>\n<td valign=\"top\">\n<p>R<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Khanna<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Production Technician &#8211; WC60<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p>270<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Fran&#195;&#167;ois<\/p>\n<\/td>\n<td valign=\"top\">\n<p>P<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Ajenstat<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Database Administrator<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p>&#160;Although the table shows only part of the results, it&#8217;s enough to demonstrate that the data is a bit out of whack. The column names are a mix of the spreadsheet title and default column names, the first two data rows are mostly <b><code>NULL<\/code><\/b> values (except for <b><code>FULL<\/code><\/b> <b><code>NAME<\/code><\/b>), and the third row contains columns names, rather than actual data.<\/p>\n<p>However, once we have the data into a temporary table, we can then convert and filter the data as necessary, as shown in the following <b><code>SELECT...INTO<\/code><\/b> statement:<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">SELECT \n&#160; CAST([AdventureWorks Employees] AS INT) AS EmployeeID,\n&#160; CAST((F2 + ' ' + F4) AS NVARCHAR(100)) AS FullName,\n&#160; CAST(F5 AS NVARCHAR(50)) AS JobTitle\nINTO EmployeeInfo5\nFROM #EmployeeInfo5\nWHERE ISNUMERIC([AdventureWorks Employees]) = 1;\n<\/pre>\n<p>Notice that we&#8217;ve had to work with the data on its own terms, using a mix of columns names, functions, and expressions to transform the data before inserting it into the <b><code>EmployeeInfo5<\/code><\/b> table. If we were then to retrieve the data from the table, we&#8217;d find results more like what we&#8217;re looking for, as shown in the following table:<\/p>\n<table>\n<tbody>\n<tr>\n<td valign=\"top\">\n<p><b>EmployeeID<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p><b>FullName<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p><b>JobTitle<\/b><\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p>259<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Ben Miller<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Buyer<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p>278<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Garrett Vargas<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Sales Representative<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p>204<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Gabe Mares<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Production Technician &#8211; WC40<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p>78<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Reuben D&#8217;sa<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Production Supervisor &#8211; WC40<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p>255<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Gordon Hee<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Buyer<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p>66<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Karan Khanna<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Production Technician &#8211; WC60<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p>270<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Fran&#195;&#167;ois Ajenstat<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Database Administrator<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p>22<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Sariya Harnpadoungsataya<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Marketing Specialist<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p>161<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Kirk Koenigsbauer<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Production Technician &#8211; WC45<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p>124<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Kim Ralls<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Stocker<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p>&#160;Although the table shows only some of the 290 rows now stored in <b><code>EmployeeInfo5<\/code><\/b>, it&#8217;s enough to demonstrate the quality of the data and how you can use a temporary table to get there.<\/p>\n<h3 id=\"eighth\">&#8220;How do I limit the data I retrieve from an Excel spreadsheet to specific rows and columns?&#8221;<\/h3>\n<p>In the previous question, we looked at how to use a temporary table to transform our data. This approach provides a way to limit the rows and columns we ultimately import into our tables. However, we can achieve the same results directly through the <b><code>OPENROWSET<\/code><\/b> function. <\/p>\n<p>Most of examples we&#8217;ve looked at so far have used the <b><code>OPENROWSET<\/code><\/b> function to retrieve all data from a specific spreadsheet. We specified that spreadsheet as the function&#8217;s last argument. However, we can instead specify a <b><code>SELECT<\/code><\/b> statement as the third argument:<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">SELECT *\nFROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',\n&#160; 'Excel 12.0 Xml; HDR=YES; IMEX=1;\n&#160;&#160; Database=C:\\DataFiles\\EmployeeData1.xlsx',\n&#160; 'SELECT * FROM [vEmployee$]');\n<\/pre>\n<p>In this case, we&#8217;re still returning all the data from the <b><code>vEmployee<\/code><\/b> spreadsheet, only this time around we&#8217;re using a more formal approach, which essentially means we&#8217;re making more work for ourselves. The results are the same either way. However, the advantage of being able to do it this way is that we can create a more complex <b><code>SELECT<\/code><\/b> statement in order to filter out columns and rows, as well as do some other transforming along with way:<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">SELECT *\nFROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',\n&#160; 'Excel 12.0 Xml; HDR=YES; IMEX=1;\n&#160;&#160; Database=C:\\DataFiles\\EmployeeData1.xlsx',\n&#160; 'SELECT\n&#160;&#160;&#160;&#160; BusinessEntityID AS EmployeeID, \n&#160;&#160;&#160;&#160; FirstName + '' '' + LastName AS FullName, \n&#160;&#160;&#160;&#160; JobTitle \n&#160;&#160; FROM [vEmployee$]\n&#160;&#160; WHERE CountryRegionName = ''Canada''\n&#160;&#160; ORDER BY BusinessEntityID');\n<\/pre>\n<p>Notice that our <b><code>SELECT<\/code><\/b> statement is still passed in as the third argument to <b><code>OPENROWSET<\/code><\/b>. Also, because the argument must be enclosed in single quotes, we escape the single quotes within the text by adding another single quote. Now our outer <b><code>SELECT<\/code><\/b> statement returns the following results:<\/p>\n<table class=\"MsoTableGrid\">\n<tbody>\n<tr>\n<td valign=\"top\">\n<p><b>EmployeeID<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p><b>FullName<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p><b>JobTitle<\/b><\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p>278<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Garrett Vargas<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Sales Representative<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p>282<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Jos&#233; Saraiva<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Sales Representative<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p>&#160;We can achieve the same results by putting all the logic in the outer <b><code>SELECT<\/code><\/b> statement and leaving the provider string alone:<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">SELECT \n&#160; BusinessEntityID AS EmployeeID,\n&#160; FirstName + ' ' + LastName AS FullName, \n&#160; JobTitle \nFROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',\n&#160; 'Excel 12.0 Xml; HDR=YES; IMEX=1;\n&#160;&#160; Database=C:\\DataFiles\\EmployeeData1.xlsx',\n&#160; &#160;[vEmployee$]\n)\nWHERE CountryRegionName = 'Canada'\nORDER BY BusinessEntityID;\n<\/pre>\n<p>If you&#8217;re working with large quantities of data, you might see performance gains using one approach over the other. However, for the amount of data that&#8217;s usually in an Excel spreadsheet, this shouldn&#8217;t be much of an issue.<\/p>\n<h3 id=\"ninth\">&#8220;How do I use a linked server to import data from an Excel worksheet?&#8221;<\/h3>\n<p>There might be times when you want to use a linked server to persist your connection to the Excel file. That way, you need to supply the connection information only once, making the queries themselves much simpler.<\/p>\n<p>The first step is to use the <b><code>sp_addlinkedserver<\/code><\/b> system stored procedure to add the linked server to your SQL Server instance, as shown in the following example:<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">EXEC sp_addlinkedserver\n&#160; @server = 'ExcelLinkSrv1',\n&#160; @srvproduct = 'Excel', \n&#160; @provider = 'Microsoft.ACE.OLEDB.12.0',\n&#160; @datasrc = 'C:\\DataFiles\\EmployeeData1.xlsx',\n&#160; @provstr = 'Excel 12.0 Xml; HDR=YES; IMEX=1';\nGO\n<\/pre>\n<p>Most of the information here should look familiar to you if you&#8217;ve been using <b><code>OPENROWSET<\/code><\/b> to query Excel data. First, we need to name the linked server, in this case, <b><code>ExcelLinkSrv1<\/code><\/b>, and then specify the product (<b><code>Excel<\/code><\/b>), the provider, the path to the Excel file, and the provider string options.<\/p>\n<p>When it comes time to retrieve the Excel data, we need only specify the name of the linked server and the spreadsheet, as shown in the following example:<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">SELECT * FROM ExcelLinkSrv1...[vEmployee$];\n<\/pre>\n<p>The statement returns all data from the spreadsheet, as we saw with <b><code>OPENROWSET<\/code><\/b>. We can also use a linked server to import Excel data directly into a table, whether existing or new, just like we would normally do when inserting data.<\/p>\n<h3 id=\"tenth\">&#8220;I&#8217;m using a linked server to import data from an Excel spreadsheet. Is there a way to limit the columns and rows I return when I query the data?&#8221;<\/h3>\n<p>When we discussed using the <b><code>OPENROWSET<\/code><\/b> function to retrieve Excel data, we demonstrated how to qualify our queries to filter out rows and columns. We can do the same thing when using a linked server. One way to do it is to use the <b><code>OPENQUERY<\/code><\/b> function in conjunction with the linked server. <b><code>OPENQUERY<\/code><\/b> is yet another SQL Server rowset function that lets us specify a pass-through query on the linked server, which in our case, is the Excel spreadsheet. <\/p>\n<p>Let&#8217;s demonstrate how this works. First, we create our linked server:<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">EXEC sp_addlinkedserver\n&#160; @server = 'ExcelLinkSrv2',\n&#160; @srvproduct = 'Excel', \n&#160; @provider = 'Microsoft.ACE.OLEDB.12.0',\n&#160; @datasrc = 'C:\\DataFiles\\EmployeeData1.xlsx',\n&#160; @provstr = 'Excel 12.0 Xml; HDR=YES; IMEX=1';\nGO\n<\/pre>\n<p>Once we&#8217;ve set up the linked server, we&#8217;re good to go. In our <b><code>SELECT<\/code><\/b> statement, we include the <b><code>OPENQUERY<\/code><\/b> function in the <b><code>FROM<\/code><\/b> clause, just like <b><code>OPENROWSET<\/code><\/b>. When we call <b><code>OPENQUERY<\/code><\/b>, we pass in two arguments, the name of the linked server and our <b><code>SELECT<\/code><\/b> statement, as shown in the following example:<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">SELECT * FROM OPENQUERY(ExcelLinkSrv2, \n&#160; 'SELECT\n&#160;&#160;&#160;&#160; BusinessEntityID AS EmployeeID, \n&#160;&#160;&#160;&#160; FirstName + '' '' + LastName AS FullName, \n&#160;&#160;&#160;&#160; JobTitle \n&#160;&#160; FROM [vEmployee$]\n&#160;&#160; WHERE CountryRegionName = ''Canada''\n&#160;&#160; ORDER BY BusinessEntityID');\n<\/pre>\n<p>As expected, our first argument is <b><code>ExcelLinkSrv2<\/code><\/b> and our second argument is a <b><code>SELECT<\/code><\/b> statement that limits the number of columns and rows and concatenates several values. The following table shows our results:<\/p>\n<table>\n<tbody>\n<tr>\n<td valign=\"top\">\n<p><b>EmployeeID<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p><b>FullName<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p><b>JobTitle<\/b><\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p>278<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Garrett Vargas<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Sales Representative<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p>282<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Jos&#233; Saraiva<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Sales Representative<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p>&#160;We can return the same results by skipping the <b><code>OPENQUERY<\/code><\/b> function and including our logic in the outer <b><code>SELECT<\/code><\/b> statement:<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">SELECT\n&#160; BusinessEntityID AS EmployeeID, \n&#160; FirstName + ' ' + LastName AS FullName, \n&#160; JobTitle \nFROM ExcelLinkSrv2...[vEmployee$]\nWHERE CountryRegionName = 'Canada'\nORDER BY BusinessEntityID;\n<\/pre>\n<p>As with <b><code>OPENROWSET<\/code><\/b>, you might want to take performance into consideration when deciding which strategy to use. For small files, however, it shouldn&#8217;t be much of an issue.<\/p>\n<h3 id=\"eleventh\">&#8220;I&#8217;ve created a linked server that connects to an Excel workbook file and need to be able to view a list of spreadsheets within the workbook. Is there a way to retrieve this information?&#8221;<\/h3>\n<p>Yes, SQL Server provides at least one method for retrieving the spreadsheet names-the <b><code>sp_tables_ex<\/code><\/b> system stored procedure. To demonstrate how this works, let&#8217;s start with the following linked server: <\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">EXEC sp_addlinkedserver\n&#160; @server = 'ExcelLinkSrv3',\n&#160; @srvproduct = 'Excel', \n&#160; @provider = 'Microsoft.ACE.OLEDB.12.0',\n&#160; @datasrc = 'C:\\DataFiles\\EmployeeData1.xlsx',\n&#160; @provstr = 'Excel 12.0 Xml; HDR=YES; IMEX=1';\nGO\n<\/pre>\n<p>We can now use the <b><code>sp_tables_ex<\/code><\/b> system stored procedure to retrieve information about the target data source defined in the <b><code>ExcelLinkSrv3<\/code><\/b> linked server, which in this case is the <b><code>EmployeeData1.xlsx<\/code><\/b> file. To retrieve the data, we simply run the stored procedure and specify the linked server:<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">EXEC sp_tables_ex 'ExcelLinkSrv3'\n<\/pre>\n<p>In this case, the stored procedure returns the results shown in the following table:<\/p>\n<table class=\"MsoTableGrid\">\n<tbody>\n<tr>\n<td valign=\"top\">\n<p><b>TABLE_CAT<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p><b>TABLE_SCHEM<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p><b>TABLE_NAME<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p><b>TABLE_TYPE<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p><b>REMARKS<\/b><\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p>NULL<\/p>\n<\/td>\n<td valign=\"top\">\n<p>NULL<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Emp2$<\/p>\n<\/td>\n<td valign=\"top\">\n<p>TABLE<\/p>\n<\/td>\n<td valign=\"top\">\n<p>NULL<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p>NULL<\/p>\n<\/td>\n<td valign=\"top\">\n<p>NULL<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Emp3$<\/p>\n<\/td>\n<td valign=\"top\">\n<p>TABLE<\/p>\n<\/td>\n<td valign=\"top\">\n<p>NULL<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p>NULL<\/p>\n<\/td>\n<td valign=\"top\">\n<p>NULL<\/p>\n<\/td>\n<td valign=\"top\">\n<p>vEmployee$<\/p>\n<\/td>\n<td valign=\"top\">\n<p>TABLE<\/p>\n<\/td>\n<td valign=\"top\">\n<p>NULL<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p>Because we&#8217;re dealing with an Excel file, the only column we&#8217;re interested in is <b><code>TABLE_NAME<\/code><\/b>, which lists the names of the spreadsheets in that workbook (three, in this case). If we want to get at the spreadsheet names only, we have to come up with a workaround, such as creating a table variable to hold the data and then extracting the column from the variable, as shown in the following example:<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">DECLARE @worksheets TABLE\n(\n&#160; TableCat nvarchar(50),\n&#160; TableSchem nvarchar(50),\n&#160; WorkSheet nvarchar(50),\n&#160; TableType nvarchar(32),\n&#160; Remarks nvarchar(254)\n);\n&#160;\nINSERT INTO @worksheets\nEXEC sp_tables_ex 'ExcelLinkSrv3';\n&#160;\nSELECT WorkSheet FROM @worksheets;\n<\/pre>\n<p>Pretty straightforward. We define the variable, insert data into the variable, and retrieve the column that contains the spreadsheet names. The <b><code>SELECT<\/code><\/b> statement returns the following results:<\/p>\n<table class=\"MsoTableGrid\">\n<tbody>\n<tr>\n<td valign=\"top\">\n<p><b>WorkSheet<\/b><\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p>Emp2$<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p>Emp3$<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p>vEmployee$<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p>&#160;Another approach is to create a <b><code>SELECT<\/code><\/b> statement that uses the <b><code>OPENQUERY<\/code><\/b> function to run the stored procedure:<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">SELECT TABLE_NAME AS WorkSheet\nFROM OPENQUERY([srvXYZ\\sqlsrv2014], \n&#160; 'EXEC sp_tables_ex ''ExcelLinkSrv3'''); \n<\/pre>\n<p>This approach gets around the limitation in SQL Server of not being able to run a procedure directly within a <b><code>SELECT<\/code><\/b> statement. All I&#8217;ve done here is to connect to a local instance of SQL Server 2014 and pass in the <b><code>EXECUTE<\/code><\/b> statement as the function&#8217;s second argument. The statement returns the same results as the <b><code>SELECT<\/code><\/b> statement in the preceding example. <\/p>\n<p>To use this method, we might need to configure the SQL Server instance for data access. We can use the <b><code>sp_serveroption<\/code><\/b> system stored procedure to set the <b><code>Data<\/code><\/b> <b><code>Access<\/code><\/b> option to <b><code>TRUE<\/code><\/b>, as shown in the following example:<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">EXEC sp_serveroption 'srvXYZ\\sqlsrv2014', 'Data Access', TRUE;\n<\/pre>\n<p>This approach makes it easier to retrieve only the spreadsheet names, as long as you&#8217;re okay with the option being enabled. According to Microsoft documentation, <b><code>Data<\/code><\/b> <b><code>Access<\/code><\/b> lets you enable and disable a linked server for distributed query access. However, you can also use it on a local instance of SQL Server, as we&#8217;ve done here, to let you use the <b><code>OPENQUERY<\/code><\/b> function in this way.<\/p>\n<p>That said, one of the advantages of returning the list of columns in a table variable (or temporary table) is that you can create a <b><code>WHILE<\/code><\/b> loop to iterate through the list in order to retrieve data from multiple spreadsheets.<\/p>\n<h3 id=\"twelveth\">&#8220;How do I import Excel data through a linked server if the spreadsheet contains no header columns?&#8221;<\/h3>\n<p>If you know how to control the header setting when using the <b><code>OPENROWSET<\/code><\/b> function to perform ad hoc queries, you&#8217;ll have no problem controlling the setting if working with a linked server. When you create your linked server, simply set the <b><code>HDR<\/code><\/b> option to <b><code>NO<\/code><\/b>, as shown in the following example:<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">EXEC sp_addlinkedserver\n&#160; @server = 'ExcelLinkSrv4',\n&#160; @srvproduct = 'Excel', \n&#160; @provider = 'Microsoft.ACE.OLEDB.12.0',\n&#160; @datasrc = 'C:\\DataFiles\\EmployeeData2.xlsx',\n&#160; @provstr = 'Excel 12.0 Xml; HDR=NO; IMEX=1';\nGO\n<\/pre>\n<p>Once we&#8217;ve created our linked server, we can query the spreadsheets in the Excel file. However, we must use the default column names when referencing the Excel fields:<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">SELECT * FROM OPENQUERY(ExcelLinkSrv4, \n&#160; 'SELECT\n&#160;&#160;&#160;&#160; F1 AS EmployeeID, \n&#160;&#160;&#160;&#160; F2 + '' '' + F4 AS FullName, \n&#160;&#160;&#160;&#160; F5 AS JobTitle \n&#160;&#160; FROM [vEmployee$]\n&#160;&#160; WHERE F14 = ''Canada''\n&#160;&#160; ORDER BY F1');\n<\/pre>\n<p>Notice that we&#8217;re using the <b><code>F1<\/code><\/b>, <b><code>F2<\/code><\/b>, <b><code>F4<\/code><\/b>, <b><code>F5<\/code><\/b>, and <b><code>F14<\/code><\/b> column names, just as we would regular names. The <b><code>SELECT<\/code><\/b> statement returns the results in the following table:<\/p>\n<table class=\"MsoTableGrid\">\n<tbody>\n<tr>\n<td valign=\"top\">\n<p><b>EmployeeID<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p><b>FullName<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p><b>JobTitle<\/b><\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p>278<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Garrett Vargas<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Sales Representative<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p>282<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Jos&#233; Saraiva<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Sales Representative<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p>&#160;Of course, if the spreadsheet does include a header row at the top, we can simple set the <b><code>HDR<\/code><\/b> option to <b><code>YES<\/code><\/b>.<\/p>\n<h3 id=\"thirteenth\">&#8220;How do I join data in a SQL Server table with data from an Excel workbook?&#8221;<\/h3>\n<p>When we use a SQL Server rowset function to retrieve data from an Excel spreadsheet, we can include the function as part of a join condition, as we would a regular table. For example, if we want to run an ad hoc distributed query, we can join the <b><code>OPENROWSET<\/code><\/b> function to a database table or view, as shown in the following <b><code>SELECT<\/code><\/b> statement:<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">SELECT\n&#160; Emp1.BusinessEntityID AS EmpID,\n&#160; Emp1.FirstName + ' ' + Emp1.LastName AS FullName,\n&#160; Emp2.JobTitle,\n&#160; Emp2.CountryRegionName AS CountryRegion\nFROM \n&#160; HumanResources.vEmployee AS Emp1 INNER JOIN\n&#160; OPENROWSET('Microsoft.ACE.OLEDB.12.0',\n&#160;&#160;&#160; 'Excel 12.0 Xml; HDR=YES; IMEX=1;\n&#160;&#160;&#160;&#160; Database=C:\\DataFiles\\EmployeeData1.xlsx',\n&#160;&#160;&#160;&#160; [vEmployee$]) AS Emp2\n&#160; ON Emp1.BusinessEntityID = Emp2.BusinessEntityID\nWHERE Emp2.CountryRegionName &lt;&gt; 'United States'\nORDER BY Emp2.CountryRegionName;\n<\/pre>\n<p>The <b><code>OPENROWSET<\/code><\/b> function is basically returning a table that we&#8217;ve aliased as <b><code>Emp2<\/code><\/b>. We&#8217;re joining the <b><code>Emp2<\/code><\/b> table to the <b><code>vEmployee<\/code><\/b> view in the <b><code>AdventureWorks2014<\/code><\/b> database, which we&#8217;ve aliased as <b><code>Emp1<\/code><\/b>. The join is based on the <b><code>BusinessEntityID<\/code><\/b> column in each data source. The following table shows the <b><code>SELECT<\/code><\/b> statement&#8217;s results:<\/p>\n<table class=\"MsoTableGrid\">\n<tbody>\n<tr>\n<td valign=\"top\">\n<p><b>EmployeeID<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p><b>FullName<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p><b>JobTitle<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p><b>CountryRegion<\/b><\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p>286<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Lynn Tsoflias<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Sales Representative<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Australia<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p>282<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Jos&#233; Saraiva<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Sales Representative<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Canada<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p>278<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Garrett Vargas<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Sales Representative<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Canada<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p>290<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Ranjit Varkey Chudukatil<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Sales Representative<\/p>\n<\/td>\n<td valign=\"top\">\n<p>France<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p>288<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Rachel Valdez<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Sales Representative<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Germany<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p>Of course, this was an easy join to do because I created the spreadsheet by exporting the data through that view. However, the more the two data sources differ and the odder the spreadsheet&#8217;s structure, the more work you&#8217;ll have to do to join the tables. If it gets too bad, you might have to come up with a different approach, such as importing the Excel data into a temporary table and then joining that to the regular table.<\/p>\n<h3 id=\"fourteenth\">&#8220;When I try to import Excel data into SQL Server, I keep running into errors related to the OLE DB provider. I&#8217;ve received errors about not being able to initialize the provider, not being able to create an instance of the provider, and not being able to install a 64-bit version of the provider. Can you help?&#8221;<\/h3>\n<p>Few issues cause as much consternation when trying to import Excel data as trying to get the provider right. For whatever reason, Microsoft does not make this an easy process, despite that fact that Microsoft builds SQL Server and Excel and an assortment of other data-driven technologies that they&#8217;re constantly hawking as the ultimate unified platform for integrated business intelligence. Given all the hype, you would think Microsoft would go through great pains to ensure that importing Excel data is as painless a process as possible. It does not.<\/p>\n<p>In fact, SQL Server can be quite picky when it comes to importing Excel data. We&#8217;ve already discussed the issue of the provider itself, but let me reiterate. When importing data, you must use one of the following ADO providers:<\/p>\n<ul>\n<li><b><code>Microsoft.Jet.OLEDB.4.0<\/code>:<\/b> Use on SQL Server 32-bit editions for Excel 2003 files.  <\/li>\n<li><b><code>Microsoft.ACE.OLEDB.12.0<\/code>:<\/b> Use on SQL Server 32-bit editions for Excel 2007 files or on SQL Server 64-bit editions for any Excel files.<\/li>\n<\/ul>\n<p>There are other issues as well, too many to cover here, but I&#8217;ll cover what I did on my machine in order to import Excel data into SQL Server from an .xlsx file. <\/p>\n<p>First, I tried to install the 64-bit version of the ACE OLE DB provider because I&#8217;m running the 64-bit version of SQL Server. I discovered that I could not do so because the 32-bit version of Office 2010 was installed on my system. You can&#8217;t install the 64-bit provider on a computer running 32-bit Office. So Office got axed.<\/p>\n<p>After I uninstalled Office, I installed the driver. I then ran the following T-SQL statements:<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">EXEC sp_msset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', 'AllowInProcess', 1;\nGO\n&#160;\nEXEC sp_msset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', 'DynamicParameters', 1;\nGO\n<\/pre>\n<p>The <b><code>sp_msset_oledb_prop<\/code><\/b> stored procedure lets you set properties associated with the ACE OLE DB provider. In this case, I enabled the <b><code>AllowInProcess<\/code><\/b> and <b><code>DynamicParameters<\/code><\/b> properties. You can also enable the properties through <b><code>Object<\/code><\/b> <b><code>Explorer<\/code><\/b> in SSMS by accessing the provider&#8217;s properties. The properties need to be turned on in order to run distributed queries.<\/p>\n<p>Also, in case you haven&#8217;t already done so, you need to run the following statements to ensure that you can run ad hoc distributed queries (which we covered earlier):<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">EXEC sp_configure 'Show Advanced Options', 1;\nRECONFIGURE;\nGO\n&#160;\nEXEC sp_configure 'Ad Hoc Distributed Queries', 1;\nRECONFIGURE;\nGO\n<\/pre>\n<p>In addition to all this, I had to restart my computer a time or two. I can&#8217;t recall for sure the order of events, but eventually I got everything working.<\/p>\n<p>On the surface, this doesn&#8217;t seem so bad, but getting to this point was no easy task, in part because I was resistant to the idea that I had to uninstall Office 2010. (OpenOffice here I come.) I believe there are some other workarounds out there, but I finally grew weary of the process and opted to sacrifice Office. <\/p>\n<p>So that&#8217;s my story. The problems you might bump into can very depending on whether you&#8217;re running a 32-bit version or 64-bit version of SQL Server and whether you&#8217;re retrieving Excel 2007 files or Excel 2003 files. In some cases, you might also have to adjust the permissions in the appropriate temp folder to get everything working. Which temp folder? It depends. It could be the one set up for the <b><code>NetworkService<\/code><\/b> account or the one set up for <b><code>LocalService<\/code><\/b> account or another temp folder altogether. Again, it depends on your particular configuration. <\/p>\n<p>Here&#8217;s one place you might start looking for answers if your run into a roadblock: &#8220;<a href=\"http:\/\/social.technet.microsoft.com\/wiki\/contents\/articles\/24236.importing-an-excel-spreadsheet-into-a-sql-server-database.aspx\">Importing an Excel Spreadsheet into a SQL Server Database<\/a>.&#8221; If that doesn&#8217;t help, there&#8217;s always Google.<\/p>\n<\/p><\/div>\n","protected":false},"excerpt":{"rendered":"<p>It is easy to import Excel data into database tables via TSQL, using OLEDB, either by the OPENROWSET function or linking to the spreadsheet as a server. The problem is that there are certain things that aren&#8217;t obvious that you need to know about, and you feel awkward about asking such simple questions. &hellip;<\/p>\n","protected":false},"author":221841,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143531],"tags":[4168,4150,4252,5771,4190],"coauthors":[],"class_list":["post-1901","post","type-post","status-publish","format-standard","hentry","category-t-sql-programming-sql-server","tag-database","tag-sql","tag-t-sql-programming","tag-too-shy","tag-tsql"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/1901","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/221841"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=1901"}],"version-history":[{"count":7,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/1901\/revisions"}],"predecessor-version":[{"id":92525,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/1901\/revisions\/92525"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=1901"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=1901"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=1901"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=1901"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}