linking to tab delineated text file

[Richard]

When I manually link to a tab delimited table (labor.txt) in Access it works
fine. However, when I use the following Access VB program many of the columns
are merged. Apparently the program is recognizing some of the tabs, but not
all.

Sub import()
Dim db As DAO.Database
Dim td As TableDef

Set db = CurrentDb
Set td = New TableDef
With td
.Connect = "Text;FMT=TabDelimited;HDR=Yes;DATABASE=C:\Documents and
Settings\u4rf9rmd\My Documents\CRREL Reports\Detsch Calc\;"
.SourceTableName = "labor.txt"
Debug.Print .Connect
.Name = "temptable"
End With
db.TableDefs.Append td
db.Execute "SELECT * INTO permanenttable FROM temptable"
db.TableDefs.Delete td.Name
End Sub

 

[GeoffG]

Richard:

Why not use DoCmd.TransferText?

First create an Import Specification as follows:

1. Open the File menu, select Get External Data, Import.
2. Navigate to the folder containing Labor.txt, point to that file and
click the Import button.
3. In the Import Text Wizard that opens, click the Advanced button to
open the "Labor Import Specification" dialog.
4. Enter the field names in the Field Names column.
5. When the dialog is complete, click the Save As button and click OK to
save the specification as "Labor Import Specification" and return to the
previous dialog.
6. Click OK again to return to the Import Specification Wizard.
7. Click the Cancel button (as you don't want to import the text
manually).

Then you should be able to run code like this:


Sub ImportText()

Dim strPathName As String

strPathName = _
"C:\Documents and Settings\u4rf9rmd\My Documents" _
& "\CRREL Reports\Detsch Calc\labor.txt"

DoCmd.TransferText acImportDelim, "Labor Import Specification", _
"permanenttable", strPathName, True

End Sub

This assumes your permanent table already exists. If it doesn't, you could
create it first using DAO.


Geoff

 

[Richard]

Geoff,
Worked great. Have follow up question.

One of the reasons I am writing this macro is to set up multiple PCs so they
can analyzed the data. Where is the "Labor Import Specification" file saved,
so I can copy to another computer?

P.S. I am writting this program for a co-worked named Geoff (same spelling
as yours). Weird.

 

[GeoffG]

Richard,

Where is the "Labor Import Specification" file saved,
so I can copy to another computer?

The Import Specification is saved with the database. It's hidden from view.
The only way you know it exists is if you go to recreate it and save it
again. Then you'll get a message saying "It already exists. Do you want to
overwrite it?"

If you copy your copy of the database to the other computer, then the Import
Specification will go with it. I don't know if this will be a problem for
you, eg if the other copy of the database stores different data to your
copy. You'd better make a backup of the other computer's database first
before copying over your copy just in case. If you only have one computer
to deal with, you could import their data to your copy or you could create
the Labor Import Specification in their copy of the database.

This does seem fiddly. But I don't know of another way.

Geoff

 

[John Spencer]

One way to get the import specification transferred is to
-- Open the target database
-- Select File: Get External Data: Import
-- Locate the source data base and select it
-- Click options
-- Check Import/Export Specifications
-- Uncheck relationships and menus and Toolbars
-- Do NOT select any objects from the lists on the tabs
-- Click OK

All the specifications from the source database will be imported into the
target database.
(The specifications are stored in a hidden system table in the database.)


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[GeoffG]

Thanks John. Nice to know.

Geoff