vb.net ling excel null values

I'm trying to use linq to retrieve data from excel. there are a number of complications to what should be a simple task.
issues;
1. varing column count (however will not exceed 50 columns)
2. mixed column data types (would like them to all be text but excel determins some to be text and other double)

so i tried settings my connection string to imex=1 and 2 but that does not seem to work when a cell is empty. that is mainly where i'm having a problem and would hope someone can show me how to do an isnull type conversion on data in a linq call.

your help would be much appreciated.

regards
Allan
Dim filename As String = "C:\myfile.xls"
        Dim connectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & filename & ";" & "Extended Properties=""Excel 8.0;HDR=NO;IMEX=2;"""
 
 
        Dim dataAdapter As OleDbDataAdapter = New OleDbDataAdapter("SELECT * FROM [tabelle1$]", connectionString)
 
        Dim myDataSet As DataSet = New DataSet()
        dataAdapter.Fill(myDataSet, "ExcelInfo")
        Dim dataTable As DataTable = myDataSet.Tables("ExcelInfo")
        Dim query = From r In dataTable.AsEnumerable() _
           Select New With _
           { _
                .Acc1 = r.Field(Of String)(1), _
                .Acc2 = r.Field(Of String)(2), _
                .Acc3 = r.Field(Of String)(3), _
                .Acc4 = r.Field(Of String)(4), _
                .Acc5 = r.Field(Of String)(5), _
                .Acc6 = r.Field(Of String)(6), _
                .Acc7 = r.Field(Of String)(7), _
                .Acc8 = r.Field(Of String)(8), _
                .Acc9 = r.Field(Of String)(9), _
                .Acc10 = r.Field(Of String)(10), _
                .Acc11 = r.Field(Of String)(11), _
                .Acc12 = r.Field(Of String)(12), _
                .Acc13 = r.Field(Of String)(13), _
                .Acc14 = r.Field(Of String)(14), _
                .Acc15 = r.Field(Of String)(15), _
                .Acc16 = r.Field(Of String)(16), _
                .Acc17 = r.Field(Of String)(17), _
                .Acc18 = r.Field(Of String)(18), _
                .Acc19 = r.Field(Of String)(19), _
                .Acc20 = r.Field(Of String)(20), _
                .Acc21 = r.Field(Of String)(21), _
                 .Acc22 = r.Field(Of String)(22), _
                 .Acc23 = r.Field(Of String)(23), _
                 .Acc24 = r.Field(Of String)(24), _
                 .Acc25 = r.Field(Of String)(25), _
                 .Acc26 = r.Field(Of String)(26), _
                 .Acc27 = r.Field(Of String)(27), _
                 .Acc28 = r.Field(Of String)(28), _
                 .Acc29 = r.Field(Of String)(29), _
                 .Acc30 = r.Field(Of String)(30), _
                 .Acc31 = r.Field(Of String)(31), _
                 .Acc32 = r.Field(Of String)(32), _
                 .Acc33 = r.Field(Of String)(33), _
                 .Acc34 = r.Field(Of String)(34), _
                 .Acc35 = r.Field(Of String)(35), _
                 .Acc36 = r.Field(Of String)(36), _
                 .Acc37 = r.Field(Of String)(37), _
                 .Acc38 = r.Field(Of String)(38), _
                 .Acc39 = r.Field(Of String)(39), _
                 .Acc40 = r.Field(Of String)(40), _
                 .Acc41 = r.Field(Of String)(41), _
                 .Acc42 = r.Field(Of String)(42), _
                 .Acc43 = r.Field(Of String)(43), _
                 .Acc44 = r.Field(Of String)(44), _
                 .Acc45 = r.Field(Of String)(45), _
                .Acc46 = r.Field(Of String)(46), _
                .Acc47 = r.Field(Of String)(47), _
                .Acc48 = r.Field(Of String)(48), _
                .Acc49 = r.Field(Of String)(49), _
                .Acc50 = r.Field(Of String)(50) _
          }
        For Each item In query
            MsgBox(item.Acc20)
        Next
    End Sub

Open in new window

allanhuttonAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Fernando SotoRetiredCommented:
Can you upload a sample of the Excel file?
0
allanhuttonAuthor Commented:
here is the file.
book1.xls
0
Fernando SotoRetiredCommented:
Hi allanhutton;

