Is it possible to loop through all excel files in a folder using T-sql

Question

Is it possible to loop through all excel files in a folder using T-sql

chaithu559

SSCommitted

Points: 1574
More actions
July 16, 2012 at 10:25 pm

#261216

Hi Eveyone,
Is it possible to loop through excel files in a folder in sql server and pull the data to the destination(may be to a table in sql server). is there any way to do this.
I know we can do this in SSIS, But eager to find out if there's any possible way with which we can implement the same in Sql server.
Any help would be really appreciated
Regards
Chaithu

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

BrainDonor SSCoach Points: 19254 More actions · Answer 1

I've attached some code that may do what you want.

It uses xp_cmdshell to write all of the filenames into a table, then uses a cursor to read through these filenames and run dynamic sql to load the spreadsheet data into another table.

Due to problems with Jet 4.0 and the 64-bit Windows I'm using I'm unable to test it completely and haven't the time to work on that, but there are ways around it.

Hopefully though there's enough here to get you started:

exec xp_cmdshell 'dir c:\temp\*.xls /B > c:\Temp\testnames.txt'

DECLARE @File VarChar(200), @StrCmd VarChar(1000)

CREATE TABLE #FileNames(

Excel_FileNamevarChar(200)

)

BULK INSERT #FileNames

FROM 'c:\Temp\testnames.txt'

WITH (FieldTerminator = '',

RowTerminator= ''

)

select * from #FileNames

CREATE TABLE #ExcelImport(

ImportTextVarChar(MAX),

ImportNumberVarChar(MAX));

DECLARE Files CURSOR

FOR SELECT Excel_FileName

FROM #FileNames

OPEN Files

FETCH NEXT FROM Files INTO @File

WHILE @@FETCH_STATUS = 0

BEGIN

SET @StrCmd = 'INSERT INTO #ExcelImport(ImportText,ImportNumber)

SELECT * FROM OPENROWSET(''Microsoft.Jet.OLEDB.4.0'',

''Excel 8.0;Database=c:\TEMP\' + @File + ';HDR=YES'',

''SELECT * FROM [Sheet1$]'')';

select @StrCmd

exec (@StrCmd)

FETCH NEXT FROM Files INTO @File

END

CLOSE Files

DEALLOCATE Files

select * from #ExcelImport

drop table #ExcelImport

drop table #FileNames

Steve Hall
Linkedin
Blog Site

Lowell SSC Guru Points: 323518 More actions · Answer 2

like brain donor's example, my two examples would involve a cursor to process the files after i got the list of files.

my example for xp_cmdshell is really similar:

--a table to loop thru filenames drop table ALLFILENAMES

CREATE TABLE ALLFILENAMES(WHICHPATH VARCHAR(255),WHICHFILE varchar(255))

--some variables

declare @filename varchar(255),

@path varchar(255),

@cmd varchar(1000)

--get the list of files to process:

--#########################################

SET @path = 'C:\DB\'

SET @cmd = 'dir ' + @path + '*.xls /b'

INSERT INTO ALLFILENAMES(WHICHFILE)

EXEC Master..xp_cmdShell @cmd

UPDATE ALLFILENAMES SET WHICHPATH = @path where WHICHPATH is null

select * from ALLFILENAMES

if you can isntall a CLR, i would strongly suggest Elliot Whitlows example here:

http://nclsqlclrfile.codeplex.com/

one of the fine functiosn returns a table like this:

--MFGetDirectoryList

-- Parameters: @Directory

-- purpose: given a string containing a path returns a three column results table with path,name and fullfilename and path

-- usage: SELECT * FROM dbo.MFGetDirectoryList('C:\Data\')

SELECT *

FROM dbo.MFGetDirectoryList('C:\Data\') x

WHERE x.Filename LIKE '%.xls'

after that, it depends on what you want to do to the excel docs.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!