******* Export to Excelexec master..xp_cmdshell 'bcp settledb.dbo.shanghu out c: /temp1.xls -c -q -s "gnetdata / gnetdata" -u "sa" -p ""' / *********** Import Excelselect * from OpenDataSource ('Microsoft.jet.OleDb.4.0', 'data source = "c: /test.xls"; user ID = admin; password =; Extended Properties = Excel 5.0 ') ... xactions / * Dynamic file name declare @fn varchar (20), @fn =' c: /test.xls'Set @S = '' Microsoft.jet .Oledb.4.0 '', '' data source = "' @ fn '"; user ID = admin; password =; extended proties = excel 5.0 '' set @S = 'SELECT * from OpenDataSource (' @ S ') ... Sheet1 $' EXEC (@S) * / SELECT CAST (Cast) AS NVARCHAR (255)) '' Convert Alias from OpenDataSource ('Microsoft.jet .Oledb.4.0 ',' data source = "c: /test.xls"; user ID = admin; password =; extended proties = excel 5.0 ') ... XActionS / *********** *********** EXCEL is derived to remote SQLINSERT OPENDATASOURCE ('sqloledb', 'data source = remote ip; user ID = sa; password = password'). Database name .dbo. Table name (column name 1, column 2) SELECT column name 1, column name 2From OpenDataSource ('microsoft.jet.oledb.4.0', 'data source = "c: /test.xls"; user id = admin; passw ORD =; Extended Properties = Excel 5.0 ') ... XActionS / ** Import text file EXEC MASTER..XP_CMDSHEC' BCP DBNAME..TABLENAME IN C: /DT.TXT -C -SSERVERNAME -USA-PPASSWORD '/ ** Export text file EXEC MASTER..XP_CMDSHELL 'BCP DBNAME..TABLENAME OUT C: /DT.TXT -C -SSERVERNAME-ISA-PVASSWORD' or EXEC MASTER..TEC MASTER..TRP "Select * from dbname..tablename"
Queryout c: /dt.txt -c -sservername -usa -ppassword 'Exports to TXT text, separate the exec master..xp_cmdshell' bcp "in comma" library name .. Name "D: /tt.txt" -c -t, -u sa -p password 'bulk INSERT library name .. Table name from' c: /test.txt'with (Fieldterminator = ';', rowterminator = '/ n') - / * DBase IV file SELECT * from OpenRowSet ('Microsoft.jet.OleDb.4.0', 'DBASE IV; HDR = NO; IMEX = 2; Database = C: /', 'SELECT * FROM [Customer Data 4.DBF] "- * / - / * DBASE III file Select * from OpenRowSet ('microsoft.jet.oledb.4.0', 'DBASE III; HDR = NO; IMEX = 2; Database = C: /', 'SELECT * FROM [Customer Information 3. DBF] ') - * / - / * FoxPro Database Select * from OpenRowSet (' msdasql ',' Driver = Microsoft Visual FoxPro Driver; SourceType = DBF; SourceDb = C: / ',' Select * from [aa.dbf ] ') - * / / ************** Import DBF file **************** / 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 / ******** ********* Export to DBF ************** / If you want to export data into the FoxPro list that has been generated (ie, existing) FoxPro, you can use the following SQL Statement Insert Into OpenRowSet ('MSDasql', 'Drive R = Microsoft Visual FoxPro Driver; SourceType = DBF; SourceDb = C: / ',' Select * from [aa.dbf] ') SELECT * FROM Table Description: SOURCEDB = C: / Specifies the folder Aa.dbf in the FoxPro table Specify the file name of the FoxPro table. / ************* Export to Access ****************************** / INSERT INTO OpenRowSet (' Microsoft.jet.Oledb.4.0 ',' x: /a.mdb ';' admin ';' ', table SELECT * FROM database name ..b table / *********** * Import Access ******************** / INSERT INTO B table selet * from OpenRowSet ('Microsoft.jet.OleDb.4.0', 'x: /a.mdb ';' admin ';
'', A table) file named parameter declare @fname varchar (20) set @fname = 'd: /test.mdb'Exec (' select a. * From openDataSource ('' Microsoft.jet.OleDb.4.0 '" '' @ fname ''; '' admin ''; '' '' ', Topics AS A') Select * from OpenDataSource ('Microsoft.jet.OleDb.4.0', 'Data Source = "f: /NORTHWIND.MDB"; Jet OLEDB: Database Password = 123; User ID = Admin; Password =; ') ... Products ******************** Import XML file declare @idoc @ @DDeclare @doc varchar (1000) - Sample XML DocumentSet @doc = '
IDOC ??????? / ********************* EXCEL is guided to txt *************** ************************ / I want to use Select * Into OpenDataSource (...) from openDataSource to implement the contents of an Excel file Import to a text file assumes two columns in Excel, the first list is named, the second list is a very account (16-bit) and the bank account is exported to the text file, and the first 8 bits and the last 8 bits are separated.
Zou Jian: If you want to insert the statement above, the text file must exist, and there is a line: name, bank account 1, bank account 2 can then use the following statement to pay attention file name and directory according to your actual situation Modify. INSERT INTOOPENDATASOURCE ('Microsoft.jet.OleDb.4.0', 'text; HDR = YES; Database = C: /') ... [aa # txt] -, aa # txt) - * / SELECT Name , Bank account 1 = Left (bank account, 8), bank account 2 = Right (bank account, 8) from OpenDataSource ('Microsoft.jet.OleDb.4.0', 'Excel 5.0; HDR = YES; IMEX = 2; Database = C: /A.XLS '-, Sheet1 $) ... [Sheet1 $] If you want to insert and generate text files directly, use bcp declare @sql varchar (8000), @ tbname varchar (50) - First import the Excel table content into a global temporary table select @tbname = '[## Temp' Cast (newid () as varchar (40)) ']', @ SQL = 'SELECT name, bank account 1 = Left (bank account, 8), bank account 2 = Right (bank account, 8) INTO ' @ TBNAME ' from OpenDataSource ('' microsoft.jet.OleDb.4.0 ',' 'Excel 5.0; HDR = YES; IMEX = 2; Database = C: /A.XLS ') ... [Sheet1 $]' EXEC (@SQL) - then use BCP to export from global temporary table to text file set @ SQL = 'bcp " @ TBNAME '"OUT" C: /AA.TXT "/ S" (local) "/ p" "/ c'exec master..xp_cmdshell @sql - Delete Temporary Table EXEC (' Drop Table ' @ TBNAME) / ***************************************************************************************************************** ************************************************************** Call example: - Export call example ---- Export a single table EXEC File2Table 'ZJ', '', '', 'Xzkh_sa .. Region Information', 'C: /Zj.txt', 1 ---- Exporting the entire database EXEC FILE2TABLE' ZJ ',' ',' ',' Xzkh_sa ',' C: / DOCMAN ', 1 - Import Call Example ---- Import a single table exec file2table' zj ',' ',' ',' xzkh_sa .. Area, 'c: / zj.txt' , 0 --- Import the entire database EXEC file2Table 'zj', '', '', 'xzkh_sa', 'c: / docman', 0 * / if EXISTS (SELECT 1 from sysobjects where name =
'File2Table' and objectproperty (id, 'IsProcedure') = 1) drop procedure File2Tablegocreate procedure File2Table @ servername varchar (200) - the server name, @ username varchar (200) - the user name, if the NT authentication, was Empty '', @ Password Varchar (200) - Password, @ TBName Varchar (500) - Database .dbo. Table name, if not specified: .dbo. Download, export all user tables for the database, @ filename varchar (1000) - Import / Export Path / File Name, if the @TBName parameter indicates to export the entire database, this parameter is the file storage path, the file name is automatically used .txt, @ isout bit --1 is exported, 0 To introduce asclare @SQL varchar (8000) if @tbname like '%.%.%' - If the table name is specified, then directly export a single table beginset @ SQL = 'bcp' @ TBName Case when @ isout = 1 Then 'out' else 'in' end '"' @ filename '" / w' '/ s' @ Servername Case When ISNULL (@usename, ') =' 'TEN' ELSE '/ U' @ username end '/ p' isnull (@password, '') exec master..xp_cmdshell @sqlendelsebegin - Export the entire database, define a cursor, remove all user table 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 #tbfetch next from #tb @m_tbnamewhile @@ fetch_status = 0beginset @ SQL = 'bcp' @ TBNAME '..' @ m_tbname case when @ iSout = 1 TEN 'OUT' ELSE 'IN' END '"' @ filename @ m_tbname '. txt" / w' '/ s ' @ Servername Case When ISNULL (@uSername,' ') =' 'TEN' '
