Click here to Skip to main content
15,882,388 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
hello, I am trying to use LINQ to import a dataset from excel to visual studios so that I can work with it. Does anyone know the syntax for this?

Thanks!

What I have tried:

Most of the tutorials online I've found give the syntax for C# but I have yet to find a good one for visual basic
Posted
Updated 23-Apr-18 3:21am

In a short: You have to use ADO.NET[^] (OleDb) provider to connect to the Excel sheet, then you'll be able to use Linq to DataSet[^].
Steps to do:

1. Create OleDbConnection[^] to connect to the Excel file (connectionstring[^])
VB
Dim sConnStr As String = String.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 12.0;HDR={1}';", "FullFileName.xlsx", "YES|NO")
Dim oConn As OleDbConnection = New OleDbConnection(sConnStr)


2. Create OleDbCommand[^]
VB
Dim sCommStr As String = "SELECT * FROM [Sheet1$];"
Dim oComm As OleDbCommand = New OleDbCommand(sCommStr, oConn)


3. Create OleDbReader[^] to read data into DataTable[^] (use DataTable.Load(IDataReader) method[^])
VB
Dim oRdr As OleDbReader
Dim oDt As DataTable = New DataTable()
oRdr = oComm.ExecuteReader()
oDt.Load(oRdr)


4. Use Linq on datatable object
VB
Dim myData = oDt.AsEnumerable() _
    .Where(Function(x) x.Field(Of String)("Name") = "Peter") _
    .Select(Function(x) x) _
    .ToList() 'returns a List(Of DataRow)


More:
Queries in LINQ to DataSet | Microsoft Docs[^]

Other resources:
Accessing Microsoft Office Data from .NET Applications[^]
Using LINQ to Query Tables in Excel 2007 (OpenXML)[^]
Walkthrough: Office Programming (C# and Visual Basic) | Microsoft Docs[^] (there's small combobox on the right site to change language)
Create a simple data application by using ADO.NET[^]
 
Share this answer
 
v2
Comments
Member 13791602 23-Apr-18 21:43pm    
Hello, thank you for you help! I copied in the first step, and it is telling me " 'New' cannot be used on an interface". Please advise.
Maciej Los 24-Apr-18 2:07am    
I do not see your code, so i can't understand an error message.
Wendelius 24-Apr-18 13:01pm    
Nice answer!
Maciej Los 24-Apr-18 13:38pm    
Thank you, Mika.
Try: Google visual basic linq
The first 2 answers are:
LINQ in Visual Basic | Microsoft Docs[^]
Writing Your First LINQ Query (Visual Basic) | Microsoft Docs[^]
You may have some documentation to read.
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900