如何使用附加列添加excel文件 [英] how to add excel file with additional column
本文介绍了如何使用附加列添加excel文件的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
导入选项我曾经习惯将excel文件转换为sql db,但问题是我想再添加一列excel数据文件作为主键(自动编号)。我怎么能这样做?
解决方案
插入 into TableName - 如果表已存在
SELECT Sno,Data,Row_Number() Over ( Order by Sno) as 自动编号
FROM OPENROWSET (' Microsoft.ACE。 OLEDB.12.0',
' Excel 12.0 Xml; HDR = YES; Database = C: \test.xlsx',' SELECT * FROM [Sheet1
< blockquote>');
如果表不存在
SELECT Sno,Data,Row_Number() Over (订单 按 Sno) as 自动编号 into #XLImport1 FROM OPENROWSET (' Microsoft.ACE.OLEDB.12.0',
' Excel 12.0 Xml; HDR = YES;数据库= C:\test.xlsx',' < span class =code-string> SELECT * FROM [Sheet1
');
经测试:
执行 sp_configure ' 显示高级选项', 1 ;
RECONFIGURE ;
GO
执行 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',< span class =code-digit> 1
GO
SELECT Sno,Data,Row_Number() Over ( Order by Sno) 自动编号进入 #XLImport1
FROM OPENROWSET (' Microsoft.ACE.OLEDB.12.0',
' Excel 12.0 Xml; HDR = YES; Database = C:\test.xlsx',' SELECT * FROM [Sheet1
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?
解决方案 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
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:
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
这篇关于如何使用附加列添加excel文件的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文