cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jstupl
Regular Visitor

import CSV file with more than 1048576 rows

Hi,

 

I want to upload a CSV file with more than 1048576 rows. It works fine until it hits that number. Suspiciously, that number is also the maximum number of rows for Excel...

 

Is there way to get this CSV file into Power BI? The file is 600MB, well below the 1GB limit.

 

Thanks,

Jan

1 ACCEPTED SOLUTION

And finally a solution:

 

1) Import the data with the Power BI desktop app.

2) Save as Power BI .pbix file.

3) Import pbix file to online app.

View solution in original post

13 REPLIES 13
v-yuezhe-msft
Microsoft
Microsoft

Hi @jstupl,

If you use Excel to open the CSV file, you are hitting the Excel row limitation which is 1,048,576. Reference: https://support.office.com/en-us/article/Excel-specifications-and-limits-ca36e2dc-1f09-4620-b726-67c... .

In your scenario, to see the raw data in CSV file, you can consider to split the imported CSV file into different worksheets using VBA code or other online tool, then import CSV file to Power BI.

There is a similar thread about handling large CSV file for your reference.

https://social.technet.microsoft.com/Forums/office/en-US/54ca6d3a-4b1c-4318-aa8c-7ac803d3af69/what-i...
 

Thanks,
Lydia Zhang

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi @v-yuezhe-msft,

 

Thanks for your reply. I thought that Power Bi, contrary to Excel, had a larger limit to the number of rows it could handle.

 

It looks like that Power Bi has the same restriction to 1,048,576 rows?

 

Thanks,

Jan

Hi @jstupl,

There is no limit on the rows of data you can import into Power BI. Are you able to see all the data in Excel? If not, please use other program to open CSV file, make sure you can view complete data in the opened CSV file and then import data from CSV to Power BI.

Thanks,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi @v-yuezhe-msft,

 

I cannot see all the data in Excel because Excel has a limit at 1,048,576 rows. However, I can see all the data when I open the file with wordpad. Also, if I cut away the first 1,048,576 rows of the file before importing, the dataset will import into Power Bi, until it hits 1,048,576 rows again, and then stops.

 

So to me that is a very strong indication that there is actually a limit to Power Bi, at least to the number of lines it can import from a CSV file.

 

Do  you have an idea how to get around that?

 

Best,

Jan

KHorseman
Community Champion
Community Champion

I think it's not quite that Power BI and Excel can't handle that many rows, it's that they can't parse a CSV file beyond those rows. I'm sure Power BI shares some code with Excel for handling files. I would try changing the file extension and trying to import it as a delimited text file instead of a CSV.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Hi @KHorseman,

Thanks for suggesting a workaround.

 

I tried witching the extension to .txt. I got: "Power Bi only supports Excel, Power Bi Desktop, and CSV files".

 

Excel cannot handle more than 1,048,576 rows. That is the maximum you can have in a worksheet.

 

Best,

Jan

KHorseman
Community Champion
Community Champion

@jstupl you would of course need to use the query editor for text.

 

text.PNG





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




@KHorseman

 

I cannot find that option in the online app. I only see the "Get Data" option, which leads to "Files" gave me the error message above.

 

@KHorseman, @v-yuezhe-msft, @Greg_Deckler,

 

I appologize for leaving out an important detail in my original post: I was using the online version of Power BI. After my experiences, I am still convinced that there is a restrictions to the number of rows I mentioned originally.

 

However, I just downloaded the desktop version and it can actually deal with the full 4 million lines.

 

Thanks for your help,

Jan

 

 

And finally a solution:

 

1) Import the data with the Power BI desktop app.

2) Save as Power BI .pbix file.

3) Import pbix file to online app.

KHorseman
Community Champion
Community Champion

The query editor is only in the desktop app. The online app is really only useful for connecting to things like content packs and other essentially complete data models.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Greg_Deckler
Super User
Super User

A couple things that I think would be interesting to try. What happens if you delete one of the columns? Do you get more rows in? Also, what happens if you delete row 1,048,577?


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Hi@Greg_Deckler,

Thanks for the reply. I cut down the columns by 50% and started at row 1,048,577 . It shuts down after the the number of rows. So this seems to be the upper limit.

 

Best,

Jan

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.

Top Solution Authors
Top Kudoed Authors