Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Reading data from Excel using Sql Expand / Collapse
Author
Message
Posted Friday, November 16, 2012 6:44 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, July 3, 2014 5:09 AM
Points: 45, Visits: 111
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

Post #1385665
Posted Friday, November 16, 2012 6:56 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, October 16, 2014 3:22 AM
Points: 167, Visits: 691
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?"
http://stackoverflow.com/questions/10122538/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
Post #1385675
Posted Friday, November 16, 2012 6:58 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, July 3, 2014 5:09 AM
Points: 45, Visits: 111
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.
Post #1385679
Posted Friday, November 16, 2012 10:48 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, October 24, 2014 6:14 AM
Points: 65, Visits: 404
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.
Post #1385786
Posted Friday, November 16, 2012 11:05 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, December 11, 2014 10:29 AM
Points: 45, Visits: 1,180
add excel file as a linked server,
then

INSERT INTO A_Table
SELECT c1, c2 ...
FROM LinkedExcelName...Sheet1$
WHERE something
Post #1385791
Posted Friday, November 16, 2012 11:22 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 5:42 AM
Points: 12,962, Visits: 32,502
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1385803
Posted Sunday, November 18, 2012 9:45 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Wednesday, November 26, 2014 9:31 AM
Points: 717, Visits: 3,037
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
Post #1386057
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse