Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2009
    Posts
    2

    Post Unanswered: Exporting Selective Fields from a txt dump

    Apologies if this is the wrong forum, wasn't sure where to head,


    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. The system it comes from is ancient and pretty much locked down, I can't get into thing to try to alter the format at it spits the data into the .txt file it creates. The vendor of the software 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

    Many thanks for your time and any suggestions you may have.

    jack

    ------------------------------------------------------------------------------------------------------------------------------------
    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

  2. #2
    Join Date
    May 2009
    Posts
    258
    If there's always eight rows of data for each entry, as you have listed, you can do the following:
    1. Use the attached FormatData.xls Excel file
    2. Paste the data starting with the first "CMD" line into Excel, under the "Data" column header
    3. Run the FilterBlanks macro
    4. Select all rows that have blue row numbers, right-click, and choose Delete Row (make sure to select and delete the entire rows)
    5. Remove the filter
    6. Run the GetRecords macro
    7. The data you need should now be in columns B through F


    Regards,

    Ax
    Attached Files Attached Files

  3. #3
    Join Date
    Jun 2009
    Posts
    2

    Smile Thanks!

    Thank you sooooooo much, your solution worked like a charm.

    I had considered delimiters and running find and replace and god knows what else, but watching your spreadsheet in action was a humbling experience to say the least.

    Fantastic Ax.

    Regards,

    Jack

  4. #4
    Join Date
    May 2009
    Posts
    258
    Great to hear Jack! You're welcome, I do a lot with data manipulation and formatting in Excel, you explained it well enough to make it pretty smooth.

    Cheers,

    Ax

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •