If you’ve worked with Excel long enough, you’ve probably needed to get data into Excel so that you can analyze it, or maybe create a chart. Where does this data come from? It might come from a database or some other program, or even the web. And it can come in a variety of forms.
- Microsoft Excel For Mac Text Import Wizard Can 27t See All Columns Instead
- Microsoft Excel For Mac Text Import Wizard Can 27t See All Columns Excel
Excel displays the second dialog box of the Text Import Wizard. You use this second Text Import Wizard dialog box to identify the character or characters used as the delimiter to break the text into columns. For example, if the file that’s being imported is a tab-delimited file, select the Tab check box in. You can import information from your Excel spreadsheet by importing information from a comma-separated value (.csv) or a text (.txt) file and use the Text Import Wizard to build a new spreadsheet. For more information, see Prepare your Excel data source for mail merge in Word.
Whatever the case, I can guarantee you the last thing you’d ever want to do is retype it, so that option is out unless you really love typing and have tons of extra time on your hands. And copying from another program or from the web and pasting directly into a cell in a workbook might leave you with nightmarish looking results I’ve seen this, believe me.
Luckily for you, there’s a much better way to get that data into Excel. Importing! Yes, importing is not just for purveyors of fine art or exotic foods.
When you use the Text Import Wizard, you don’t open your data source in its native program. You point Excel’s Text Import Wizard to a file, and the wizard leads you through a few steps where it looks at the rows of data in the source file and makes suggestions based on the layout of the file. Of course, you’re allowed to fine tune or override these suggestions so that the data comes into Excel just the way you want.
In this file, the four pieces of information for each employee are separated (also known as “delimited”) by a comma. The first row of the file is the column names, which are also separated by commas.
The rows of information look just like this in the text file:
Start the Text Import Wizard
To import the contents of the file, select an empty cell in Excel — this can be in a new workbook, a new worksheet in an existing workbook, or in an existing worksheet. The wizard isn’t picky!
Now, click the Data tab, and in the Get External Data group, click From Text.
This starts the Text Import Wizard. Go find your file, and click Open. Notice how the Delimited button is already selected?
My data certainly is delimited by those commas, so I’ll click Next. The default delimiting character in Excel is the tab character, so it’s pre-selected here in step 2. Notice how, with the tab character as the delimiter, the preview doesn’t look right? So I see I need to change that from Tab to Comma.
Now I’ve selected Comma instead. There, see how the preview changes and looks right now?
I’ll click Next to go to the third and last step of the wizard. I can select each column and, if I want, change its data type so that it has a format I prefer or a right or left margin. But all of these columns look fine, so I’ll click Finish.
Now, Excel asks me if I want to import the data right here in the current cell or create a new worksheet. That’s nice, because what if I had selected a cell that contained data I didn’t want to overwrite? Anyway, I’m in blank cell in a new worksheet, so I’ll accept Existing Worksheet and cell =$A$1, and click OK.
And here’s my imported data! As a plus, the Text Import Wizard (in most cases) optimizes the width of the columns to fit the data so you don’t have to.
— Gary Willoughby
-->Applies to:SQL Server (all supported versions) Azure SQL Database
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.
List of methods
You can use the following tools to import data from Excel:
Export to text first (SQL Server and SQL Database) | Directly from Excel (SQL Server on-premises only) |
---|---|
Import Flat File Wizard | SQL Server Import and Export Wizard |
BULK INSERT statement | SQL Server Integration Services (SSIS) |
BCP | OPENROWSET function |
Copy Wizard (Azure Data Factory) | |
Azure Data Factory |
If you want to import multiple worksheets from an Excel workbook, you typically have to run any of these tools once for each sheet.
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.
Important
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).
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).
SQL Server Import and Export Wizard
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.
- In SQL Server Management Studio, connect to an instance of the SQL Server Database Engine.
- Expand Databases.
- Right-click a database.
- Point to Tasks.
- Click one of the following options.
- Import Data
- Export Data
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.
To learn about other ways to launch the Import and Export wizard, see Start the SQL Server Import and Export Wizard.
SQL Server Integration Services (SSIS)
![27t 27t](/uploads/1/1/9/7/119772117/852297545.png)
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.
For more info about these SSIS components, see the following topics:
To start learning how to build SSIS packages, see the tutorial How to Create an ETL Package.
Microsoft Excel For Mac Text Import Wizard Can 27t See All Columns Instead
OPENROWSET and linked servers
Important
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.
Note
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.
Distributed queries
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.Important
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.
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.The following code sample uses
OPENROWSET
to import the data from the Excel Sheet1
worksheet into a new database table.Here's the same example with
OPENDATASOURCE
.To append the imported data to an existing table instead of creating a new table, use the
INSERT INTO ... SELECT ... FROM ...
syntax instead of the SELECT ... INTO ... FROM ...
syntax used in the preceding examples.To query the Excel data without importing it, just use the standard
SELECT ... FROM ...
syntax.For more info about distributed queries, see the following topics:
- Distributed Queries (Distributed queries are still supported in SQL Server 2016, but the documentation for this feature has not been updated.)
Linked servers
You can also configure a persistent connection from SQL Server to the Excel file as a linked server. The following example imports the data from the
Data
worksheet on the existing Excel linked server EXCELLINK
into a new SQL Server database table named Data_ls
.You can create a linked server from SQL Server Management Studio, or by running the system stored procedure
sp_addlinkedserver
, as shown in the following example.For more info about linked servers, see the following topics:
For more examples and info about both linked servers and distributed queries, see the following topics:
Prerequisite - Save Excel data as text
To use the rest of the methods described on this page - the BULK INSERT statement, the BCP tool, or Azure Data Factory - first you have to export your Excel data to a text file.
In Excel, select File | Save As and then select Text (Tab-delimited) (*.txt) or CSV (Comma-delimited) (*.csv) as the destination file type.
If you want to export multiple worksheets from the workbook, select each sheet and then repeat this procedure. The Save as command exports only the active sheet.
Tip
For best results with data importing tools, save sheets that contain only the column headers and the rows of data. If the saved data contains page titles, blank lines, notes, and so forth, you may see unexpected results later when you import the data.
The Import Flat File Wizard
Import data saved as text files by stepping through the pages of the Import Flat File Wizard.
As described previously in the Prerequisite section, you have to export your Excel data as text before you can use the Import Flat File Wizard to import it.
For more info about the Import Flat File Wizard, see Import Flat File to SQL Wizard.
BULK INSERT command
BULK INSERT
is a Transact-SQL command that you can run from SQL Server Management Studio. The following example loads the data from the Data.csv
comma-delimited file into an existing database table.As described previously in the Prerequisite section, you have to export your Excel data as text before you can use BULK INSERT to import it. BULK INSERT can't read Excel files directly. With the BULK INSERT command, you can import a CSV file that is stored locally or in Azure Blob storage.
For more info and examples for SQL Server and SQL Database, see the following topics:
BCP tool
BCP is a program that you run from the command prompt. The following example loads the data from the
Data.csv
comma-delimited file into the existing Data_bcp
database table.As described previously in the Prerequisite section, you have to export your Excel data as text before you can use BCP to import it. BCP can't read Excel files directly. Use to import into SQL Server or SQL Database from a test (CSV) file saved to local storage.
Important
For a text (CSV) file stored in Azure Blob storage, use BULK INSERT or OPENROWSET. For an examples, see Example.
For more info about BCP, see the following topics:
Copy Wizard (Azure Data Factory)
Import data saved as text files by stepping through the pages of the Azure Data Factory Copy Wizard.
As described previously in the Prerequisite 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 the Copy Wizard, see the following topics:
- Tutorial: Create a pipeline with Copy Activity using Data Factory Copy Wizard.
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 Prerequisite 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:
To start learning how to copy data with Azure data factory, see the following topics:
Common errors
Microsoft.ACE.OLEDB.12.0' has not been registered
This error occurs because the OLEDB provider is not installed. Install it from Microsoft Access Database Engine 2010 Redistributable. Be sure to install the 64-bit version if Windows and SQL Server are both 64-bit.
The full error is:
Cannot create an instance of OLE DB provider 'Microsoft.ACE.OLEDB.12.0' for linked server '(null)'
This indicates that the Microsoft OLEDB has not been configured properly. Run the following Transact-SQL code to resolve this:
The full error is:
Microsoft Excel For Mac Text Import Wizard Can 27t See All Columns Excel
The 32-bit OLE DB provider 'Microsoft.ACE.OLEDB.12.0' cannot be loaded in-process on a 64-bit SQL Server
This occurs when a 32-bit version of the OLD DB provider is installed with a 64-bit SQL Server. To resolve this issue, uninstall the 32-bit version and install the 64-bit version of the OLE DB provider instead.
The full error is:
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
Cannot initialize the data source object of OLE DB provider 'Microsoft.ACE.OLEDB.12.0' for linked server '(null)'
Both of these errors typically indicate a permissions issue between the SQL Server process and the file. Ensure that the account that is running the SQL Server service has full access permission to the file. We recommend against trying to import files from the desktop.
The full errors are: