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

Excel 365 Pro Plus with Power Query.

Start PQ with 885 row, 1 column Table in *.xlsx file.

http://www.mediafire.com/file/2hjk0uyyjpf72le/09_16_19.xlsx/file

http://www.mediafire.com/file/cq0qw4ut4qqgx0t/09_16_19.pdf/file

Answer 3

Hi LifanLou,

Have you tried the method provided above?

Please tell us if you need more help.

Best Regards,

Hugo

Answer 4

Hi Hugo,

Brilliant finding! I've tested on many different lines and you're quite right, a double quote (") in the text stops query to read lines properly unless a second double quote appears in same line.

I'll see if we can limit the imput to block any quote mark.

As this is a query function issue. Would you be able to report it to MS?

Thank you!

Lifan

Answer 5

And appologies for late reply. I thought I used business email when I registered the forum, turns out it's my personal one and haven't checked for a few days.