Excel to Sql Server.

Question

User-1068576637 posted

I am using vb.net and want to upload excel file into sql server. My problem is i want to delete first 5 rows from the excel before reading excel data

It is web application.

Sample code :

dt = xlsInsert(uploadedFile, "Sheet1")
dt.TableName = "BULKUPLOAD"
Dim sw As New StringWriter()
dt.WriteXml(sw, XmlWriteMode.IgnoreSchema)

Public Function xlsInsert(ByVal pth As String, ByVal ShtName As String) As System.Data.DataTable
Dim strcon As String = String.Empty
strcon = "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source=" & pth & ";Extended Properties='Excel 8.0;HDR=YES;'"
Dim strselect As String = "Select * from Sheet1"

End Function

Answer 1

User1724605321 posted

Hi Mohammed,

I am using vb.net and want to upload excel file into sql server. My problem is i want to delete first 5 rows from the excel before reading excel data

If your requirement is not to read the first five rows in excel ,you could get that by:

Select * from [NameOfExcelSheet$A6:E]

Here, as per the above command string, it will read all rows starting from row# ‘6’ and 5 columns from column# ‘A’ to ‘E’

Best Regards,

Nan Yu

Answer 2

User-1068576637 posted

Thank you.