?
Solved

Import Text file into MS Access 2007

Posted on 2014-11-27
5
Medium Priority
?
199 Views
Last Modified: 2014-11-30
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.
0
Comment
Question by:yimsoin57
  • 2
  • 2
5 Comments
 
LVL 52

Expert Comment

by:Gustav Brock
ID: 40470038
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
 

Author Comment

by:yimsoin57
ID: 40470061
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
 
LVL 52

Accepted Solution

by:
Gustav Brock earned 2000 total points
ID: 40470063
Well, then you would use an update query rather than an append query.

/gustav
0
 
LVL 26

Expert Comment

by:Nick67
ID: 40470640
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
 

Author Closing Comment

by:yimsoin57
ID: 40473170
Everything is perfectly done as advised.  Thank you.
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…
Suggested Courses

578 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question