I have a more data in excel file .so i have to import it into sql database using vb.net.can anyone send the source code?
+1
A:
If it's a one-off job, use DTS or SSIS. No code required.
Otherwise, you can open Excel as a data source, suck up its contents and insert into your database.
Marcelo Cantos
2010-04-05 06:18:09
ok but i want in vb.net code because we have 1000's of files to import tats y
2010-04-05 06:33:08
That's a fair bit of work and relies on intimate knowledge of your particular circumstances. No one on SO is going write it for you.
Marcelo Cantos
2010-04-05 06:35:18
but i"m using file browser to get the excel file from the path and then i have to convert this is my process actually can u help
2010-04-05 06:45:54
Sorry, but that doesn't make things any clearer. Actually, I'm more confused about you want than before.
Marcelo Cantos
2010-04-05 06:55:00
ok i'll explain it clearly ..i have more than 1000 files in the excel format.from tat i have to import into sql database by using windows form using vb.net.In tat i have File upload to choose the file to covert... this is my main objective.can u help me now?
2010-04-05 06:59:31
Marcelo has suggested a way to do it using VB. Open Excel as a data source. It is the recognised way of doing it.
CResults
2010-04-05 09:58:31
but i cant able to use opendatasource or openrowset...provide other way to import
2010-04-05 12:05:48
What do you mean you "cant able to use" them? Are they unavailable for some strange reason, or do you just not know how to write the code?
Marcelo Cantos
2010-04-05 12:52:37
sorry we cant able acces the main server that means openrowset/opendatasource tats y... this is my code ...but its shows error can u help me
2010-04-05 13:03:10
I cannot for the life of me figure out that last response ("sorry we cant..."). Also, don't post code in a response. Add it to the question and also indicate the error message you get.
Marcelo Cantos
2010-04-05 22:05:50
I don't mean to be rude (I really do want to help you here), but your english needs to improve dramatically if you are to have any hope of getting help on stackoverflow. I have tried quite hard to figure out what your specific problem is, and I just can't tease it out from your comments.
Marcelo Cantos
2010-04-05 22:07:36
A:
Dim ExcelConnection As New
System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data
Source=C:\MyExcelSpreadsheet.xlsx;Extended Properties=""Excel 12.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
Dim SQLconn As New SqlConnection() Dim ConnString As String = "Data Source=MMSQL1;Initial Catalog=DbName; User Id=UserName;
Password=password;" SQLconn.ConnectionString = ConnString SQLconn.Open()
Using bulkCopy Asd SqlBulkCopy = New SqlBulkCopy(SQLConn) bulkCopy.DestinationTableName = "TableToWriteToInSQLSERVER"
Try objDR = objCmdSelect.ExecuteReader bulCopy.WriteToServer(objDR) objDR.Close() SQLConn.Close()
Catch ex As Exception MsgBox(ex.ToString) End Try End Using
Mike
2010-10-26 05:56:18