SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 SSIS and Import/Export (2012)
 Capture Excel Data with Multiple Spreadsheets (dat
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

brubakerbr
Yak Posting Veteran

USA
53 Posts

Posted - 03/06/2014 :  13:55:55  Show Profile  Reply with Quote
This one will be a doozy so I'll explain it the best I can.

I am working on a project to create what we call a "Balanced Scorecard" for our Financials department. They update a series of spreadsheets that detail our financial information like Gross Sales, Gross Margin, Free Cash Flow, etc.

All but the "Free Cash Flow" are on one tab of a spreadsheet, the "Free Cash Flow" is on it's own tab. What I need to do is grab ONLY the totals from specific rows (Examp: Gross Sales in Row 6) and specific cells (Examp: Gross Sales total in Cell D6) and pull it into a database.

The catch to this is that the excel spreadsheets are individual (one spreadsheet for Jan, one for Feb, etc) and the rows do not stay consistent. I'm not necessarily worried about the previous months as once they are in SQL they will not change but I will need to have this package look into a folder monthly and grab the newest file and repeat the process.

What I want to do is create an SSIS package that will grab the totals from these specific cells and put them in a table. I will need to also create an expression that will time stamp the data with the Month and Year of the report (File Name has the Month and Year [2014-01 Misc File Name] in it) So I am sure there is a way to grab that and add it to the expression.

In the end I need a Table(s) that has the following information it it.

|Year|Month |Label |Actual |Budget |
|2014|January |Gross Sales|$22,456,789.65|$27,896,321.45|
|2014|February|Gross Sales|$24,456,321.89|$25,987,456.12|

I would assume I would need a table for each Line Total (please correct me if I'm wrong and making this over complicated).

I am not familiar with Powershell, C# or Javascript, I have mainly worked with T-SQL and SSIS packages.

Thank you for any help you can provide.

Brian D. Brubaker
Business Intelligence Analyst
Viega LLC
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000