1
$\begingroup$

I need to merge data from 1000s of excel files provided by different operations managers on productivity and other reports. The excel files have similarity of data but the headers are all custom since being different manager and different clients.

For example manager A will have a.xlx and manager b will have a.xlx, but the headers for each will be different though the data inside will be usually the same. Each day 100 different excel files are updated by all team members via new files e.g a_todays_date.xlx and manager b using /a.xlx.

Is this something that can be handled via python ML libraries.

What is the best way to merge all these data and save to the DB on a per day basis. The average data would per day would be around 15GB. The end goal is to create dashboard.

Improve this question
$\endgroup$
2
  • 1
    $\begingroup$ Sometime the most efficient thing to do is review the processes in the company. You should standardize the column names which are the same across different managers. Then, you can simply merge the data which is exclusive to a few managers all together. This will yield one database with everyone's entries. Better yet, Excel should not be used for this. You should set up a server with a database. Then each manager should have a GUI that helps him enter in the data in a user friendly way, you can then manipulate these entries and put them into the database as you wish. All automated. Easy Peasy. $\endgroup$ Commented Jul 11, 2017 at 18:36
  • $\begingroup$ Thank you, I suggested standardized column names, but they are apprehensive about it and don't want to change for various business reason. So i am stuck with large number of excel files with different column headers and trying to see how to work with them. $\endgroup$ Commented Jul 12, 2017 at 5:30

1 Answer 1

Reset to default
1
$\begingroup$

I suggest SSIS (SQL Server Integration Services) . It is designed for handling collecting data from different sources and export to database. You can design a simple data flow and run it from SQL serevr on schedule (daily in your case)

Improve this answer
$\endgroup$
0

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.