March 11, 2009 at 5:32 am
Is it possible in SSIS to read excel in which I want to skip some rows of excel sheet (for eg first 10 rows) then read header row(11th row) and then read data rows (12th row onwards) ?
Also after doing some processing, I need to write data in another excel sheet in same format as the first one.
:rolleyes:
March 11, 2009 at 9:36 am
Sachi
Do not know if this work in SSIS, but when using OPENROWSET I can start either reading or writing by using:
'SELECT * FROM [Sheet1$a4:c]')
Sheet1$ - sheet name
a4:c - row column designation of starting Excel cell
You might find exactly what you need by reading these excellent articles:
http://www.simple-talk.com/sql/t-sql-programming/sql-server-excel-workbench/
March 16, 2009 at 6:25 pm
In BIDS in the connections manager window right click and from the contect menu select "New Flat File Connection"
In the connection Manager Click on the general tab click the "Browse" button and select the Excel file. Now at the bottom of this connection editor on the same tab there is "Header rows to skip" - Well just type in the number of rows you want to skip. Then there is a tick box on the same tab at the bottom "Column names in the first row of data now". So if you want to skip ros 1 to 6 and row 7 has headers then Rows to skip is 6 and then tick the "Column names in the first row of data now" box.
Hope this helps.
Ells
🙂
March 28, 2009 at 7:35 am
Hi Bitbucket,
Your solution didn't worked in my case... 🙁
Hi Ell's,
I tried by the way you suggested, but I am still facing problem like I am not able to retrieve columns properly as what encoding can be used for excel, it shows all squares in preview window... Also it doesn't takes column seperator or row seperator, whatever I select...??
:rolleyes:
March 30, 2009 at 1:59 am
Sachi,
would it be possible for you to attach the spreadsheet? Or just a sample? And a create statement for the table it is going into. It would make it a lot easier to help you.
Thanks.
Mark.
March 31, 2009 at 3:46 pm
You might want to look into Conditional Splitting.
Set your Excel file as a data source and pass each row through the conditional split. You can send each row of data down a different path for output to various destinations to perform other tasks or transformations.
So long as each row can be identified with a set of criteria, you can construct an expression for each path returning true or false to be sent down that path name.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply