Forum Discussion
How to extract unstructured data into excel
So I have data taken from a website and want to process it using Excel but I only have a .txt file. Here is the data like this. I've tried using the WRAPPROWS function but that doesn't work. Any suggestion? There is a condition where specific rows are empty, this makes other data messy.
Challenge is sometimes a Problem record consists of 6 rows, sometimes 7
In attached file a Power Query approach. In the Source step replace the file path (line File.Contents) to the text file with yours:Source = Csv.Document( File.Contents("X:\Abc\Def\ProblemsSample.txt"), [Delimiter="#(lf)", Columns=1, QuoteStyle=QuoteStyle.None] )
- What happens when you you copy the URL of that page and then open Excel and click the From Web button on the Data tab and paste the URL in the box?
- Unfortunately I can't access the website, I can only process the data provided in the form of a txt file
- Right. Does the sire offer any other formats for exporting perhaps?
Challenge is sometimes a Problem record consists of 6 rows, sometimes 7
In attached file a Power Query approach. In the Source step replace the file path (line File.Contents) to the text file with yours:Source = Csv.Document( File.Contents("X:\Abc\Def\ProblemsSample.txt"), [Delimiter="#(lf)", Columns=1, QuoteStyle=QuoteStyle.None] )- Thank you for your help, it is very helpful
I have an additional question, what if the data has one additional column? I tried to change the existing script, but it gave an error. Can you help me?
Attached query seems good... Don't forget to update line File.Contents in the Source step
