Use commands in SQL Server's query analyzer to import data in an Excel table

There is a time column in the Excel file, and the text and time are mixed. For example, there are many hh: Mm columns in the file at a.m.. It is convenient to use dtswizard to import data, but hh: Mm changes to null, setting the import data type in the import settings cannot be implemented.

To implement the Statement, the following statements can be implemented:

 

The Microsoft Jet Database Engine can use the installable index sequential access method (isam) driver to access data in the format of other database files (such as Excel workbooks. To open the external formats supported by the Microsoft JET 4.0 ole db provider, specify the database type in the extended properties of the connection. The jet ole db Provider supports the following database types for Microsoft Excel workbooks:

Excel 3.0
Excel 4.0
Excel 5.0
Excel 8.0

Note:: For Microsoft Excel 5.0 and 7.0 (95) workbooks, use the Excel 5.0 source database type. For Microsoft Excel 8.0 (97), 9.0 (2000), and 10.0 (2002) workbooks, use the Excel 8.0 source database type. The example in this article uses an Excel Workbook in the format of Excel 2000 and Excel 2002.

Code

1.

Select * into newtable1

From OpenDataSource ('Microsoft. Jet. oledb.4.0 ',

'Data source = "C: \ yyyy_wind.xls ";

User ID =; Password =; extended properties = "Excel 8.0; HDR = yes; IMEX = 1" ')... sheet1 $

2.

Select * into newtable2

From OpenDataSource ('Microsoft. Jet. oledb.4.0 ',

'Data source = c: \ yyyy_wind.xls; extended properties = "Excel 8.0; HDR = yes; IMEX = 1" ')... sheet1 $

3.

Select * into newtable3

From OpenRowSet ('Microsoft. Jet. oledb.4.0 ',

'Excel 8.0; database = c: \ yyyy_wind.xls; HDR = yes; IMEX = 1', sheet1 $)

 

 

This sheet refers to the default Sheet 1 in Excel, which can be modified. The actual file path is used.

There are actually a lot of information on the Internet, but it is difficult to figure out because both OpenDataSource and OpenRowset can be used and double quotation marks "" single quotation marks ''are added. The key issue is that when OpenDataSource is used, the content of extended properties is separated by semicolons and enclosed in double quotation marks. sheet1 $ is out of brackets, and OpenRowset is relatively concise.

The following describes two parameters: HDR and IMEX.

If HDR is yes, the first row of the Excel file is the column name of the sqlserver table. If it is no, it is the first row of the table.

IMEX = 1 means tells the driver to always read "intermixed" Data columns as text

Here, driver refers to the Excel driver.

The Excel driver reads a certain number of rows in the specified source (eight rows by default) to predict the Data Type of each column. If it is estimated that the column may contain the hybrid data type (especially when the text data is mixed), the driver will decide to use the dominant data type, return NULL values for units that contain other types of data. (If the number of data types is the same, the value type is used .)

The setting of most cell formats in an Excel worksheet does not affect the determination of this data type. (It is said that when writing an Excel table, it is set to all text. Yes, thanks !)

Due to security improvement, sqlserver2005 still has problems using the above statements directly (2000 can be used directly ).

Follow the prompts to perform the following operations:

From the Start Menu, choose configuration tools> SQL Server 2005 peripheral application configurator> service and connected peripheral configurator> datebase engine to start the service, and then restart the sqlserver service. Because SQL Server 2005 does not have a chart in the lower-right corner of the taskbar like 2000, you need to go to management tools> services through the control panel to stop and start the SQL Server service.

Run regedit to go to the Registry Editor HKEY_LOCAL_MACHINE \ SOFTWARE \ Microsoft SQL Server \ mssql.1 \ providers \ microsoft. Jet. oledb.4.0 and create a New DWORD named disallowadhocaccess with a value of 0.