November 16, 2012 at 6:44 am
Hi,
could any one shed some light to read data from Excel sheet using SQL code.
Requirements
1)Excel sheet will be placed in some server path
2)Read the data available in particular column and from particular excel sheet.(say sheet 1 or sheet2 etc)
3)do we have any challenges while reading data from excel 2007 or 2010
November 16, 2012 at 6:56 am
Look at SSIS (SQL Server Integration Services) which is one of the components of the SQL Server installation.
You should be able to quite easily set up a package which you can schedule to import specific data from one source to another (in your case Excel to SQL Server).
- SSIS on MSDN:
http://msdn.microsoft.com/en-us/data/ff660749.aspx
- YouTube presentation about SSIS + Excel + SQL Server:
http://www.youtube.com/watch?v=79mi31caAag
- StackOverflow "Import multiple Excel files into SQL Server 2008 R2 using SSIS Packages?"
- SqlServerCentral Forum discussion "Import Data from Multiple Excel Sheets using SSIS":
http://www.sqlservercentral.com/Forums/Topic637794-148-1.aspx
HTH,
B
November 16, 2012 at 6:58 am
Hey Hi,
I know we can use SSIS to get data from excel and load in to SQL table from where i can achieve requirements.
I thought of using only SQL to get data from excel and achieve the requirements so that we need not to go for SSIS.
November 16, 2012 at 10:48 am
subramani.rudrappa 78855 (11/16/2012)
Hey Hi,I know we can use SSIS to get data from excel and load in to SQL table from where i can achieve requirements.
I thought of using only SQL to get data from excel and achieve the requirements so that we need not to go for SSIS.
I'm not aware of a way to use SQL directly. That is why Microsoft developed SSIS.
November 16, 2012 at 11:05 am
add excel file as a linked server,
then
INSERT INTO A_Table
SELECT c1, c2 ...
FROM LinkedExcelName...Sheet1$
WHERE something
November 16, 2012 at 11:22 am
you can create a linked server to point to an excel spreadsheet, and then reference it just like shanghaigirl-1125377's example.
here's my detailed notes and example on getting it set up and ready to rock :
a couple of prerequisites:
install the AccessDatabaseEngine_x64.exe from microsoft:
http://www.microsoft.com/en-us/download/details.aspx?id=13255
make sure you open an Administrative command prompt window, and run it with the c:\Downloads\AccessDatabaseEngine_x64.exe /passive
command line flag;
this will force the install of the drivers, even if you have 32 bit office installed;
otherwise you get some error about 32 bit Office preventing the install.
After that is isntalled:
--Required settings for the provider to work correctly as a linked server
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
and then the code for the linked server:
--#################################################################################################
--Linked server Syntax for Excel with ACE 64 driver
--#################################################################################################
GO
DECLARE
@srvproduct nvarchar(256),
@provider nvarchar(256),
@location nvarchar(100),
@provstr nvarchar(100),
@catalog sysname,
@sql varchar(1000)
--add an excel spreadsheet as a linked server.
SET @server = 'MyExcelACE'
SET @srvproduct = ''
SET @provider = 'Microsoft.ACE.OLEDB.12.0'
set @provstr = 'Excel 12.0'
SET @datasrc ='C:\Data\BlockGroups_2010\AKblockgroup.xls'
EXEC sp_addlinkedserver @server,@srvproduct,@provider,@datasrc,NULL,@provstr
EXEC dbo.sp_AddLinkedSrvLogin @server, FALSE, NULL, Admin, NULL
--what spreadsheets (table equivilents are available?
EXEC sp_tables_ex 'MyExcelACE'
--you MUST know the name of the spreadsheet;
--spreadsheet name has a dollar sign at the end of it!
--I've personally never gor a spreadsheet that has a space in it
--for example "Activity Data" =ActivityData$ never got [Activity Data$] or [Activity Data]$ to work
--to work, so I end up editing the spreadsheet to remove spaces if that happens.
SELECT * FROM MyExcelACE...ActivityData$;
SELECT * FROM MyExcelACE...Sheet1$;
Lowell
November 18, 2012 at 9:45 am
subramani.rudrappa 78855 (11/16/2012)
Hey Hi,I know we can use SSIS to get data from excel and load in to SQL table from where i can achieve requirements.
I thought of using only SQL to get data from excel and achieve the requirements so that we need not to go for SSIS.
If you are importing data on a routine basis as part of a inter-application data transfer, then SSIS is a good choice.
For one-off ad hoc importing, however, I much prefer to use OPENROWSET() queries. There are few fiddly bits to get right or the process fails:
- The spreadsheet cannot be open while you run the import.
- You must know the name and path of the spreadsheet and the name of the worksheet you want to import and hard-code them into your query (unless you resort to dynamic SQL).
- You have to enable ad hoc distributed queries on your server.
- If you have mixed data types in the same column, the type conversion can fail if the process guesses wrong about how to type the column. Google for "Excel IMEX=1" for more information and a work-around.
Once you get all that working once, it's easy to re-do for other uses. I don't know what "best practice" is considered here, but I do a lot of one-time imports from Excel, and this is my favorite tool in the box.
Rich
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply