Max number of columns in Excel

[skalkhathran]

Dear All, Does anyone know what is the maximum number of columns in excel that can be retrieved? 

[Pstork1]

I don't believe there is a specific limit on the number of columns that can be in the table.  But there is a hard limit on the overall size of the Excel file.  The file can't be larger than 2MB. That will limit how many columns you can have.

[skalkhathran]

Hello Pstork1, thanks for your response. the issue i am having is that the excel file has exceed 2000 rows and i am not able to retrieve the data after the column number 2000. Do you have any solution for that? how to extend the number of rows accepted?

[timl]

Hi @skalkhathran 

There is no way to extend the number of rows that is accessible beyond 2000.

As @Pstork1  says, there is a 2MB limit to the size of your Excel file. If you want to extend the amount of data that you can store in a spreadsheet by extending the number of columns, you'll likely hit the 2MB soon and you'll end up in the same situation

The only workable solution is to work out a way in which you can partition your data, and to split it across multiple Excel files. You would then create a separate connection for each Excel file.

Alternatively, if you're working with read-only Excel data that doesn't change, you can import it as static data and there isn't any limit to the amount of rows that you can import this way.

[Pstork1]

You asked about columns, not Rows.  I don't believe there is a limit to 2,000 columns, but as @timl said there is no way to increase beyond the 2,000 rows other than retrieve the content through import.  But then you can't save changes back.

[skalkhathran]

Thank you all. I am trying now to take the extra rows and convert them into colums. so i solve the issue of the extra rows.

[Pstork1]

Rows and Columns really aren't interchangeable.  Rows represent records with specific fields (columns).  Switching them around won't really give you usable data.

[timl]

@skalkhathran 

Is the any reason why you can't use a SharePoint list?

If you're spending time transposing rows to columns, I would suggest that a more effective use of your time would be to migrate your data to SharePoint, which would then provide a much more future proof platform on which to work on.

[skalkhathran]

thank you for your input. i am limited only to excel.