Import external data (Excel, TXT) to SqlSe through heterogeneous query (OpenDataSource)

Whether using sqlcmd or using C # logic processing or directly performing operations in SQL Server, the following two methods can be summarized: 1, SSIS; 2, OLEDB driver engine. SSIS is actually easy to use. I will not discuss it here. Most people use the second method. below, I use the second method to export data in SqlServer.

Whether using sqlcmd or using C # logic processing or directly performing operations in SQL Server, the following two methods can be summarized: 1, SSIS; 2, OLEDB driver engine. SSIS is actually easy to use. I will not discuss it here. Most people use the second method. below, I use the second method to export data in SqlServer.

Whether using sqlcmd or using C # logic processing or directly executing operations in SQL Server, the following two methods can be summarized:
1, SSIS;
2. OLEDB driver engine.

SSIS is actually easy to use. I will not discuss it here. Most of them use the second method. below, I also use the second method to import excel and txt files in SqlServer to illustrate how to import data through OLEDB flexibly in the project!
1. the necessary environment indicates that this instance uses SQL statements to import data in SqlServer.
Before starting the instance, you may need to install the AccessDatabase engine package.
(The 32-bit office component is installed on the 64-bit host, therefore, you need to install the office driver package (this engine package is used to transfer data between office system files and office applications). In this step, you must first uninstall the 32-bit office component, otherwise, the system prompts that the driver is not successfully installed)
My engine package is as follows:
Http://www.microsoft.com/zh-cn/download/details.aspx? Id = 13255
After the installation is complete, the specific configuration can be configured here. We recommend that you use the default configuration if not necessary.

2. Code preparation-database creation and database creation in the stored procedure. It is not mentioned here that a database that matches the imported data is created.
For project expansion and ease of use and management, you need to create a stored procedure to import external data in batches.
Because this example uses excel and txt as examples, you need to create two stored procedures-"sp_ReadXLSSource" and "sp_ReadSource ". The Code is as follows:
Stored Procedure: sp_ReadXLSSource

