Excel Power Pivot does not correctly interpret tab-delimited file

Question

Setup: Windows 10, Microsoft Excel for Microsoft 365 MSO version 16.0.12827.20328 64-bit

I have a number of Excel-based "data prep" templates that leverage Power Pivot to aggregate large data files into consumable pivot tables -- these designs all revolve around tab-delimited text files that have historically, including Wednesday June-10 2020, been interpreted successfully by Power Pivot.

To demonstrate the problem, let's start with this tab-delimited file:

When I attempt to import this file using Power Pivot, you can see that the tabs are not recognized as delimiters:

Power Pivot has no issue interpreting comma-delimited files though:

The existing infrastructure relies on tab-delimited data, so while switching from tab-delimited data to comma-delimited data is an acceptable long-term solution, I need a short-term solution and believe that Power Pivot should be able to interpret tabs.

Can anyone advise on what I am doing wrong here, or if this is potentially a bug with Power Pivot?

Thanks! -Dan

[PII Removed by MSFT]

Answer 1

Appreciate the quick response Arck -- I have submitted this feedback to Microsoft via the Excel interface.

Thanks! -Dan

Answer 2

Hi Dan,

Thanks for using Microsoft products and posting in the community, I’m glad to offer help.

I noticed that you seem to have received a response asking you to get help by phone, these are fraudulent information, because Microsoft support will not contact community members through any private contact method. If you encounter such information again in the future, you can Report them directly, and we will appreciate your contribution to this community.

Reference: Microsoft Community Code of Conduct

Back to your question, according to your detailed description, I tested the same file on two different versions of Excel, here are the results:

1.    Sample file

 

2.    The current latest version: Version 2006 (Build 13001.20144) Current Channel (Preview) Can reproduce

 

3.    The older version: Version 1908 (Build 11929.20838) Semi-Annual Enterprise Channel No issue

 

Since the older version doesn’t have this issue, this is most likely due to a mistake caused by the update, in this case, considering that we don’t have the abilities to help you deal with it, would you mind sending a feedback to the relevant team to report it?

Reference:

-      How do I give feedback on Microsoft Office?

-      Update history for Microsoft 365 Apps

I can understand that it may cause inconvenience to your work, and we both hope it can be fixed as soon as possible with the efforts of the relevant team, and please wait for the next update to see if it will be fixed, your understanding will be highly appreciated.

If you have any concerns, please feel free to let me know, I’ll continue to help you.

Best Regards,

Arck

Answer 3

Hi,

You should import data into Power Query first and use the split column feature there to get data into multiple columns.  Then from there push the data into the PowerPivot.

Answer 4

Hi,

Any chance this will be solved soon? I am stuck and cannot use my reports anymore.

Thanks a lot