Copy specific positions from text file into excel

excell+access

New Member
Joined
Dec 28, 2006
Messages
17
Greetings, I am still so lost on VBA and would love some help how to copy or import specific data from long text files to build a smaller excel report.

Sample text files I believe are Tab Delimited
The file is long and has multiple unique records in each text file.
To identify each of the unique records it has a Control Number.
It looks like this: CTR_:_###########
Then below that is the main reference items to the Control Number
Below that is all of the events that were recorded against the Control Number
Then a new record will follow.

I want to be able to Extract out certain data elements from the record post into an excel document
then repeat till the end of the file.

Thoughts?

Sample File


CTR : 1234567890

F1 F2 F3(DTTM) F4 F5 F6 F7 F8 F9
-----------------------------------------------------------------------------------------------------------
Data1 Data2 DTTM Data4 Data5 Data6 Data7 Data8 Data9
F10 : Data10 F11 : Data11
F12 : Data12 F13 : Data13
------------------------------------------------------ ---------------------------------------------------
F14 : Data15 F16 : Data16
F17 : Data17 F18 : Data18
F19 : Data19 F20 : Data20
F21 : Data21 F22 : Data22
F23 : Data23 F23 : Data23
F24 : Data24 F25 : Data25
F26 : Data26 F27 : Data27
F28 : Data28 F29 : Data29
F29 : Data29 F30 : Data30
F31 : Data31

F32 F33 F34 F35 F36 F37 F38 F39 F40 F41
----------------------------------------------------------------------------------------------------------------------
Data32 Data33 Data34 Data35 Data36 Data37 Data38 Data39 Data40 Data41
Data32 Data33 Data34 Data35 Data36 Data37 Data38 Data39 Data40 Data41
Data32 Data33 Data34 Data35 Data36 Data37 Data38 Data39 Data40 Data41
Data32 Data33 Data34 Data35 Data36 Data37 Data38 Data39 Data40 Data41
Data32 Data33 Data34 Data35 Data36 Data37 Data38 Data39 Data40 Data41
Data32 Data33 Data34 Data35 Data36 Data37 Data38 Data39 Data40 Data41
Data32 Data33 Data34 Data35 Data36 Data37 Data38 Data39 Data40 Data41
Data32 Data33 Data34 Data35 Data36 Data37 Data38 Data39 Data40 Data41
Data32 Data33 Data34 Data35 Data36 Data37 Data38 Data39 Data40 Data41
Data32 Data33 Data34 Data35 Data36 Data37 Data38 Data39 Data40 Data41
Data32 Data33 Data34 Data35 Data36 Data37 Data38 Data39 Data40 Data41
Data32 Data33 Data34 Data35 Data36 Data37 Data38 Data39 Data40 Data41
Data32 Data33 Data34 Data35 Data36 Data37 Data38 Data39 Data40 Data41
Data32 Data33 Data34 Data35 Data36 Data37 Data38 Data39 Data40 Data41
Data32 Data33 Data34 Data35 Data36 Data37 Data38 Data39 Data40 Data41
Data32 Data33 Data34 Data35 Data36 Data37 Data38 Data39 Data40 Data41


Then a new record
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
The code below will open a text file and read it in 1 line at a time, dumping the contents to the activesheet.

Code:
r = 1
Open "C:\YourFile.txt" for Input as 1
While Not EOF(1)
   Line Input #1, t
   c = 1
   Do
      If Instr(t, vbTab) > 0 Then
         s = Left(t, Instr(t, vbTab) - 1)
         t = Mid(t, Instr(t, vbTab) + 1)
      Else
         s = t
         t = ""
      End If
      Cells(r, c) = s
      c = c + 1
   Loop Until t = ""
   r = r + 1
Wend
Close 1

You can modify it to look for "CTR :" and only dump the data you are interested in into the worksheet
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,557
Messages
6,114,288
Members
448,563
Latest member
MushtaqAli

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