[MSSQL] Excel 을 DB 처럼 (Openrowset)

출처 : http://www.sql-server-helper.com/tips/read-import-excel-file-p01.aspx

Part 1: Querying an Excel Worksheet OPENROWSET To query or read an Excel file without using DTS, you can use theOPENROWSET function.  The OPENROWSET function includes all connection information necessary to access remote data from an OLE DB data source, and in this case from an Excel file data source.  This method is an alternative to accessing tables in a linked server and is a one-time, ad-hoc method of connecting, accessing and updating remove data using OLE DB.  The OPENROWSET function can be referenced in the FROM clause of a query as though it is a table name.  The OPENROWSET function can also be referenced as the target table of an INSERT, UPDATE or DELETE statement, as will be shown later, subject to the capabilities of the OLE DB provider. The syntax of the OPENROWSET function is as follows: OPENROWSET ( 'provider_name', { 'datasource'; 'user_id'; 'password' | 'provider_string' }, { [ catalog.][schema.]object | 'query' } ) In this article, we will only be using the following syntax of the OPENROWSET: OPENROWSET ( 'provider_name', 'provider_string', 'query' ) The provider_name is a character string that represents the friendly name of the OLE DB provider as specified in the registry.  In the case of Excel, the provider_name that we will use is "Microsoft.Jet.OLEDB.4.0". The provider_string is a provider-specific connection string that is passed to initialize the OLE DB provider.  provider_stringtypically encapsulates all the connection information needed to initialize the provider.  For Excel, the provider_string that we will use is "Excel 8.0;Database=Excel File Name". The query is a string constant sent to and executed by the provider.  SQL Server does not process the query but processes query results returned by the provider (a pass-through query).

Querying an Excel Worksheet Let's say you have an Excel file named Addresses.xls containing addresses of people with the following sample data: | A | B | C | D | E | F | ------------------------------------------------------------------------------------------------ 1 | FirstName | LastName | Address | City | State | ZIP | 2 | Mickey | Mouse | Walt Disney World | Lake Buena Vista | FL | 32830 | 3 | Donald | Duck | Walt Disney World | Lake Buena Vista | FL | 32830 | 4 | George | Bush | 1600 Pennsylvania Avenue NW | Washington | DC | 20500-0003 | 5 | George | Clooney | 151 El Camino Drive | Beverly Hills | CA | 90212-2704 | 6 | Tom | Cruise | 9830 Wilshire Boulevard | Beverly Hills | CA | 90212-1804 | To query this data from the Excel file without using DTS to import it to a table in SQL Server, you can do the following SELECT statement with OPENROWSET: SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:\Source\Addresses.xls', 'SELECT * FROM [Sheet1$]') Sheet1 is the name of the worksheet in the Excel file.  Make sure that the name of the worksheet is followed by the dollar sign ($) when specifying in the query.  If the dollar sign is not included, the following error will be encountered: Server: Msg 7399, Level 16, State 1, Line 1 OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. [OLE/DB provider returned message: The Microsoft Jet database engine could not find the object 'Sheet1'. Make sure the object exists and that you spell its name and the path name correctly.] OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IColumnsInfo::GetColumnsInfo returned 0x80004005: ]. The result of this query is as follows: FirstName LastName Address City State ZIP ---------- ---------- ------------------------------ -------------------- ------ ---------- Mickey Mouse Walt Disney World Lake Buena Vista FL NULL Donald Duck Walt Disney World Lake Buena Vista FL NULL George Bush 1600 Pennsylvania Avenue NW Washington DC 20500-0003 George Clooney 151 El Camino Drive Beverly Hills CA 90212-2704 Tom Cruise 9830 Wilshire Boulevard Beverly Hills CA 90212-1804 By default, the first row in the Excel file is assumed to be the column header, as can be seen from the output. It should be noted that the Excel file must be located in the SQL Server where you are connected and not on your local machine.  The C: drive specified in the OPENROWSET function is the C: drive of the SQL Server you are connected to and not your local C: drive.  If the Excel file specified in the provider_string parameter of the OPENROWSET function does not exist or is incorrect, the following error will be encountered: Server: Msg 7399, Level 16, State 1, Line 1 OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. The provider did not give any information about the error. OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize returned 0x80004005: The provider did not give any information about the error.]. Just like any SELECT statement, conditions can be included in the query to filter out records from the Excel worksheet by simply specifying it in the WHERE clause.  As an example, to display only the addresses of people from Florida you can do the following: SELECT * INTO TargetTableName FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:\Source\Addresses.xls', 'SELECT * FROM [Sheet1$]') WHERE [State] = 'FL' This query gives the following result: FirstName LastName Address City State ZIP ---------- ---------- ------------------------------ -------------------- ------ ---------- Mickey Mouse Walt Disney World Lake Buena Vista FL 32830 Donald Duck Walt Disney World Lake Buena Vista FL 32830 Part 2: Querying an Excel Worksheet Without Header and With Mixed Column Data Type Part 3: Importing an Excel Worksheet

요건 추가 자료

Importing an Excel Worksheet With Correct Data Types

By default, the data type of string/text values from an Excel worksheet is translated to NVARCHAR(255).  This may not be the data type you want for your columns and this may not be length you want.  There are two ways to override the data type generated by the OLE DB provider when it creates the table.

The first way is to specify the data types you want for each column in the SELECT statement using the CAST function:

SELECT CAST([FirstName] AS VARCHAR(20)) AS [FirstName],
       CAST([LastName] AS VARCHAR(20)) AS [LastName],
       CAST([Address] AS VARCHAR(50)) AS [Address],
       CAST([City] AS VARCHAR(30)) AS [City],
       CAST([State] AS VARCHAR(2)) AS [State],
       CAST([ZIP] AS VARCHAR(10)) AS [ZIP]
INTO [dbo].[Addresses_Temp]
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
                'Excel 8.0;Database=C:\Source\Addresses.xls;IMEX=1',
                'SELECT * FROM [Sheet1$]')

The data types of each column will now be based on the data type specified in the CAST function and not based on the default NVARCHAR(255).

The second way is to first create the destination table, then use the INSERT statement together with the SELECT statement to import the data from the Excel worksheet:

CREATE TABLE [dbo].[Addresses_Temp] (
    [FirstName]   VARCHAR(20),
    [LastName]    VARCHAR(20),
    [Address]     VARCHAR(50),
    [City]        VARCHAR(30),
    [State]       VARCHAR(2),
    [ZIP]         VARCHAR(10)
)
GO

INSERT INTO [dbo].[Address_Temp] ( [FirstName], [LastName], [Address], [City], [State], [ZIP] )
SELECT [FirstName], [LastName], [Address], [City], [State], [ZIP]
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
                'Excel 8.0;Database=C:\Source\Addresses.xls;IMEX=1',
                'SELECT * FROM [Sheet1$]')