rows limit in excel

Rakesh Kumar 20 Reputation points
2023-12-27T06:23:18.77+00:00

I am transferring data from an on-premises database to a data lake in CSV format. When I attempt to open the CSV file in Excel, I encounter a row limit of approximately 1 million rows, while the source database contains over 4 million rows. What is the recommended approach to handle this situation?

Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
11,624 questions
Microsoft 365 and Office Excel For business Windows
{count} votes

Accepted answer
  1. Nandan Hegde 36,146 Reputation points MVP Volunteer Moderator
    2023-12-27T07:46:52.25+00:00

    Hey,

    this is a limitation on Excel

    https://support.microsoft.com/en-au/office/excel-specifications-and-limits-1672b34d-7043-467e-8e27-269d656771c3

    So you can either open the CSV file as txt file or load data into SQL or use synapse serverless to query the CSV file data from database or use databrick notebooks etc

    User's image

    1 person found this answer helpful.
    0 comments No comments

2 additional answers

Sort by: Most helpful
  1. Emi Zhang-MSFT 30,046 Reputation points Microsoft External Staff
    2023-12-27T07:41:13.87+00:00

    Hi,

    The maximum number of rows and columns on a worksheet in Excel is 1,048,576 rows by 16,384 columns. You cannot increase the rows and columns in Excel. In my opinion you should change the application to help you analysis the data in .csv file.

    Just checking in to see if the information was helpful. Please let us know if you would like further assistance.


    If the response is helpful, please click "Accept Answer" and upvote it.

    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments No comments

  2. Olaf Helper 47,436 Reputation points
    2023-12-27T07:43:48.7766667+00:00

    When I attempt to open the CSV file in Excel, I encounter a row limit of approximately 1 million rows

    1 Mio is the limit of Excel.

    What is the recommended approach to handle this situation?

    Not opening a CSV file in Excel, that will cause always issue. CSV is a text file so use a text editor if you want to view the file content.

    0 comments No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.