Reading a File

Reading a File

am 17.11.2007 15:02:54 von roy

Hi all,

Thanks for your replies.I have to develop an application which will
read a .DAT file.There are several hundred records in this file.Also I
get another excel file with adds and deletes.
There are records in this Excel file with matches in the DAT
file.Whereever the records are marked "deleted" in the excel file,I
have to find the similar records on the DAT file and delete those
records.The records marked "add" in the excel file have to be added or
appended to the DAT file.

The DAT file is as follows:

ImpMonth00100001234567X2000.000123400.0000
ImpMonth00100002334567A7000.000133400.0000
ImpMonth00100003234567B9000.000153400.0000
ImpMonth00100004234567F5000.000128400.0000


The Excel file is :

1284567X6 add 5640
2334567A7 del 1334
3234567B9 del 1534.

At present,I am doing it manually by importing the DAT file to a excel
file.I then import this file and the excel file with add/deletes to an
access database.I then have a query which compares both the tables and
deletes the matching records.Later I have a append query,which inserts
the records marked add.

But this is so tedious and i want to automate it to give it to the
users.

Any solutions?

I have posted this to the excel group too.since I don't mind if the
solution is by using Excel or Acces VBA.Apologies for multi-posting.

Thanks

Roy

Re: Reading a File

am 17.11.2007 19:39:33 von Smartin

On Nov 17, 9:02 am, Roy wrote:
> Hi all,
>
> Thanks for your replies.I have to develop an application which will
> read a .DAT file.There are several hundred records in this file.Also I
> get another excel file with adds and deletes.
> There are records in this Excel file with matches in the DAT
> file.Whereever the records are marked "deleted" in the excel file,I
> have to find the similar records on the DAT file and delete those
> records.The records marked "add" in the excel file have to be added or
> appended to the DAT file.
>
> The DAT file is as follows:
>
> ImpMonth00100001234567X2000.000123400.0000
> ImpMonth00100002334567A7000.000133400.0000
> ImpMonth00100003234567B9000.000153400.0000
> ImpMonth00100004234567F5000.000128400.0000
>
> The Excel file is :
>
> 1284567X6 add 5640
> 2334567A7 del 1334
> 3234567B9 del 1534.
>
> At present,I am doing it manually by importing the DAT file to a excel
> file.I then import this file and the excel file with add/deletes to an
> access database.I then have a query which compares both the tables and
> deletes the matching records.Later I have a append query,which inserts
> the records marked add.
>
> But this is so tedious and i want to automate it to give it to the
> users.
>
> Any solutions?
>
> I have posted this to the excel group too.since I don't mind if the
> solution is by using Excel or Acces VBA.Apologies for multi-posting.
>
> Thanks
>
> Roy

HI Roy,

The DAT file looks like it might just be text (not binary). Does it
have a consistent structure? If so you can build and save an import
spec for in Access. These import specs can be tapped by a macro to
automate importing a file in the "TransferText" macro command. You can
do much the same thing for your spreadsheet using the
"TransferSpreadsheet" macro command.

Does this give you some ideas?