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.
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:
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:
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:
Choose to import
from a data source.
Scroll all the way down to the bottom of the list of data sources, and choose
Excel:
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:
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:
Tick the
Use first row as column headers box.
You can now say in which role SSAS will try to access this data:
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:
Excel removes the trailing $ from each name by default.
That should be it!
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:
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:
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:
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:
Being yourself, as so often in life, is probably the best policy.
You can now choose which tables you want to import:
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.
The species table that we'll import.
Follow the steps shown above for Excel, but choose to import a text file:
Choose the last option in the list: importing data from a text file.
You can now try to find your 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:
At the bottom right of the dialog box which appears, choose to look for CSV files.
SSAS Tabular should automatically detect the file type:
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:
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.
