Ways for Importing Excel Data

Question

Post reply

Ways for Importing Excel Data

Ramesh Saive

SSC-Insane

Points: 24275
More actions
September 18, 2006 at 3:58 am

#114799

Friendz,
I need to build an SP which will import data from excel files to the system. We've 15-20 excel files which has 25000+ records.
I just want to know what/which is the best way to accomplish the task?

--Ramesh
Data sources. The source of data in an Excel workbook can be a worksheet, to which the $ sign must be appended (for example, Sheet1$), or a named range (for example, MyRange). In a SQL statement, the name of a worksheet must be delimited (for example, [Sheet1$]) to avoid a syntax error caused by the $ sign. The Query Builder automatically adds these delimiters. When you specify a worksheet or range, the driver reads the contiguous block of cells starting with the first non-empty cell in the upper-left corner of the worksheet or range. Therefore you cannot have empty rows in the source data, or an empty row between title or header rows and the data rows.
Missing values. The Excel driver reads a certain number of rows (by default, 8 rows) in the specified source to guess at the data type of each column. When a column appears to contain mixed data types, especially numeric data mixed with text data, the driver decides in favor of the majority data type, and returns null values for cells that contain data of the other type. (In a tie, the numeric type wins.) Most cell formatting options in the Excel worksheet do not seem to affect this data type determination. You can modify this behavior of the Excel driver by specifying Import Mode. To specify Import Mode, add IMEX=1 to the value of Extended Properties in the connection string of the Excel connection manager in the Properties window. For more information, see PRB: Excel Values Returned as NULL Using DAO OpenRecordset.

David Burrows SSC Guru Points: 65138 More actions · Answer 1

You can use

SELECT * FROM

OPENROWSET('Microsoft.Jet.OLEDB.4.0',

'Excel 8.0;HDR=YES;Database=C:\temp\book1.xls', [sheet1$])

and use dynamic sql to substitute the workbook name

but beware that when SQL reads Excel files is uses the first few rows (using a value in the registry) to determine what the column data type is and will return null if any data does not match the datatype chosen

There are some MS articles (as well as a few on this site) that describe ways to try to overcome this but I have never got it to work correctly

Far away is close at hand in the images of elsewhere.
Anon.

Charles Barnhart-229428 SSC-Addicted Points: 405 More actions · Answer 2

Using the IMEX=1 option overcomes the problem (if it is a problem) of SQL Server engine trying to determine data type and size. It can lead to real problems if a column has mixed numbers and alpha characters or null values. Also, if the column contains a large amount of text that is not represented in the first 8 rows. See excerpt from MSDN: