Need help with VBA to extract data from txt into an Access table

[johnny.c.phan]

Hello,
I want to have a MS Access process (guessing it will be a macro with
some VBA code) to extract data from a TXT file and insert into a table
in an MS Access database. The txt file is automatically generated daily
by some other program and is date specific.

There are four data/fields that I need to extract and insert.

Table Field #1
Field Name: Date
Format: mm/dd/yyyy
Source: TXT file, second line below "YESTERDAY" text string

Table Field #2
Field Name: Personal
Format: long integer
Source: TXT file, second line below "PERSONALPAYEES" text string, first
tab delimited column

Table Field #3
Field name: Personal_PickList
Format: long integer
Source: TXT file, second line below "PERSONAL_PICKLIST_PAYEES" text
string, second tab delimited column

Table Field #4
Field Name: PickList
Format: long integer
Source: TXT file, second line below "PICKLIST_PAYEES" text string,
third tab delimited column

Any advice would be greatly appreciated.

jp

 

[aaron.kempf]

hey

if you used a real database; like MSDE or SQL Server; you could use BCP
or DTS to do this.

MDB just isn't meant for real-world use ANYWHERE

best of luck; ADP is tough to learn but it is the only route to go

 

[Ron Hinds]

First import it in the conventional way. After selecting the file, the
Import Text Wizard pops up. Click the Advanced button and define the data
the way you want. Then, save the Import Spec. Now, in code in a Module, use
that named spec with the DoCmd.TransferText method (see Access Help on
TransferText).

 

[Dirk Goldgar]

Hello,
I want to have a MS Access process (guessing it will be a macro with
some VBA code) to extract data from a TXT file and insert into a table
in an MS Access database. The txt file is automatically generated
daily by some other program and is date specific.

There are four data/fields that I need to extract and insert.

Table Field #1
Field Name: Date
Format: mm/dd/yyyy
Source: TXT file, second line below "YESTERDAY" text string

Table Field #2
Field Name: Personal
Format: long integer
Source: TXT file, second line below "PERSONALPAYEES" text string,
first tab delimited column

Table Field #3
Field name: Personal_PickList
Format: long integer
Source: TXT file, second line below "PERSONAL_PICKLIST_PAYEES" text
string, second tab delimited column

Table Field #4
Field Name: PickList
Format: long integer
Source: TXT file, second line below "PICKLIST_PAYEES" text string,
third tab delimited column

I don't think Ron Hinds' suggestion of starting with the text import
wizard is going to help much in this case, because the data are
positional on multiple lines of the text file. It seems to me your best
bet will be to use the builtin VBA I/O statements Open, Line Input, and
Close to open the file and read it line by line, identifying the lines
that contain the data you want, parsing out the data from these lines,
and inserting it into your table (probably via a recordset). I don't
have a good picture of what the input file looks like, though. Does the
file contain the source data for multiple output records, or just one?

 

[aaron.kempf]

BCP is about 10,000 times this powerful.

DTS is about 10,000 times more powerful.

MDB just doesn't cut the cheese anymore, kids
stop the madness; stop using crap products

DEMAND MORE FROM MICROSOFT