Share via

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

Anonymous
2019-09-16T02:29:52+00:00

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]

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments
Answer accepted by question author
  1. Anonymous
    2019-09-16T05:44:15+00:00

    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

    1 person found this answer helpful.
    0 comments

12 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2019-09-23T00:55:04+00:00

    Hi Lifan,

    Glad to know that the method works for you.

    I will try to report this issue to see if the related team will fix it.

    Best Regards,

    Hugo

    0 comments
  2. Anonymous
    2019-09-23T00:57:26+00:00

    Hi Lifan,

    It's nothing.

    We also fell sorry for the inconvenience caused by this issue.

    Best Regards,

    Hugo

    0 comments
  3. Anonymous
    2019-09-23T23:15:39+00:00

    My colleague just worked out a solution, change the Open file type from csv to Text. It now opens all lines. Then just add in a split column step.

    So the problem is just with csv file type.

    0 comments
  4. Anonymous
    2019-09-24T13:47:18+00:00

    Hi Lifan,

    Thanks for sharing another solution for this issue.

    After consulting, I would like to explain that the issue (double quotes without endings in the imported txt file caused import failure) may be an expected behavior. Please refer to this article: Text Import Wizard.

    This means when we try to import text (includes .txt and .csv) to Excel, Excel will try to find another quote " to determine which format will the contents (wrapped in double quotes) displays. But it cannot find another quote " so it doesn't know how to display the contents behind the first quote ", hence the error.

    Besides, would you mind sharing the detailed steps of the solution your colleague found? Since we haven't tested import the CSV file to Excel, I'm not sure what the "change the Open file type from csv to Text" means and what did you do to change the format.

    Appreciate for your understanding.

    Best Regards,

    Hugo

    0 comments