Windows Noob friendly database solution

[kartikoli]

I work in a Lead Generation industry so we generate new leads on a daily basis, My responsibility includes to cross check duplicates and make sure No old leads are sent to sales. So have to check newly generated leads against previous leads (hubspot). Since we work on google spreadsheet its almost impossible to add more than 2M leads on a sheet and expect it will be fast. Now what I've done is created 5 excel files and added data from hubspot then use vlookup formula to grab all the matching leads for given website, the team take those as actual DNC. The process is working great but I was wondering if there is any easier way as I have to check all 5 excel files and repeat the process for all of them.

 

[vivek.krishnan]

Any SQL flavour should be good enough. You will need to learn the language. But the tradeoff is worth it.

 

[ibose]

DB Browser for SQLite

sqlitebrowser.org

 

[kartikoli]

Any SQL flavour should be good enough. You will need to learn the language. But the tradeoff is worth it.

I would love to learn but sadly don't have the time, I work 7 days a week but if there is anything easy to start I would like to give it a shot

DB Browser for SQLite

Thanks, this looks promising will check in detail after my shift and watch couple of videos if available

 

[dvader]

+1 for sqlite,

You can literally start using it like excel and as you gain familiarity with it, you will eventuLly be proficient in SQL.

and since this is SQL, you can use different sql clients with it. DB Browser shared above happens to be the best opensource solution for beginners and experts alike.

 

[lockhrt999]

MS Access is made just for you.

 

[kartikoli]

MS Access is made just for you.

What would you recommend, MS Access or DB Browser? Since I'll have to learn from scratch so its better to put effort is slightly future proof considering my busy schedule. I've watched couple of videos on Youtube and DB Browser looks easy for starting though a long way to go to writing codes etc... but for now ChatGPT Zindabad

 

[lockhrt999]

What would you recommend, MS Access or DB Browser? Since I'll have to learn from scratch so its better to put effort is slightly future proof considering my busy schedule. I've watched couple of videos on Youtube and DB Browser looks easy for starting though a long way to go to writing codes etc... but for now ChatGPT Zindabad

DB browser is a client viewer for an SQL database like SQL lite. I haven't used it personally. I use a similar solution https://nocodb.com/ with another SQL db called postgresql. I wouldn't suggest any non-coder to delve into database world without learning a good deal about RDBMS and SQL. It's a huge undertaking. You might be able get started with a client tool like db browser or nocodb but at some point you'll have to maintain those databases and that's a different ball game. Databases are made for programmers. These are not an end user products.

MS Access, on the other hand, is made just your purpose. It's a database solution but made for non-programmers. It directly integrates with excel. (indirectly with google spreadsheets). Simple to learn and maintain.

 

[ibose]

MS Access, on the other hand, is made just your purpose. It's a database solution but made for non-programmers. It directly integrates with excel. (indirectly with google spreadsheets). Simple to learn and maintain.

Maintaining it is actually a nightmare especially in a distributed or multi-user environment. We had to get rid of it for all our projects to restore our sanity.

 

[lockhrt999]

Maintaining it is actually a nightmare especially in a distributed or multi-user environment. We had to get rid of it for all our projects to restore our sanity.

Maintaining MS Access in a distributed environment? How does that even work? Did you share the access db through dropbox or what? It's more of a personal solution.

 

[ibose]

Maintaining MS Access in a distributed environment? How does that even work? Did you share the access db through dropbox or what? It's more of a personal solution.

Ever heard of Sharepoint ? Also Access is used often, even now, in a corporate environment.
My point was that it is prone to failures even for single user like we found. So the OP should not be surprised if it fails to work one fine morning.

 

[kartikoli]

I tried slightly different approach and it worked. Asked ChatGPT to give me python code for doing everything what I do manually.

import os
import pandas as pd
xlsx_folder = "E:\GX-DNCs"
csv_file = "E:\GX-DNCs\DNC Check.csv"
output_csv = "MatchingRows.csv"
# Read the DNC Check.csv file
dnc_df = pd.read_csv(csv_file)
# Create an empty DataFrame to store the matching rows
matching_rows = pd.DataFrame()
# Iterate over each xlsx file in the folder
for file_name in os.listdir(xlsx_folder):
if file_name.endswith(".xlsx"):
file_path = os.path.join(xlsx_folder, file_name)

