Importing a txt document into an access form or excel spreadsheet

[JackD]

Hi all,

I have been given a text dump which runs into thousands of pages in a truly awful format. I had hoped to import this text file into an Access table (via Excel if necessary) which I could run queries against. This system it comes from is ancient and pretty much locked down, I can't get into thing to try to alter the format at source it spits the data into the .txt file it creates. The vendor of the software does went belly up a while ago so there is no support to call on.

I have copied and pasted a sample of below.

The only parts I am interested in is the text that appears after

Partner
Fee Earner
Department
Work Type

Having the date somehow extracted from the end of that long header that appears for each record would be very useful too, giving me 5 columns (using the first record in the list below) that would look like

Partner__________Fee Earner__________ Department_______________________ Work Type____________________ Date
ZMK director1______ BR another name ______ C&D Commercial & Development (Closed) ___MLA0 Misc Advice - L&CS _________ 23/11/04

and so on for the remainder of the file. (approx 25k records)

Can anyone assist me with a solution or suggestions which will extract only the data that appears after these respective headings and sticks it into a columnar format shown above so I can then run queries against it?

Here is a sample of the text file and how it looks within


------------------------------------------------------------------------------------------------------------------------------------
AXAC519 LC Services User: BXS New matter report Time 15:00 Date 11/06/09
V.0300 No selection made Matters opened from 11/07/00 to 11/06/09 VDU No. 01 Page 659
------------------------------------------------------------------------------------------------------------------------------------
Primary selection: ALL

Client/Matter No. Client name Matter description Date opened
----------------- ----------- ------------------ -----------
CMD 00001620 L&CS - Commercial & Development (Client Closed) use DL, MLA0 Chesters Gdns Repayment of Disco 23/11/04
Branch 0001 LC Services
Partner ZMK director1
Fee earner BR another name
Department C&D Commercial & Development (Closed)
Work type MLA0 Misc Advice - L&CS
Business source 0001 N/A
Business type 0001 N/A Estimated cost 0.00

CMD 00001621 L&CS - Commercial & Development (Client Closed) use DL, MLA0 Chapel View Repayment of Discoun 23/11/04
Branch 0001 LC Services
Partner ZMK director1
Fee earner BR another name
Department C&D Commercial & Development (Closed)
Work type MLA0 Misc Advice - L&CS
Business source 0001 N/A
Business type 0001 N/A Estimated cost 0.00

CMD 00001622 L&CS - Commercial & Development (Client Closed) use DL, MLA0 Lovett Walk Not of Transfer 25/11/04
Branch 0001 LC Services
Partner ZMK director1
Fee earner BR another name
Department C&D Commercial & Development (Closed)
Work type MLA0 Misc Advice - L&CS
Business source 0001 N/A
Business type 0001 N/A Estimated cost 0.00

CMD 00001623 L&CS - Commercial & Development (Client Closed) use DL, MLA0 - Lord Lawson Youth Wing Licence 25/11/04
Branch 0001 LC Services
Partner ZMK director1
Fee earner ZPCM person mentioned (Leaver)
Department C&D Commercial & Development (Closed)
Work type MLA0 Misc Advice - L&CS
Business source 0001 N/A
Business type 0001 N/A Estimated cost 0.00

CMD 00001624 L&CS - Commercial & Development (Client Closed) use DL, LRS Land adjoining Stannerford Road 25/11/04
Branch 0001 LC Services
Partner ZMK director1
Fee earner CJ another person
Department C&D Commercial & Development (Closed)
Work type LRS Land Registry Searches
Business source 0001 N/A
Business type 0001 N/A Estimated cost 0.00

CMD 00001625 L&CS - Commercial & Development (Client Closed) use DL, MGMT Service Plan 25/11/04
Branch 0001 LC Services
Partner ZMK director1
Fee earner MB another person
Department C&D Commercial & Development (Closed)
Work type MGMT Management
Business source 0001 N/A
Business type 0001 N/A Estimated cost 0.00


Thanks,

JackD

 

[DCrake]

This is achievable using the Open File for Input command. Whereby you open the text fiel and read each line one at a time and evaluate each line to determine what is what.



CMD 00001620 L&CS - Commercial & Development (Client Closed) use DL, MLA0 Chesters Gdns Repayment of Disco 23/11/04
Branch 0001 LC Services
Partner ZMK director1
Fee earner BR another name
Department C&D Commercial & Development (Closed)
Work type MLA0 Misc Advice - L&CS
Business source 0001 N/A
Business type 0001 N/A Estimated cost 0.00


So are you saying these are the only items you need?

David

 

[DCrake]

Please find a section of the solution you are looking for. In this sample it contains a target table (TblRawData) and a module (ModMain).

Within ModMain are 2 functions

DropHeader()
ImportTargetFile()

You need to call DropHeader first.

If you are calling this from VB as opposed Access you will need to make changes to the code around the CurrentProject.Path to Application.Path syntax.

Read the comments to see how it works.

David