Import external Data (excel,txt) to SQL Server via heterogeneous queries (OPENDATASOURCE)

Whether in sqlcmd or in C # logic, or directly in SQL Server, a personal summary can be which comes for either of these ways:
1,ssis;
2,oledb Drive engine.

SSIS really works, and now it's not going to be discussed here. Most of them use the second method, below, I also in the second method in SQL Server to import Excel and TXT format file specific examples to illustrate how to flexibly import data through OLE DB in the project!
First, the necessary environment description This example uses SQL statements to complete the import operation of the data in the server.
Before this instance starts, you may need to install the Accessdatabase engine package first
(I am a 64-bit machine with 32-bit Office components installed, so I need to install the Office Driver engine package separately (this engine package is intended to enable the data transfer between Office system files and Office applications)-you need to uninstall the 32-bit Office components first in this step. Otherwise, the driver installation is not successful)
I use the engine package as follows:
http://www.microsoft.com/zh-cn/download/details.aspx?id=13255
After the installation is complete, the specific configuration can be equipped here, the recommendation is not necessary, the default is good.

Second, code preparation-build database and stored procedure build database There is not much to say, to build databases that match the imported data.
For project development and ease of use management, stored procedures need to be created to bulk import external data.
Because this example takes Excel and TXT as a separate example, you need to create a new two stored procedure-"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 (+) Declare @FirstfieldValue varchar (+)--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_ The ID (n ' ' [dbo].[' + @trgTable + '] ') ' and type in (n ' U ') ') ' Set @mySql = @myIntermediate + ' DROP TABLE ' + @trgTableexec (@my SQL)--print @mySql--load tableset @mySql = ' SELECT ' + @xlsFields + ' into ' + @trgTable + ' from OPENROWSET (' Microsoft.a Ce. 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 (+) DECLARE @FirstfieldValue varchar (--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 + ' I NTO ' + @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)

Third, file preparation, import source and Schema.ini configuration file for Excel files, the relative TXT format is much simpler, but the following code

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$] ')--(for office2007 and above, 2003 with Jet-"microsoft.jet.oledb.4.0")

Other things to note is to ensure that the data format in Excel is a unified text format, here is simply a case diagram, not detailed description.
Img-blog3
For TXT format files, I use the following format.
Img-blog4
The marked part is the title used;
OK, here's the key, we need to import the configuration in the TXT format alone schema.ini! If the import is unsuccessful or wrong, most of the steps are wrong.
(PS: This profile and the import source in the same folder, in addition, the folder directory should not be too long or contain spaces, and so on, these own attention!) ）
The canonical format of Schema.ini is as follows.
Img-blog5
It is important to note that within the domain value, the first row represents the data source file name, the second line to line sixth is the necessary setup and description, everyone changes as needed, and the seventh line must be incremented by Col from the 1 index, and the column name and format displayed on the right. Specific interested students can try their own operation, look at the difference.

Four, execute and check execution code as follows

Use Woox_cqmexecute sp_activatedistributedqueries-----------------------------SAP Tables------------------------- ----DECLARE @LoadPath varchar 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] '

The following code can be used to test the import data whether there is a gap, students remember to change the hardcored I used.

SELECT * from OpenDataSource (' microsoft.ace.oledb.12.0 ', ' Data source= ' C:\InitialData\SAPdata "; Extended properties=text ') ... Sap_tw06s#txt

OK, probably the code is like this, if there is any do not understand or have different views, the message can be discussed!

Import external Data (excel,txt) to SQL Server via heterogeneous queries (OPENDATASOURCE)