# Read the xlsx file
xlsx_df = pd.read_excel(file_path)

# Perform the matching based on the websites
merged_df = pd.merge(xlsx_df, dnc_df, left_on='Website', right_on='Website', how='inner')

# Concatenate the matching rows with the overall matching_rows DataFrame
matching_rows = pd.concat([matching_rows, merged_df], ignore_index=True)
# Save the resulting matching rows to a new CSV file
matching_rows.to_csv(output_csv, index=False)
print("Matching rows have been saved to", output_csv)

Basically check all DNC files (xlsx) and match them with the websites in a csv file to give matching rows that have websites in csv file. It worked great, Now I have 2 questions
1. The program worked great for the first time but when i tried to repeate the process using updated csv file it didn't work so I made a copy of same .py file it worked. Do I have to repeat the step everytime?
2. Is there a possibility that this approach might not work and break in future? (Its important to know that its not working rather than getting wrong output)

 

[lockhrt999]

Ever heard of Sharepoint ? Also Access is used often, even now, in a corporate environment.

Nope. I always considered it as some bloatware that came with office. Gotta check.

I suggested MS access because it's super easy to get started and it never crashed on me. Plus a full real time integration with excel is a huge bonus.

 

[dvader]

Nope. I always considered it as some bloatware that came with office. Gotta check.

I suggested MS access because it's super easy to get started and it never crashed on me. Plus a full real time integration with excel is a huge bonus.

Far from it, Sharepoint is more popular in enterprise settings. I have yet to see an MNC that doesn't use sharepoint. However, I despise Access, Excel and Sharepoint to my bones (Personal bias ofc).

@OP if SQL seems too much for you at this time, access is next best non-database database product you can use.

 

[kartikoli]

Far from it, Sharepoint is more popular in enterprise settings. I have yet to see an MNC that doesn't use sharepoint. However, I despise Access, Excel and Sharepoint to my bones (Personal bias ofc).

@OP if SQL seems too much for you at this time, access is next best non-database database product you can use.

Right now my current process is working specially after Python introduction. It does the job in a single click which is great but I would love to spend some time in learning new skills so if SQL is the way forward I should atleast try to learn and if unsuccessful then move ahead with MS Access.

 

[dvader]

Right now my current process is working specially after Python introduction. It does the job in a single click which is great but I would love to spend some time in learning new skills so if SQL is the way forward I should atleast try to learn and if unsuccessful then move ahead with MS Access.

You will not be disappointed with SQL in any shape or form. Give yourself 6 months and you will feel more comfortable writing SQL queries than anything else on the planet. There is a reason every banking database is SQL based.

And you dont have to learn entire SQL either, you can do it in pieces as and when you get time.

 

[kartikoli]

You will not be disappointed with SQL in any shape or form. Give yourself 6 months and you will feel more comfortable writing SQL queries than anything else on the planet. There is a reason every banking database is SQL based.

Any pointer where to start? My expertise is in Lead generation so the broader scope would be Big Data.

And you dont have to learn entire SQL either, you can do it in pieces as and when you get time.

Yes, learn as time permits

 

[TEUser2K1]

> My expertise is in Lead generation so the broader scope would be Big Data.

There are databases and applications specifically for Big Data, some are open source.

 

[asingh]

Your still not storing data, but just running a vlookup (or full inner join) on two files. If you do not want to aggregate then this will work. Else you need to store, and break up the data too. If you are pushing 2M inserts, SQL Db will start to stall once you cross 100M. And you will need to cleanup index everyday.

For scale look at Snowflake/and aws S3. They integrate well, use Python as the glue.

 

[greenhorn]

I've done this on powerBI desktop. Slightly different use case - we had a CRM that was buggy and the customer master import would fail or (or worse write incomplete data)
had to export the customer master, compare with the excel file that had been imported, identify the gaps and reload them. Getting the data out was a challenge if the volume was too high, but again, there are tools.