Automating the File-Import-Text Delimited routine

[londonkiwi]

Hi there

I really want to automate the import of a series of text files into an Access97 table.

All text files have a standards format eg "Col1","Col2","Col3"

I have tried the TransferText routine, one of the standard Macro Access macro actions - this has limitations

Can anyone point me to some off the shelf code that I can throw in a macro??

cheers

Kiwi

 

[Lizard]

Hi Kiwi,

I made the same automation very simply.
I defined a fixed place on the drive to store my text files and linked them to the DB.
As a CSV file will not show the column headings and I wanted titles in my tables, I created a table with the title with the same format as the linked tables.
After that, for each table, run a "delete query" (that empties the table)and then an "append query" that fills the empty table with the (new) data in the linked table. Put everything in a macro and that's all.
You'll have troubles indeed with the "TransferText"

Good luck.

Olivier

 

[tlbroadbent]

Check these links.

thread181-90472

http://support.microsoft.com/support/kb/articles/Q158/9/40.ASP

http://support.microsoft.com/support/kb/articles/Q230/2/65.ASP

http://p2p.wrox.com/archive/vba_access/2001-05/9.asp

Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[ElGuapo]

In my opinon the best way to automate the importation of data into acces tables is to use the VB.

That way you will have complete control of the happening and will not be vunerable to stupid errors that occasionaly appear when using the access built in macros and queries.

Set a recordset that gets data from the table to which you would like to append new data.

Dim R as recordset
set R= currentdb.openrecordset("Table&quot

Then open the txt file from which you would like to get the data for input:

open "file.txt" for input as #1

Then loop through the file and write the file data to the table (use the input or get statement (depends of the way your data is stored into the txt file)

dim inputdata as string ' the variable you will the data to

EXAMPLE:
do until EOF(1)
Input #1, text
with R
.addnew
!field=text ' the field in the table you would like to store the data to
.update
.end with
r.movenext
Loop

close #1

This code works only if data is delimited by writing each record in a separate row. It also the data only to one of the columns in the table.

In order to get it works correctly for you, you have to modifiy it a little (using get statement if neccesary and writing new with R statements to append the data to other columns).

Enjoy!

ElGuapo

 

[londonkiwi]

Thanks all, will sit down and digest it all.

cheers
Kiwi