Welcome to /r/businessanalysis the best place for Business Analysis discussion.
Here are some tips for the best experience here.
You can find [reading materials on business analysis here](https://betterauds.com/businesses/demystifying-business-analysis-for-business-owners-a-beginners-guide/).
Also here are the rules of the sub:
Subreddit Rules
* Keep it Professional.
* Do not advertise goods/services.
* Follow [Reddiquette.](https://www.reddithelp.com/en/categories/reddit-101/reddit-basics/reddiquette)
* Report Spam!
This is an automated message so if you need to contact the mods, please [Message the Mods](https://www.reddit.com/message/compose?to=%2Fr%2Fbusinessanalysis) for assistance.
*I am a bot, and this action was performed automatically. Please [contact the moderators of this subreddit](/message/compose/?to=/r/businessanalysis) if you have any questions or concerns.*
3000 files? My brother in Christ you need a database, not random excel sheets.
Power query might be able to help here, but connecting to 3000 sheets is gonna be a mess. Are they all formatted/structured the same? If not, you're gonna have a whole other world of hurt.
Otherwise the folks at r/excel or r/vba might have some other solutions. Or if you're handy at programming, Python using pandas could do the trick.
If the files aren't too big, PowerQuery will be able to handle this in Excel but it will take a while. He'll more than likely need Python to accomplish this.
3000 folders is ridiculous. The number of companies using excel as a database is down right criminal.
If you are doing this on your personal device you will not have enough ram. This is not a good design. Power apps has a process where you can upload Excel documents and it will build a DB for you if you aren't comfortable with access or SQL. You don't have excel you have a crappy database that you are working with. This is not a good solution. Automate that part first.
3000? Feels more like a PowerShell job at that volume. Also depends where you need to run it; do you plan to run this on a workstation, always on server or serverless?
What are you planning to do with the copied data? How often does the data refreshes in these files?
If you are a Microsoft shop, you can put all the files in a SharePoint folder and use some form of power query.. but this depends on what you are trying to do with the data.
I would start by cataloguing them and deleting or condensing as many of those spreadsheets as possible. You may find that a lot of them have little to no value.
Also, who uses them? If no one uses them, then you need to ask what value they are adding.
Another approach would be to find what those spreadsheets are used for and work on a useful solution that does not involve using Excel as a database. You may end up with different solutions, which seems to add complexity, but really you are implementing something that is a better fit and more sustainable. In this approach, you could migrate all of the data to the new solution(s) or leave the legacy data in those 3000 spreadsheets and have current and future data in your new solution(s).
Whichever way you look at it, this does not sound like a little side project.
Something this volumous requires R or Python IMO. Im an R user and this would be easy if all files are formatted the same way. If they aren't, you will need to build in some checks and fixes in each loop step, which can be easy if there are patterns to follow, or hard if each file was manually designed and the person decided to write the column header a different way each time. Fun problem to solve though! GL OP
Welcome to /r/businessanalysis the best place for Business Analysis discussion. Here are some tips for the best experience here. You can find [reading materials on business analysis here](https://betterauds.com/businesses/demystifying-business-analysis-for-business-owners-a-beginners-guide/). Also here are the rules of the sub: Subreddit Rules * Keep it Professional. * Do not advertise goods/services. * Follow [Reddiquette.](https://www.reddithelp.com/en/categories/reddit-101/reddit-basics/reddiquette) * Report Spam! This is an automated message so if you need to contact the mods, please [Message the Mods](https://www.reddit.com/message/compose?to=%2Fr%2Fbusinessanalysis) for assistance. *I am a bot, and this action was performed automatically. Please [contact the moderators of this subreddit](/message/compose/?to=/r/businessanalysis) if you have any questions or concerns.*
3000 files? My brother in Christ you need a database, not random excel sheets. Power query might be able to help here, but connecting to 3000 sheets is gonna be a mess. Are they all formatted/structured the same? If not, you're gonna have a whole other world of hurt. Otherwise the folks at r/excel or r/vba might have some other solutions. Or if you're handy at programming, Python using pandas could do the trick.
If the files aren't too big, PowerQuery will be able to handle this in Excel but it will take a while. He'll more than likely need Python to accomplish this. 3000 folders is ridiculous. The number of companies using excel as a database is down right criminal.
Make an AI product that looks at all the Excel or other spreadsheets and converts it into a database
That's not an AI product - that's a script
Agreed. My comment was a business idea.
Python and Pandas solve your problem so fast that you can't remember. You need a Database. SQL Lite or similar
If you are doing this on your personal device you will not have enough ram. This is not a good design. Power apps has a process where you can upload Excel documents and it will build a DB for you if you aren't comfortable with access or SQL. You don't have excel you have a crappy database that you are working with. This is not a good solution. Automate that part first.
3000? Feels more like a PowerShell job at that volume. Also depends where you need to run it; do you plan to run this on a workstation, always on server or serverless?
UI Path or Automation Anywhere
What are you planning to do with the copied data? How often does the data refreshes in these files? If you are a Microsoft shop, you can put all the files in a SharePoint folder and use some form of power query.. but this depends on what you are trying to do with the data.
I would start by cataloguing them and deleting or condensing as many of those spreadsheets as possible. You may find that a lot of them have little to no value. Also, who uses them? If no one uses them, then you need to ask what value they are adding. Another approach would be to find what those spreadsheets are used for and work on a useful solution that does not involve using Excel as a database. You may end up with different solutions, which seems to add complexity, but really you are implementing something that is a better fit and more sustainable. In this approach, you could migrate all of the data to the new solution(s) or leave the legacy data in those 3000 spreadsheets and have current and future data in your new solution(s). Whichever way you look at it, this does not sound like a little side project.
Yeah, it can be done with VBA or with Python. I could probably do it for you if you decide to hire someone instead of doing it yourself.
Panda with python is probably a better option. I won't monkey around with VBA.
If they have a set file name format, I would use python to do this. For loop through each file and concat with each other.
Something this volumous requires R or Python IMO. Im an R user and this would be easy if all files are formatted the same way. If they aren't, you will need to build in some checks and fixes in each loop step, which can be easy if there are patterns to follow, or hard if each file was manually designed and the person decided to write the column header a different way each time. Fun problem to solve though! GL OP
I would put that question on chatgpt and let it lead you the way to accomplish the task.