255 Column Limit with ACE 14 OLE DB Provider

There’s a column limit issue I’ve run across that perhaps others have also.  It involves PowerPivot data source of an Excel workbook, CSV file, etc.  Apparently,  255 columns is the default (i.e. limit) for the ACE 14 OLE DB provider for Microsoft Access which is the provider used when your data source is a .csv or .xlsx file.

I encountered this issue while working to improve performance on a large dashboard application, which takes a few minutes to update.  The idea was to have a PowerPivot table in worksheet A do the aggregations.  There number of table columns is about 355.  A filtered pivot table in worksheet B would source to the aggregated results and drive the dashboard.  Unfortunately, the 255 column limit came up in two areas.  First, regular Excel pivot tables have a 255 column limit.  Second, PowerPivot connecting to .xlsx or .csv files pulls only the first 255 columns.

There’s a work-around.  In workbook B, link to the PowerPivot table range in workbook A.  Convert the workbook B range to a table.  Finally, in workbook B create a linked table in PowerPivot.  All 255+ columns will be added to a PowerPivot table.

Here’s a link to the solution I came across:  https://social.msdn.microsoft.com/Forums/sqlserver/en-US/06ff6ff8-7882-41ce-9a26-4b61d10d011c/how-many-columns-in-a-csv-should-powerpivot-support?forum=sqlkjpowerpivotforexcel

Does anyone have a better way of creating a 255+ column PowerPivot table from .xlsx or .csv data?

Cheers,

Ron