Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
brubakerbr
Yak Posting Veteran
53 Posts |
 Posted - 2014-03-06 : 13:55:55
|
| 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. BrubakerBusiness Intelligence AnalystViega LLC |
|
|
|
|
|
|
|
Capture Excel Data with Multiple Spreadsheets (dat