This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
Okay. My data is trapped in a single field at import. I need to parse the text into fields. I assume a Multi Row Formula tool is needed. And, I know I can use the empty rows to distinguish between different parts or groupings of text that correspond to a given field. The empty rows can be 1-n.
The first row is the field ORGANIZATION. In the example Coffee Company.
The next row of text is the field DOCUMENT TYPE. In the example below it is News Release.
The next row of text is the field NOTICE ID. In the example below it is 10-55.
The next row of text is the field ARTICLE TITLE.
This next row of text always starts with Washington. Instead of one line there are 1-n lines of text before the next empty row. These rows need to be merged under the field INTRODUCTION.
The remaining fields of text fall under the fields. Instead of one line there may be 1-n lines of text before the next empty row. These rows need to be merged under each respective grouping as BODY 1, BODY 2, BODY 3, etc.
I'm sure someone will have a pretty elegant solution, but I'd tackle it by parsing it on the newline characters if it isn't coming in that way already. You could parse the fields by relative position to get everything except the "Body" sections because it seems like that would be the only variable number of fields. Since it's an unknown number, you could make it a macro that outputs the Body sections as 1,2,3 etc. etc. then join it back to the regular fields. You could also do it as I have below and just pivot it back. If you already have the data set up as you have below, you should get the right number of body segments, mine added more due to the /n when I saved.
Are you reports coming in one at a time or a long import? If individual, you should be able to do the above and union records together. If one long report, you're right, you'd could to do a multi-row formula to identify the start/stop of each record and could pass it through a macro doing the same parsing as above then union the results back together with a batch macro. to get rid of the -x- (if that format stays the same, you can do a Regex to identify that row and remove them or drop the last "body" segment if you when the batch macro way.
@Bdonahue Nice. But, would it be possible to attach the workflow. Otherwise I can't tell how you setup each tool. I just see the tools you used and the outcome, which appears to be exactly what I need.
@hellyars , i believe you are looking for something like the attached. it groups the blocks of text and then concatenates them into the "fields" you described, however, you missed the "On the web oursite" (line 3 in your example). I added that in as 'WEBSITE LINK' as a place holder, or you can just filter that field out.