Text file split

[mwm39]

Hi All,

I have Text files with multiple segments with headings. For example General Value,  List of Stops, List of Events etc. Pls check attached file. 

1. is there a way to seperate every segmement from each file and append them in seperate files?

For example lets say I have 10 TXT files , each File has segemtns and I want to extract similar segments from each TXT file and combine in Excel format. Hope it is clearly explained. If not please let me know and will try to add few more images. Thanks 

 

All above are in one Text file 

[christinepayton]

Use the SharePoint folder connector if you're trying to combine files. These look like tab-delimited text files, so I would try splitting on tab and see if that does it.

[JoeBarry]

Hi @mwm39 

I don't work with text files that much, but I think Power BI splits everything into columns by delimiter. Here would be my work around, there is probably better resolutions.

As the data looks quite different in each segment, I would consider splitting these into four seperate queries. 

if this will an onrunning process and the name of the segments wont change going forward. Please save the file and and future files to a shared SharePoint folder(No personal folders, if you leave it's difficult for people to get access to a personal folder). 

• Create SharePoint folder and name it as you wish
• Add one text file to the folder. If you have more, add them after you finish everything below. 
• Rename the Text with the Name of the data and the date it was extracted. You can use the date later to define when the data was valid
• Copy the SharePoint site URL. it will look like this https://mycompany.sharepoint.com/sites/Data
• Connect to the Site using the SharePoint folder connector add the above URL
• Choose Transform the Data
• Filter far right column the folder URL
• You should see that there is 1 file that you added earlier
• I believe there should be a Binary colum. Click on the two arrows to download the file
• Here you are going to make another copies of the queries. On the left, click on the query name and Control & C, Then Control and V. Rename them.
• Rename the queries to General, Stops, Events, Rejected
• Your file will load, it should split everything into columns (I can't say exactly how it will, as I have no data to work from)
• As mentioned we need four sperate queries, will create the first General.
• Remove the first Rows until the first GEN is in the first row, in your case it looks like two Rows
• Rename the columns to something that makes sense
• Filter the GEN column, just to show GEN
• Make transformations that you need in the columns
• There should be a Source.Name column, here the date is in there. Extract the date  Transform tab > Extract, choose the delimter to extract from and convert to date
• Repeat the above in the other queries for the other segments, Filter by EVN for Events, STP for Stop and REJ for Reject.

Later when you add the other Text files to the SharePoint list, Power BI will automatically transform the files.

Thanks

Joe

If this post helps, then please Accept it as the solution