Avatar of keplan
keplanFlag for Australia

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
Microsoft SQL Server 2008Microsoft SQL Server 2005

Avatar of undefined
Last Comment
keplan
ASKER CERTIFIED SOLUTION
Avatar of lcohan
lcohan
Flag of Canada image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Avatar of keplan
keplan
Flag of Australia image

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.
Avatar of lcohan
lcohan
Flag of Canada image

Why are you not using that VB script in a SQL SSIS step prior to the step that does the import?
Avatar of keplan
keplan
Flag of Australia image

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
Avatar of keplan
keplan
Flag of Australia image

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
Avatar of lcohan
lcohan
Flag of Canada image

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.
Avatar of keplan
keplan
Flag of Australia image

ASKER

The solution hasn't address properly
Microsoft SQL Server 2005
Microsoft SQL Server 2005

Microsoft SQL Server 2005 is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning. It includes support for managing XML data and allows a database server to be exposed over web services using Tabular Data Stream (TDS) packets encapsulated within SOAP (protocol) requests.

72K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo