Automate stripping csv attachment from email and upload to SQL Server

Hi Experts,
My client receives a .csv file from a supplier via email on a daily basis.  I need to figure out a way to automate the upload of this .csv data to MS SQL Server as the email is received.  I need a solution that runs server-side (Microsoft Exchange) which I believe rules out a VB Script running in Outlook which I know could otherwise do the job.
I'm very experience in working with MS Access and VBA but I'm not sure that that really helps here.  A quick google has made me wonder if PowerShell is the answer, but I know nothing about it and could do with some guidance/examples.
LVL 3
Oliver WastellMemberAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Anthony PerkinsCommented:
I need a solution that runs server-side
Nope.  You are going to need a client app to process the email.
0
Oliver WastellMemberAuthor Commented:
Is it not possible to use something like Windows Task Scheduler to periodically check Microsoft Exchange for new emails meeting a certain criteria and perform the import from there?  

I'm very reluctant to use a client app as I'm producing a reporting facility that pulls together data from five different software applications and I don't want the availability of data to be dependent on certain people having opened Outlook.
0
Anthony PerkinsCommented:
Is it not possible to use something like Windows Task Scheduler to periodically check Microsoft Exchange for new emails meeting a certain criteria and perform the import from there?  
I cannot see how that would work.

I don't want the availability of data to be dependent on certain people having opened Outlook.
I agree 100%.  Email should be your last resort.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Oliver WastellMemberAuthor Commented:
My client has upgraded to a software product that is online and the supplier won't give direct access to the SQL Server database (understandably because the SQL Server database contains data for all their clients).  They already have a facility to email csv files (or xml if I want) to their clients.  If I can work with this it will be the path of least resistance but it needs to be a robust solution.

Is there another technology that I could use, i.e. php and mysql to work within the existing constraints?

Or is there a simple solution that I could push for the supplier to implement, i.e. ftp the csv data to a folder somewhere and then have something scanning this on a periodic basis?  I saw something like this when googling using php and cron jobs. If so how could I implement this using microsoft products?  No doubt the supplier will charge to implement and I'll need to liaise with my client to ensure that they are happy to foot the bill.  Obviously, the simpler the solution the cheaper it should be.
0
Anthony PerkinsCommented:
Yes.  A far better approach to email is for you to receive a file via FTP and act upon it.  SSIS is a good tool to use to import the data.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Oliver WastellMemberAuthor Commented:
@acperkins: thanks for all your inputs and help.  It looks like that's probably the way to go then.  I see that you are a high ranking expert in a lot of fields, so I don't expect to get a better answer than this, but I'll leave the question open for a few days just in case anyone has any other ideas.
0
Anthony PerkinsCommented:
Absolutely.  I am also curious to see if anyone else has a different approach.
0
Jim P.Commented:
While I agree that a client side solution is about the worst way to do it, do you have the ability to change the e-mail account(s) it is sent to?

If so then you should be able to fire up Outlook and set a rule that automatically saves the file to disk. Then from there use the rest of the processing should be easy.
0
Oliver WastellMemberAuthor Commented:
Hi jimpen,  thanks for the input, yes I can have the data emailed to any email account, but the availability of the data is still dependent upon Outlook being opened on a daily basis. Are you suggesting that there is a way to ensure that this happens?
0
Jim P.Commented:
You can create a scheduled task that opens Outlook at a certain time.
rem change to the appropriate path
set outlook="c:\program files (x86)\microsoft office\office14\outlook.exe"
SCHTASKS /Create /RU SYSTEM /SC Daily /TN OpenOutlook /TR %outlook%  /SD %date:~4,10% /ST 01:15:00 /F /Z

Open in new window

This would open it at 1:15 AM on the day it was created.

Then have the email account it access already preset and use the command line switches set to use that profile.

In the profile build a rule that looks for the specified e-mail and as part of the process dumps the attachment to disk. It isn't pretty but it should work.

Back in my prior company they didn't want to set up an intranet (large company, small IT dept.) So I found a software that was always on and designed to catch e-mails. And it could dump to an Access DB. I then built an HTML form we put in a shared location. The form e-mailed to the account and the SW would process the form data into Access. Again, not pretty but worked.
0
Oliver WastellMemberAuthor Commented:
Thanks for your inputs guys.  I'll split the points as acperkins suggestion to use ftp and SSIS is the route I think I should push the supplier down, but jimpen's approach whilst not providing a server-side solution does provide a work-around that I can use during development (before pushing for the ftp solution).
0
Anthony PerkinsCommented:
Was there something that we could have done to have received an "A" grade?  Or was this just an over-sight on your part?  

In case you need to review here are the EE Guidelines on grading:
What grade should I award?
0
Oliver WastellMemberAuthor Commented:
@acperkins: sorry my grading perhaps wasn't in line with the EE Guidelines - I'm happy to have reopened and award an A grade (how do I go about that?).  The reason I gave a B grade was because I was looking for a server side solution for stripping csv file from an email, but I guess if there is no such solution it is unfair to penalize the alternative solutions provided.  Sorry for that.
0
Anthony PerkinsCommented:
Just click on the Request Attention link near the top of this page and post a comment there to have the grade changed.

Thanks and I appreciate you getting back to us with an explanation.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.