Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Import CSV from email into table on SQL server

Status
Not open for further replies.
Hfnet

Hfnet

IS-IT--Management
Dec 31, 2003
369
GB
We get a daily email of the previous days' deliveries from our courier service. The email has a .csv attachment.

Currently I open the CSV in Excel and change the format of column a from general to number, no decimal places. This column holds the consignment numbers and in the CSV it appears as xxxxxxxxE+13 (e.g).

I then save it as an XLS and import it into my SQL server table.

What I would love to happen is to do this automatically. I use Outlook and Thunderbird, so either option is good. The subject line changes but always contains "PODs".

How can I :

1) extract the csv from the email on receipt of the email
2) ensure that colimn a is formatted as number, no decimal places.
3) import the data into SQL Server Table

 
Sort by date Sort by votes
Instead of emailing the file, can the vendor FTP the file into a directory on a daily basis, and then send an email as a confirmation check. Otherwise, you will need to write a script to extract the attached file from an email message... which I believe can be done, but may require some coding. Having an FTP is a better approach, imho. You can create a DTS Package in SQL Server to directly import the file and "Transform" fields as necessary via teh Data Pump/Transformation Task. It is not difficult, but SQL Server DTS experience is required. Next, you could schedule a job to run and sweep the FTP target directory of the vendor data on a daily basis or whenever... htwh,

Steve Medvid
"IT Consultant & Web Master"

Chester County, PA Residents
Please Show Your Support...
 
Upvote 0 Downvote
No, I think the whole system is automated their end to send out emails. I guess I'll need to auto-extract the csv to a directory and go from there.

Thanks for your advice.
 
Upvote 0 Downvote
Perhaps check out setting up an Outlook Rule... Not sure if the rules permit saving of Attached files? Worth checking out though...

Steve Medvid
"IT Consultant & Web Master"

Chester County, PA Residents
Please Show Your Support...
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

OCM
  • Locked
  • Question
Populate access table from excel
Replies
6
Views
3K
OCM
OCM
A
  • Locked
  • Question
Print Table attachment
Replies
2
Views
189
andre.meyer
A
ameedoo3000
  • Question
Clear all table contents without deleting records
Replies
15
Views
644
ameedoo3000
ameedoo3000
renigar
  • Locked
  • Question
Access 365: Search LastName in one table to put EmployeeID in another
Replies
8
Views
736
renigar
renigar
lameid
  • Locked
  • Question
Execution Slowing on iterative generation of PDF's
Replies
3
Views
610
lameid
lameid

Part and Inventory Search

Sponsor

Back
Top