Import Text file into MS Access 2007

I have a text file (.txt) which has thousands of lines with the following format:

aaaaaaa-bbbb;CCCCCCCC;DDDD

each position represents customer_number;customer_name;project_name in the database respectively.  Please note there is no field heading in the text file

MS Access 2007 database has a table name "customer" which has the fields as follows:

customer_number,customer_name,project_name respectively.


I should be grateful if someone could help me providing a VB script or a batch file to run an import of the data in the text file into MS Access 2007.  Thank you.
yimsoin57Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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.

Gustav BrockCIOCommented:
Just run the import wizard at menu External Data. Choose to link the data, not import.
In this, select the separator as semicolon.

Then create a simple append query that uses the linked data as source and your Customer table as target.

/gustav
0
yimsoin57Author Commented:
Actually my text file gets updated twice a day which I do not know which lines are updated.  I need the data be imported into the data and replace the old data not append.
0
Gustav BrockCIOCommented:
Well, then you would use an update query rather than an append query.

/gustav
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
Nick67Commented:
Some of it can be coded.
As /gustav  suggests, create a linked table.
I do this with CSV files all the time.

Then for code
'kill everything in Customer
DoCmd.SetWarnings False
CurrentDb.Execute "DELETE Customer.* FROM Customer;", dbSeeChanges
DoCmd.SetWarnings True
'Insert everything from the linked CSV into Customer
'code for the insert here

Open in new window


Now, if you have created the linked table as /gustav has suggested, tell us the name of the new linked table and perhaps /gustav would be so kind as to supply the insert part of the SQL code (I always use recordset code, which wouldn't be efficient for a bulk insert like this)
0
yimsoin57Author Commented:
Everything is perfectly done as advised.  Thank you.
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
VB Script

From novice to tech pro — start learning today.