Help: Importing txt file into Excel spreadsheet.

pip7441622

New Member
Joined
Jan 26, 2005
Messages
1
I am trying to create an Excel spreadsheet out of a txt file containing the following text:

[Document]
apc_ep_divest_land.apc_divest_land_a_num_sufx=CO0092015-97
apc_ep_divest_land.apc_divest_land_c_doc_type=CON - Contract & amendments, Init. summary
apc_ep_divest_land.title=DIVESTITURE
apc_ep_divest_land.apc_w_date=09/01/2004
apc_ep_divest_land.apc_divest_land_e_pg_count=68
apc_ep_divest_land.r_object_id=0900ff6881afd03e
PATH=H:\ImgExt\SEC New Land Documents\files\0900ff6881afd03e.tif

[Document]
apc_ep_divest_land.apc_divest_land_a_num_sufx=CO0100006-80
apc_ep_divest_land.apc_divest_land_c_doc_type=CON - Contract & amendments, Init. summary
apc_ep_divest_land.title=DIVESTITURE
apc_ep_divest_land.apc_w_date=09/01/2004
apc_ep_divest_land.apc_divest_land_e_pg_count=189
apc_ep_divest_land.r_object_id=0900ff6881afd041
PATH=H:\ImgExt\SEC New Land Documents\files\0900ff6881afd041.tif


This pattern is followed for many more records in the text file, with a record beginning with [Document] and ending with the PATH line. I need to make the "apc..." the column headings, with anything following the = to be in its own cell. Basically I need to extract the data after the =, with each record ( [Document] to the end of the PATH) being a single row. There are thousands of records, so doing this manually would not be desired. Any help at all would be appreciated. Thank You.
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
What do you get when you run Data, Get External Data, Import Text Files? Perhaps after it is imported, you could parse it more easily there. Good Luck!
 
Upvote 0
I think this is what you might be looking for.
Assumptions:
1) data imported into Excel starts in cell A1 and is only in column A
2) each [Document] section is in the exact same layout with same number of rows
3) 1 blank row between each section

Can certainly be modified is assumptions are incorrect...

Code:
Sub macro1()
Dim lngDestRow As Long, lngSrcRow As Long, lngLastRow As Long

    Application.ScreenUpdating = False
    Rows(1).Insert
    Rows(1).Insert 'just to help with the loop later
    Range("A1") = "apc_ep_divest_land.apc_divest_land_a_num_sufx"
    Range("B1") = "apc_ep_divest_land.apc_divest_land_c_doc_type"
    Range("C1") = "apc_ep_divest_land.title"
    Range("D1") = "apc_ep_divest_land.apc_w_date"
    Range("E1") = "apc_ep_divest_land.apc_divest_land_e_pg_count"
    Range("F1") = "apc_ep_divest_land.r_object_id"
    Range("G1") = "PATH"
    
    lngDestRow = 2 'start putting the data in row 2
    lngSrcRow = 2
    lngLastRow = Range("A65536").End(xlUp).Row
    
    Do While lngSrcRow <= lngLastRow - 8
        lngSrcRow = lngSrcRow + 2 'skipempty row and skip [Document] row
        Range("A" & lngDestRow) = Right(Range("A" & lngSrcRow), Len(Range("A" & lngSrcRow)) - InStr(Range("A" & lngSrcRow), "="))
        lngSrcRow = lngSrcRow + 1
        Range("B" & lngDestRow) = Right(Range("A" & lngSrcRow), Len(Range("A" & lngSrcRow)) - InStr(Range("A" & lngSrcRow), "="))
        lngSrcRow = lngSrcRow + 1
        Range("C" & lngDestRow) = Right(Range("A" & lngSrcRow), Len(Range("A" & lngSrcRow)) - InStr(Range("A" & lngSrcRow), "="))
        lngSrcRow = lngSrcRow + 1
        Range("D" & lngDestRow) = Right(Range("A" & lngSrcRow), Len(Range("A" & lngSrcRow)) - InStr(Range("A" & lngSrcRow), "="))
        lngSrcRow = lngSrcRow + 1
        Range("E" & lngDestRow) = Right(Range("A" & lngSrcRow), Len(Range("A" & lngSrcRow)) - InStr(Range("A" & lngSrcRow), "="))
        lngSrcRow = lngSrcRow + 1
        Range("F" & lngDestRow) = Right(Range("A" & lngSrcRow), Len(Range("A" & lngSrcRow)) - InStr(Range("A" & lngSrcRow), "="))
        lngSrcRow = lngSrcRow + 1
        Range("G" & lngDestRow) = Right(Range("A" & lngSrcRow), Len(Range("A" & lngSrcRow)) - InStr(Range("A" & lngSrcRow), "="))
        lngSrcRow = lngSrcRow + 1
        
        lngDestRow = lngDestRow + 1
    Loop
    
    Rows(lngDestRow & ":" & lngLastRow).Delete
    

    Application.ScreenUpdating = True
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,255
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top