VB.NET - Get Data to a Datatable from multiple excel sources

Question

I've 2 excel sheets. One has the asset mapping (assets tagged to each employee) & the other has the organization head count (employee ID & the Name of all the valid employees). Now I want to check whether all the assets are tagged to a proper associate (i.e., employees tagged to a asset should be currently working in the organization), if not, I need to get the details of assets that are tagged to invalid users. For this I need to have a join condition from the 2 excel sheets & should load the result to a datatable (which I can latter show in a datagrid view or use to compose a mail to senior leadership).

So, I want to load the datatable from these 2 excel sources, with below similar query,

select a.[Asset ID], a.[EmpNo] from [AssetMapping$] as a 
Where a.[EmpNo] Not IN (select b.[EmpID] from [EmployeeDetails$] as b)

However I am not able to refer two excel sheets while loading data to a datatable (but I can do so if the data are present in two different sheets within same excel, which is not I am looking for).

Kindly help me in getting the required data from 2 different excel sheets to a single datatable.

Thanks in advance.

I think a way is to manually add rows and content to the datatable using a loop reading from the sources — Allan S. Hansen
– Allan S. Hansen, Commented Dec 27, 2013 at 8:54
@Allan Thanks a lot for your suggestion. However my data is quite huge & I don't want to have any manual work other then referring the 2 excel sheet paths. Also even if we use loop, how can I get the data from 2 different sources..? Is there any other way, where I can accomplish task in Vb.Net? — user3138788
– user3138788, Commented Dec 27, 2013 at 9:05

Maciej Los · Accepted Answer · 2013-12-27 15:50:28Z

1

If you use OledDb providers (MS Jet database engine), try to use IN statement.

select a.[Asset ID], a.[EmpNo]
from [AssetMapping$] as a
Where a.[EmpNo] Not IN (select b.[EmpID] from [EmployeeDetails$] IN 'C:\Data\SecondWorkbook.xls' 'Excel 8.0;' as b)

For further information, please see: How To Use ADO.NET to Retrieve and Modify Records in an Excel Workbook With Visual Basic .NET

I hope it will helps.

See my article on CodeProject: http://www.codeproject.com/Tips/702769/How-to-Get-data-from-mulitple-Workbooks-using-one

edited Dec 27, 2013 at 15:50

answered Dec 27, 2013 at 9:09

Maciej Los

8,6211 gold badge22 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

user3138788 Over a year ago

Thanks for your valuable sugestion. However I did tried & its giving me a syntax error.. Syntax error. in query expression 'a.[EmpNo] Not IN (select b.[EmpID] from [EmployeeDetails$] IN 'C:\Data\SecondWorkbook.xls' 'Excel 8.0;' as b)'. Source=Microsoft JET Database Engine Is there anything else I am missing to add?

user3138788 Over a year ago

A special thanks to you.. Your suggestion has helped me in getting the required result. The earlier syntax error was due to usage of single quotes instead of double quotes, select [Asset ID], [EmpNo] from [Sheet1$] Where [EmpNo] Not IN (SELECT [EmpID] FROM [EmployeeDetails$] IN "C:\Data\SecondWorkbook.xls" "EXCEL 8.0;") The above query is working perfectly fine & its what I am looking for.

Maciej Los Over a year ago

I'm glad you've found solution ;) Could you be so nice to vote-up my answer?

user3138788 Over a year ago

Unfortunately I am a newbie to this site & I don't have enough reputation to vote-up. Once I get enough reputation I will make sure to vote-up for your valuable solution.

Maciej Los Over a year ago

I'm newbie too... Thank you very much ;)

Collectives™ on Stack Overflow

VB.NET - Get Data to a Datatable from multiple excel sources

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related