how do i read and store excel data in my data base

[infydiv]

Hi,

My data in excel format is which user will upload:

ID a b c d e

11 5 6 8 9 3

12 3 4 6 7 9


and from the front end
user will select date ranges along with browse option

Fromdate,Todate,Select excel file option and one update button.

The moment user will click on update buton the data should get stored in db(Sql server 2000) in this way.

Eg: 03/15/2010,03/19/2010,excel file selected

ID Date Value

11 03/15/2010 5

11 03/16/2010 6

11 03/17/2010 8

11 03/18/2010 9

11 03/19/2010 3


12 03/15/2010 3

12 03/16/2010 4

12 03/17/2010 6

12 03/18/2010 7

12 03/19/2010 9


i m using visual studio 2003,vb.net,sql cerver 2000.

Can any body please help me. Thanks in advance.

I need it urgent...

Thanks,

 

[jmcilhinney]

First up, there's not much point the user entering a date range. What happens if they enter a range that covers 10 days but there are only 5 columns of data? What happens if they enter a range that covers 5 days but there are only 10 columns of data? It would make more sense for the user to enter just a start date and then you calculate the end date yourself based on how many columns of data there are. If the number of columns is always going to be the same then you can hard code the range and calculate the end date as soon as the user enters a start date.

As for the import, I'd suggest using ADO.NET to read the data into a DataTable first. You can then loop through that table, process the data and insert the result into another DataTable. You can then use ADO.NET to save the data to SQL Server. Note that you'll use an OleDbConnection, etc, for Excel and a SqlConnection, etc, for SQL Server. See ConnectionStrings.com for connection details.

 

[infydiv]

Many Many Thank u
I'm trying ... if i get any problem then again i'll contact you.i have deadline for this tomorrow.

Thanks,
Divya

 

[infydiv]

i took it in a dataset..but can you please share the code for loop part...

 

[infydiv]

Please please help me......

 

[JuggaloBrotha]

The loop would be roughly something like this:

For Each dr As DataRow In YourDataSet.TableName.Rows
  'You have full access to the data in all columns in the current row.
Next dr

 

[infydiv]

hi JuggaloBrotha,

Still I m not getting it. I'm having the problem in inner loop part.

Thanks,
Divya

 

[infydiv]

Can any body please help in writing code:
Ihave written this one to send it into data set.

  Private Sub ReadEmployeeData(ByVal filenm As String)
        Dim bool As Boolean
        insertconn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath("") + "\Doc\" + fn1 + ";Extended Properties=Excel 8.0"
        cn1 = New OleDb.OleDbConnection(insertconn)
        cn1.Open()
        Try
            query = "select * from [Report$]"
            MyCommand = New System.Data.OleDb.OleDbDataAdapter(query, cn1)

            ds = New System.Data.DataSet
            MyCommand.Fill(ds)
            Dt = ds.Tables(0)
            If Dt.Rows.Count > 0 Then
                DataGrid1.Visible = False
                DataGrid1.DataSource = ds
                DataGrid1.DataBind()
            End If

            Dim new_ds As DataSet = FlipDataSet(ds)
            ' Flip the DataSet 
            Dim my_DataView As DataView = new_ds.Tables(0).DefaultView
            Me.DataGrid1.DataSource = my_DataView
            Me.DataGrid1.DataBind()


            DataGrid1.Visible = True


        Catch ex As Exception
            Response.Write(ex.Message)
            lblMessage.Text = "sheet not found, please check the file and upload again"

        Finally
            MyCommand.Dispose()
            cn1.Close()
            cn1.Dispose()

        End Try
        If bool = True Then
            query = ""
                   End If
    End Sub

    Public Function FlipDataSet(ByVal my_DataSet As DataSet) As DataSet
        For Each dr As DataRow In my_DataSet.Tables
   
          Dim table As New DataTable
           For i As Integer = 0 To Dt.Rows.Count
              Dim r As DataRow = Nothing
              r = table.NewRow()
next

     Next dr  
  End Function

 

[infydiv]

Please help me in flipdataset function

 

[infydiv]

Please Please help me to convert this data into desired data set as soon as possible.