Excel Sheet in ASP

Migration of excel sheet to SQL Database in ASP

[deepali2005]

Hello,

I have excel sheet with specific fields. I want to add the excel sheet data in SQL Server Database.

I am new to this. so please if anyone have any idea ca give me information.

Thanks in advance...

[txbakers]

look up the FileSystemObject. You can open a text file for reading, then read it row by row, then insert it into your database.

It works with text files, so better to save your Excel as a CSV file.

[Jalinder]

Some code that may help you:

Set xlApp= server.CreateObject("Excel.Application")
xlApp.Interactive = False
Set xlBook= xlApp.Workbooks.Open(strFileName)
Set xlSheet= xlBook.Worksheets(1)
Set region = xlSheet.UsedRange
For Each Row In region.Rows
...
Next

[emsaw]

In the Excel Visual Basic Editor, Add a reference to 'Microsoft ActiveX Data Objects 2.8 Library', and then you can use simple ADO to take data from your worksheet and insert it into your database:

 Dim Conn1 As ADODB.Connection
 Dim Cmd1 As ADODB.Command
 Dim Rs1 As ADODB.Recordset
 Dim strTmp As String
 Dim sConnect As String
 Dim sSp As String
 sConnect = "driver={sql server};" & _
    "server=#*$!#*$!x;" & _
    "Database=xxxxxxx;UID=xxxxxxx;PWD=xxxxxxx;"
 sSp = "INSERT INTO Authors(au_fname) VALUES (" & CELLS(1,1).Value &")"
 
 Set Conn1 = New ADODB.Connection
 Conn1.ConnectionString = sConnect
 Conn1.Open
 Set Cmd1 = New ADODB.Command
 Cmd1.ActiveConnection = Conn1
 Cmd1.CommandText = sSp
 Cmd1.CommandType = adCmdText ' adCmdStoredProc if it's a stored procedure
 
 Set Rs1 = Cmd1.Execute()
 
 Conn1.Close
 Set Cmd1 = Nothing
 Set Rs1 = Nothing
 Set Conn1 = Nothing

HTH,

mark

[rebelde]

If you don't need an automated process, I recommend the Import Data Wizard.