March 31, 2014 at 3:02 pm
Here's my situation...
I'm working on a project for a client (I'm a newbie SSIS user, but have about 20 yrs. of SQL Server experience... just not with SSIS until now) and I'll be looping through various client files (Excel files) that they receive via a Sharefile location. The Sharefile location syncs automatically down to the client's local desktop.
I've implemented some test code thus far that loops through each client's Excel files and moves them initially out of the original location and into another folder location to indicate that the files are ready to be processed. The filenames of each client's exported file are random and can change from export to export each week/month, and the names of the first worksheet (there's only one worksheet in each Excel file) can be random from export to export each week/month as well.
What I'd like to do is to:
(1) Move all of the files that are found within the specified folders to a new location as indicated above to indicate that files have been received and they're ready to be processed (this file movement portion is already in place and accounts just fine for the random file names from each client's exported Excel files).
(2) *** Here's the piece I'm trying to figure out ***... I can either loop through the now-moved Excel files and find each file's first worksheet and rename each file's first worksheet to something constant and save each file before I continue on with the remaining work, or I can just allow for the sheets to all be named differently in the now-moved Excel files and deal with retrieving data from randomly-named sheets on the fly as I loop through each Excel file.
It would seem to be easiest within SSIS if I could setup some sort of task (not sure if I'm calling "task" by the right name or if it's technically something else I'm trying to do) to loop back through the newly-moved files once they're all out of the original Sharefile location to the new location and rename each file's first worksheet to something standard. So let's say a file from a client is called "ABCCompany.xls" and they've randomly named their only worksheet within the file to "PayrollInfo". I could have the SSIS package code find that first worksheet and rename it to "Sheet1" and save the edited Excel file. Then the continued loop through the folders would possibly find another file, and we'll say it's named "XYZPayroll" with its first and only worksheet randomly named "Info", and the SSIS code would automatically rename the "Info" worksheet again to my standard name of "Sheet1" and so on for each file.
That way going forward, I'd at least know that as I looped through the randomly-named Excel files to do stuff to the data in them, the 1st worksheet (and actually the only worksheet present in each file) would be named consistently as "Sheet1".
Any suggestions / examples / links anyone can provide on how to do this would be greatly appreciated.
March 31, 2014 at 9:35 pm
I'm not aware of any SSIS to do it, however, if the machine that will do the processing has Excel installed you can use some simple PowerShell to automate opening each spreadsheet, renaming the worksheet, and closing/saving it again. For example with Office 365 University installed and nothing else I can construct a workbook and iterate the sheets like this...
Add-Type -AssemblyName Microsoft.Office.Interop.Excel
$excel = New-Object -ComObject "Excel.Application"
$excel.Visible = $true
$workbook = $excel.Workbooks.Add()
$worksheet = $workbook.Sheets.Add()
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($worksheet) | Out-Null
foreach($sheetNumber in 1..4) {
$worksheet = $workbook.Sheets.Item("Sheet" + $sheetNumber)
$worksheet.Activate()
In your case it will be something very similar. I'd give it a shot.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply