Power Query import from txt (fixed width method) - many lines merge into one cell

Question

Hi all, I've run into a strange problem, would like some help from the pros please.

Source file is a txt file, shared via url below.

txt file attached

I use Office 365 Pro Plus, Excel version 1903.

I first tested opening the file with Excel, using fixed width method (to work out width count of each column, so I can use the info when I import into Query). I got 885 rows of data in Excel.

Then I tried Data - From csv/txt, choose Fixed Width, put in the same width I worked out earlier, load. I got 300 rows of data in Excel.

Looking inside query editor, the rest of data is merged into one cell (see below).

I've checked many places but couldn't work out what's wrong with the file.

Especially strange is that Excel opens txt fine without query.

Any suggestion please! Thanks!

[PII is masked by Hugo Liang MSFT Support]

Answer 1

Hi LifanLou,

Per my test, I would like to suggest you replace the double quotes in 299 rows to another character (I replaced the double quotes to a blank).

It seems that double quotes without endings cause this problem, the complete double quotes of row 358 work fine.

Hoping this work for you.

Best Regards,

Hugo

Answer 2

Hi Hugo,

Per your screenshot, header is Step 2 of 3 (Delimited Data), and we're talking about Fixed Width, you should refer to the next paragragh Step 2 of 3 (Fixed Width Data). This is definitely a bug with the query function.

My alternative solution is this,

Data - From CSV/Text, choose the file, choose Fixed Width, set any width for example 0,100, then Transform Data.

In the query editor, double click the Source step.

In the Open File As selection, change the existing selection csv to txt.

Now we'll get all rows of data, just need to do Split Column by Number of Characters.

cheers,

Lifan

Answer 3

Hi Lifan,

Thanks for your sharing of the alternative solution.

For the possible bug with the query function, would you mind sending a Feedback in Excel?

I can understand your feelings and I can agree with you about this idea, but I'm not able to report the limit of this feature successfully on my side since the article has referred the work way of importing the Delimited text even it hasn't mentioned that in importing Fixed width data section.

You can send a feedback in Excel via clicking File -> Feedback and input the detailed steps to reproduce this issue in the form after you chose an appropriate button.

Appreciating for your patience and understanding.

Best Regards,

Hugo

Answer 4

Hi Hugo,

Not a problem, feedback sent.

Thanks for your help!

p.s. a little hard to believe a clear bug is not recognised....

Lifan

Answer 5

Hi Lifan,

Also thanks for your understanding.

Your precious feedback can help to improve Office products better.

Best Regards,

Hugo