[SOLVED] Modifying data already in the database?

Hi all,

So, we are building an app to get out of previous SaaS that will hopefully work better. One of the steps was to import the data from our SaaS into the our bubble app. This has been done, but there is a problem with the data; many of the ‘types’ are clumsy. So, for example, addresses are one ‘field’ and written out such as ’ 123 main st// sometown// state// zip’.

What I want to be able to do is to parse all of these addresses, phone numbers, names, etc, into their own ‘field’/column in the database. Using the example above, I would like to make each bit of data in between the slashes have it’s own column such as ‘street number’ ‘rm number’ ‘city’ ‘state’ ‘zip’, without having to reenter all this info. Especially having this info still associated with the same use?

Is this possible with a work flow?

If your data from your previous app is saved to a CSV, you can open that in Excel and split your single Address Column into multiple columns using the Text to Columns option. Then save the new CSV and upload that to Bubble. Here’s a tutorial on that option in Excel:

I just tested this here:

After its split it will look like this:

Before you upload the new CSV as app data, make sure the fields for this are setup in Bubble within the User Data Type. After the CSV is uploaded, it will allow you to connect the Excel Columns to the new fields. :slight_smile:

Right, I got it. That totally makes sense! Had no idea excel had that kind of functionality. Totally need to try it out.

However, will it matter if all the addresses aren’t the same format? For example, some may have just a street number or everything but a zip.

Hmm I’m not 100% sure; Excel will separate columns based upon what you set as the delimiter (so in the example I used the //), which tells Excel start a new column at every /.

I think that could be a problem if a User only has a City for example, and nothing really needs to be divided, then City would go into the first column which is where Street Numbers are supposed to go (I think?). Unless the formatting for that single field was

“(empty Street) // Sometown // (empty State) // (empty Zip)” That would keep columns consistent because you’re still separating based on //.

2 Likes

Even if that is the case, those are much fewer. I can remove those and take care of them separately, and have excel do the bulk of the rest.

2 Likes

Yeah, that is exactly how it worked. I had to manually edit a few. But the vast majority was done by the computer. Took about an hour over all, but that is soo much better than an all day thing that it would have been otherwise.

Thank you so much for this!

2 Likes

Awesome!! My pleasure! :blush: Glad to know it saved you some time!

1 Like

But how would you do it if your data was already in Bubble? I have a workflow that imports files that have a specific naming convention where each segment of the file name means something else. Example: SEN716-D-PCWR-REF-42-IFC. Currently I modify that string for searching purposes into SEN716 D PCWR REF 42 IFC. However, I now also need to break out each segment of that file name into a different field for further evaluation.

How would that be done?

Thanks,
George

@gnelson I have not tried that before in Bubble, but I think it may be possible with regex, to capture the different parts of the file name before and after certain hyphens. I will try to see if I can find anything that would let you do that!

1 Like

Thank you very much!

1 Like

No problem! :slight_smile: Nigel’s post here has the correct regex to use:

I did a quick example with the file name example you gave, which first replaces the hyphens in the string with spaces, extracts it with regex in a multiline input, and then displays each segment in a repeating group:

Editor:

Preview:

If you’d like to save different parts of that file name to different fields, you could reference the parts using ‘item #’ from the repeating group. Is this what you were trying to set up?

1 Like

Thank you, and yes that’s exactly what I’m trying to set up. I’ll give it a try and report back.

George

1 Like

Hello Faye,

Using your example I was able to accomplish what I needed. My “solution” seems convoluted to me, but it worked perfectly. I needed to separate all this out during the API workflow I use to upload these files in bulk. Later when I have the time I will follow up with what I did in case it helps someone else, but also to find out a more efficient process.

Thanks,
George

1 Like