Importing file comma delimited
- Thread starter deekras
- Start date
David R
I know a few things...
- Local time
- Today, 01:18
- Joined
- Oct 23, 2001
- Messages
- 2,633
Have you looked into making an Import Spec for your file? You only have to do it once, and save it, then refer to that spec in your macro. Search the forum for more complete answers, as macros aren't my forte.
Good luck,
David R
Good luck,
David R
T
TSellers
Guest
I remember that it was not too difficult:
What I did was make a master table up that contained the fields I needed. As David says, do an import manually to create the import specification. I made the .txt file a link table. I then made up a delete query to clear the master table, and an append query to append the contents of the link table to it. I then created a macro that set warnings off, ran the queries, and then quit. I then wrote a batch file that executed Access and this macro from the command line, that I scheduled to run at the same time each day.
What I did was make a master table up that contained the fields I needed. As David says, do an import manually to create the import specification. I made the .txt file a link table. I then made up a delete query to clear the master table, and an append query to append the contents of the link table to it. I then created a macro that set warnings off, ran the queries, and then quit. I then wrote a batch file that executed Access and this macro from the command line, that I scheduled to run at the same time each day.
kilroyscarnival
Registered User.
- Local time
- Today, 07:18
- Joined
- Mar 6, 2002
- Messages
- 76
If all you have to do is bring new data into an existing table, that's pretty easy to write as a macro.
Press NEW to create a new Macro.
In the first row in the Action column, use the pulldown menu to select TransferText.
Then look at the boxes at the bottom left. The first one should be Import Delimited.
The second one, Specification Name, you can leave blank, especially if your data is already tab delimited.
Table Name is the destination table. Make sure you type that name in exactly - there's no pulldown.
File Name is exactly where your source data is located. Enter the entire path, for example, "c:\my documents\data files\march 2002 records.txt". Without the quotation marks, of course.
Has Field Names means do you have column headers in your source file. If YES, make sure they are the same nomenclature you have in your existing Table -- unless you are importing to a new table.
You can skip the last two, HTML Table Name and Code Page, unless you need them. (F1 for help on that one...I couldn't really grasp the latter.)
You might want to end with a message box saying "Transfer Completed!" To do that, go to the second row, and use the Action pulldown to MsgBox. At the bottom, type in whatever message you want in the Message window, and give the Title something like "Transfer Status" or something that will make sense given the message.
I am just learning this but because I'm setting up a database for other people, I had to make my macro first run an append query, to transfer the data in the existing table into an Archive Table (and add the month and year), then delete all the records in the first table, then import the new data. That way they can use the same report setup every month and everything's all connected. It took me a few tries, but I got it all to work.
Good luck with your project!
--Ann
Press NEW to create a new Macro.
In the first row in the Action column, use the pulldown menu to select TransferText.
Then look at the boxes at the bottom left. The first one should be Import Delimited.
The second one, Specification Name, you can leave blank, especially if your data is already tab delimited.
Table Name is the destination table. Make sure you type that name in exactly - there's no pulldown.
File Name is exactly where your source data is located. Enter the entire path, for example, "c:\my documents\data files\march 2002 records.txt". Without the quotation marks, of course.
Has Field Names means do you have column headers in your source file. If YES, make sure they are the same nomenclature you have in your existing Table -- unless you are importing to a new table.
You can skip the last two, HTML Table Name and Code Page, unless you need them. (F1 for help on that one...I couldn't really grasp the latter.)
You might want to end with a message box saying "Transfer Completed!" To do that, go to the second row, and use the Action pulldown to MsgBox. At the bottom, type in whatever message you want in the Message window, and give the Title something like "Transfer Status" or something that will make sense given the message.
I am just learning this but because I'm setting up a database for other people, I had to make my macro first run an append query, to transfer the data in the existing table into an Archive Table (and add the month and year), then delete all the records in the first table, then import the new data. That way they can use the same report setup every month and everything's all connected. It took me a few tries, but I got it all to work.
Good luck with your project!
--Ann
Similar threads
- Question
