import to multiple sheets

[ianjwerner]

I have a CSV file/text file that has over the line limit. I want to use excel Data > get data from text/csv but load the data into two different sheets.

Currently I am getting an error that says load only what will be visible or cancel. Is it possible to update the import settings?

 

[Matt Allington]

What are you trying to do? You can load millions of rows into the data model (Power Pivot) and summarise the data from there. It’s hard to image you want to be able to see more than a million lines of data in detail on one page. There are other ways of presenting the data.

What is Power Pivot and Why You Should Care

People often describe Power Pivot as Pivot Tables on Steroids - this is not correct. In this post I explain "What is Power Pivot" and why you should care.

exceleratorbi.com.au

 

[ianjwerner]

What are you trying to do? You can load millions of rows into the data model (Power Pivot) and summarise the data from there. It’s hard to image you want to be able to see more than a million lines of data in detail on one page. There are other ways of presenting the data.

What is Power Pivot and Why You Should Care

People often describe Power Pivot as Pivot Tables on Steroids - this is not correct. In this post I explain "What is Power Pivot" and why you should care.

exceleratorbi.com.au

I am trying to load a text file from an ERP program that has too many lines. We need the file in an excel format as a historical reference and current reference document so that multiple employees can utilize the information.

I need to have a way where I can import the data and split it into 2 or 3 tabs in excel. Currently I am opening the document in Notepad and splitting up the information into multiple CSV documents, opening the CSV files and creating a tab for each in excel.

 

[Matt Allington]

You can do this using the approach I covered above. I know one thing for sure; employees cannot easily find data in a multi-sheet workbook with a million records on each sheet. If they want to find the data, they have to search. So given this fact, why not do as I have proposed.
1. load the data into the data model as a flat table
2. you can insert a pivot table into the workbook with filters on the key search criteria
3. You can double click on the results in the pivot table to extra a subset of the records that have been returned.

 

[Matt Allington]

Microsoft is continuing to build out this capability for Power BI. This should give you an idea of what I am talking about

Announcing the creation of connected tables in Excel Online

You can now create tables in Excel online using data from Power BI semantic models.

powerbi.microsoft.com