Import Multiple Spreadsheets

[sabloomer]

I need to say two things before I start. One, "Thank You" to anyone who takes the time to read this and respond. Two, I am very much a newbie so bear with my stupid questions.

I have an Access database that is growing beyond what I am comfortable having in Access. I also look at this a way to become more comfortable with SQL Server. One of the tasks that my Access database does is import spreadsheets that we get back from remote users. We send them a spreadsheet with their current forecast. These remote users modify their forecast and then send back the Excel spreadsheets. These sheets do not have a constant name, instead I keep a log of imported files and compare to that each time a file attempts to be imported. The following is my VBA code that does all of this...

Public Sub import_spreadsheets()
Dim rst As Recordset, sql As String
Dim strDirPath, MyName As String
DoCmd.SetWarnings False

On Error GoTo cant_import

'strDirPath = "C:\My Documents\Access\Import Files\"
strDirPath = "\\Uinet4a\Vol1\Shared\Product Supply\Access Projects\Prod Supply Database\Imp_Files\"

write_log "Import Spreadsheets Process", "Start"

'Delete existing temp table
write_log "Delete Temp Table", "Start"
CurrentProject.Connection.Execute "DELETE * FROM tblTemp", , adExecuteNoRecords
write_log "Delete Temp Table", "End"

' Display the names in C:\ that represent files.
MyName = clean_comments(Dir(strDirPath, vbNormal)) ' Retrieve the first entry.
Do While MyName <> &quot;&quot; ' Start the loop.
'If Left(MyName, 4) = &quot;imp_&quot; And Right(MyName, 4) = &quot;.xls&quot; Then
If Right(MyName, 4) = &quot;.xls&quot; Then
'Test to see if the file has already been imported
sql = &quot;SELECT * FROM tbl_imported_files &quot; & _
&quot;WHERE (((file_name)='&quot; & MyName & &quot;'))&quot;
Set rst = New Recordset
rst.Open sql, CurrentProject.Connection, adOpenKeyset, adLockOptimistic

If rst.BOF = True And rst.EOF = True Then 'this files has yet to be imported
'Write filename to table
rst.AddNew
rst!file_name = MyName
rst!trans_date = Date
rst.Update

write_log &quot;Import file - &quot; & MyName, &quot;Start&quot;
'Table Long File Name Sheet Range
'DoCmd.TransferSpreadsheet acImport, 8, &quot;tblTemp&quot;, strDirPath & MyName, True, &quot;Forecast!Excel_Master&quot;
DoCmd.TransferSpreadsheet acImport, 8, &quot;tblTemp&quot;, strDirPath & MyName, True, &quot;all_data&quot;

write_log &quot;Import file - &quot; & MyName, &quot;End&quot;

End If

rst.Close

End If
MyName = Dir ' Get next entry.
Loop


write_log &quot;Import Spreadsheets Process&quot;, &quot;End&quot;

Exit Sub

cant_import:
Select Case Err.Number
Case 3011, 2391 'Can't Import Sheet
write_log &quot;Failed Spreadsheet Import &quot; & MyName, &quot;Error&quot;
DoCmd.SendObject , , , &quot;me@XXX.com&quot;, &quot;my_boss@XXX.com&quot;, &quot;&quot;, &quot;Import Error&quot;, &quot;Spreadsheet &quot; & MyName & &quot; Failed&quot;, False, &quot;&quot;&quot;&quot;
Resume Next
Case Else
write_log &quot;MAJOR spreadsheet Import Error&quot;, &quot;Error&quot;
End Select
End Sub


Can anyone tell me how I can me how I can do the same thing from within SQL Server? I can set up a DTS Package to import a single spreadsheet, but I can't figure out how to pass it a name as a variable. It might just be that I am looking at the problem the wrong way. Sometimes I get stuck in the &quot;Well in Access I would ....&quot; frame of mind.

Thank You,

Scott

 

[JayKusch]

Well lets see if we can get you started ...

First I will assume that all of your excel worksheets(wsheets) will be in one folder. So we first need to get a list of the wsheets we need to process.

Let create a temp table called #Wsheets.

CREATE TABLE #Wsheets (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[WsheetName] [varchar] (400) NULL
) ON [PRIMARY]


Now lets load and clean the temp table w/ the wsheets needing to be processed. Your path names will of course need to reflect your system ...

EXEC Master.dbo.xp_cmdshell 'dir C:\Excel\*.xls /s > C:\Excel.txt'

INSERT INTO #Wsheets EXEC Master.dbo.xp_cmdshell 'type C:\Excel.txt'

EXEC Master.dbo.xp_cmdshell 'del C:\Excel.txt'

UPDATE #Wsheets
SET WsheetName = RTRIM(LTRIM(WsheetName))

UPDATE #Wsheets
SET WsheetName = REPLACE(WsheetName,'Directory of ','')
WHERE WsheetName LIKE '%Directory of %'

DELETE #Wsheets
WHERE WsheetName NOT LIKE '%.xls'
OR WsheetName IS NULL

UPDATE #Wsheets
SET WsheetName = SUBSTRING(WsheetName,40,200)
WHERE WsheetName LIKE '%.xls'

select * from #Wsheets


OK ... the SELECT statment above should show you the names
of the wsheets that are to be processed ...

Now lets define/set a few working variable ...

declare @RecCnt Int
declare @RecMax Int
declare @WsheetName VarChar(200)

set @RecCnt = 1
set @RecMax = (SELECT MAX(ID) FROM #Wsheets)
set @WsheetName = ''


Alright ... lets process these wsheets with a WHILE statment and get the data out of each and into a table(s)


WHILE @RecCnt <= @RecMax
BEGIN

-- Here we get the wsheet name

SET @WsheetName =
( SELECT RTRIM(LTRIM(WsheetName))
FROM #Wsheets
WHERE ID = @RecCnt )

-- test that it is a valid name

IF @WsheetName <> '' AND
@WsheetName IS NOT NULL
BEGIN

-- Need to create a linked server connection to
-- the desired wsheet. Once again!!! change the
-- pathname below to your needed value!!

declare @SQLCommand VarChar(600)
set @SQLCommand =

'EXEC master..sp_addlinkedserver '+
'''' + 'ExcelSource' + '''' + ',' +
'''' + 'Jet 4.0' + '''' + ',' +
'''' + 'Microsoft.Jet.OLEDB.4.0' +
'''' + ',' + '''' + 'c:\Excel\' +
@WsheetName + '''' + ',' +
'NULL,' + '''' + 'Excel 5.0' + ''''

print @SQLCommand -- Just to see the syntax

EXEC (@SQLCommand)


-- Now that the wsheet is &quot;attached&quot; we can
-- query it or we can insert its data into
-- a table. I have give both examples.

-- Simple Select

select * from ExcelSource

-- Insert into your table, columns must
-- match nbr of rows in wsheet

insert into MyTable
select * from ExcelSource

-- Now DROP the current link so we can
-- create the next one.

exec
master..sp_dropserver 'ExcelSource', NULL

END

-- Set counter to plus 1 to get
-- next possible wsheet name

SET @RecCnt = @RecCnt + 1
END

As a final cleanup, drop the temp table and last connection created and we are done.

drop table #Wsheets
exec master..sp_dropserver 'ExcelSource', NULL

This is a very rough outline to follow. I am sure you will beed to tweak it in a number of place to get it to work properly for you. You may also want to look in BOL on info for accessing Excel data thru a linked server. Just look under sp_addlinkedserver. Hope this get you on your way.

Thanks

J. Kusch