Importing non-hidden rows from Excel?

I have a need to import only non-hidden rows from an excel spreadsheet. When I create the package in SSIS, it imports everything. Due to the use of the data on the spreadsheet, we cannot simply delete the data.

Is there a special setting in the Excel Source or Connection manager that can be set to "only import non-hidden rows"?

Also, how do I go about setting the sheet with an index instead of the actual Sheet name? The user changes the sheet name at random, but I know I only need the first two sheets on the file.

Thanks!

Matt Michuta

No, there is no setting like this. You at the mercy of the Excel OLE DB Provider here and it doesn't possess functionality like you are requesting. You will have to filter those rows out in the pipeline.

Not sure about the sheet index problem - I don't ever use the Excel provider. I suspect you can't do that either. Perhaps try the data access forum if no-one here knows: https://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=87&SiteID=1

-Jamie

|||You should be able to do this with a little bit of creative vba coding in excel. You can write a macro that will copy all the data to a sheet that has a static name and link the action to an event or button. Since you can use sheet indexes and row properties in vba you can get exactly what you need, and since the sheet you're copying to has a static name you can use the excel source in SSIS to import it.

Importing Data From Excel or Access

Hello,

I am trying to import a table from Access and/or Excel. I have attempted with both programs. I get errors possibly due to some bad data over the years. Is it possible to tell SQL Server to import a table and discard any errors? or is there a way to scrub the data before importing to make sure all possible causes of errors are corrected or addressed?

Hello my friend,

I would clean the data before attempting an import. Deleting data that you know is bad is quicker than trying to clean it, every record older than 3 years for instance not fitting in with new structure. I cannot recommend anything else without seeing the structure or a sample of the data. Good luck.

Kind regards

Scotty

|||

Hi,

You can try to use DTS or SSIS to import data. But it is not contained in the Express edition.

|||

I agree with Scotty. Ensure the import data fits the existing structure and rules within your database before attempting to import it. Anything that does not conform should be deleted. Only import good data that passes whatever test you set it.