How do you transfer or export SQL Server 2005 data to Excel
Use "External data" from Excel. It can use ODBC connection to fetch data from external source: Data/Get External Data/New Database Query
That way, even if the data in the database changes, you can easily refresh.
How to export SQL Server 2005 rows to excel? Everytime I do it I get errors
I would export to csv, and then open that csv in Excel. Works for me. Whenever I tried to move data directly between Excel and SQL Server 2005 via wizards and SSIS, I was frequently encountering bugs. I don't do it anymore.
Data export from SQL Server to Excel
You can still use the import/export wizard and select option 2 in the wizard step Specify Table Copy or Query
. Meaning:
You can use a query like this. Since I don't know your table names,let's call the first table ChildTable (the one with the FK) and the second table, the one with country names, CountryTable.
SELECT A.Col1, A.Col2, B.Country_Name,
FROM dbo.ChildTable A
INNER JOIN dbo.CountryTable B
ON B.Id = A.Country_Id
transfer data from Excel to SQL Server
You can do this easily using SSIS, you can refer to these two links for full details.
- Link 1
- Link 2
[EDIT]
If you have Express then you can try the below commands to setup a linked server and get the data
EXEC sp_addlinkedserver ExcelData,'Jet 4.0','Microsoft.Jet.OLEDB.4.0','C:\MyData.xls', NULL, 'Excel 5.0;'
GO
Then you can select the data into your tables
INSERT INTO Listings ...
SELECT column1 AS intListingID, <put all columns here> FROM ExcelData...Data
GO
For other options check this link
How to export SQL Server 2005 query to CSV
In Management Studio, select the database, right-click and select Tasks->Export Data
. There you will see options to export to different kinds of formats including CSV, Excel, etc.
You can also run your query from the Query window and save the results to CSV.
Export data from table to excel column name
In SQL Server 2005, you can use SQL Server Integration Services (SSIS) to export data to Excel. Here's an article that might get you started:
http://searchsqlserver.techtarget.com/generic/0,295582,sid87_gci1306800,00.html
On 2000, you can use Data Transformation Services (DTS). Using DTS to export data to Excel was much easier than doing it in SSIS, it's too bad they made it more complicated in SSIS.
http://support.microsoft.com/kb/319951
There is also a simpler way to do it using OPENROWSET (this may require creating the Excel file before doing the export):
http://www.mssqltips.com/tip.asp?tip=1202
One additional simple way to do this is to just query your database by dumping the values into a comma-separated list, then saving off the results as a .csv file.
The simpler methods might require some clever coding to get the column names into the excel spreadsheet.
T-SQL: Export to new Excel file
This is by far the best post for exporting to excel from SQL:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926
To quote from user madhivanan
,
Apart from using DTS and Export wizard, we can also use this query to export data from SQL Server2000 to Excel
Create an Excel file named testing having the headers same as that of table columns and use these queries
1 Export data to existing EXCEL file from SQL Server table
insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=D:\testing.xls;',
'SELECT * FROM [SheetName$]') select * from SQLServerTable
2 Export data from Excel to new SQL Server table
select *
into SQLServerTable FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=D:\testing.xls;HDR=YES',
'SELECT * FROM [Sheet1$]')
3 Export data from Excel to existing SQL Server table (edited)
Insert into SQLServerTable Select * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=D:\testing.xls;HDR=YES',
'SELECT * FROM [SheetName$]')
4 If you dont want to create an EXCEL file in advance and want to export data to it, use
EXEC sp_makewebtask
@outputfile = 'd:\testing.xls',
@query = 'Select * from Database_name..SQLServerTable',
@colheaders =1,
@FixedFont=0,@lastupdated=0,@resultstitle='Testing details'
(Now you can find the file with data in tabular format)
5 To export data to new EXCEL file with heading(column names), create the following procedure
create procedure proc_generate_excel_with_columns
(
@db_name varchar(100),
@table_name varchar(100),
@file_name varchar(100)
)
as
--Generate column names as a recordset
declare @columns varchar(8000), @sql varchar(8000), @data_file varchar(100)
select
@columns=coalesce(@columns+',','')+column_name+' as '+column_name
from
information_schema.columns
where
table_name=@table_name
select @columns=''''''+replace(replace(@columns,' as ',''''' as '),',',',''''')
--Create a dummy file to have actual data
select @data_file=substring(@file_name,1,len(@file_name)-charindex('\',reverse(@file_name)))+'\data_file.xls'
--Generate column names in the passed EXCEL file
set @sql='exec master..xp_cmdshell ''bcp " select * from (select '+@columns+') as t" queryout "'+@file_name+'" -c'''
exec(@sql)
--Generate data in the dummy file
set @sql='exec master..xp_cmdshell ''bcp "select * from '+@db_name+'..'+@table_name+'" queryout "'+@data_file+'" -c'''
exec(@sql)
--Copy dummy file to passed EXCEL file
set @sql= 'exec master..xp_cmdshell ''type '+@data_file+' >> "'+@file_name+'"'''
exec(@sql)
--Delete dummy file
set @sql= 'exec master..xp_cmdshell ''del '+@data_file+''''
exec(@sql)
After creating the procedure, execute it by supplying database name, table name and file path:
EXEC proc_generate_excel_with_columns 'your dbname', 'your table name','your file path'
Its a whomping 29 pages but that is because others show various other ways as well as people asking questions just like this one on how to do it.
Follow that thread entirely and look at the various questions people have asked and how they are solved. I picked up quite a bit of knowledge just skimming it and have used portions of it to get expected results.
To update single cells
A member also there Peter Larson posts the following:
I think one thing is missing here. It is great to be able to Export and Import to Excel files, but how about updating single cells? Or a range of cells?
This is the principle of how you do manage that
update OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=c:\test.xls;hdr=no',
'SELECT * FROM [Sheet1$b7:b7]') set f1 = -99
You can also add formulas to Excel using this:
update OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=c:\test.xls;hdr=no',
'SELECT * FROM [Sheet1$b7:b7]') set f1 = '=a7+c7'
Exporting with column names using T-SQL
Member Mladen Prajdic also has a blog entry on how to do this here
References: www.sqlteam.com (btw this is an excellent blog / forum for anyone looking to get more out of SQL Server). For error referencing I used this
Errors that may occur
If you get the following error:
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' cannot be used for
distributed queries
Then run this:
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ad Hoc Distributed Queries', 1;
GO
RECONFIGURE;
GO
Related Topics
Is This a Good Way to Model Address Information in a Relational Database
Database in Use Error with Entity Framework 4 Code First
Operation Not Allowed When the Object Is Closed When Running More Advanced Query
Why Is a Primary-Foreign Key Relation Required When We Can Join Without It
Convert from Date to Epoch-Oracle
Create Postgresql Role (User) If It Doesn't Exist
Update Multiple Columns in SQL
Differencebetween a Stored Procedure and a View
What Is the Most Appropriate Data Type for Storing an Ip Address in SQL Server
What's the Difference Between Charfield and Textfield in Django
When to Use SQL Sub-Queries Versus a Standard Join
Database Naming Conventions by Microsoft
Psql Invalid Command \N While Restore SQL
The Alter Table Statement Conflicted with the Foreign Key Constraint
Why Can't I Use an Alias in a Delete Statement