[SOLVED] Import CSV from zipped Email Attachment to SQL Server

Import CSV from zipped Email Attachment to SQL Server

Every day I receive an email from an external 3rd party which has data attached in a zipped CSV format.

What I want to do is automate the import of this data to a SQL server database. The email is not local to the server, so presumably I need to unzip and save the file to a folder somewhere the server can see it, then simply bulk insert it?

Is it possible to get exchange server to automate this saving and unzipping, or do I need to do it via another application? I have an access database running on a daily schedule which conceivably do this. The access database could also link to the SQL tables and perform the insert, but I don't know the relative merits and performance of doing so vs a bulk insert or SQL based alternative?

Solution

I've had some time to work on this, and while it's not pretty I've got it working.

First I use outlook vba cobbled together from snippets on the web and here to save the attachments into a folder, and unzip them

Then I use the following in SQL to loop through the same folder and import the csv files.

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

--some variables
declare @filename varchar(255),
        @path     varchar(255),
        @sql      varchar(8000),
        @cmd      varchar(1000)


--get the list of files to process:
SET @path = Full_UNC_Path
SET @cmd = 'dir ' + @path + '*.csv /b'
print @cmd
INSERT INTO  #ALLFILENAMES(WHICHFILE)
EXEC Master..xp_cmdShell @cmd
UPDATE #ALLFILENAMES SET WHICHPATH = @path where WHICHPATH is null


--cursor loop
declare c1 cursor for SELECT WHICHPATH,WHICHFILE FROM #ALLFILENAMES where WHICHFILE like '%.csv%'
open c1
fetch next from c1 into @path,@filename
While @@fetch_status <> -1
  begin

  --bulk insert won't take a variable name, so make a sql and execute it instead:
   set @sql = 'BULK INSERT tbl_MyInbound FROM ''' + @path + @filename + ''' '
       + '     WITH ( 
               FIELDTERMINATOR = '','', 
               ROWTERMINATOR = ''\n'', 
               FIRSTROW = 2 
            ) '
print @sql
exec (@sql)

  fetch next from c1 into @path,@filename
  end
close c1
deallocate c1


--Extras

--delete from ALLFILENAMES where WHICHFILE is NULL
--select * from #ALLFILENAMES
drop table #ALLFILENAMES

if (@prevXpCmdshell = 0)
begin
exec sp_configure 'xp_cmdshell', 0
reconfigure
end

if (@prevAdvancedOptions = 0)
begin
exec sp_configure 'show advanced options', 0
reconfigure
end

However, I'd like to delete the file as well, after importing it to prevent reimport etc. I tried the following but it didn't work, outputting Access is denied

set @cmd = 'del ' + @path + @filename
EXEC master..cp_cmdShell @cmd

If I can just figure out why I can't delete the file, then I would be a happy guy. Any ideas?