Import a text file

[Nuno Gomes]

Hello,


I need to import a text using some conditions (for example: field1, begins
in position 14 and have 20 caracters, and so on ...)

Once I do not have experience in ACCESS I which to kmow your opinion. What
is the easy way to import a text file? And, i not, what is the best way
import a text file?...


Thanks,

Nuno Gomes

 

[Rui]

It is very simple.

Just go to the 'file' menu, select 'get external data' and then choose import.

A new window should appear. Select txt files on the drop down box and
navigate to the folder where your file is located. Open the file and an
import wizard should come up. There you can define all the colums and its
attibutes (i.e. text size 20).

Note: if you are going to import txt files often, you may want to save the
import definition.

Once imported, you can always view or query the table to make any tweaks you
may need.

Cheers
Rui


(espero que ajude! )

 

[Nuno Gomes]

Rui,

Thank you for your quickly answer.

But, what i need is how import a text file (import fixed) by code. Not by
the Access menus.

I'm doing some tests and i discover that i need to use the
"Docmd.TransferText" command. My problem, at the moment is how to use it?...
I need to define the text struture to import and i can't see how...




Thanks for all,
Nuno Gomes

(Obrigado)

 

[Douglas J. Steele]

Follow Rui's advice. Do the import once through the File | Get External Data
route, and save the definition. You can then use that saved definition in
conjunction with TransferText.

 

[Rui]

No problem.

I usually split this into 2 steps.

1 - First i do it manually. As I mentioned before, file->get external
data->import
Once the txt file is selected and the wizard is running, I click on the
advanced button - this allows me to specify the field/column names of the
data I am about to import. Then, still in the same window I press the save as
button in order to save the specification for later use (in step 2, i.e
myspec). Then just continue the wiz till you have a table (ie. tbl4import)

2 - Now that you have saved the spec with a name and actually have a table
all you have to do is something like

sub importcmd()
docmd.transfertext acimportdelim, "spec1", "tbl4import", "filename.txt"
end sub

(the code should be like that, sorry I dn't have access to msAccess at the
moment)

one last thing, you need to provide the full path to the txtfile if this is
not in the same folder as the mdb database. i.e. "c:\\some folder\\anther
folder\\import.txt"

Hope this helps. Take care
Rui

 

[Nuno Gomes]

Thank you to all...
I've made it!


Just a another question, in the same contest...
Where is the file spec? I've made the file specification in the advanced
button from the import window. But were the access saves it?... The form
(mdb) i made with that specifications works in another PC?


Thank you,

Nuno Gomes

 

[Rui]

It saves/compiles it into the database as far as I know.

Up to access 2003, this is the only way I know to create and access the file
specification. Even if it is to export data I still do it the same way I told
you.

In 2007, you can actually access these specs at a click of a button (i am
pretty sure I read/seen it somewhere) and it is really easy to do but I don't
like access 2007 so I can help you there.

As for the last bit of your question, as the info is saved in the mdb, if
you copy the mdb file, it will still be available.

Rui

(ena, ajudei alguem!!!)

 

[Douglas J. Steele]

It's stored in a couple of system tables in the database file, so yes it
should work on other PCs.