- Magga: PLEX: multimédia az egész lakásban
- eBay-es kütyük kis pénzért
- sziku69: Fűzzük össze a szavakat :)
- sziku69: Szólánc.
- D1Rect: Nagy "hülyétkapokazapróktól" topik
- Luck Dragon: Asszociációs játék. :)
- Őskoczka
- sh4d0w: Netflix? Ugyan, VW előfizetés!
- btz: Internet fejlesztés országosan!
- bambano: Bambanő háza tája
Aktív témák
-
lakisoft
veterán
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 SQLServerTable2 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
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'
Aktív témák
Hirdetés
- Motoros topic
- AMD K6-III, és minden ami RETRO - Oldschool tuning
- Battlefield 6
- Revolut
- Autós topik látogatók beszélgetős, offolós topikja
- Samsung Galaxy S25 Ultra - titán keret, acélos teljesítmény
- iPhone topik
- Hardcore café
- Okos Otthon / Smart Home
- Kodi és kiegészítői magyar nyelvű online tartalmakhoz (Linux, Windows)
- További aktív témák...
- BESZÁMÍTÁS! Gigabyte B450M R5 5600 16GB DDR4 512GB SSD GTX 1080Ti 11GB ZALMAN I3 NEO Chieftec 650W
- BESZÁMÍTÁS! Sapphire Nitro+ RX 7900XTX 24GB videokártya garanciával hibátlan működéssel
- Prémium PC házak akár 20-40% kedvezménnyel eladók garanciával, számlával!
- Jogtiszta Microsoft Windows / Office / Stb.
- antivirus szoftverek, licencek, Legion Pro 7 RTX GAMING laptopok, Dokkolók
Állásajánlatok
Cég: FOTC
Város: Budapest