Import TXT

[Guest]

I receive a txt file (in .dat extention) with multiple lines, with no tab,
comma, or any delimitations between the fields, I need to import this data to
an Access table, each field in the .dat file has a fixed position, like this:

1AB123TUP0525
1AC456GJL0565

First 3 characters are the id, so I need to import them to the id field in
my table, char 4-6 are the Department id, char 7-11 are the firmcode, and
char 12-13 are the quantity; after importing that line, pass to the next line.

How can I create a function, macro or VBA code to do this automatically?

Thanks in advance

 

[Ken Snell [MVP]]

This is a fairly common task. Start with this Google search for a number of
suggested methods. Post back for more specific questions and issues.
http://groups-beta.google.com/groups?q=access+import+text+file+parse

Note that you will need to change the file extension of the file for ACCESS
to import the file (.dat is not on "approved" list); or you'll need to
change Registry Editor key setting to allow .dat as an extension.

 

[John Nurick]

This looks like a standard fixed-width file. Use Get External
Data|Import to launch the Import Text Wizard, select "Fixed Width" in
the first page wizard, and take it from there.

 

[Guest]

I did, but I need to doit automatically, so a "standart user" can do the
import...

 

[John Nurick]

Use the Advanced button in the import text wizard to create and save an
import specification. You can then use a VBA procedure to perform the
import by passing the file name, specification name etc. to
DoCmd.TransferText

If the file name is not constant, either use the InputBox() function so
the user can type the name to use, or else use the code at
www.mvps.org/access/api/api0001.htm to display the standard Open File
dialog.

 

[Guest]

Can you please send me an example code?

Im really new in programming, as you can see

Thanks in advance

 

[John Nurick]

I'll assume that you have created and saved an import specification as
per my last message. You haven't mentioned the names of the table, the
specification or the file, so I'll call them "tblT", "tblT_ImportSpec"
and "MyFile.txt" respectively, and I'll assume that the file is on the
current user's desktop.

In that case you'd put a button on a form, and in the button's Click
event procedure put code like this:

Dim strFileName As String
Dim strPath As String

strPath = Environ("USERPROFILE") & "\Desktop\"
strFileName = "MyFile.txt"

DoCmd.TransferText acImportFixed, "tblT_SpecName", _
"tblT", strPath & strFileName




If you want the user to be able to type the file name, replace
strFileName = "MyFile.txt"
with
strFileName = InputBox("Enter the filename to import")