SQL Server: Export data to CVS with headers using BCP
Categories: Database; Tagged with: BCP • CSV • ETL • SQL • SQL Server • T-SQL; @ September 25th, 2012 14:04Use this BCP script to generate CSV file:
bcp "SELECT ''ID'', ''Username'' UNION ALL SELECT CAST(u.ID as varchar(10)),u.USERNAME from DavidTest.dbo.DavidUser u" queryout "c:\output.csv" -c -t"," -r"\n" -SServerName -T
If you want to run in SSMS, you can use:
USE MASTER
GO
DECLARE @query varchar(4000)
SET @query = '"SELECT...."';
EXEC xp_cmdshell @bcpOut
GO
for more info:
http://msdn.microsoft.com/en-us/library/aa174646(v=sql.80).aspx
SQL Server: New Linked Server from Excel
Categories: Database; Tagged with: SQL • SQL Server • T-SQL; @ September 13th, 2012 19:50We can create a new linked server using Excel:
Select the Provider, Input the data source(excel path), and provider string, keep ‘Excel 8.0’.
Save, and you can see the server .
then you can query from the new server:
SELECT * FROM DAVIDEXCEL...Sheet1$;
SQL Server: Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server
Categories: Database; Tagged with: SQL • SQL Server • T-SQL; @ September 13th, 2012 18:41I got some Error when I trying to select data from Excel file using Microsoft.Jet.OLEDB.4.0:
The OLE DB provider “Microsoft.Jet.OLEDB.4.0” for linked server “(null)” reported an error. The provider did not give any information about the error.
Cannot initialize the data source object of OLE DB provider “Microsoft.Jet.OLEDB.4.0” for linked server “(null)”.
Solution:Make sure you can access the file from the DB server.
SQL Server: Read,Write data from Excel/CSV/Text file using T-SQL
Categories: Database; Tagged with: SQL • SQL Server • T-SQL; @ September 13th, 2012 18:28
-- Select Data from Excel file
SELECT *
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\DavidTemp\channel.xls', [Sheet1$])
-- Select data from CVS file:
SELECT * FROM OPENROWSET('MICROSOFT.JET.OLEDB.4.0',
'Text;Database=C:\DavidTemp\;','SELECT * FROM [channel.csv]')
-- Select data From text file:
SELECT * FROM OPENROWSET ( 'Microsoft.Jet.OLEDB.4.0',
'TEXT;Database=C:\DavidTemp\','SELECT * from channel_noData.txt')
REF:
Ad Hoc Querying
SSIS: Export data to CSV with column names 导出数据到CSV并包含标题
Categories: Database; Tagged with: ETL • SQL • SQL Server • SSIS; @ September 7th, 2012 15:38When exporting data to “csv” using SSIS, I found there was no column name in the output file. And the solution is quite simple and inelegant,
refer to the following screen capture:
