Transform Ugly Data with Excel

Sultix

New Member
Joined
Mar 28, 2007
Messages
23
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 ?
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
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..
 

Attachments

  • 2022_11_29_12_19_48_SAMPLE2_Editor.png
    2022_11_29_12_19_48_SAMPLE2_Editor.png
    37.2 KB · Views: 43
Upvote 0
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.

1669873598648.png


Let me know how it goes.
 
Upvote 0
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.
 
Upvote 0
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
Rich (BB code):
Sample txt
 
Upvote 0

Forum statistics

Threads
1,223,989
Messages
6,175,808
Members
452,670
Latest member
nogarth

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top