CODEWITHSUNDEEP
excelt-sqlautomationimport-from-excelvba
ANASI-Newbie
ANASI-Newbie

Reputation: 111

Auto Importing into TSQL Table from different Excel sheets

I am trying to auto-import data from 12 different files, each file with 6-10 sheets. Is there a process by which data can be auto extracted by 'selected sheets' ONLY from each file and loaded into a single file (or SQL table).

Example:

File A with Columns "Name" Column "Amount". 
File A has total of 4 sheets (2 sheets named "ABC Trend", "DEF Trend")
File B - 6 sheets (2 sheets named "XXX Trend", "DEF Trend")

Output:

import into Table C (or File C - One Sheet) with 'Trend' data

"Name", "Amount", "FromFile", "FromSheet"
Jo,      56.3 ,      A ,     ABC Trend
Mary,    16.3 ,      A ,     ABC Trend
Dave,    26.3 ,      A ,     ABC Trend
Jim,     26.3 ,      A ,     DEF Trend
Mary,    16.3 ,      A ,     DEF Trend
Dave,    26.3 ,      A ,     DEF Trend
Shu,     16.3 ,      B ,     XXX Trend
Marie,   16.3 ,      B ,     XXX Trend
Tom,     26.3 ,      B ,     XXX Trend
Jack,    26.3 ,      B ,     DEF Trend
Ma,      16.3 ,      B ,     DEF Trend
Doe,     26.3 ,      B ,     DEF Trend

*Ideal would be to load into a SQL table

Any help would be greatly appreciated.

Upvotes: 1

Views: 64

Answers (2)

ASH
ASH

Reputation: 20312

Try what Juan suggested, but do it like this:

SELECT * FROM [SheetName$A1:B2]

Does that work for you?

Upvotes: 0

Juan Piaggio
Juan Piaggio

Reputation: 190

Option 1: SSIS

If you are trying to do a recurring process to run every day/week/month, I would recommend to use Integration Services SSIS

This is a nice tutorial: https://www.simple-talk.com/sql/ssis/moving-data-from-excel-to-sql-server-10-steps-to-follow/

You can run this job using a SQL Agent.

Option 2: OLEDB connection

Step 1: download "2007 Office System Driver: Data Connectivity Components " if not installed https://www.microsoft.com/en-us/download/details.aspx?id=23734

Step 2: Enable Ad Hoc Distributed Queries

       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

Step 3: Run Query

SELECT  exl.name
INTO #myExcelData
FROM OPENROWSET ('Microsoft.Ace.OLEDB.12.0'
,'Excel 12.0; Database=C:\Projects\StackOverflow\A.xlsx; Extended Properties=''EXCEL 12.0;HDR=NO;IMEX=1'
,'SELECT * FROM [Sheet1$]') AS exl

Option 3: Wizard If this is a one time process, you can use wizard

Upvotes: 4

Related Questions