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

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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
Click to expand...

It seems like you described one problem and provided an example for another
 
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
 
Back
Top