{"id":194,"date":"2009-06-29T05:09:00","date_gmt":"2009-06-29T05:09:00","guid":{"rendered":"https:\/\/www.inogic.com\/blog\/?p=194"},"modified":"2009-06-29T05:09:00","modified_gmt":"2009-06-29T05:09:00","slug":"reading-excel-workbooks-using-getoledbschematable-returns-a-sheet-with-_-suffix","status":"publish","type":"post","link":"https:\/\/www.inogic.com\/blog\/2009\/06\/reading-excel-workbooks-using-getoledbschematable-returns-a-sheet-with-_-suffix\/","title":{"rendered":"Reading Excel workbooks using GetOleDbSchemaTable returns a sheet with _ suffix"},"content":{"rendered":"
We have a CRM add-on for Importing Price Lists into CRM. For this tool, we expect the details to be provided in an Excel sheet. The tool reads the excel workbook provided and imports the prices.Once we had an issue with one of our customers complaining of the data successfully being imported, yet an error message is displayed ‘Sheet1$’_ not found. What was intriguing was the error could only be replicated with the sheet that are customer had provided us. If we were to create a new Excel workbook and provide it for import, we would receive no errors \ud83d\ude41 It was a classic example of “it works on my machine…” )
\nAfter researching into this issue further, we found the reason.
\nThe tool used the following line of code to read all sheets from the workbook
\ndtTables = objExcelConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
\nThis returned 2 data tables, one with the name ‘Sheet1$’ and the other named ‘Sheet1$’_.
\nthe first sheet ‘Sheet1$’ is the normal naming convention when you read an excel workbook. But where did the second sheet ‘Sheet1$’_ come from?? If you open the excel workbook, you will find only one sheet in the workbook named ‘Sheet1’.
\nThe second sheet ‘Sheet1$’_ is a hidden sheet that Excel creates each time you filter the records on a sheet. It will create a hidden copy of the sheet. This is the sheet that the program tries to read through the code.
\nTo fix this problem we added the following code to catch such sheets
\nif(SheetName.EndsWith(“_”))
\n{
\ndo nothing;
\n}
\nelse
\n{
\ndo operation
\n}\n<\/div>\n","protected":false},"excerpt":{"rendered":"

We have a CRM add-on for Importing Price Lists into CRM. For this tool, we expect the details to be provided in an Excel sheet. The tool reads the excel workbook provided and imports the prices.Once we had an issue with one of our customers complaining of the data successfully being imported, yet an error\u2026 Read More »<\/a><\/span><\/p>\n","protected":false},"author":13,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[19],"tags":[70,752,1850],"class_list":["post-194","post","type-post","status-publish","format-standard","hentry","category-dynamics-crm","tag-sheet_","tag-excel","tag-worksheet-duplicate"],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/www.inogic.com\/blog\/wp-json\/wp\/v2\/posts\/194","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.inogic.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.inogic.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.inogic.com\/blog\/wp-json\/wp\/v2\/users\/13"}],"replies":[{"embeddable":true,"href":"https:\/\/www.inogic.com\/blog\/wp-json\/wp\/v2\/comments?post=194"}],"version-history":[{"count":0,"href":"https:\/\/www.inogic.com\/blog\/wp-json\/wp\/v2\/posts\/194\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.inogic.com\/blog\/wp-json\/wp\/v2\/media?parent=194"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.inogic.com\/blog\/wp-json\/wp\/v2\/categories?post=194"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.inogic.com\/blog\/wp-json\/wp\/v2\/tags?post=194"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}