Import excel file to SQL express

How do I Import excel file to SQL express

Do you mean you want to import the data from the Excel file, or store the Excel file itself in a BLOB field?

If the former, you can use the Jet 4.0 OLEDB provider to query Excel using SQL in exactly the same way as if it is an Access table. Select the data into a datatable, then export it to your Sql Server database.

|||I want to import the data from the Excel file to a Sql express database table|||Here's how to do it:http://davidhayden.com/blog/dave/archive/2006/05/31/2976.aspx|||

Hi Websta4,

Some additional information here, you can also use T-SQL script likes below:

SELECT*INTO myTableFROMOPENROWSET('Microsoft.Jet.OLEDB.4.0',

'Excel 8.0;Database=d:\test.xls', [Sheet1$])

You can run it directly in Management Studio or Copy the script in SqlCommand and execute it with NonQuery for .NET developing.

If you want to know any other way of importing data from Excel to SQL Server, I think this link is valuable.

http://support.microsoft.com/kb/321686

|||

I'm using SQL Server 2005 Express, SQL Server Management Studio Express and VWD 2005 Express, Excel 2003 and have tried your code above ie:

SELECT*INTO SomeTableFROMOPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=c:\test.xls', [Sheet1$])

but I get the error:

Msg 15281, Level 16, State 1, Line 1

SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure. For more information about enabling 'Ad Hoc Distributed Queries', see "Surface Area Configuration" in SQL Server Books Online.

Lost and confused. What does this mean? Is this the easiest way for importing Excel data into Sql Express for novices?

|||

This option is set to disabled bydefault you need to enable it from the Surface Area Configuration Menu in Programs>Sql Server 2005>configuration Tools>Sql Surface Area Configuration.

After the pop up will come select surface area configuration for features

Then it will open with a tree of feature.. for Clikc on AdHoc Queries Node and Enable the Check Box on right hand side

Let me know if you found any problem. But you should be admin to do that

|||

Satya, thank you!! That worked.

Question: When I run the SQL command I get an error saying the table already exists. For example: Let's say the Excel file gets updated every month. The first time you import he Excel data it will work fine. But the second time (and any other time), it will fail becuase it says the table already exists. Is there a parameter or switch or some other command to tell it to append the exisiting contents or overwrite the exisiting contents in the SQL Server Express database table?

Error:Msg 2714, Level 16, State 6, Line 1

There is already an object named 'SomeTable' in the database.

|||

THe simple way is to modify your query a bit like this

SELECT*INTO SomeTemp_TableFROMOPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=c:\test.xls', [Sheet1$])

INSERT INTO PERMANENT_TABLE

Select * from SomeTemp_Table

DROP TABLE SomeTemp_Table

-- where the PERMANENT_TABLE is your table in which each month you want to insert data

-- SomeTemp_Table is table a temporary one which will be used to to copy data from excel and then to your Permanent_Table

Mark as answer if this helped you

sql