Regrouping Delimited Data

Danielle Arteaga

I pull data from an internal system and add it to Smartsheet via Data Uploader. When it comes in, it looks like the above, with Biz Entity, Sub Business Entity and Product Family grouped as delimited data. However, I want to regroup it so that each Biz Entity/Sub Business Entity/Product Family group is together in a single cell - like this:

Data Center Networking-NX9K-APIC

This has turned into a nightmare scenario where I am exporting the data from the internal system to Excel, converting the delimited data to columns, trimming the data to remove leading spaces, then regrouping it using TEXTJOIN before running it through Data Uploader. The result is adding dozens of otherwise useless columns to the sheet, and I have to believe there is an easier way to do this, but I'm scratching my head. Any ideas?

Bee Ng

Hi @Danielle Arteaga ,

It sounds like you may want to take a different approach to solve this problem.

First, I would suggest seeing if you could get the data from the source system in the format that you need it. I know for many systems, there is an option to create reports, like we can in Smartsheet, and specify the columns you want to include, or even prep and merge fields together before exporting from the source system.

If that’s not possible, then a second option may be to do all your pre-processing in Excel. This is the option that I use most. In many cases you can use standard Excel functions to achieve the format of the data you need, and you can exclude unwanted columns in the process, so that you end up with only the one column of data that is needed, in your case.

One of the capabilities in Excel that can help with this pre-processing is “Get & Transform”. It is super powerful and can easily take source data and convert, reformat, merge, filter, etc. the data into the shape you want it to be. Then you can save that process so you can invoke it at any time.

Trying to do this in Smartsheet will be challenging.

Hope this helps.

Bee

Business Manager, AcuWorkflow

https://www.acuworkflow.com/