Exporting data from excel into sql server - newbie

Using SQL Server 2005 Standard

The basic question
What is the best way to export data from an excel spreadsheet into a sql server table?
My Application
Getting data indicating hours worked from employee timesheets into a centralised DB, then running analysis reports on it.
The columns and datatypes in the excel sheet are as follows:
Week (int) | EmployeeID (int) | JobNum (int) | ActivityNum (int) | Hours (int)

There will be a new excel file each week that, once the employee has filled out the data, would need to be saved and exported to the sql table. The columns in the sql table are exactly the same as the excel table with the addition of a RecordID primary key column.

Can I create a macro button that they can push when they have completed their timesheet OR would it be better to tell the employees to save copies of their timesheets in a certain folder on the company network and then run a batch on all the files in the folder at the end of the day?

Or is there another more efficient solution? Would I use SSIS for this or something else?

I've never used SSIS before and am a newbie at SQL Server too.

Thanks for any help you can give me.

Hi,

I would not create an Excel button for it because of maintenance and security concerns.

I would rather have the user go to a web page where he pick the excel file to be imported in SQL.

This would then take the spreadsheet from the user's machine (any folder) and import it to SQL.

Something like the code bellow. Note that if you run the package from the code, you must wait package completion and may get a time out while if you run a job that runs the package, then you do not have to wait, the server will do it on the background.

You may want to delete the excel file from the directory after it has been loaded, that will avoid bugs.

Now all you have to do is to develop the package :-)

Philippe

Code Snippet

Imports Microsoft.SqlServer.Dts.Runtime

Imports System.Data

Imports System.Data.SqlClient

Partial Class _Default

Inherits System.Web.UI.Page

Protected Sub Button1_Click(ByVal sender As Object, _

ByVal e As System.EventArgs)

If FileUpload1.HasFile Then

Try

FileUpload1.SaveAs("\\localhost\SM Files\" & FileUpload1.FileName)

Label1.Text = "File name: " & _

FileUpload1.PostedFile.FileName & "<br>" & _

"File Size: " & _

FileUpload1.PostedFile.ContentLength & " kb<br>" & _

"Content type: " & _

FileUpload1.PostedFile.ContentType & " kb<br>" & _

"The file and the cube are now processed by the server. test"

' Load_Me()

Runsp()

Catch ex As Exception

Label1.Text = "ERROR: " & ex.Message.ToString()

End Try

Else

Label1.Text = "You have not specified a file."

End If

End Sub

Protected Sub Runsp()

' Dim rowCount As Integer

Dim previousConnectionState As ConnectionState

Dim conn As New SqlConnection("server=datamart;integrated security=true;" + "database=MSDB")

Dim cmd As New SqlCommand("msdb.dbo.sp_start_job @.job_name ='Distributor NPD Stocking data and cube'", conn)

previousConnectionState = conn.State

Try

If conn.State = ConnectionState.Closed Then

conn.Open()

End If

' rowCount = cmd.ExecuteNonQuery()

cmd.ExecuteNonQuery()

Finally

If previousConnectionState = ConnectionState.Closed Then

conn.Close()

End If

End Try

End Sub

Protected Sub Load_Me()

Dim app As New Application

Dim pack As Package = app.LoadPackage("C:\Projects\ssis Packages\Dist Stocking.dtsx", Nothing)

pack.Execute()

'' Dim result As DTSExecResult = pack.Execute()

End Sub

End Class

|||Thanks for your answer, I ended up figuring out how to do it myself using SSIS and used a ForEach loop container containing a data flow that uses Excel Source --> Data Conversion --> Conditional Split (to filter out the rows with 0 hours) --> SQL Server Destination.

I just tell the employees to save a copy of their timesheets on a network folder that I set up and the foreach loop runs through all the timesheets and imports all the data, then I just delelte the files and repeat each week.

Works out great!

Thanks again for your suggestion.