January 19, 2015 at 11:01 am
I have a package that reads in all of the sheets of an Excel spreadsheet and puts them into a table.
In one case where there was a single sheet, every record was being duplicated.
What I finally figured out through debugging is that it was first processing a table _xln#_FilterDatabase and then a table Sheet1$. Sheet1 was the only sheet on the table.
In another case (same program), I was getting the error 'Column "F3" cannot be found at the datasource.' In this case, the filtered area only covered 2 of the columns.
The cause was the same. The first table it processed was _xln#_FilterDatabase.
Even if I removed the filter, I would get the same results. So, evidently the filtered area remained in the spreadsheet.
I got around this by putting in a check that the table name must have $ in it. Otherwise, I'll skip that particular table.
What I'm wondering is if there is a better way to make this check. For instance, is there a way to tell what type of table I'm processing?
Also, does Excel have any other types of tables other than sheets that might have a $ in them?
I've tried looking for something on internals of how Excel is used in SSIS and haven't been successful. There's a lot of step-by-step instructions which have been very helpful, but nothing that contains the behind-the-scene gotcha's like this. If anyone knows of some documentation on this, please point me to it.
January 19, 2015 at 1:25 pm
I have run against the same issue with filter tables in Excel. In the end I filtered them out just like you did.
Regarding your question about other kind of tables in Excel that can have a $ in their name: I believe named ranges can have it too (not 100% sure).
I do know you can specify ranges when importing Excel with SSIS:
SELECT * FROM [sheet1$A1:B10]
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
January 19, 2015 at 4:09 pm
Koen Verbeeck (1/19/2015)
I have run against the same issue with filter tables in Excel. In the end I filtered them out just like you did.
With your greater expertise, it's reassuring to know that you solved it the same way.
Regarding your question about other kind of tables in Excel that can have a $ in their name: I believe named ranges can have it too (not 100% sure).
I do know you can specify ranges when importing Excel with SSIS:
SELECT * FROM [sheet1$A1:B10]
Unfortunately, I can't count on the sheet name being the same from one time to the next. Otherwise, that would be one way to solve it.
Thanks for taking the time to respond to this. I really appreciate it.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply