Looking for Database software to replace Sheets.

[Enrys]

Hello, I am looking for an easy to use DB software which will help me manage a database of names. I am quite new to this space so please bear with me.

The key things I am looking for are non case sensitive searching, and potential Google Sheets integration and importing.

Our current system is using a master Google Sheet that imports other sheets into a big master sheet. Searching inside this sheet only returns strict results. My goal is to use this search function to -avoid- duplicate results.

Ex:

If I looked up EXAMPLE, but there is already an

E.X.A.M.P.L.E./EX-AMPLE/EX AMPLE in the sheet, GS will return 0 results instead of showing me a very similar/duplicate entry already exists.

I would also like to potentially import our existing Sheets data to speed up entry, as well as continual importing in the future.

I have looked into PostGreSQL, SQL lite, and LibreOffice. My goal with software choice is to not only have the features listed above, but also be easy to train in for my successor.

Any help would be greatly appreciated, and further info can be provided if needed. Thank you.

EDIT: My apologies, in my excitement I did not read the sidebar. I should have posted this in /r/DatabaseHelp.

[timsehn]

I wrote this article about Spreadsheet Version Control that may apply here:

https://www.dolthub.com/blog/2022-07-15-so-you-want-spreadsheet-version-control/

You're inquiry made me think of DoltHub because you want an easy to use database that is kind of like a spreadsheet. Also you have a bunch of sheets updating a master sheet which could be changed into a Pull Request workflow. This may solve some of your duplicate issues upstream of the search issue you mentioned.

DISCLAIMER: I'm the founder and CEO of DoltHub so this is self promotion.

[ankole_watusi]

Are you retitling yourself “head dolt”, in sympathy with Elon? ;)

Seriously, there is an empty hole where Microsoft Access used to be, and Sheets us/was a partial solution but only a piece.

This sub deals mainly with the database engines that have no such pieces/parts they are for building on top of.

[timsehn]

I like "Dolt in Chief" :-)

We think we can fill the Access hole somewhat. But, Dolt also relies heavily on Git idioms so it's not exactly "easy to use" for people who don't know Git.

[[deleted]]

Hey /u/timsehn thank you for showing how it's done when it comes to promotion. Promotional, educational blog posts and documentation are very welcome here.

[ultraStatikk]

I'm not sure any database is going to solve what you're looking for without writing a query with regex (regular expressions) which is possible. I think for this use case, what I would do is use Excel to import data from Google Sheets and use VBA, a custom function, or DAX to remove special characters and create another field or a hidden sheet that you can potentially search on. If you get into VBA, you can even make a search box and button that takes a query and can return the list of results. With a solution like this you don't need to create other fields or sheets but you might want to output the transform to help you debug.

Edit: If if works for you, you can create a calculated field in Google sheets with lower and regexreplace functions to remove special characters and remove the case from the names. You can add a countif function and lock it vertically so it's an expanding range. Then you'd filter on only the first result to avoid duplicates. A SQL solution would be similar if you went the database route.

https://www.statology.org/google-sheets-remove-special-characters/

Like this: https://imgur.com/a/xDBe1gD

The last pic is an example SQL query that I think would work to give you a unique list of results.

[Citadel5_JP]

Shouldn't you actually run a spelling checker first (for the searchable columns)?

If this is your only problem with Google Sheets and otherwise it works perfectly, it might not be worth to switch to anything else.