Excel转为MS SQL Server7.0 http://www.yesky.com/20000627/85700.shtmlSQL Script: ExportToExcel http://www.mssqlcity.com/Scripts/scrImpExp.htmHOW TO: Transfer Data to Excel by Using SQL Server Data Transformation Services http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q319951
我要在程序里实现导入Excel或导出Excel,我不想用SQL Server的DTS啊~~~~~~~导入Excel的方法我找到了~~~~~ 还差个导出的~~~~~~~-- 导入Excel到SQL数据库的方法 SELECT * INTO XLImport6 FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0', 'Data Source=C:\abc.xls; Extended Properties=Excel 8.0')...[Sheet1$]SELECT * INTO XLImport7 FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:\abc.xls', [Sheet1$])SELECT * INTO XLImport8 FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:\abc.xls', 'SELECT * FROM [Sheet1$]'先谢谢 saucer(思归)~~~~~~~~
on http://www.mssqlcity.com/Scripts/scrImpExp.htm, there is a script download at http://www.mssqlcity.com/Scripts/ImpExp/ExportToExcel.sql ExportToExcel Version: SQL Server 7.0/2000 Created by: Alexander Chigrik http://www.MSSQLCity.com/ - all about MS SQL (SQL Server Articles, FAQ, Scripts, Tips and Test Exams).This stored procedure can be used to insert the result set of the particular select statement into Excel file (c:\ImportToExcel.xls, by default). You can pass the server name, user name, user password, the select statement to execute, and the file name to store the results set, as in the example below:EXEC ExportToExcel @server = '.', @uname = 'sa', @QueryText = 'SELECT au_fname FROM pubs..authors', @filename = 'c:\ImportToExcel.xls'
To saucer(思归) ExportToExcel.sql 我下载过了,使用起来慢~ 我只有两行的Sheet表,要十秒有多~~ 有其它SQL Server 自身提供的方法吗?像导入的,简单直观~
use bcp, write out a csv file, :-), something likexp_cmdshell "bcp <dbname>..<tablename> out c:\file.csv -Usa -P<password> -c"
To saucer(思归)xp_cmdshell "bcp <dbname>..<tablename> out c:\file.csv -Usa -P<password> -c" 这一句不错,但未能对数据源作控制,导出了整个表,但我只想导出表的一部份~~ 太贪心了吧~~ 呵呵~ 非常感谢你对该贴的讲解~ 还有其它方法吗?
no need to use a temporary table, you can use straight sql statement like thismaster..xp_cmdshell 'bcp "SELECT * FROM pubs.dbo.t1" queryout c:\t1.csv -c -t, -Usa -P[password] -S[server name]'
SELECT * INTO XLImport8 FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:\abc.xls', 'SELECT * FROM [Sheet1$]' 请问 雲海飛龍 我用你的方法导入了数据后发现同一列如果有的是纯数字但有的是字符的话那么纯数字的可以正确导入但字符的就导入了null值了怎么办呢?
以前也碰到过这个问题,可是后来上面说改用ACCESS于是就逃过了:)
dts和bcp怎样用真是没有SQL语句实现批量导入Excel数据吗?
http://www.yesky.com/20000627/85700.shtmlSQL Script: ExportToExcel
http://www.mssqlcity.com/Scripts/scrImpExp.htmHOW TO: Transfer Data to Excel by Using SQL Server Data Transformation Services
http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q319951
SELECT * INTO XLImport6
FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
'Data Source=C:\abc.xls;
Extended Properties=Excel 8.0')...[Sheet1$]SELECT * INTO XLImport7
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\abc.xls', [Sheet1$])SELECT * INTO XLImport8
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\abc.xls',
'SELECT * FROM [Sheet1$]'先谢谢 saucer(思归)~~~~~~~~
http://www.mssqlcity.com/Scripts/ImpExp/ExportToExcel.sql
ExportToExcel
Version: SQL Server 7.0/2000
Created by: Alexander Chigrik
http://www.MSSQLCity.com/ - all about MS SQL
(SQL Server Articles, FAQ, Scripts, Tips and Test Exams).This stored procedure can be used to insert the result set of the
particular select statement into Excel file (c:\ImportToExcel.xls,
by default).
You can pass the server name, user name, user password, the select
statement to execute, and the file name to store the results set,
as in the example below:EXEC ExportToExcel @server = '.',
@uname = 'sa',
@QueryText = 'SELECT au_fname FROM pubs..authors',
@filename = 'c:\ImportToExcel.xls'
ExportToExcel.sql 我下载过了,使用起来慢~ 我只有两行的Sheet表,要十秒有多~~ 有其它SQL Server 自身提供的方法吗?像导入的,简单直观~
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\abc.xls',
'SELECT * FROM [Sheet1$]'
请问 雲海飛龍
我用你的方法导入了数据后发现同一列如果有的是纯数字但有的是字符的话那么纯数字的可以正确导入但字符的就导入了null值了怎么办呢?