March 14, 2012 at 1:12 pm
I receive daily excel 2003 workbooks from my company's main customer to load into a sql server 2008 database r2 standard edition. Right now I am manually copying the data from the worksheet I need to work with and passing the data to entirely to new worksheet in a new workbook.
My company says that I can not ask the user to change the way we receive the data from them. The current method is going to stay.
I now want to be able to automate the process of obtaining the data from these workbooks.
However I am having the following problems I need to work with and I am wondering if you have any suggestions how to to automate the following steps:
1. There are lots of tabs in the workbook and I always need to go to worksheet in the middle of the workbook to obtain the nformation I am looking for.
2. There are hidden columns that I always have to unhide.
3. For your information, the worksheets are grouped together. However I do not need to ungroup the worksheets to obtain the data I need to work with.
What way would you suggest I follow to accomplish this goal? Can you tell me and/or point me to a reference that will show me how to accomplish this goal?
I think an SSIS package would solve this problem but I have no experience working with SSIS packages. Thus can you tell me what you would suggest?
March 20, 2012 at 5:48 am
Hi Wendy,
You can extract data from an Excel spreadsheet using SSIS but I've had some issues in the past, especially when the number of columns was over 255, as Excel 2007 let you have more columns (I can't remember if Excel 2003 let you), but there was no way to bring the columns in to SSIS as the drivers didn't support it.
Does the Tab in Excel that you need to open remain the same, or does this change each month?
Open up SSIS and have a play!
Graham
March 20, 2012 at 12:45 pm
In answer to your question,"Does the Tab in Excel that you need to open remain the same, or does this change each month?", the data will change everytime the SSIS package is executed?
Can you answer the following for me:
1. How can I get SSIS to go to the exact tab I am looking for each month?
2. When I obtain the workbook from the user where there are columns that are hidden, do I need to unhide those values for SSIS to work? If so, can you tell me how to unhide the values in the excel workbook that I need to use?
March 20, 2012 at 5:42 pm
wendy elizabeth (3/14/2012)
I receive daily excel 2003 workbooks from my company's main customer to load into a sql server 2008 database r2 standard edition. Right now I am manually copying the data from the worksheet I need to work with and passing the data to entirely to new worksheet in a new workbook.
You don't need SSIS for this. If the goal is to transfer data from one spreadsheet to another, just write a macro that will import the spreadsheet from the other company into an open sheet on your final destination and have the macro copy the data for you.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 20, 2012 at 8:41 pm
My goal is to transfer data from a selected tab in a workbook and transfer the data to sql server. I wangt to use ssis since that is what it's purpose is.
Thus can you tel me how to use SSIS to obtain data from a selected worksheet in a workbook? To accomplish this goal, I may need to unhide data columns and remove external links in the excel workbook.
March 20, 2012 at 9:00 pm
To load data from excel, you can use a script task written in .net using excel application component. This way you can use all features of excell, and read data, even if some of the columns are hided.
The other easy way is to use macro as Jeff said and copy data to a new file as a plain excel into a required format and then use the new file with SSIS to load data into sql server.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply