Extract data from multiple Excel sheets

Question

I am relatively new to MySQL and VBA, I have 12 Excel sheets each has over 85000 rows and 18 columns , generally these sheets describe Gross sales, profit and volume of sales for each product of Telecom service provider .

I would like rearrange columns of gross sales and profit for each product in descending order and extract top 30 products and total amount of sales in each quarter and net profit.

I would like to know if MySQL or VB Script is efficient to handle such a large data base ?.

I good at c++,matlab programming , i have never tried MySQL or any relational database programming before, kindly please suggest me ways to approach this question

What is the significance of the 12 worksheets? Are they monthly reports that list every sale including product name, gross sales, profit and volume? Are you trying to create a summary page that lists each product and it's sales per quarter? — , Aug 12 '16 at 12:08
If this is just a yearly report I'd wouldn't bother with a database. It would take about a minute to process the information using VBA. — , Aug 12 '16 at 12:19

score 2 · Accepted Answer · edited May 23 '17 at 11:45

2

I don't think 12*18*85,000 = 18,360,000 should be a problem for a relational database.

Those tasks you discribe should be quite simple with SQL statements (select, group by, order by).

See:

edited May 23 '17 at 11:45

Community

1
1

answered Aug 12 '16 at 11:46

Ulli Schmid

1,167
1
8
16

Hello, Thanks for your reply,I am using Ubuntu and i am using wine how could i install MySQL in wine – DevanDevak Aug 12 '16 at 11:52
@Devan You may want to do a tutorial or a course such as this: https://www.codecademy.com/learn/learn-sql – Ulli Schmid Aug 12 '16 at 11:58
Also could you please provide me some brief template how i should i create a SQL query. – DevanDevak Aug 12 '16 at 12:08
Do a tutorial. You will probably need SQL in the future. – Ulli Schmid Aug 12 '16 at 12:10
Yes, I am learning through the tutorial, Is there any way to load the EXCEL into MySQL and create a Database first – DevanDevak Aug 12 '16 at 12:23
http://stackoverflow.com/questions/1310166/how-to-import-an-excel-file-in-to-a-mysql-database – Ulli Schmid Aug 12 '16 at 12:34
1

You might want to look at Microsoft Powerquery as an option too. – Gordon K Aug 12 '16 at 12:43
I am using ubuntu, how can access microsoft powerquery in ubuntu ? – DevanDevak Aug 12 '16 at 16:16
PowerQuery is a Microsoft Excel plugin. – Ulli Schmid Aug 12 '16 at 16:20
I use Ubuntu 14.04 and i have Libreoffice , I have wine – DevanDevak Aug 12 '16 at 16:24

Extract data from multiple Excel sheets

1 Answers1