Has anyone achieved to use ODBC connection from Excel file into Powerpivot?

0
votes

I have installed Powerpivot for Excel 2010. I don't have Access 2010 so I thought could I arrange the data in the similar manner as I would for a database.

I'm wanting to query an excel file that has rows of self-generated data into Powerpivot in order to perform simple pivot table. In a sense attempt to get an overview of information about a data set.

At the moment, I'm unable to get set up correctly the ODBC I'm hoping I need to accept an Excel file and to get PowerPivot to accept a database from an Excel file.

Edit: I come to understand that I need to set up a table correctly in Excel so that the ODBC works correctly. Does the picture I provided be right manner to set up a table or any other manner?

enter image description here

Has anyone attempted to do this and if so what would the steps be?

Thanks, Peter.

2
excelpowerpivot

2 Answers

0
votes

Peter, I am working on the basis that your data is in a recognizable table on a worksheet (and that you are not interested in using the standard Excel import method through 'From Other Sources')

If you create the connection to the Excel file in a very particular way it is possible to then query it as if it were a database.

  • Create a Connection to the spreadsheet in question in Excel through Data>Connections>Add.

  • In the PowerPivot window on the Design tab click on existing connections and find the connection you just created. Select the connection and Open.

  • You need to name the connection and then you will be offered the normal import options. Select the sheet you want to get the table from (its actually not important which one you choose at this point).

  • Once the table has been imported you can re-enter the setup through the 'table properties' on the design tab and you will now be able to 'Switch to' the query editor at which point you can not only write SQL to query your DB but reference any other .xlsx you like.

Jacob

0
votes

the easiest way is to use a linked table from the excel sheet which has the data - is there any reason why you have not considered this as an option?