Click here to Skip to main content
15,918,123 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
i have a excel sheet which has no fixed format which has first 2 rows blank.
then third row has header name, then again its blank up to 5th row. starting from 6th row it has data till the row where cell has /////(5 forward slash., here onward every cell contains header left to the : and value right to the : like(header: value) and it goes till next 5 forward slashes /////.

row 1
row 2(both r blank)
head1	head2	head3	head4 (table one)
			
			
data	data	data	data
data	data	data	data
data	data	data	data
data	data	data	data
data	data	data	data
data	data	data	data
data	data	data	data
data	data	data	data

/////	(table 2)		
head: data		
head: data			
head: data			
head: data			
head: data			
/////			

the excel files looks like this(white spaces are blank cells).

these are 2 different tables in same sheet. i need to import there 2 tables into 2 different sql tables using ssis packages. please help me out.
Posted
Updated 11-May-14 1:49am
v3

1 solution

If you know the range where data are stored, you can define it in SQL query:

SQL
SELECT *
FROM [Sheet1$A6:D20]


Please, refer these:
Excel Connection Manager[^]
Processing Excel files in SSIS 2008 – Lessons Learned[^]
http://www.connectionstrings.com/excel/[^]
Accessing Microsoft Office Data from .NET Applications[^]
 
Share this answer
 
Comments
Pranav_ 12-May-14 0:26am    
Maciej Los: the problem is that range is not fixed. it starts from 6th row and ends up at the row before /////(5 forward slashes).
Maciej Los 12-May-14 2:41am    
If you had read related articles, you'll find this one: HOW TO: Retrieve Meta Data from Excel by Using GetOleDbSchemaTable in Visual C# .NET[^].
It's possible to get the range occupied by tabular data.
Pranav_ 12-May-14 7:44am    
bro u are awesome, but I am looking for a SSIS solution in BIDS

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900