USE [CUSTOMS]GO/****** Object:  StoredProcedure [dbo].[sp_ReadXLSSource]    Script Date: 11/21/2014 3:01:39 PM ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER PROCEDURE [dbo].[sp_ReadXLSSource]@trgPath varchar(255),@trgTable varchar(255),@xlsFile varchar(255),@xlsSheet varchar(255),@xlsFields varchar(8000)ASdeclare @mySql varchar(8000)declare @myIntermediate varchar(8000)declare @Firstfield varchar(100)declare @FirstfieldValue varchar(100)--Lookup lastfieldset @Firstfield = dbo.fnGetFirstField(@xlsFields)set @FirstfieldValue = RTRIM(LTRIM(SUBSTRING(@Firstfield,2,CHARINDEX(']',@Firstfield,0)-2)))--PRINT @FirstfieldValue--Drop table if existsset @myIntermediate = 'IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''[dbo].[' + @trgTable + ']'') AND type in (N''U''))'set @mySql = @myIntermediate + ' DROP TABLE ' + @trgTableexec(@mySql)--PRINT @mySql--Load Tableset @mySql = 'SELECT ' + @xlsFields + ' INTO ' + @trgTable + ' FROM OPENROWSET(''Microsoft.ACE.OLEDB.12.0'',  ''Excel 12.0;DATABASE=' + @trgPath + '\' + @xlsFile + ';IMEX=1'',  ''Select * from ' + @xlsSheet + ''')'+ 'WHERE NOT ' + @Firstfield + ' IS NULL'--PRINT @mySqlexec(@mySql)

Stored Procedure: sp_ReadSource

USE [WOOX_CQM]GO/****** Object:  StoredProcedure [dbo].[sp_ReadSource]    Script Date: 11/21/2014 2:54:33 PM ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER PROCEDURE [dbo].[sp_ReadSource]@trgPath varchar(255),@trgTable varchar(255),@trgFields varchar(8000)ASdeclare @mySql varchar(8000)declare @myIntermediate varchar(8000)declare @Firstfield varchar(100)declare @FirstfieldValue varchar(100)--Lookup lastfieldset @Firstfield = dbo.fnGetFirstField(@trgFields)set @FirstfieldValue = RTRIM(LTRIM(SUBSTRING(@Firstfield,2,CHARINDEX(']',@Firstfield,0)-2)))--PRINT @FirstfieldValue--Drop table if existsset @myIntermediate = 'IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''[dbo].[' + @trgTable + ']'') AND type in (N''U''))'set @mySql = @myIntermediate + ' DROP TABLE ' + @trgTableexec(@mySql)--PRINT @mySql--Load Tableset @mySql = 'SELECT ' + @trgFields + ' INTO ' + @trgTable + ' ' +'FROM OpenDataSource (''Microsoft.ACE.OLEDB.12.0'',''Data Source="' + @trgPath + '";Extended properties=Text'')...' + @trgTable + '#txt ' + 'WHERE NOT ' + @Firstfield + ' IS NULL AND ' + @Firstfield + ' <> ' + '''' + @FirstfieldValue + ''''--PRINT @mySqlexec(@mySql)

Iii. File preparation. importing the source and Schema. ini configuration files is much simpler than the txt format for excel files, but the following code is critical.

Truncate table WOOX_INFORMATICA..VBPA; insert into WOOX_INFORMATICA..VBPA select * from openrowset ('Microsoft. ACE. OLEDB.12.0 ', 'excel 12.0; Database = C: \ InitialData \ db_data.xlsx; HDR = YES', 'select * FROM [VBPA $] ') -- (applicable to office2007 and later versions, 2003 use Jet-"Microsoft. jet. OLEDB.4.0 ")

Note that the data format in excel is a unified text format. Here, we only provide a simple example chart, which is not described in detail.


For txt files, the format is as follows.
Authorization + signature + 7W8yOuyu7PJuaa78tXfs/Signature + signature + vNDPwqOswe3N4qOszsS8/rzQxL/Signature = "brush: SQL;"> export sap_tvstz.txt] FORMAT = Delimited (|) colNameHeader = TrueMaxScanRows = 0 CharacterSet = character = 'col1 = SKIP1 TEXTCol2 = SKIP2 TEXTCOL3 = VSBED TEXTCOL4 = LADGR char (100) COL5 = WERKS TEXTCOL6 = LGORT TEXTCOL7 = VSTEL TEXT

The first line indicates the name of the data source file within the domain value. The second and sixth lines are necessary settings and instructions; the column names and formats displayed on the right must increase progressively from 1 index according to Col. If you are interested, you can try to see the difference.

4. Run and check the Execution Code as follows:

USE WOOX_CQMEXECUTE sp_ActivateDistributedQueries----------------------------- SAP Tables -----------------------------DECLARE @LoadPath varchar(2000)SET @LoadPath = 'C:\InitialData\SAPdata'--import Excel--EXECUTE sp_ReadXLSSource @LoadPath, 'SAP_TCURF', 'SAP_TCURF.xls', '[Sheet1$]','[KURST],[FCURR],[TCURR],[GDATU], [FFACT],[TFACT],[FromDate],[ToDate]'--import txt--EXECUTE sp_ReadSource @LoadPath, 'SAP_TVSTZ', '[VSBED],[LADGR],[WERKS],[LGORT],[VSTEL]'

Use the following code to test whether there is a gap between the imported data. Remember to change your hardcored.

select * FROM OpenDataSource ('Microsoft.ACE.OLEDB.12.0','Data Source="C:\InitialData\SAPdata";Extended properties=Text')...SAP_TW06S#txt

OK. This is probably the Code. If you don't understand anything or have different opinions, you can leave a message to discuss it!