Use IMEX=1 in the connection string and change all, AccXX = r.Field(Of String)(XX), _, to .AccXX = r(XX).ToString(), _ as shown in the code snippet.

Fernando
 
Dim filename As String = "C:\temp\myfile.xls"
Dim connectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & filename & ";" & "Extended Properties=""Excel 8.0;HDR=NO;IMEX=1;"""
 
Dim dataAdapter As OleDbDataAdapter = New OleDbDataAdapter("SELECT * FROM [Sheet1$]", connectionString)
 
Dim myDataSet As DataSet = New DataSet()
dataAdapter.Fill(myDataSet, "ExcelInfo")
Dim dataTable As DataTable = myDataSet.Tables("ExcelInfo")
 
Dim query = From r In dataTable.AsEnumerable() _
   Select New With _
   { _
        .Acc1 = r(1).ToString(), _
        .Acc2 = r(2).ToString(), _
        .Acc3 = r(3).ToString(), _
        .Acc4 = r(4).ToString(), _
        .Acc5 = r(5).ToString(), _
        .Acc6 = r(6).ToString(), _
        .Acc7 = r(7).ToString(), _
        .Acc8 = r(8).ToString(), _
        .Acc9 = r(9).ToString(), _
        .Acc10 = r(10).ToString(), _
        .Acc11 = r(11).ToString(), _
        .Acc12 = r(12).ToString(), _
        .Acc13 = r(13).ToString(), _
        .Acc14 = r(14).ToString(), _
        .Acc15 = r(15).ToString(), _
        .Acc16 = r(16).ToString(), _
        .Acc17 = r(17).ToString(), _
        .Acc18 = r(18).ToString(), _
        .Acc19 = r(19).ToString(), _
        .Acc20 = r(20).ToString(), _
        .Acc21 = r(21).ToString(), _
        .Acc22 = r(22).ToString(), _
        .Acc23 = r(23).ToString(), _
        .Acc24 = r(24).ToString(), _
        .Acc25 = r(25).ToString(), _
        .Acc26 = r(26).ToString(), _
        .Acc27 = r(27).ToString(), _
        .Acc28 = r(28).ToString(), _
        .Acc29 = r(29).ToString(), _
        .Acc30 = r(30).ToString(), _
        .Acc31 = r(31).ToString(), _
        .Acc32 = r(32).ToString(), _
        .Acc33 = r(33).ToString(), _
        .Acc34 = r(34).ToString(), _
        .Acc35 = r(35).ToString(), _
        .Acc36 = r(36).ToString(), _
        .Acc37 = r(37).ToString(), _
        .Acc38 = r(38).ToString(), _
        .Acc39 = r(39).ToString(), _
        .Acc40 = r(40).ToString(), _
        .Acc41 = r(41).ToString(), _
        .Acc42 = r(42).ToString(), _
        .Acc43 = r(43).ToString(), _
        .Acc44 = r(44).ToString(), _
        .Acc45 = r(45).ToString(), _
        .Acc46 = r(46).ToString(), _
        .Acc47 = r(47).ToString(), _
        .Acc48 = r(48).ToString(), _
        .Acc49 = r(49).ToString(), _
        .Acc50 = r(50).ToString() _
  }
 
For Each item In query
    MsgBox(item.Acc20)
Next

Open in new window

0
Fernando SotoRetiredCommented:
Or when the number of columns can vary then you can use code like this.
Dim filename As String = "C:\temp\myfile.xls"
Dim connectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & filename & ";" & "Extended Properties=""Excel 8.0;HDR=NO;IMEX=1;"""
 
Dim dataAdapter As OleDbDataAdapter = New OleDbDataAdapter("SELECT * FROM [Sheet1$]", connectionString)
 
Dim myDataSet As DataSet = New DataSet()
dataAdapter.Fill(myDataSet, "ExcelInfo")
Dim dataTable As DataTable = myDataSet.Tables("ExcelInfo")
 
Dim query = From r In dataTable.AsEnumerable() _
            Select r
 
For Each item In query
    Console.WriteLine(item(20).ToString())
Next

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Fernando SotoRetiredCommented:
Have you tried the solutions?
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.