Need Help in importing delimited file into Access

[cyberbuoy]

Hi,

Firstly, I am using Access 2003 to import data from a text file that is asterisk- delimited using the TransferText Action in a Macro. All is working well with the import except I need to pull a certain part of the text file's name as a yet another column apart from the columns already being imported. So as an example; If the Text file was called Employee_Car_Sales_2008_04.txt which has data (say) as below;

Jason*Welington*23
Lina*Ray*45
Victor*Adams*34
Sabrina*Rodriguez*44
Jean*Cockren*19
Steve*Inskeep*58

Then I want Access to not only load

Employee First Name & Employee Last Name & Sales

But also

Employee First Name & Employee Last Name & Sales & Month

Month should be read from the text file's name and should be 200804 in this case (i.e. the month of April 2008). Where I am coming from on this is that I will have such text files being read each month into this table and those files will carry the name of the month with the same "naming convention". So My Access DB Table should look something like;

Jason Welington 23 200804
Lina Ray 45 200804


Victor Adams 34 200804
Sabrina Rodriguez 44 200804
Jean Cockren 19 200804
Steve Inskeep 58 200804

Just as an example.. Table continues further

Jason Welington 53 200805
Lina Ray 35 200805
Victor Adams 35 200805
Sabrina Rodriguez 14 200805
Jean Cockren 10 200805
Steve Inskeep 23 200805

if it reads in a txt file called Employee_Car_Sales_2008_05.txt etc....
Hope it makes sense. Thanks a ton for looking and do let me know if you know there's a way to do it in Access. I am total novice for MS Programming .. but good with Java... and this is my first time trying to do Macros etc. in Access. Anyways, need to get this done for my boss !

Thanks,
Cyberbuoy.

[NTC]

I would suggest that you have an unbound text box on the form/screen that the user is using when they invoke the import event.

In the unbound text box have them type in the relevent file ID; in your case 200804.

Have the import occur into a temp table. Have an UpdateQuery that then applies the value in the unbound textbox to each record. Then have an AppendQuery that moves the data to the main table. (then have a DeleteQuery that clears the temp table.)

One can automate the process to "find" the file ID. But because you note that the actual file name is: Employee_Car_Sales_2008_04 - - then you are going to have to perform additional text line manipulation to trim it down to just 200804....plus of course in order for that code to work there has to be closely uniform file naming - if it varies widely then it will be extremely challenging.

In any case I would follow my route as a first step. Get it working. Then if you want to automate the file ID naming - proceed with that secondly.

Hope this helps.