vb.net Excel file to SQL table
-
The user saves an excel file to the system, then I grab that save directory path of that file. That much is done. I want to know how to open that excel file and send all the records (row by row or however) into a SQL Server table. I think I already have a connection established. I don't want to use DTS, it should be done programmatically. Thank you in advance for any help!
-
The user saves an excel file to the system, then I grab that save directory path of that file. That much is done. I want to know how to open that excel file and send all the records (row by row or however) into a SQL Server table. I think I already have a connection established. I don't want to use DTS, it should be done programmatically. Thank you in advance for any help!
You're going to have to use the Excel Automation library to open Excel, load your sheet, and parse each line of your sheet and add it to the database yourself. You'll have to write the SQL statements, build the database command object and parameters, then assign the data from each cell to the appropriate parameter object, and finally execute it. You'll have to do this for each and every line of the Excel sheet you want to add to the database. This[^] little article should get you started on Excel Automation. It does NOT cover any database code. RageInTheMachine9532 "...a pungent, ghastly, stinky piece of cheese!" -- The Roaming Gnome
-
You're going to have to use the Excel Automation library to open Excel, load your sheet, and parse each line of your sheet and add it to the database yourself. You'll have to write the SQL statements, build the database command object and parameters, then assign the data from each cell to the appropriate parameter object, and finally execute it. You'll have to do this for each and every line of the Excel sheet you want to add to the database. This[^] little article should get you started on Excel Automation. It does NOT cover any database code. RageInTheMachine9532 "...a pungent, ghastly, stinky piece of cheese!" -- The Roaming Gnome
That example would help me get from sql to populate an excel sheet, but I'm looking for the opposite order. I've got an excel file saved, now I can get the excel file to open. Now I just need to have maybe a loop go through taking each row from the xl file and shooting it into an existing sql table. Thank you for your help.
-
The user saves an excel file to the system, then I grab that save directory path of that file. That much is done. I want to know how to open that excel file and send all the records (row by row or however) into a SQL Server table. I think I already have a connection established. I don't want to use DTS, it should be done programmatically. Thank you in advance for any help!
Sorry i just read the title and its obvious that you are using vb.net but this should actually still work, i have it working in both, but this is a very cut down copy of what i've used as what i am doing is completely different Not sure if this will help but if it is VB6 you are using it might so let me know, cheers Private Sub Form_Load() Dim oExcel As Object Dim oWB As Object Dim ows As Object Dim oRS As New ADODB.Recordset Dim i As Long Dim newI As Long 'ammended Dim oConn As New ADODB.Connection oConn.ConnectionString = "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Initial Catalog=tempdb;Data Source=JETSTREAM" oConn.Open 'Set yourself up a Table with all the fields required and fill a recordset oRS.Open "SELECT * FROM TempTable", oConn, adOpenKeyset, adLockOptimistic 'Create yourself the excel application Set oExcel = CreateObject("Excel.Application") Set oWB = oExcel.Workbooks.Open(PathtoASpreadSheet) Set ows = oWB.Worksheets("Sheet1") 'Loop through the first column until you hit the first blank cell 'This is a quick and dirty way there is probably a better way out there. 'ammended can't remember bloody overflow thing, also can't remember if i have the cells 'reference (i, A) round the right way. For i = 1 To 65535 If ows.Cells(i, "A") = "" Then newI = i Exit For End If Next 'Then loop through every column and every cell adding the data to the recordset 'in the correct fields For i = 1 To newI 'Can't remember if .value is required for the cells. oRS.AddNew oRS.Fields("FirstField").Value = ows.Cells(i, "A").Value oRS.Fields("SecondField").Value = ows.Cells(i, "B").Value 'etc, etc, oRS.MoveNext 'updates the recordset and the table oRS.Update Next "If i was king cigarettes would be free."
-
Sorry i just read the title and its obvious that you are using vb.net but this should actually still work, i have it working in both, but this is a very cut down copy of what i've used as what i am doing is completely different Not sure if this will help but if it is VB6 you are using it might so let me know, cheers Private Sub Form_Load() Dim oExcel As Object Dim oWB As Object Dim ows As Object Dim oRS As New ADODB.Recordset Dim i As Long Dim newI As Long 'ammended Dim oConn As New ADODB.Connection oConn.ConnectionString = "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Initial Catalog=tempdb;Data Source=JETSTREAM" oConn.Open 'Set yourself up a Table with all the fields required and fill a recordset oRS.Open "SELECT * FROM TempTable", oConn, adOpenKeyset, adLockOptimistic 'Create yourself the excel application Set oExcel = CreateObject("Excel.Application") Set oWB = oExcel.Workbooks.Open(PathtoASpreadSheet) Set ows = oWB.Worksheets("Sheet1") 'Loop through the first column until you hit the first blank cell 'This is a quick and dirty way there is probably a better way out there. 'ammended can't remember bloody overflow thing, also can't remember if i have the cells 'reference (i, A) round the right way. For i = 1 To 65535 If ows.Cells(i, "A") = "" Then newI = i Exit For End If Next 'Then loop through every column and every cell adding the data to the recordset 'in the correct fields For i = 1 To newI 'Can't remember if .value is required for the cells. oRS.AddNew oRS.Fields("FirstField").Value = ows.Cells(i, "A").Value oRS.Fields("SecondField").Value = ows.Cells(i, "B").Value 'etc, etc, oRS.MoveNext 'updates the recordset and the table oRS.Update Next "If i was king cigarettes would be free."
I already have a connection established, the following is code that I came up with that opens the .xls that I am after. I just don't know how to finish this code off to loop through the XL rows and put those records into a SQL table. Public Sub Batch(ByVal Path As String) Dim objExcel As New Excel.Application() Dim objWrkBk As Excel.Workbook Dim objSht As Excel.Worksheet Dim objRng As Excel.Range objWrkBk = objExcel.Workbooks.Open(Path) End Sub Thank you for any continued support.
-
Sorry i just read the title and its obvious that you are using vb.net but this should actually still work, i have it working in both, but this is a very cut down copy of what i've used as what i am doing is completely different Not sure if this will help but if it is VB6 you are using it might so let me know, cheers Private Sub Form_Load() Dim oExcel As Object Dim oWB As Object Dim ows As Object Dim oRS As New ADODB.Recordset Dim i As Long Dim newI As Long 'ammended Dim oConn As New ADODB.Connection oConn.ConnectionString = "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Initial Catalog=tempdb;Data Source=JETSTREAM" oConn.Open 'Set yourself up a Table with all the fields required and fill a recordset oRS.Open "SELECT * FROM TempTable", oConn, adOpenKeyset, adLockOptimistic 'Create yourself the excel application Set oExcel = CreateObject("Excel.Application") Set oWB = oExcel.Workbooks.Open(PathtoASpreadSheet) Set ows = oWB.Worksheets("Sheet1") 'Loop through the first column until you hit the first blank cell 'This is a quick and dirty way there is probably a better way out there. 'ammended can't remember bloody overflow thing, also can't remember if i have the cells 'reference (i, A) round the right way. For i = 1 To 65535 If ows.Cells(i, "A") = "" Then newI = i Exit For End If Next 'Then loop through every column and every cell adding the data to the recordset 'in the correct fields For i = 1 To newI 'Can't remember if .value is required for the cells. oRS.AddNew oRS.Fields("FirstField").Value = ows.Cells(i, "A").Value oRS.Fields("SecondField").Value = ows.Cells(i, "B").Value 'etc, etc, oRS.MoveNext 'updates the recordset and the table oRS.Update Next "If i was king cigarettes would be free."
That stuff you emailed me looks much cleaner, thank you. I'm having trouble with the connection string though: oConn.ConnectionString = "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Initial Catalog=TempExcel;Data Source=JETSTREAM" I have a windows auth to connect, in other words, I don't need to enter username or password to get in. I'm not sure how to adjust this. Also, in "SELECT * FROM TheTable", what is TheTable? Is it some name from the .xls somehow? Thank you for your patience.