Hi,
Here is the query to export the data from Excel to sqlserver
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$]')
To Access the function such as OPENROWSET Go to
MicroSoftSqlServer2005--->ConfigurationTools--->
SqlSurfaceAreaConfiguration
Click on Surface Area Configuration For features
and Enable OPENROWSET and OPENDATASOURCE support
2 comments:
Doesn't it require MS Office (Excel) to be installed in the Database Server, where the SQL Server runs?
Can i point an excel sheet over web, instead of pointing a file from the local drive?
say,
http://www.ak-sabin.com/my-excel.xls
Yes, it doesnot require to have MS office installed in the machine.
I was unable to insert in to the newly sreated MyAddressTable as I was not refering the Database name.
Slight changes in the query
CREATE TABLE [dbo].[MyAddressTable] (
[FirstName] VARCHAR(20),
[LastName] VARCHAR(20),
[ZIP] VARCHAR(10)
)
GO
SELECT * FROM [MyTestDB].[dbo].[MyAddressTable]
INSERT INTO [MyTestDB].[dbo].[MyAddressTable] ( [FirstName], [LastName], [ZIP] )
SELECT [FirstName], [LastName], [ZIP]
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=E:\sabin\Try.xls;IMEX=1',
'SELECT * FROM [Sheet1$]')
Please reply if the path can be of an excel sheet through http://
Regards,
AK Sabin,
Bangalore.
Post a Comment