Excel PowerPivot Table Import Wizard suddenly not recognizing delimiters RRS feed

  • Question

  • I have been using Power Pivot Table Import Wizard to import a pipe delineated file of almost 2 million rows successfully for weeks.  Now when I load the same file, all columns are shown as a single column with the pipe character between each.  Previously, this file would load in about 15 seconds (Win10 I7 16Gb environment).  The only way I can get it to load now is to establish a Schema.INI file indicating the pipe delimiter which works, but the load time is now 2 minutes.  I know the Schema.INI is invoking the ODBC driver, so it may be slower than the native Power Pivot Text Import.   Power Query works fine, recognizing the pipe character without the Schema, but still take 2 minutes to load.  The real question is why PowerPivot native Import Wizard no longer recognizes the pipe delimiter without Schema assistance.

    Does anyone know what mechanism Power Pivot native text import without Schema.INI is using to load so fast and what may have broken to stop it from recognizing the delimiters.  

    I have run a full reinstallation on Excel with no impact.  While 2 minutes is workable, there does seem to be some kind of bug that is disabling the native Text File Import.  Having to update the Schema.INI with any file name change is burdensome. 

    Running Win10 / I7/ 16gb Office 365 Enterprise Build 12827.20210

    Any ideas would be appreciated.

    Saturday, May 23, 2020 12:31 PM

All replies

  • I encountered the exact same problem today where I was unable this month to import a tab-delimited file of 1.3 million rows because they loaded into Power Pivot into one column.  It worked flawlessly for everyone on our team last month, but not today. 

    I was able to import the data into Power Query without a problem today; and I was able to import a smaller set of the same type of data into Excel today without an issue (both using tab-delimited); but we've built so much in Power Pivot that we need a solution.  

    Thank you for the suggestion of the Schema.INI file - that worked for us as well but it is slower and I hope not the permanent solution.  I hope that Microsoft releases a fix for this.  
    Wednesday, June 3, 2020 11:38 PM
  • Tom,

    I did discover that if you PowerQuery and turn off the auto detect column types it will load in the same time as it used to.  Not an ideal situation, but a better fall back than the Schema.ini based load times.

    Wednesday, June 3, 2020 11:57 PM