import data from excel only from specific cells

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » T-SQL (SS2K8) » import data from excel only from specific...

import data from excel only from specific cells

Rate Topic

Display Mode

Topic Options

Author

Message

Sapen

Posted Friday, April 13, 2012 12:35 PM

Ten Centuries

Group: General Forum Members
Last Login: Friday, May 17, 2013 7:49 AM
Points: 1,281, Visits: 1,564

Hello Everyone,

Hope all is well.

I was wondering if I can import data from an excel sheet only from specific cells to sql server 2008 r2. I can we can do this through sql import task but I want to know I can select data from specific cells only.

Thanks for your help.

Post #1283352

Yev-704641

Posted Tuesday, February 19, 2013 3:34 PM

Forum Newbie

Group: General Forum Members
Last Login: Friday, May 03, 2013 2:22 PM
Points: 3, Visits: 57

WHen adding Excel Source and under "Show Advanced Editor" and in Component Properties you could specify your own OpenRowset like Sheet1$A3:H3 where A3 is first column and H3 is last column to read data from.

Post #1421877

Yev-704641

Posted Tuesday, February 19, 2013 3:36 PM

Forum Newbie

Group: General Forum Members
Last Login: Friday, May 03, 2013 2:22 PM
Points: 3, Visits: 57

If you need something in specific cell you could look at this. I was looking for something similar and found it very useful.

http://www.sqlservercentral.com/articles/Integration+Services+(SSIS)/70024/

Post #1421878

raym85

Posted Tuesday, February 19, 2013 4:00 PM

Old Hand

Group: General Forum Members
Last Login: Friday, May 17, 2013 3:51 PM
Points: 376, Visits: 165

couldnt you copy the excel schema into a table form and sync up the rows and columns that way?

Post #1421880

Kevin.roberts25

Posted Tuesday, February 19, 2013 4:51 PM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, February 21, 2013 1:42 AM
Points: 6, Visits: 12

try using an open query

SELECT [Col1], [Col2], [Col3] INTO DestinationTable FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
'Data Source=C:\YourFolderLocation\yourFileName.xls;Extended Properties=Excel 8.0')...[WorkSheetName$]

SELECT  [Col1], [Col2], [Col3] INTO DestinationTable FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\YourFolderLocation\yourFileName.xls', [WorkSheetName$])

SELECT  [Col1], [Col2], [Col3] INTO DestinationTable FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\YourFolderLocation\yourFileName.xls', 'SELECT * FROM [WorkSheetName$]')

Post #1421892

« Prev Topic | Next Topic »

Permissions