Importing delimited text files into SQL with VBA

[Craigieboy]

Can anyone help me!

I need to import delimited text files into a SQL database. The text files are delivered by email to Outlook 2000 as attachments. I have written the code that saves the attachements to an area on my hard disk, move the email to a folder in my Inbox and make a connection to the database, but it's the importing to SQL part that I'm having problems with. I need the code to handle the whole routine so that no human intervention is required.

Has anyone done this before? Any help would be appreciated.

Many thanks in advance,
Craig

 

[fluteplr]

Have a look at DTS (Data Transformation Services). If you can create a DTS script that will do the import, you can have it write the script out as VB code which you should be able to use to complete what you are trying to do.

 

[Craigieboy]

Hi there,

I've got it sussed - well sort of...

My problem lay at the SQL query part - the line below now automatically inserts a file to the dBase in the way I want. It needs a little more fine tuning, however it does work. So, when email arrives the VBA code inserts the attached delimited file into the databse without my intervention.

BULK INSERT tbl_job FROM 'D:\fileName.txt' WITH (FIELDTERMINATOR = '|')

If anyone at all can add to this, or improve it please feel free.

Craig