Exporting query result into excel file

Hi i am newbie . I want to transfer the query result into excel file throgh asp code. can any one help me out.

Also how can i automate my routing task that is data in excel to be updated in the table which is in SQL server. Say x.xls is the daily file maintaned by me and i want y table in sql server to be appended once this file is saved.Writing a macro in Excel could be one of the solutions!!!sql

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.sql

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.

Exporting a table to Excel using bcl utility (Newbie)

I am trying to learn SQL 2005 Express and I am having a problem exporting (if that is the correct word) a table to Excel.

I have created a view in the Northwind database called MyCustomerView and want to practice working with the table.

From the command prompt:

bcp Northwind.dbo.MyCustomerView out MCV.xls -S -T

I then get the error message:

c:\> bcp Northwind.dbo.MyCustomerView out MCV.xls -S -T

SQLState = 08001, NativeError = 10061

Error = [Microsoft][SQL Native Client]TCP Provider: No connection could be made because the target machine actively refused it.

SQLState = HYT00, NativeError = 0

Error = [Microsoft][SQL Native Client]Login timeout expired

SQLState = 08001, NativeError = 10061

Error = [Microsoft][SQL Native Client]An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.

This is on a single machine running Widows XP Home.

Help Please

Did you enable remote connections on the SQL Server express machine ? See the Screencast on my site for more information and a walkthrough. Do you have SQL Browser started ? SQL Server will pick a port to bind it to the service. If SQL Browser is not started you will have to specify it within the server declaration. If started it will directly redirect you to the appropiate instance.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Thanks Jens

Everything you suggested I have tried, but am still getting the errors.

Export to Exel

I am newbie in exporting data.

i am using visual studio.net 2003 and using vb.net to create a webapplication.

My problem is how do i query my data from MS-SQL and export to microsoft Exel??

Can some one gave me the sample code?

Thanks

I had success to export my data to exel,

but why must i save the file before i open it??

Can anyone help me solve this problem??

My coding is Below :

Dim

rAsNew Columnar_Landed

r.SetDatabaseLogon("username", "password", "192.xxx.xxx.xxx,xx", "DTZ-VMS")

r.RecordSelectionFormula = query

Dim oStreamAsNew MemoryStream

oStream = r.ExportToStream(CrystalDecisions.Shared.ExportFormatType.ExcelRecord)

HttpContext.Current.Response.Clear()

HttpContext.Current.Response.Buffer =

True

HttpContext.Current.Response.ContentType = "application/vnd.ms-excel"

HttpContext.Current.Response.BinaryWrite(oStream.ToArray())

HttpContext.Current.Response.End()

|||

Please search the forum. You will find a lot of material on it. Search by "Gridview to excel". one important link is:

http://forums.asp.net/thread/1573815.aspx

Please mark this post as resolved if you r done

Export Schema and Data

This is a totally newbie question, but...

I've create a database, I'm able to script the schema to a query window, file, etc.

I can't for the life of me find out how to export my data so that it is scripted into insert statements. The data is standardized lists of data I will be distributing with the DB.

For those of you familiar with mySQL, this would be the output of the mysqldump command which dumps schema and data all into one file

mysqldumb <db> -u user -p > mydatafile.txt

thanks,

-David

You won't be able to export it to INSERT statements, but it can be exported to a flatfile with column and row delimiters. Please see Books Online topic bcp utility.

|||

Greg-

Ok, I'm familiar with BCP, was just trying to figure out a better way. I'll use BCP and either DTS or write a perl script to parse my data into scripted insert statements.

Thanks,

-David

|||

Well, you can always use TSQL to generate it for you as well, and save the results as a file. This can be expensive if your table is huge. Here's one example:

select 'INSERT into dbo.t1 (col1, col2, col3) values (''' + col1 + ''', ''' + col2 + ''', ' + cast(col3 as varchar(10)) + ')'

from dbo.t1