asked on

Can SSIS package extend it's capabilities to extracts data from cells in a excel file rether than sheets level?

I have excel files (reports type data in excel sheets), I need these data to be import into SQL table. at the moment I spend most time to massage the data to reformat to extract into SSIS tranformation. This is time consuming task, is there are any machanism on SSIS component or can SSIS be extend it's capabilities by directly reading excel cell or range of excell cells and load the data into SQL table. I am thinking about solution VB scripts or C# script which can read data from the cell level of the excel worksheet. The standard way of reading data from excel source with excel's sheet level. I need to go another level down to read the range of cells and load those data into particular tables using SSIS.

Appreciate any help on this.

Thanks in Advance

ASKER CERTIFIED SOLUTION

lcohan

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

keplan

ASKER

Thanks Lcohan, but my requirment is import only specific excel cells. The excel file I mentioned on this is really a report type, it has graphs, it has tables and so on... it is hard to bring down to a SQL table and cleanse, what I really require is to import data only cell levels. So I can extract what I need.
I have VB script that can capable of specifying the cell range and create another clean excel sheet with 2 dimentional.
It is done by manually at the moment.

lcohan

Why are you not using that VB script in a SQL SSIS step prior to the step that does the import?

keplan

ASKER

Hi Lcohan,

you are right, since due to lack of VB script knowledge, I do not know how to do that. Currently I am manaully placing the cell range and run the VB script on excel, and the take that result into new excel worksheet.
I do not know how to modify that VB script to itterative, to cpature the range of the excel cell.

Any help on this would be appreciate

thanks,
manjula

keplan

ASKER

i have added the VB script for your reference, I need to modify this script to capture range of different excel tables in a single excel sheet.
MergSheetsVBScirpt-C.txt

lcohan

I have to admit my VB skills are way behind SQL so anyone else that can help with the VB script???
I suggest you add VB scripting to the question so more people can have a look at it and help.

keplan

ASKER

The solution hasn't address properly