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?

Improve company productivity with a Business Account.Sign Up

x
 
Gustav BrockConnect With a Mentor CIOCommented:
Well, then you would use an update query rather than an append query.

/gustav
0
 
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
 
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
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.

All Courses

From novice to tech pro — start learning today.