WiseOwl Training - Established 1992 Wise Owl Training

Established May 1992
30 years in business
Wise Owl Training
30 years in business
See 479 reviews for our classroom and online training
A tutorial showing how to import data from Excel, Access and many other sources
Part two of a five-part series of blogs

You can import data into tabular models from virtually any data source, as shown by this tutorial.

  1. Importing data from other data sources
  2. Importing data from Excel, Access, CSV files or the clipboard (this blog)
  3. Importing data from SSRS (Reporting Services)
  4. Importing from Analysis Services cubes/tabular models
  5. Importing from other data sources

This blog is part of our online SSAS Tabular tutorial; we also offer lots of other Analysis Services training resources.

Posted by Andy Brown on 04 January 2016

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

Importing data from Excel, Access, CSV files or the clipboard

This blog shows how to import data from all of the above formats.

If you're coming from a PowerPivot background, be aware that there is no such thing in SSAS Tabular as a linked table (you can import from an Excel workbook, but you can't link to it).

Before we begin - a huge caveat for SWOTS

If you're a SWOT, you've got problems.  For me, SWOT stands for:

  • Sixty-four bit
  • Windows, but
  • Office
  • Thirty-two bit

OK, I had to work hard to get the acronym to work!  If you - like me - have a new 64-bit laptop but have followed Microsoft's advice to install Office as a 32-bit application, you'll have problems importing data from Access, Excel or text files. 

Problems importing

When you come to import your data, you'll either get Error next to each table or (worse) Visual Studio will hang at this point.

I wish I had a solution!  You can find possible workarounds here and here and here and here, but none of them worked in the long term for me.  Microsoft's website contains a page with the following ominous phrase:

Warning from Microsoft

You have been warned!


My solution? I'm currently using 32-bit laptops for training on SSAS Tabular, which isn't ideal. 

The only other reliable solution would appear to be to uninstall Office 32-bit, and install Office 64-bit instead.

Importing from Excel

Having explained why you may not be able to import anything at all, let's proceed! 

You can import multiple worksheets from an Excel workbook in one go:

Importing worksheets

Here we'll import sets of species, animals and products; each set of data is in a separate worksheet, with column headings in the top row.


To start, first double-click on your model to edit it, then choose to import data:

Import menu

Choose to import from a data source.


Scroll all the way down to the bottom of the list of data sources, and choose Excel:

Excel data source

Bizarrely, Excel - surely one of the most likely data sources? - is the penultimate one in the (long) list.

Choose to browse to find your file:

Browse button

Click on this button to find the Excel workbook from which you want to import.

Once you've found your file (you can download the one used above here), make sure that you say that the first row of each workbook contains the column names:

Use first row as column headers

Tick the Use first row as column headers box.

You can now say in which role SSAS will try to access this data:

Impersonation role

The option most likely to give the best results is to impersonate yourself!

You can now give each of the workbook's tables a friendly name:

Friendly names for Excel tables

Excel removes the trailing $ from each name by default.

That should be it!

Finishing import

When you click on the Finish button, SSAS Tabular will import (for our example) 3 separate tables of data, which will behave from this point onwards exactly like tables imported from SQL Server.


Or not, if you're a SWOT (see the top of this page for what this means).

Importing from Access

Importing tables from Microsoft Access is similar (you can test the following steps out using this sample database file).  Choose to import data from Access:

Access data source

Choose to import data into your model as for Excel, but then choose Microsoft Access from the dialog box which appears.


You can then browse to find your database:

Browse button for Access

Click on this button and find the Access database some or all of whose tables you want to import.


If the database has security set, you'll need to enter your user name and password:

User name and password

The product recalls database doesn't have any security set, so you can leave these fields blank.


As for Excel, you can then specify in what role you'll try to access the data:

Impersonation for Access

Being yourself, as so often in life, is probably the best policy.


You can now choose which tables you want to import:

Choosing Access tables

You can choose tables to import and take the opportunity to give each a friendly name.

And there my diagrams stop, because I'm a SWOT (see top of this page).  However, I have got this to work on other laptops, and the tables import just like for SQL Server or Excel.

Importing from text files

If you have a text file such as this CSV example, you can import it in much the same way as for an Excel one.

Species table as CSV

The species table that we'll import.


Follow the steps shown above for Excel, but choose to import a text file:

Text file import

Choose the last option in the list: importing data from a text file.


You can now try to find your text file:

Browsing for text file

Click on this button to try to find the file you want to import.


For a CSV file, you'll need to change the default file format Windows is searching for:

Comma Separated Files

At the bottom right of the dialog box which appears, choose to look for CSV files.


SSAS Tabular should automatically detect the file type:

Comma separator

There's no need to change the column separator punctuation character, because Excel has set it correctly.  You can also give the table of data to be imported a friendly name at this stage.

Again, you'll now have to say who you are pretending to be:

Impersonation for text files

The option shown is probably your best bet.


And if you're not a SWOT (see top of page for what this means) like me, you'll now be able to import your data!


It's now a relief to move onto the next part of this blog: importing data from Reporting Services.

This blog has 0 threads Add post