Help with extracting data from multi-line text in attribute table

202
5
10-08-2019 08:59 AM
Highlighted
New Contributor III

Any chance anyone knows a way to reverse engineer the multi-lines text in a single record of an attribute table into multiple features with multiple fields?.  It looks like they are trying to do a many to one relationship by listing many records and dates (sometimes other attributes) in a single attribute value of "comments."  I am trying to extract each record and all the different fields. It is almost like they embedded a pivot table.  The added complication is that in the same "comments" field they also have single record data with many different fields and the UniqueID is not in the comment field.  This started as a shapefile and is not a file geodatabase.  The original data creation is unknown.

Reply
0 Kudos
5 Replies
Highlighted
Regular Contributor

Although Excel in the wrong hands is uniquely qualified to create such messes, can you put it back in Excel to do at least some of the fixes? Look into Excel Text to Data to split fields apart and Copy/Paste/Transpose to get data going down the page, not across, and Copy/Fill to fill out noncomments fields. These are great tools to know. I also see reserved words for field names. Both Z and M are enabled (which is unlikely but was a default at some point), so I think you want a completely new feature class schema also. You can fix the field names in Excel as well.

Highlighted
MVP Esteemed Contributor

What do you expect the final format to look like?  Take the first row of the first screenshot, for example?

Reply
0 Kudos
Highlighted
New Contributor III

Unfortunately this is the same dataset.  In the 1st screen shot, I need the record replicated 4x and fields for name and date created, so the same coordinate is associated with each name.  In the second screen shot I need additional fields created for the record.  Ultimately, the final schema will have all the fields are mentioned the different records.  The final dataset have a unique record for each person or unknown and contain all the data fields.  In many cases the values in the field will be blank, because there is no data available.  Here is an excel mockup

Highlighted
New Contributor III

If I sort the Comments field, I can create different datasets for each "type" of scenario incase it is easier to do different things to different areas of the data 

Reply
0 Kudos
Highlighted
MVP Esteemed Contributor

Are you trying to get rid of the new line between each of the strings?  If so see https://community.esri.com/thread/228309-where-clause-for-n

I replaced the '\n' character in a multi-line field value with an update cursor.  (Scroll down to my Feb 11 entry)

I think Dana Nolan‌ hit the nail squarely on the head with: Excel in the wrong hands is uniquely qualified to create such messes

Reply
0 Kudos