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

keplan
keplan used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
View Solution Only
Database Analyst
Commented:
One option would be to read the Excel sheets into SQL tables and go from there:

Please have a look at my solution below hopefully it helps you as well:

http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL_Server_2008/Q_26828432.html?cid=1131#a34918553
keplan

Author

Commented:
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.
lcohanDatabase Analyst

Commented:
Why are you not using that VB script in a SQL SSIS step prior to the step that does the import?
Acronis in Gartner 2019 MQ for datacenter backup

It is an honor to be featured in Gartner 2019 Magic Quadrant for Datacenter Backup and Recovery Solutions. Gartner’s MQ sets a high standard and earning a place on their grid is a great affirmation that Acronis is delivering on our mission to protect all data, apps, and systems.

Access the MQ here
keplan

Author

Commented:
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

Author

Commented:
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
lcohanDatabase Analyst

Commented:
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

Author

Commented:
The solution hasn't address properly

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial