SQL 2012 :: Output A Query As Excel File
Nov 16, 2013
I'm a newbie to SQL. I'm using SQL Server 2012 on my local machine and I need to find a way to output my queries as Excel files. I came across these codes for Interactive SQL (what is intercative SQL by the way?) but they don't work in the SQL query window:
SELECT * FROM SalesOrders;
OUTPUT USING 'Driver=Microsoft Excel Driver (*.xls);
DBQ=c: estsales.xls;
READONLY=0' INTO "newSalesData";
It seems the "OUTPUT" command is not a valid command. I really liked that piece of code (may be because it is so simple and carries over very few lines)! Do we have something similar for SQL Server 2012 that can do the job?
View 9 Replies
ADVERTISEMENT
Aug 2, 2006
I need to create a query (SQL 2000) that renders a formatted excel (xml or xls) file for each row that is outputted.
The details, I have a Campaign table that contains information for Auto and Life "Leads" and the data is submitted by telemarketers directly into the database. I need to render a file for each line, and it would be good if It were an Excel XML or XLS file, because that's what we've been using for a while.
View 4 Replies
View Related
Apr 21, 2015
Using below script to export the select statement result to .xls
declare @sql varchar(8000)
select @sql = 'bcp "select * from Databases..Table" queryout c:bcpTom.xls -c -t, -T -S' + @@servername
exec master..xp_cmdshell @sql
But result is not exporting in seperate tabs, all 4 column details are exporting in single cell.
how to export the data in columns to separate tabs in excel.
View 2 Replies
View Related
Feb 11, 2014
I have database tables for
Stores
StoreId, Name
Products
ProductId, Name
Transactions
TransactionId, StoreId, ProductId
I was just given an excel file with a list of 300 Stores.
I need to find out if these stores are selling our products and if they are , how many products they are selling.
One way of doing this , that I can think of right now is individually querying the Transactions table for each of the store in the excel sheet and then copy the results output back to the excel sheet.
Is there a way I can write a query against all the Store names from the excel file ? I need to get this done in the next few hours.
View 9 Replies
View Related
May 25, 2014
I have a 3rd party dashboard application that I can only use SQL authenticated logins to connect to the database.
I'm trying to create a query within the application that will directly access an excel file through a linked server.
As a test, I login to SSMS as the sql auth user to run the linked server query below but the following error is returned:
select *
from Corporate...[Sheet1$]OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "Corporate" returned message "Cannot start your application. The workgroup information file is missing or opened exclusively by another user.".
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "Corporate" reported an error. Authentication failed.
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "Corporate".
When I login as a Windows auth user, I can successfully run the above query.
I noticed within the linked server's security definition that I cannot specify a windows auth user as the mapped Remote User or as the Remote login
I've tried creating a Credential object with the identity of the windows user and assign that object to the sql auth user but to no avail. I still get the same error
I am using SQL Server express so the option of an automated server agent job to import the excel file is not available.
Details:
SQL Server Express 2012
Office version: Excel 10
Provider: Microsoft.ACE.OLEDB.12.0
View 9 Replies
View Related
Oct 11, 2004
OK, another problem but the same member, i am feeling REALLY thick now!
I have a job set up that extracts data from a table and puts to an Excel sheet, this is then emailed to certain recipients.
why is the data appending in the Excel file rather than overwriting? How can i prevent this?
View 7 Replies
View Related
Jun 8, 2004
Has anyone had experience formatting an excel file (i.e. run a macro) after it is (created &) outputted from a DTS package?
Also an easier question:
What is the best (easiest) way to create a unique filename in Excel with a datetimestamp in the file name (i.e. MyFile-20040608.xls)
Thanks!
View 3 Replies
View Related
May 5, 2008
I would like to create a SQL job that runs a basic query monthly and saves the results as a .xls file and emails the results to a user. I've been unsuccessful with sp_send_dbmail in that the output file is always a single column. Any suggestions?
Thanks, Dave
View 5 Replies
View Related
Jul 7, 2006
I'm writing an SSIS package to run a series of select statements and save the results of each to a sheet in an Excel file. An indirect configuration file with these entries is used:
Package.Variables[User::ExcelTemplateFilePath].Properties[Value]
Package.Connections[AcculoanDatabase].Properties[ConnectionString]
Package.Connections[ExcelReportFile].Properties[ExcelFilePath]
At the start of the package this setup will happen.
1) copy ExcelTemplateFilePath to ExcelFilePath
2) Set User::ReportFilePath to ExcelFilePath
Creating a script task to do this is not a problem. However the task fails on validation of the tasks that write the select results to the Excel file sheets. I'm thinking the validation fails because the Excel file is not copied before the validation is done.
How can I arrange for the setup script to run and copy the Excel file before validation?
Is there is a better way or am I just doing this wrong?
Thanks
View 2 Replies
View Related
Aug 3, 2007
I have set up an export package in SSIS and I would like to assign a file name based on the value in a table on my SQLServer. For example, my file name might be Monthly Reporting Package 20070731.xls where 20070731 is a value in my date table. I can retrieve my date by the following select statement: "SELECT Date FROM DecodeTables.dbo.Date". Can I set up a variable to look up this value and then concatenate it into my file name?
View 3 Replies
View Related
Jul 20, 2004
I am using bcp to get the query output in excel file. But I am not able to see the column headings. Is there a way to get the column heading also in the output file.
the command I am using is this.
declare @x varchar(300)
set @x = 'bcp "select * from Northwind..orders" queryout c: est.csv -S local -U sa -P passwd -c -C RAW -t "," -r '
exec master..xp_cmdshell @x
go
View 7 Replies
View Related
Jul 8, 2015
I would like to know if it is possible to date stamp the output file for the DBCC integrity check?
The following cmd works fine however I tried different ways to date stamp the output file with no luck.
sqlcmd -U backup -P Password -S Server -Q"DBCC CHECKDB('DatabaseName') WITH ALL_ERRORMSGS" -o"G:LOGSDBCCResults.txt"
View 5 Replies
View Related
Mar 2, 2014
I am trying to create an ssis package with dynamic csv file as output. and out format contains query output.
sample file name:
Unique identifier + query output + systemdate();
The expression is looking like this.
@[User::FilePath] + @[User::FileName] + ".CSV"
-- user filepath is a variable from ssis package. File name is the output from SQL query. using script task i have assigned the values to @[User::FileName] .
When I debugged the script task the value getting properly but same variable am using for Flafile destination. but its not working.
View 3 Replies
View Related
Sep 21, 2015
I have been given a request at work that requires us to run the SQL scripts that are held in a report configuration table and output the results as .csv or.xls files in a desired folder with a file name that is also specified within the report config table.
An example of the table is as per script below with column A being the desired file name to be created and Column B contains the script that will be executed.
Ideally I require a script that i can drop into a Stored Proc that will run down each row in the table and export and create each rows results as a separate file in a desired folder.
------------------------------------------------------------------------------------------
-----SAMPLE TABLE SCRIPT -----------------------------------------------------------
------------------------------------------------------------------------------------------
/****** Object: Table [dbo].[Test_Table_PS] Script Date: 21/09/2015 09:14:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Test_Table_PS](
[File_Name] [nvarchar](100) NULL,
[Code] ....
View 7 Replies
View Related
Jan 29, 2014
Below is the sample data
create table #sample
(name varchar(100),
id int)
insert into #sample values ('customerid','15339119')
insert into #sample values ('Title','15339119')
insert into #sample values ('firstname','15339119')
insert into #sample values ('prevcr','2146822710')
insert into #sample values ('currcr','2146822710')
insert into #sample values ('brandcode','2146822710')
I need output as attached ...
View 5 Replies
View Related
Nov 17, 2014
I have an excel file which i am reading through SSIS through MICROSOFT Office 12.0 Access Database Engine OLE DB Provider. My problem is that if the Excel file has data in a column as 123.45 which is displayed as 123. SSIS is able to pick 123 only and not 123.45. however if I change the data type to Numeric in the excel, SSIS is able to read data as 123.45.Any solution other than changing data type in Excel?
View 2 Replies
View Related
Jun 28, 2015
I want to import excel file in sqlserver , i was informed that we have to pay of drivers like .jet and .ace.
i have sqlserver and OS windows licences and do not want to pay just to import excel in sqlserver.
q1) Is ssis requires .jet or .ace or any other componenet for xls import which is lincenced.
apart from sqlserver and OSwindows licence ,.
Q2)is it possible to convert xls into micorsoft word and then transport it.
Q3) do i have to pay any thing if i use bulk insert to import .txt or .csv file.
what other type of files can be imported by bulk insert.
Q4) how many type of files like .txt or .csv , can be imported using bcp?
View 4 Replies
View Related
Jun 23, 2015
I am currently trying to migrate a excel file into SQL 2012 database on an Oracle Virtual Box VM. I am new to SQL and this type of IT in general.
View 3 Replies
View Related
Jul 25, 2001
I have a query something like this:
select "bcp EISAT_08_18.."+name +" OUT C:"+ name+".TXT -c -t -SCJACOBI"
from sysobjects
where type = 'U'
ORDER BY NAME
When I run the above query I want to output the result of the query to a file.
Can someone help me on that?
View 8 Replies
View Related
Jul 31, 2001
Hi all,
When I run a query in the sql query analyzer I need to write the output of that query in to another file. In Oracle its spool. Can someone help me on this please. Thank you!!!
View 1 Replies
View Related
Feb 27, 2006
Hi,
In a stored procedure how do I output the result of a query to a text file?
Regards,
Bharathram G
View 3 Replies
View Related
Apr 13, 2006
I have an sp that runs a query in xp_cmdshell/BCP combo that puts the output into an XML file (using for xml auto hint on the end of the query).
I personally feel that I should now (under SQL 2K5) be able to do this within a query, not going any where near xp_cmdshell or bcp, and then use SSIS in the final step to ftp it up to the clients site.
It builds a command string in the sp, then runs that command string via xp_cmdshell.
I guess what I'm asking is, is it possible to run a query within an SP with the output going to an XML file on the server automatically? AND that file to be a well formed XML file?
Any suggestions?
View 4 Replies
View Related
Mar 23, 2015
I am working on a code that will convert the query output into a html query output. That is the output of the query will be along with html tags so that we can save it as a html file.
The stored procedure that i have used is downloaded from symantec website and is working fine. Below is the code of that stored procedure.
/****** Object: StoredProcedure [dbo].[sp_Table2HTML] Script Date: 12/21/2011 09:04:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_Table2HTML] (
@TABLENAME NVARCHAR(500),
@OUTPUT NVARCHAR(MAX) OUTPUT,
[Code] ....
The code works fine and i am able to get the output with html tags. The problem occurs when i insert stylesheet in the code. I tried to enforce styles using a stylesheet for the table returned in my sql code, so that it will look good. below is the stylesheet code that i inserted between <head> and </head> tags.
<style type="text/css">table.gridtable { font-family: verdana,arial,sans-serif; font-size:10px; color:#333333;border-width:1px; border-color: #666666; border-collapse: collapse; } table.gridtable td {border-width: 1px; padding: 8px; border-style: solid; border-color: #666666; background-color: #ffffff;}</style>
If I run the procedure without the style sheet code, it works fine. but when i run the procedure with style sheet code i am getting the below errors.
Msg 105, Level 15, State 1, Line 98
Unclosed quotation mark after the character string '</table></body><'.
Msg 102, Level 15, State 1, Line 98
Incorrect syntax near '</table></body><'.
[Code] .....
I checked the code and i am not able to find what is the mistake. I tried changing the quotation mark but it didn't worked.
View 6 Replies
View Related
Aug 29, 2007
Hi,
I want to export/output result of a query to a CSV file using SQL Server 2005. How can i do it ? I just want to do it all using SQL Server 2005 query window without having to use some 3rd Party control or software. Is it possible and how? Is there some SP which can convert the result to a CSV File ?
Thanking you...
View 4 Replies
View Related
Apr 3, 2001
I have come across manual queries being run daily and the results saved to a txt file on the network.
I basically want to set run these up a stored procedure and set up as a scheduled task to run daily.
Does anyone know if you can automatically save the results of a query/stored procedure to a text file on a network??
Many Thanks..
View 1 Replies
View Related
Aug 22, 2001
HI,
I have a small problem with SQL server 7.0 where I have to write the output of a query directly to a file without the user going to the menu and clicking on save. Scenario is:
A file is having a query the results of which has to be outputted to a file like in oracle where we have spooling function. Is there any functionality that mimic the oracle functionality where by I can get the output directly to a file?
thanks for your help in advance.
thanks,
Sravan.
View 2 Replies
View Related
Jan 24, 2001
Hi All
I am using cursor in my SP, according to my condition I might be getting around 600 records all this records to send to text file, I have tried few option some how I am able to create file and I am geeting only the last record in my output file, I want to know how can I append records into output file(text file), If some one can give me some suggestion that will be great.
Thanks in advance
Regards
Ram
View 1 Replies
View Related
Aug 28, 2014
how to output txt file in query by simple way ? This query have error.
Select * from invoice
into 'c:abc.txt'
View 8 Replies
View Related
Jul 23, 2005
Hi,How would I go about writing the results of an SQL query to a textfile?I cannot find any info in the Online help files.For example, I would like the results of:SELECT * FROM TableATo be written to the file result.txtAlex
View 10 Replies
View Related
Oct 5, 2015
I have 4 different queries in one SSMS New query window that are returning expected results in 4 resultsets. However I want to output these results to a single .js file one after the other in the order of queries. Is that possible?
View 7 Replies
View Related
Mar 7, 2014
I am using VS2012 and creating a package on a 64bit machine to import some data from a .xlsx file. My question is that I am getting an error for the Excel connection manager, do I need to install some kind of excel drive or excel itself on the machine in order to be able to import the data?
View 6 Replies
View Related
Feb 5, 2015
I am using vs 2010 and I have an .xls file that I am trying to import into SQL Server 2012, and I have most of it figured out, but I have a date field that is giving me problems, and what I would like to do is put that date in a variable so I can add it to every record in my SQL Table.
I am using a SQL Task Editor with an excel connection and I have no problem getting other data from the excel document and putting into my variable, its just the date that I have problems.
View 0 Replies
View Related
Aug 20, 2015
Basically I'm running a number of selects, using unions to write out each select query as a distinct line in the output. Each line needs to be multiplied by -1 in order to create an offset balance (yes this is balance sheet related stuff) for each line. Each select will have a different piece of criteria.
Although I have it working, I'm thinking there's a much better or cleaner way to do it (I use the word better loosely)
Example:
SELECT 'Asset', 'House', TotalPrice * -1
FROM Accounts
WHERE AvgAmount > 0
UNION
SELECT 'Balance', 'Cover', TotalPrice
FROM Accounts
WHERE AvgAmount > 0
What gets messy here is having to write a similar set of queries where the amount is < 0 or = 0
I'm thinking something along the lines of building a table function contains all the descriptive text returning the relative values based on the AvgAmount I pass to it.
View 6 Replies
View Related
