Code Simplified – Viral Sarvaiya

Code Simplified – Viral Sarvaiya, Web Developer Friends, dot net Developer, Sql Server Developer

Get the record of the excel file in the dataset

Posted by Viral Sarvaiya on September 3, 2009

here i demonstrate that how to get the data of the excel file in to the dataset.

the function have the two arguments,
1) path of the excel file, from where the excel file is.
2) name of the excel file.


Public Function ReadXLS(ByVal path As String, ByVal filename As String) As DataSet

Dim ds As New DataSet
Dim strConn As String
//this is the connection string
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & path & filename & ";Extended Properties=Excel 8.0;"
//take the connection of the OLEDB
Dim con As New OleDbConnection(strConn)
Dim dt As DataTable
If con.State = ConnectionState.Open Then
con.Close()
End If
con.Open()
dt = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing)
If dt Is Nothing Then
Return Nothing
End If
//take the name of the all the sheet of the excel file to the erray
Dim excelSheets() As String = New [String](dt.Rows.Count - 1) {}
Dim i As Integer = 0
For Each row As DataRow In dt.Rows
excelSheets(i) = row("TABLE_NAME").ToString()
If con.State = ConnectionState.Open Then
Dim adpt As OleDbDataAdapter = New OleDbDataAdapter("select * from [" & excelSheets(i) & "]", con)
adpt.Fill(ds)
return ds
i += 1

end if
Next
ds.Dispose()
ds.Clear()
con.Close()
End Function

i put this code, that have some error in a particular situation, when u have a numeric and varchar datatype mix in a single column then this code give error, the particular datatype is converting NULL in the dataset.

the solution is change the connection string as below…


strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & path & filename & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1;ImportMixedTypes=Text;TypeGuessRows=10"""

now the error has been solved.

to know other interesting problem click here

enjoy coding….

Advertisements

2 Responses to “Get the record of the excel file in the dataset”

  1. Elcoj said

    Everything dynamic and very positively! 🙂

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: