Importing text file

[Will_888]

hi, im trying to create a database that will store exam results on a database with ready made tables. The problem is that the exam boards send the results in a string of text with the first 5 characters representing the EntryCode and the next 4 representing the ResultsCode etc. I wanted to know how i would create a button to extract the relevent information from the string of text and import it into a table. Would i need to use some kind of an array??

Any help would be much appreciated. thanks.

 

[david.brent]

Hello there.
I would probably do something like this...

Start the Import... Wizard (Right Click on the database window and select Import...).

Navigate to where your file lives (Remember to change the the 'Files of Type' to Text Files)

Select your file and click on import.

Go through tke wizard and on the Last screen click on Advanced. Then click on Save As...

Select a meaningful name like - My Import Spec - and click OK (Remember the name here it's important.

Now in your buttons on_click event use something like this...

DoCmd.TransferText acImportFixed,"My Import Spec","[Put Your Table Name Here]", "[Put the path to your file here, incliding the .txt bit", True (if the file has filed names, if not use False)

I'm doing this from memory so hopefully it's OK, this PC hasn't got Access installed

 

[Will_888]

Yeh, someone showed me that way. But the problem is that by using the access import wizard, each time i import a text file i have to divide the line of text into the fields. Also, i have to use the whole line of text, but i only want certain parts from it. Im trying to make the process as automatic as possible so that the user doesnt have to use the access wizards.

thanks

 

[Will_888]

Scrap my last post, i experimented abit more with what you said he kinda know what direction im going. Thanks alot.

 

[modest]

Part of remembering the Import Spec Name is so that it will remember what you want to keep and what you can discard. It is your template and keeps the format the same.

This way you do it once by hand, but after that could be automated.



If you wanted to do this in code... you'd have to import the file delimited (probably by spaces). And then break up each field using some sort of query or function. Look into strlen() and mid$() if you really want to learn more about it string commands... or look into making queries with "like 'blah*' ".

For more information, post an example text file that you wanna import.

 

[Will_888]

i set up a specification and use the code below which is attached to a button when clicked.

DoCmd.TransferText acImportFixed, "Test", "Aaa", ["C:\aaa.txt"], True

But when i click the button there is an error message saying "Access can't find 'l' referred to in your expression".

Any ideas? thanks.

 

[modest]

1) What does your import file look like??
2) What does your table look like?
3) acImportFixed -> acImportDelim
4) take away the brackets from the file name

 

[Will_888]

i took away the brackets and its works now, lol, thanks alot for all you help.

 

[modest]

You might also want to change the True to False.

True means that your text file has column headings, false means it doesnt.
If you select true, you might be leaving out the first line of your file when you import.