asked on

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

Fernando Soto

Can you upload a sample of the Excel file?

allanhutton

ASKER

here is the file.
book1.xls

Fernando Soto

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

ASKER CERTIFIED SOLUTION

Fernando Soto

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Fernando Soto

Have you tried the solutions?