none
Use VB.net to read from Excel in input into SQL Server RRS feed

  • Question

  • Here is the code I have so far...
      
            Dim dtExcel As New DataTable
            Dim strConn
            Dim ExcelConnection As New System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.4.0;Data Source=" + FullFilePath + ";Extended Properties=""Excel 8.0 Xml;HDR=Yes""")

            ExcelConnection.Open()

            Dim expr As String = "SELECT * FROM [Sheet1$]"

            Dim objCmdSelect As OleDbCommand = New OleDbCommand(expr, ExcelConnection)
            Dim objDR As OleDbDataReader = objCmdSelect.ExecuteReader()

            Do While objDR.Read()

            Loop

    I would like for it to start reading the headers at row 5. Is this possible? There are 50 columns to each record. What do I do to loop through to read the spreadsheet from line 5?

    Wednesday, November 27, 2013 8:45 PM

Answers

  • Hello,

    Have a try with codes like below:

    Dim i As Integer = 1
    
                    Dim reader As OleDbDataReader = objOleDbCmd.ExecuteReader();
    
                    while (reader.NextResult() && reader.HasRows)
    
                    {
    
                        if (i < 5)
    
                        {
    
                            i++//skip 5 times
    
                        }
    
                        else
    
                        {
    
                            //after skip 5 times, then begin to read data
    
                        }
    
                    }
    

    To define a temp variable and set its value to 5, in the loop, skip 5 five times and then read the data since the DataReader is forward-only.

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, November 28, 2013 8:46 AM
    Moderator