Transform Ugly Data with Excel

[Sultix]

Hi,

i have a weird text file.. I tried various ways to convert to readable excel file.

if i use Data / Txt to column function.. it only reads 22 Columns with headers.

If i use Power Query it only reads one or two column .. if i remove headers it still does not separate the column with | to 45 columns but is able to recognize 22 columns.

First 3 lines (rows) have headers with separating | for the column
next 3 rows are the data for the headers also separating with |. It should be separatable.

I do not understand why excel does not recognize.. recognize only first line (row) as headers, but ignores the second and third line (rows) column headers.

Apparently Datawatch Monarch is able to recognize it which i cant afford. Is there a way how to transform data which has three lines of column headers in text file and the dataset 3 lines (rows) for the column headers

I have tried access aswell, power query, power pivot .. nothing seems to be working. Is there any other ways or am i doing the wrong way ?

 

[Dan_W]

Would it be possible to see the text file?

 

[Sultix]

sure, i have reduced to three entries. i have anonymized certain informations. this is how it looks.. first 3 lines are headers, next three are rows as data for the columns headers

Lines with TX can be skipped..

 

[Dan_W]

Hi. My apologies for not responding earlier. When I looked at your attachment on the phone, I didn't then realise that it was an image and not a text file. The reason I suggested a text file because there is method called Regular Expressions, which is a way of extracting text by using patterns. I had hoped that perhaps there was an obvious pattern in the text such that we could use this approach, but it's difficult to gauge from image.

That said, just looking at it, are you certain that those are three rows in the header, and not just one row that has the word wrap feature enabled on your text viewer. If it's just the one row, that makes it easier to deal with. (It looks an awful lot like it's the one row) Also, given that each field appears to be delimited by the | symbol, you can easily transform it by using the Text To Columns feature (available in the Data Tab). This will split up text in selected cells by a given character (see screen capture below), and deposit the results in the adjacent columns.

Let me know how it goes.

 

[Sultix]

Hi Dan,
i wanted upload a txt file, but the forum disabled so that is why i uploaded an image instead.
No it has nothing to do with wrap. This is shown exactly like that in text, csv, excel, access no matter which format i use or export or transform.. with or without wrap
First three rows are headliner, somehow the source cut off into three seperate rows which should have been all in one single row. That would make it easier to convert.
Same goes with the remaining rows where the data for headliners are all seperated to 3 rows aswell.
The List has actually over 60k rows, it makes impossible to edit manually.

 

[GraH]

The picture does not really help since we de not see what's going on when we try steps out.
The data looks to be structured in partitions and sub sections. Once you see the pattern, but here must be one, it becomes do-able.
Can't you past a few sanitized in a text code block? Like below

Sample txt