导出到TXT文本,用逗号分开 exec master..xp_cmdshell 'bcp "库名..表名" out "d:\tt.txt" -c -t ,-U sa -P password'
BULK INSERT 库名..表名 FROM 'c:\test.txt' WITH ( FIELDTERMINATOR = ';', ROWTERMINATOR = '\n' )
--/* dBase IV文件 select * from OPENROWSET('MICROSOFT.JET.OLEDB.4.0' ,'dBase IV;HDR=NO;IMEX=2;DATABASE=C:\','select * from [客户资料4.dbf]') --*/
--/* dBase III文件 select * from OPENROWSET('MICROSOFT.JET.OLEDB.4.0' ,'dBase III;HDR=NO;IMEX=2;DATABASE=C:\','select * from [客户资料3.dbf]') --*/
--/* FoxPro 数据库 select * from openrowset('MSDASQL', 'Driver=Microsoft Visual FoxPro Driver;SourceType=DBF;SourceDB=c:\', 'select * from [aa.DBF]') --*/
/**************导入DBF文件****************/ select * from openrowset('MSDASQL', 'Driver=Microsoft Visual FoxPro Driver; SourceDB=e:\VFP98\data; SourceType=DBF', 'select * from customer where country != "USA" order by country') go /***************** 导出到DBF ***************/ 如果要导出数据到已经生成结构(即现存的)FOXPRO表中,可以直接用下面的SQL语句
insert into openrowset('MSDASQL', 'Driver=Microsoft Visual FoxPro Driver;SourceType=DBF;SourceDB=c:\', 'select * from [aa.DBF]') select * from 表
*/ if exists(select 1 from sysobjects where name='File2Table' and objectproperty(id,'IsProcedure')=1) drop procedure File2Table go create procedure File2Table @servername varchar(200) --服务器名 ,@username varchar(200) --用户名,如果用NT验证方式,则为空'' ,@password varchar(200) --密码 ,@tbname varchar(500) --数据库.dbo.表名,如果不指定:.dbo.表名,则导出数据库的所有用户表 ,@filename varchar(1000) --导入/导出路径/文件名,如果@tbname参数指明是导出整个数据库,则这个参数是文件存放路径,文件名自动用表名.txt ,@isout bit --1为导出,0为导入 as declare @sql varchar(8000)
if @tbname like '%.%.%' --如果指定了表名,则直接导出单个表 begin set @sql='bcp '+@tbname +case when @isout=1 then ' out ' else ' in ' end +' "'+@filename+'" /w' +' /S '+@servername +case when isnull(@username,'')='' then '' else ' /U '+@username end +' /P '+isnull(@password,'') exec master..xp_cmdshell @sql end else begin --导出整个数据库,定义游标,取出所有的用户表 declare @m_tbname varchar(250) if right(@filename,1)<>'\' set @filename=@filename+'\'
set @m_tbname='declare #tb cursor for select name from '+@tbname+'..sysobjects where xtype=''U''' exec(@m_tbname) open #tb fetch next from #tb into @m_tbname while @@fetch_status=0 begin set @sql='bcp '+@tbname+'..'+@m_tbname +case when @isout=1 then ' out ' else ' in ' end +' "'+@filename+@m_tbname+'.txt " /w' +' /S '+@servername +case when isnull(@username,'')='' then '' else ' /U '+@username end +' /P '+isnull(@password,'') exec master..xp_cmdshell @sql fetch next from #tb into @m_tbname end close #tb deallocate #tb end go
/**********************Excel导到Txt****************************************/ 想用 select * into opendatasource(...) from opendatasource(...) 实现将一个Excel文件内容导入到一个文本文件
select * from openrowset ('MicroSoft.Jet.OleDB.4.0', 'Excel 5.0;HDR=yes;Database=D:\aa.xls' ,sheet1$) where id not in(select id from table)
不确定sheet表名的导入datasetusing System; using System.Collections.Generic; using System.Text; using System.Data; using System.Data.SqlClient; using System.Data.OleDb; using System.Threading; using System.IO;namespace DbTools { internal class DbToolFunc { public delegate void ErrorHandler(string errorMessage); public event ErrorHandler errorMessage; public delegate void DataCompletedHandler(DataSet data); public event DataCompletedHandler dataCompleted; private Thread _threadShow; public DbToolFunc() { } public DbToolFunc(string xlsPathName) { _xlsPathName = xlsPathName; } private string _xlsPathName = null; public string xlsFileName { set { this._xlsPathName = value; } } private DataSet _data = null; public DataSet ImportData { set { this._data = value; } } private DataSet _errordata = null; public DataSet ErrorData { get { return this._errordata; } } private void GetDataProc() { OleDbConnection objConn = null; try { DataSet ds = new DataSet(); if (String.IsNullOrEmpty(_xlsPathName)) { if (this.errorMessage != null) { if (this.errorMessage != null) this.errorMessage("文件名没有指定!"); } return; } if (!File.Exists(_xlsPathName)) { if (this.errorMessage != null) { if (this.errorMessage != null) this.errorMessage("文件不存在!"); } return; } string strConn = "Provider=Microsoft.Jet.OleDb.4.0;" + "data source=" + _xlsPathName + ";Extended Properties='Excel 8.0; HDR=YES; IMEX=1'"; objConn = new OleDbConnection(strConn); objConn.Open(); DataTable schemaTable = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); OleDbDataAdapter sqlada = new OleDbDataAdapter(); foreach (DataRow dr in schemaTable.Rows) { string strSql = "Select * From [" + dr[2].ToString().Trim() + "]"; OleDbCommand objCmd = new OleDbCommand(strSql, objConn); sqlada.SelectCommand = objCmd; sqlada.Fill(ds, dr[2].ToString().Trim()); } objConn.Close(); if (this.dataCompleted != null) this.dataCompleted(ds); } catch (Exception ex) { objConn.Close(); if (this.errorMessage != null) this.errorMessage(ex.Message); } } public void GetDataSetFromExcel() { if (this._threadShow != null && this._threadShow.ThreadState == ThreadState.Running) { return; } this._threadShow = new Thread(new ThreadStart(GetDataProc)); this._threadShow.Start(); } }
将Excel中数据导入DataTable: Public Function GetOdbcDataTable(ByVal strSQL As String, ByVal FilePath As String, ByRef Value As DataTable) As Integer Dim conn As OleDb.OleDbConnection Dim AdoCmd As OleDb.OleDbCommand Dim Adapt As New OleDb.OleDbDataAdapter 'conn = New OleDb.OleDbConnection("Driver={Microsoft Excel Driver (*.xls)};DBQ=" & FilePath) conn = New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & FilePath & ";Extended Properties=Excel 8.0;") conn.Open() AdoCmd = New OleDb.OleDbCommand AdoCmd.Connection = conn Value = New DataTable Try AdoCmd.CommandType = CommandType.Text AdoCmd.CommandText = strSQL Adapt.SelectCommand = AdoCmd Adapt.Fill(Value) Catch ex As Data.OleDb.OleDbException AdoCmd.Dispose() Adapt.Dispose() conn.Close() conn.Dispose() Throw New Exception(ex.Message) Return -1 'TODO:无法连接数据库 Catch ex As Exception AdoCmd.Dispose() Adapt.Dispose() conn.Close() conn.Dispose() Throw New Exception(ex.Message) Return -1 End Try AdoCmd.Dispose() Adapt.Dispose() conn.Close() conn.Dispose() Return 0 End Function其中 strSql = "select * from [" & strSheetName & "$]" FilePath = "Excel文件的物理位置" Value = "接收Excel数据的DataTable"
select * from openrowset
('MicroSoft.Jet.OleDB.4.0',
'Excel 5.0;HDR=yes;Database=D:\aa.xls'
,sheet1$)
public static DataSet ExcelToDS(string Path)
{
DataSet ds = new DataSet();
try
{
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + Path + ";" + "Extended Properties=Excel 8.0;";
OleDbConnection conn = new OleDbConnection(strConn);
conn.Open();
string strExcel = "";
OleDbDataAdapter myCommand = null;
strExcel = "select * from [sheet1$]";
myCommand = new OleDbDataAdapter(strExcel, strConn); myCommand.Fill(ds, "table1");
}
catch (Exception ex)
{
System.Windows.Forms.MessageBox.Show(ex.Message);
}
return ds;
}
******* 导出到excel
EXEC master..xp_cmdshell 'bcp SettleDB.dbo.shanghu out c:\temp1.xls -c -q -S"GNETDATA/GNETDATA" -U"sa" -P""'
/*********** 导入Excel
SELECT *
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\test.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...xactions
SELECT cast(cast(科目编号 as numeric(10,2)) as nvarchar(255))+' ' 转换后的别名
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\test.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...xactions
/** 导入文本文件
EXEC master..xp_cmdshell 'bcp "dbname..tablename" in c:\DT.txt -c -Sservername -Usa -Ppassword'
/** 导出文本文件
EXEC master..xp_cmdshell 'bcp "dbname..tablename" out c:\DT.txt -c -Sservername -Usa -Ppassword'
或
EXEC master..xp_cmdshell 'bcp "Select * from dbname..tablename" queryout c:\DT.txt -c -Sservername -Usa -Ppassword'
导出到TXT文本,用逗号分开
exec master..xp_cmdshell 'bcp "库名..表名" out "d:\tt.txt" -c -t ,-U sa -P password'
BULK INSERT 库名..表名
FROM 'c:\test.txt'
WITH (
FIELDTERMINATOR = ';',
ROWTERMINATOR = '\n'
)
--/* dBase IV文件
select * from
OPENROWSET('MICROSOFT.JET.OLEDB.4.0'
,'dBase IV;HDR=NO;IMEX=2;DATABASE=C:\','select * from [客户资料4.dbf]')
--*/
--/* dBase III文件
select * from
OPENROWSET('MICROSOFT.JET.OLEDB.4.0'
,'dBase III;HDR=NO;IMEX=2;DATABASE=C:\','select * from [客户资料3.dbf]')
--*/
--/* FoxPro 数据库
select * from openrowset('MSDASQL',
'Driver=Microsoft Visual FoxPro Driver;SourceType=DBF;SourceDB=c:\',
'select * from [aa.DBF]')
--*/
/**************导入DBF文件****************/
select * from openrowset('MSDASQL',
'Driver=Microsoft Visual FoxPro Driver;
SourceDB=e:\VFP98\data;
SourceType=DBF',
'select * from customer where country != "USA" order by country')
go
/***************** 导出到DBF ***************/
如果要导出数据到已经生成结构(即现存的)FOXPRO表中,可以直接用下面的SQL语句
insert into openrowset('MSDASQL',
'Driver=Microsoft Visual FoxPro Driver;SourceType=DBF;SourceDB=c:\',
'select * from [aa.DBF]')
select * from 表
说明:
SourceDB=c:\ 指定foxpro表所在的文件夹
aa.DBF 指定foxpro表的文件名.
/*************导出到Access********************/
insert into openrowset('Microsoft.Jet.OLEDB.4.0',
'x:\A.mdb';'admin';'',A表) select * from 数据库名..B表
/*************导入Access********************/
insert into B表 selet * from openrowset('Microsoft.Jet.OLEDB.4.0',
'x:\A.mdb';'admin';'',A表)
********************* 导入 xml 文件
DECLARE @idoc int
DECLARE @doc varchar(1000)
--sample XML document
SET @doc ='
<root>
<Customer cid= "C1" name="Janine" city="Issaquah">
<Order oid="O1" date="1/20/1996" amount="3.5" />
<Order oid="O2" date="4/30/1997" amount="13.4">Customer was very satisfied
</Order>
</Customer>
<Customer cid="C2" name="Ursula" city="Oelde" >
<Order oid="O3" date="7/14/1999" amount="100" note="Wrap it blue
white red">
<Urgency>Important</Urgency>
Happy Customer.
</Order>
<Order oid="O4" date="1/20/1996" amount="10000"/>
</Customer>
</root>
'
-- Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc
-- Execute a SELECT statement using OPENXML rowset provider.
SELECT *
FROM OPENXML (@idoc, '/root/Customer/Order', 1)
WITH (oid char(5),
amount float,
comment ntext 'text()')
EXEC sp_xml_removedocument @idoc
********************导整个数据库*********************************************/
用bcp实现的存储过程
/*
实现数据导入/导出的存储过程
根据不同的参数,可以实现导入/导出整个数据库/单个表
调用示例:
--导出调用示例
----导出单个表
exec file2table 'zj','','','xzkh_sa..地区资料','c:\zj.txt',1
----导出整个数据库
exec file2table 'zj','','','xzkh_sa','C:\docman',1
--导入调用示例
----导入单个表
exec file2table 'zj','','','xzkh_sa..地区资料','c:\zj.txt',0
----导入整个数据库
exec file2table 'zj','','','xzkh_sa','C:\docman',0
*/
if exists(select 1 from sysobjects where name='File2Table' and objectproperty(id,'IsProcedure')=1)
drop procedure File2Table
go
create procedure File2Table
@servername varchar(200) --服务器名
,@username varchar(200) --用户名,如果用NT验证方式,则为空''
,@password varchar(200) --密码
,@tbname varchar(500) --数据库.dbo.表名,如果不指定:.dbo.表名,则导出数据库的所有用户表
,@filename varchar(1000) --导入/导出路径/文件名,如果@tbname参数指明是导出整个数据库,则这个参数是文件存放路径,文件名自动用表名.txt
,@isout bit --1为导出,0为导入
as
declare @sql varchar(8000)
if @tbname like '%.%.%' --如果指定了表名,则直接导出单个表
begin
set @sql='bcp '+@tbname
+case when @isout=1 then ' out ' else ' in ' end
+' "'+@filename+'" /w'
+' /S '+@servername
+case when isnull(@username,'')='' then '' else ' /U '+@username end
+' /P '+isnull(@password,'')
exec master..xp_cmdshell @sql
end
else
begin --导出整个数据库,定义游标,取出所有的用户表
declare @m_tbname varchar(250)
if right(@filename,1)<>'\' set @filename=@filename+'\'
set @m_tbname='declare #tb cursor for select name from '+@tbname+'..sysobjects where xtype=''U'''
exec(@m_tbname)
open #tb
fetch next from #tb into @m_tbname
while @@fetch_status=0
begin
set @sql='bcp '+@tbname+'..'+@m_tbname
+case when @isout=1 then ' out ' else ' in ' end
+' "'+@filename+@m_tbname+'.txt " /w'
+' /S '+@servername
+case when isnull(@username,'')='' then '' else ' /U '+@username end
+' /P '+isnull(@password,'')
exec master..xp_cmdshell @sql
fetch next from #tb into @m_tbname
end
close #tb
deallocate #tb
end
go
/**********************Excel导到Txt****************************************/
想用
select * into opendatasource(...) from opendatasource(...)
实现将一个Excel文件内容导入到一个文本文件
假设Excel中有两列,第一列为姓名,第二列为很行帐号(16位)
且银行帐号导出到文本文件后分两部分,前8位和后8位分开。
如果要用你上面的语句插入的话,文本文件必须存在,而且有一行:姓名,银行账号1,银行账号2
然后就可以用下面的语句进行插入
注意文件名和目录根据你的实际情况进行修改.
insert into
opendatasource('MICROSOFT.JET.OLEDB.4.0'
,'Text;HDR=Yes;DATABASE=C:\'
)...[aa#txt]
--,aa#txt)
--*/
select 姓名,银行账号1=left(银行账号,8),银行账号2=right(银行账号,8)
from
opendatasource('MICROSOFT.JET.OLEDB.4.0'
,'Excel 5.0;HDR=YES;IMEX=2;DATABASE=c:\a.xls'
--,Sheet1$)
)...[Sheet1$]
/*邹建 2003.08-----------------*/
DataSet FormatInfo()
{
string serverPath = @"D:\Pro12580\Pro12580Web\ServerPath\";
string ClientPath = this.fileload.PostedFile.FileName;
string fileName = null;
if (this.fileload.PostedFile.FileName != "")
{
try
{
if (Path.GetExtension(ClientPath) == ".xls")
{
fileName = Path.GetFileName(ClientPath);
this.fileload.SaveAs(serverPath + fileName);
}
else
{
Response.Write("<script>alert('导入文件只能是Excel2003版!');</script>");
}
}
catch (System.Exception ex)
{ }
finally
{
this.fileload.Dispose();
} OleDbConnection oleConn = new OleDbConnection();
oleConn.ConnectionString = "Provider=Microsoft.Jet.Oledb.4.0;data source=" + serverPath + fileName + ";Extended Properties=Excel 8.0;";
oleConn.Open();
DataTable dt = oleConn.GetSchema("Tables");
DataTableReader dtReader = new DataTableReader(dt);
OleDbCommand dc = new OleDbCommand("", oleConn);
OleDbDataAdapter da = null;
DataSet ds = new DataSet();
while (dtReader.Read())
{
dc.CommandText = "select * from [" + dtReader["Table_Name"] + "]"; da = new OleDbDataAdapter(dc);
DataTable data_dt = new DataTable(dtReader["Table_Name"].ToString().Replace('$', ' ').Trim()); da.Fill(data_dt);
ds.Tables.Add(data_dt);
break;
}
return ds;
}
return null;
}
//判断导入的数据的表
protected void btnInput_Click(object sender, EventArgs e)
{
if (!string.IsNullOrEmpty(this.fileload.PostedFile.FileName))
{
DataSet ds = FormatInfo();
if (ds != null)
{ nTotalCount = ds.Tables[0].Rows.Count;//统计信息记录的总数量
string fileName = this.fileload.PostedFile.FileName;
if (fileName.Contains("11"))
{ this.InsertEntertainment(ds);
}
else if (fileName.Contains("22"))
{ this.InsertCommunity(ds);
}
else if (fileName.Contains("33"))
{ this.InsertRestaurants(ds);
}
else if (fileName.Contains("44"))
{ this.InsertTravelAgents(ds);
}
else if (fileName.Contains("55"))
{ this.InsertAttractions(ds);
}
else if (fileName.Contains("66"))
{ this.InsertLivingService(ds);
} Response.Write("<script>alert('完毕');</script>"); this.OutErrorDataToExcel();//导出一张电子表文件(保存无效的信息以及重复的信息)
} }
else
{
Response.Write("<script>alert('请选择导入文件');</script>");
}
}
在写这个方法的类里面你可以先写一个判断数据的存在性的 方法
然后 如果存在 当做异常抛出
然后如果没有新增
在也页后台 从新调用一个方法把错误或者重复的数据插入另一个表(如果你需要这样做的话)
//这个是我们把错误的和无效的数据倒入另外一个履历表
this.ErrorOrRepeatDataCreate(dr, 1);//无效信息
在写这个方法的类里面你可以先写一个判断数据的存在性的 方法
然后 如果存在 当做异常抛出
然后如果没有新增
在也页后台 从新调用一个方法把错误或者重复的数据插入另一个表(如果你需要这样做的话)
//这个是我们把错误的和无效的数据倒入另外一个履历表
this.ErrorOrRepeatDataCreate(dr, 1);//无效信息
('MicroSoft.Jet.OleDB.4.0',
'Excel 5.0;HDR=yes;Database=D:\aa.xls'
,sheet1$)
where id not in(select id from table)
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Data.OleDb;
using System.Threading;
using System.IO;namespace DbTools
{
internal class DbToolFunc
{
public delegate void ErrorHandler(string errorMessage);
public event ErrorHandler errorMessage;
public delegate void DataCompletedHandler(DataSet data);
public event DataCompletedHandler dataCompleted; private Thread _threadShow; public DbToolFunc() { } public DbToolFunc(string xlsPathName)
{
_xlsPathName = xlsPathName;
} private string _xlsPathName = null;
public string xlsFileName
{
set { this._xlsPathName = value; }
} private DataSet _data = null;
public DataSet ImportData
{
set { this._data = value; }
} private DataSet _errordata = null;
public DataSet ErrorData
{
get { return this._errordata; }
} private void GetDataProc()
{
OleDbConnection objConn = null;
try
{
DataSet ds = new DataSet();
if (String.IsNullOrEmpty(_xlsPathName))
{
if (this.errorMessage != null) { if (this.errorMessage != null) this.errorMessage("文件名没有指定!"); }
return;
}
if (!File.Exists(_xlsPathName))
{
if (this.errorMessage != null) { if (this.errorMessage != null) this.errorMessage("文件不存在!"); }
return;
}
string strConn = "Provider=Microsoft.Jet.OleDb.4.0;" + "data source="
+ _xlsPathName + ";Extended Properties='Excel 8.0; HDR=YES; IMEX=1'"; objConn = new OleDbConnection(strConn);
objConn.Open();
DataTable schemaTable = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
OleDbDataAdapter sqlada = new OleDbDataAdapter();
foreach (DataRow dr in schemaTable.Rows)
{
string strSql = "Select * From [" + dr[2].ToString().Trim() + "]";
OleDbCommand objCmd = new OleDbCommand(strSql, objConn);
sqlada.SelectCommand = objCmd;
sqlada.Fill(ds, dr[2].ToString().Trim());
}
objConn.Close();
if (this.dataCompleted != null) this.dataCompleted(ds);
}
catch (Exception ex)
{
objConn.Close();
if (this.errorMessage != null) this.errorMessage(ex.Message);
}
} public void GetDataSetFromExcel()
{
if (this._threadShow != null && this._threadShow.ThreadState == ThreadState.Running)
{
return;
}
this._threadShow = new Thread(new ThreadStart(GetDataProc)); this._threadShow.Start();
}
}
Dim conn As OleDb.OleDbConnection
Dim AdoCmd As OleDb.OleDbCommand
Dim Adapt As New OleDb.OleDbDataAdapter
'conn = New OleDb.OleDbConnection("Driver={Microsoft Excel Driver (*.xls)};DBQ=" & FilePath)
conn = New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & FilePath & ";Extended Properties=Excel 8.0;")
conn.Open()
AdoCmd = New OleDb.OleDbCommand
AdoCmd.Connection = conn
Value = New DataTable
Try
AdoCmd.CommandType = CommandType.Text
AdoCmd.CommandText = strSQL
Adapt.SelectCommand = AdoCmd
Adapt.Fill(Value)
Catch ex As Data.OleDb.OleDbException
AdoCmd.Dispose()
Adapt.Dispose()
conn.Close()
conn.Dispose()
Throw New Exception(ex.Message)
Return -1 'TODO:无法连接数据库
Catch ex As Exception
AdoCmd.Dispose()
Adapt.Dispose()
conn.Close()
conn.Dispose()
Throw New Exception(ex.Message)
Return -1
End Try
AdoCmd.Dispose()
Adapt.Dispose()
conn.Close()
conn.Dispose()
Return 0
End Function其中 strSql = "select * from [" & strSheetName & "$]"
FilePath = "Excel文件的物理位置"
Value = "接收Excel数据的DataTable"