Click here to Skip to main content
15,867,686 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
import option i have used to convert excel file into sql db but the problem is i want to add one more column with excel data file for primary key(auto number). how can i do this?
Posted
Comments
ArunRajendra 26-Sep-13 1:21am    
Does the table exist in the database while you are importing?
Muhamad Faizan Khan 26-Sep-13 1:22am    
no
coded007 26-Sep-13 1:59am    
Just add another column in the table as increment and try to load the file. I will solves your problem

1 solution

SQL
Insert into TableName -- If Table Already exists
SELECT Sno,Data,Row_Number() Over (Order by Sno) as AutoNumber 
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
                'Excel 12.0 Xml;HDR=YES;Database=C:\test.xlsx','SELECT * FROM [Sheet1$]');

If Table not exists
SQL
SELECT Sno,Data,Row_Number() Over (Order by Sno) as AutoNumber into #XLImport1 FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
                'Excel 12.0 Xml;HDR=YES;Database=C:\test.xlsx','SELECT * FROM [Sheet1$]');

Tested:
SQL
Exec sp_configure 'show advanced options', 1;
RECONFIGURE;
GO

Exec sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;
GO

EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'AllowInProcess' , 1 
GO
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'DynamicParameters' , 1
GO

SELECT Sno,Data,Row_Number() Over (Order by Sno) as AutoNumber into #XLImport1 
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
		'Excel 12.0 Xml;HDR=YES;Database=C:\test.xlsx','SELECT * FROM [Sheet1$]');

Select * FROM #XLImport1
Drop Table #XLImport1

EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'AllowInProcess' , 0
GO
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'DynamicParameters' , 0
GO

Exec sp_configure 'Ad Hoc Distributed Queries', 0;
RECONFIGURE;
GO

Exec sp_configure 'show advanced options', 0
RECONFIGURE;
GO

Using OpenDataSource
SQL
SELECT * INTO #XLImport3 
FROM OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0',
                    'Data Source=C:\test.xlsx;Extended Properties=Excel 12.0')...[Sheet1$]

Check this CleanCode Import to SqlServer from Excel
 
Share this answer
 
v3
Comments
Muhamad Faizan Khan 26-Sep-13 3:21am    
i received this error
Msg 15281, Level 16, State 1, Line 2
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', search for 'Ad Hoc Distributed Queries' in SQL Server Books Online.

as i execute this

--Excel 2007-2010
SELECT * --INTO #Cars
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'Excel 12.0 Xml;HDR=YES;Database=C:\Bukhari.xlsx','SELECT * FROM [Sheet1$]');

I am using SQL Express 2012
Raja Sekhar S 26-Sep-13 3:23am    
Thats why i have given queries for enabling adhoc queries use my tested code...
Muhamad Faizan Khan 26-Sep-13 3:36am    
when i try to execute this query
--Excel 2007-2010
SELECT * --INTO #Cars
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'Excel 12.0 Xml;HDR=YES;Database=C:\Bukhari.xlsx','SELECT * FROM [Sheet1$]');
i got this error
Cannot create an instance of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".
Raja Sekhar S 26-Sep-13 3:43am    
use this

Exec sp_configure 'show advanced options', 1;
RECONFIGURE;
GO

Exec sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;
GO

EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'AllowInProcess' , 1
GO
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'DynamicParameters' , 1
GO

SELECT Sno,Data,Row_Number() Over (Order by Sno) as AutoNumber into #XLImport1
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'Excel 12.0 Xml;HDR=YES;Database=C:\test.xlsx','SELECT * FROM [Sheet1$]');

Select * FROM #XLImport1
Drop Table #XLImport1

EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'AllowInProcess' , 0
GO
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'DynamicParameters' , 0
GO

Exec sp_configure 'Ad Hoc Distributed Queries', 0;
RECONFIGURE;
GO

Exec sp_configure 'show advanced options', 0
RECONFIGURE;
GO

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900