Urgent!!!!!!!!!!! Reading values using column names from Excel spreadsheet and insert into database

[Guest]

H

Hope someone out there knows a solution for thi

My Situation i

I have to read an excel spreadsheet which contains about 200 columns. Each column has a name associated with it. Now i can read the 200 columns using an arraylist (getvalue(0), getvalue(1) and soon) and could succesfully get the values

Now i want to read the spreadsheet with the cloumn names associated with each column as if suppose 2 more columns get added tommmorow I cannot change the number(getvalue()) in my code

for ex: If i have

FirstName Last Name
Abc de
Xyz Ds

as 2 columns in Excel spreadsheet. What i shld be able to do is,

PersonName pNameObj.firstname=FirstName.value


Somebody help me !!!!!!!!!!!

It's urgent

 

[Raterus]

I have no clue what you are getting at, and I don't believe anyone else does
either. You lost me right about here.

for ex: If i have

FirstName Last Name
Abc def
Xyz Dsf

It seems like you described one problem and provided an example for another

 

[Yama]

Hello,

Perhaps this could help you.

Dim ds As New DataSet
'Open an excel file for reading
Dim m_fileName As String = Server.MapPath("/Upload/" + oFile)
Dim tblName() As String = oFile.Split(".")
Dim conn As New OleDbConnection
'Connection to uploaded file
Dim strConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" + m_fileName + ";" + "Extended Properties=""Excel 8.0;HDR=YES;IMEX=1"""
Try
conn.ConnectionString = strConn
conn.Open()
Dim dtTables As DataTable = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing)
Dim tableName As String = dtTables.Rows(0)(dtTables.Columns.Item(2).ColumnName())
Dim strSQL As String = "SELECT * " & _
" FROM [" + tableName + "]"

Dim da As New OleDbDataAdapter(strSQL, conn)

da.TableMappings.Add("Table", oFile.Replace(".xls", ""))
da.Fill(ds)
If ds.Tables(0).Rows.Count < 1 Then
Exit Sub
End If

Dim dc As DataColumn
Dim dt As DataTable = ds.Tables(0)
Dim strColumnName As String
Dim fn, ln As String

For i As Integer = 0 To ds.Tables(0).Rows.Count - 1
For Each dc In dt.Columns
strColumnName = dc.ColumnName

If strColumnName.StartsWith("LAST") OrElse _
strColumnName.StartsWith("Last") OrElse _
strColumnName.StartsWith("last") Then
ln = ds.Tables(0).Rows(i)(strColumnName).ToString
End If
'First Name
If strColumnName.StartsWith("first") OrElse _
strColumnName.StartsWith("First") OrElse _
strColumnName.StartsWith("FIRST") Then
fn = ds.Tables(0).Rows(i)(strColumnName).ToString
End If
Next
Next
Catch ex As Exception
Session("ErrorMessage") = "Exception Error: Please call MIS for further assistance.<br>The error message: " + ex.Message
Throw New Exception
Finally
conn.Close()
conn.Dispose()
End Try


Hope this helps,


Yama Kamyar