How to import txt files into one column

Stevie787 · Apr 20, 2020

Hi,

I'm having an issue with the import of a files within a user defined folder. I need all txt to be in one column, the below code works fine for one txt file but when trying to import the second it creates a new column to the left instead of continuing below with Last row being defined as "i".

I feel like I've been staring at a brick wall for the past day, can someone help.....

VBA Code:

Sub ErrorFinder()

Dim c As Range
Dim j As Integer
Dim Source As Worksheet
Dim Target As Worksheet
Dim wb As Workbook
Dim wbCSV As Workbook
Dim myPath As String
Dim myFile As Variant
Dim fileType As String
Dim i As Integer
    
    
'Change worksheet designations as needed
Set Source = ActiveWorkbook.Worksheets("Input")
Set Target = ActiveWorkbook.Worksheets("Results")

'Clear Previous trace Data
Sheets("Input").Range("A2:A100000").Clear


'Get Target Folder Path From User
With Application.FileDialog(msoFileDialogFolderPicker)
        .Title = "Select Source Folder"
        .AllowMultiSelect = False
        .Show
        myPath = .SelectedItems(1) & "\"
End With

'Specify file type
fileType = "*.trc*"

'Target Path with file type
myFile = Dir(myPath & fileType)

 i = Sheets("Input").Cells(1, "A").End(xlUp).Row + 1
     
'Loop through each Trace file in specified folder
  Do While myFile <> ""
    Sheets("Input").Select

           i = Sheets("Input").Cells(1, "A").End(xlUp).Row + 1
        ' IMPORT DATA FROM TEXT FILE
        With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & myPath & myFile, _
          Destination:=ActiveSheet.Cells(i, 1))
            .TextFileParseType = xlDelimited
            .TextFileConsecutiveDelimiter = False
            .TextFileTabDelimiter = False
            .TextFileSemicolonDelimiter = False
            .TextFileCommaDelimiter = False
            .TextFileSpaceDelimiter = False
            .TextFileOtherDelimiter = "|"
            .Refresh BackgroundQuery:=False
        End With

    i = i + 1
    myFile = Dir
    
  Loop

    'Start transposing the errors
    
    'Clears Results from previous Trace
    Sheets("Results").Range("A2:A10000").Clear
    
    j = 2 ' Start copying to row 2 in target sheet
    For Each c In Source.Range("A2:B100000")   'Do 100000 rows
        If InStr(c, "er99") > 0 Then
           Source.Rows(c.Row).Copy Target.Rows(j)
           j = j + 1
        End If
    Next c
    
    
'Select Results Sheet
Sheets("Results").Select
    
'Clear and insert Formula's

Range("B2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents
Range("B2").Select
ActiveCell.FormulaR1C1 = _
    "=IFERROR(MID(RC[-1],FIND(""00 "",RC[-1])+3,FIND(""00 "",RC[-1])-FIND(""00"",RC[-1])-1),"""")"
    
Range("B2").Select
    Selection.AutoFill Destination:=Range("B2:B99"), Type:=xlFillDefault
Range("B2:B99").Select
Range("C2").Select
    ActiveCell.FormulaR1C1 = "=RC[-1]"
Range("C3").Select
    ActiveCell.FormulaR1C1 = "=RC[-1]"
Range("C2:C3").Select
    Selection.AutoFill Destination:=Range("C2:C100"), Type:=xlFillDefault
Range("C2:C100").Select
       

End Sub

Stevie787 · Apr 21, 2020

Can anyone help with this?

Joe4 · Apr 21, 2020

When debugging these things, I like to try to recreate exactly what you have on your end, so I can walk through some actual examples.
So it would be helpful if we could see a sample of what your data file looks like. There is a tool here which allows you to post images. See: XL2BB - Excel Range to BBCode
Also, it would be good to know if the sheets you are pasting this information to already have anything in them, and if so, what the structure of those sheets look like.

Stevie787 · Apr 21, 2020

Hi Joe,

Thanks for taking a look at this.

The first part of the code which I have an issue with points to a folder which the target files are kept. I've attached a screenshot of the two files which were imported, the code inserts a new column into A and counts 1 down to paste into A3 when importing the second file. What I need is the second file to be pasted just below the first file for the code on the following page to pick out the errors and manipulate the results.

I thought by inserting this code: Destination:=ActiveSheet.Cells(i, 1) it would count rows as per the loop and add 1 for every file.

The files which are imported vary in length so I've taken a snippet of on of the files.



Paste Trace File In Here
< 10:53:12.422 8AF#8000#00: C0CEid0486
> 10:53:12.438 8AF#8000#00: C0CEid0486er00/00
< 10:53:12.453 8AF#8000#00: C0QCid0487
> 10:53:12.469 8AF#8000#00: C0QCid0487er00/00qc1
< 10:53:12.469 8AF#8000#00: C0AWid0488
> 10:53:12.812 8AF#8000#00: C0AWid0488er00/00
< 10:53:12.812 8AF#8000#00: C0AWid0489
> 10:53:13.171 8AF#8000#00: C0AWid0489er00/00
< 10:53:13.171 8AF#8000#00: C0RTid0490
> 10:53:13.233 8AF#8000#00: C0RTid0490er00/00rt0 0 0 0 0 0 0 0
< 10:53:13.249 8AF#8000#00: C0ZAid0491
> 10:53:13.389 8AF#8000#00: C0ZAid0491er00/00
< 10:53:13.389 8AF#8000#00: C0IVid0492
> 10:53:14.809 8AF#8000#00: C0IVid0492er00/00
< 10:53:14.809 8AF#8000#00: C0JEid0493
> 10:53:15.012 8AF#8000#00: C0JEid0493er00/00
< 10:53:15.012 8AF#8000#00: C0CPid0494cl00000000000000cb00000000000000
> 10:53:15.058 8AF#8000#00: C0CPid0494er00/00
< 10:53:15.074 8AF#8000#00: C0HOid0495
> 10:53:15.105 8AF#8000#00: C0HOid0495er00/00
< 10:53:15.105 8AF#8000#00: C0CDid0496
> 10:53:15.121 8AF#8000#00: C0CDid0496er00/00
< 10:53:15.121 8AF#8000#00: C0AZid0497
> 10:53:15.433 8AF#8000#00: C0AZid0497er00/00
< 10:53:15.433 8AF#8000#00: P1RVid0498
> 10:53:15.464 8AF#8000#00: P1RVid0498na0000005544nb0000003539nc0000008822nd0000007207
< 10:53:15.464 8AF#8000#00: P2RVid0499
> 10:53:15.480 8AF#8000#00: P2RVid0499na0000034010nb0000002635nc0000049515nd0000039585
< 10:53:15.480 8AF#8000#00: P3RVid0500
> 10:53:15.495 8AF#8000#00: P3RVid0500na0000032490nb0000002496nc0000046530nd0000037133
< 10:53:15.495 8AF#8000#00: P4RVid0501
> 10:53:15.511 8AF#8000#00: P4RVid0501na0000031139nb0000002385nc0000044171nd0000035139
< 10:53:15.511 8AF#8000#00: P5RVid0502
> 10:53:15.526 8AF#8000#00: P5RVid0502na0000029988nb0000002295nc0000042024nd0000033322
< 10:53:15.526 8AF#8000#00: P6RVid0503
> 10:53:15.542 8AF#8000#00: P6RVid0503na0000029132nb0000002230nc0000040329nd0000032029
< 10:53:15.542 8AF#8000#00: P7RVid0504
> 10:53:15.558 8AF#8000#00: P7RVid0504na0000028188nb0000002153nc0000038618nd0000030758
< 10:53:15.558 8AF#8000#00: P8RVid0505
> 10:53:15.573 8AF#8000#00: P8RVid0505na0000027385nb0000002065nc0000037189nd0000029647
< 10:53:15.573 8AF#8000#00: I0RVid0506
> 10:53:15.589 8AF#8000#00: I0RVid0506na0000008431
! 10:53:16.587 8AF#8000#00: USB Info: ABORT_PIPE
! 10:53:16.634 8AF#8000#00: USB Info: CLOSE USB_READ_THREAD
< 11:01:57.964 8AF#8000#00: C0RQid0101
> 11:01:57.996 8AF#8000#00: C0RQid0101rq0000
< 11:01:57.996 8AF#8000#00: C0QBid0102
> 11:01:58.011 8AF#8000#00: C0QBid0102er00/00qb1
< 11:01:58.011 8AF#8000#00: C0RIid0103
> 11:01:58.027 8AF#8000#00: C0RIid0103er00/00si2013-09-16sn6619
< 11:01:58.027 8AF#8000#00: C0QMid0104
> 11:01:58.058 8AF#8000#00: C0QMid0104er00/00ka000180xt30xa30xw08000xl01xr00xm03500xx11400ys090xu3540xv3700yu0060kl360kc0yx0060ke00000000xn00xo00ym0060kr0km360
< 11:01:58.058 8AF#8000#00: C0RMid0105
> 11:01:58.136 8AF#8000#00: C0RMid0105er00/00kb0Dkp08 C00000 X00000 P10000 P20000 P30000 P40000 P50000 P60000 P70000 P80000 I00000
< 11:01:58.136 8AF#8000#00: C0RFid0106
> 11:01:58.152 8AF#8000#00: C0RFid0106er00/00rf7.1S A 2011-03-02
< 11:01:58.152 8AF#8000#00: P1RFid0107
> 11:01:58.167 8AF#8000#00: P1RFid0107rf3.8S b 2011-02-22
< 11:01:58.167 8AF#8000#00: P1RJid0108
> 11:01:58.183 8AF#8000#00: P1RJid0108jd2019-08-26js1
< 11:01:58.183 8AF#8000#00: P2RFid0109
> 11:01:58.183 8AF#8000#00: P2RFid0109rf3.8S b 2011-02-22
< 11:01:58.198 8AF#8000#00: P2RJid0110
> 11:01:58.198 8AF#8000#00: P2RJid0110jd2019-08-26js1
< 11:01:58.198 8AF#8000#00: P3RFid0111
> 11:01:58.214 8AF#8000#00: P3RFid0111rf3.8S b 2011-02-22
< 11:01:58.214 8AF#8000#00: P3RJid0112
> 11:01:58.230 8AF#8000#00: P3RJid0112jd2019-08-26js1
< 11:01:58.230 8AF#8000#00: P4RFid0113
> 11:01:58.245 8AF#8000#00: P4RFid0113rf3.8S b 2011-02-22
< 11:01:58.245 8AF#8000#00: P4RJid0114
> 11:01:58.245 8AF#8000#00: P4RJid0114jd2019-08-26js1
< 11:01:58.245 8AF#8000#00: P5RFid0115
> 11:01:58.261 8AF#8000#00: P5RFid0115rf3.8S b 2011-02-22
< 11:01:58.261 8AF#8000#00: P5RJid0116
> 11:01:58.276 8AF#8000#00: P5RJid0116jd2019-08-26js1
< 11:01:58.276 8AF#8000#00: P6RFid0117
> 11:01:58.292 8AF#8000#00: P6RFid0117rf3.8S b 2011-02-22
< 11:01:58.292 8AF#8000#00: P6RJid0118
> 11:01:58.308 8AF#8000#00: P6RJid0118jd2019-08-26js1
< 11:01:58.308 8AF#8000#00: P7RFid0119
> 11:01:58.308 8AF#8000#00: P7RFid0119rf3.8S b 2011-02-22
< 11:01:58.308 8AF#8000#00: P7RJid0120
> 11:01:58.323 8AF#8000#00: P7RJid0120jd2019-08-26js1
< 11:01:58.323 8AF#8000#00: P8RFid0121
> 11:01:58.354 8AF#8000#00: P8RFid0121rf3.8S b 2011-02-22
< 11:01:58.354 8AF#8000#00: P8RJid0122
> 11:01:58.370 8AF#8000#00: P8RJid0122jd2019-08-26js1
< 11:01:58.370 8AF#8000#00: C0QBid0123
> 11:01:58.386 8AF#8000#00: C0QBid0123er00/00qb1
< 11:01:58.386 8AF#8000#00: X0RFid0124
> 11:01:58.401 8AF#8000#00: X0RFid0124rf1.3S 2009-11-13
< 11:01:58.401 8AF#8000#00: X0RJid0125
> 11:01:58.417 8AF#8000#00: X0RJid0125jd2019-05-28js1
< 11:01:58.417 8AF#8000#00: I0RFid0126
> 11:01:58.417 8AF#8000#00: I0RFid0126rf3.4S b 2012-02-13
< 11:01:58.417 8AF#8000#00: I0RJid0127
> 11:01:58.432 8AF#8000#00: I0RJid0127jd2019-02-25js1
< 11:01:58.432 8AF#8000#00: C0RMid0128
> 11:01:58.495 8AF#8000#00: C0RMid0128er00/00kb0Dkp08 C00000 X00000 P10000 P20000 P30000 P40000 P50000 P60000 P70000 P80000 I00000
< 11:01:58.526 8AF#8000#00: C0QMid0129
> 11:01:58.573 8AF#8000#00: C0QMid0129er00/00ka000180xt30xa30xw08000xl01xr00xm03500xx11400ys090xu3540xv3700yu0060kl360kc0yx0060ke00000000xn00xo00ym0060kr0km360
< 11:01:58.573 8AF#8000#00: C0SRid0130
> 11:01:58.604 8AF#8000#00: C0SRid0130er00/00sr031
< 11:01:58.635 8AF#8000#00: C0QBid0131
> 11:01:58.666 8AF#8000#00: C0QBid0131er00/00qb1
< 11:01:58.666 8AF#8000#00: C0RUid0132
> 11:01:58.698 8AF#8000#00: C0RUid0132er00/00ru00910 08050 30000 30000
< 11:02:00.757 8AF#8000#00: C0RVid0133vo00
> 11:02:00.819 8AF#8000#00: C0RVid0133er00/00vd2020-02-03 10:25vs1
< 11:02:00.819 8AF#8000#00: C0RVid0134vo01
> 11:02:00.897 8AF#8000#00: C0RVid0134er00/00vd2020-02-03 10:25vs1
< 11:02:00.897 8AF#8000#00: C0RVid0135vo05
> 11:02:00.975 8AF#8000#00: C0RVid0135er00/00vd2020-03-14 23:59vs1
< 11:02:00.975 8AF#8000#00: C0RSid0136
> 11:02:01.006 8AF#8000#00: C0RSid0136er00/00ts2020-02-03 10:26
< 11:02:01.006 8AF#8000#00: C0RIid0137
> 11:02:01.022 8AF#8000#00: C0RIid0137er00/00si2013-09-16sn6619
< 11:02:01.069 8AF#8000#00: C0TTid0138tt33tf0tl0319tv00700tg6tu0
> 11:02:01.084 8AF#8000#00: C0TTid0138er00/00
< 11:02:01.116 8AF#8000#00: C0TTid0139tt22tf0tl0424tv00650tg2tu0
> 11:02:01.147 8AF#8000#00: C0TTid0139er00/00
< 11:02:01.162 8AF#8000#00: C0TTid0140tt11tf0tl0490tv00150tg2tu1
> 11:02:01.209 8AF#8000#00: C0TTid0140er00/00
< 11:02:01.225 8AF#8000#00: C0TTid0141tt03tf1tl0219tv00100tg1tu0
> 11:02:01.272 8AF#8000#00: C0TTid0141er00/00
< 11:02:01.287 8AF#8000#00: C0TTid0142tt34tf0tl0235tv00010tg2tu0
> 11:02:01.334 8AF#8000#00: C0TTid0142er00/00
< 11:02:01.350 8AF#8000#00: C0TTid0143tt23tf1tl0424tv00600tg2tu0
> 11:02:01.396 8AF#8000#00: C0TTid0143er00/00
< 11:02:01.412 8AF#8000#00: C0TTid0144tt12tf0tl0490tv03500tg2tu1
> 11:02:01.459 8AF#8000#00: C0TTid0144er00/00
< 11:02:01.474 8AF#8000#00: C0TTid0145tt04tf0tl0871tv12500tg3tu0
> 11:02:01.506 8AF#8000#00: C0TTid0145er00/00
< 11:02:01.521 8AF#8000#00: C0TTid0146tt24tf0tl0820tv01800tg3tu1
> 11:02:01.568 8AF#8000#00: C0TTid0146er00/00
< 11:02:01.584 8AF#8000#00: C0TTid0147tt13tf0tl0820tv12500tg3tu1
> 11:02:01.630 8AF#8000#00: C0TTid0147er00/00
< 11:02:01.646 8AF#8000#00: C0TTid0148tt05tf1tl0871tv10650tg3tu0
> 11:02:01.693 8AF#8000#00: C0TTid0148er00/00
< 11:02:01.708 8AF#8000#00: C0TTid0149tt25tf0tl1080tv54200tg5tu0
> 11:02:01.755 8AF#8000#00: C0TTid0149er00/00
< 11:02:01.771 8AF#8000#00: C0TTid0150tt14tf0tl0220tv00010tg0tu0
> 11:02:01.818 8AF#8000#00: C0TTid0150er00/00
< 11:02:01.833 8AF#8000#00: C0TTid0151tt06tf0tl0519tv00700tg2tu1
> 11:02:01.864 8AF#8000#00: C0TTid0151er00/00
< 11:02:01.896 8AF#8000#00: C0TTid0152tt26tf0tl1080tv00010tg5tu0
> 11:02:01.927 8AF#8000#00: C0TTid0152er00/00
< 11:02:01.942 8AF#8000#00: C0TTid0153tt15tf0tl0256tv00350tg4tu0
> 11:02:01.989 8AF#8000#00: C0TTid0153er00/00
< 11:02:02.005 8AF#8000#00: C0TTid0154tt07tf0tl0519tv03500tg2tu1
> 11:02:02.052 8AF#8000#00: C0TTid0154er00/00
< 11:02:02.067 8AF#8000#00: C0TTid0155tt27tf0tl0190tv00010tg0tu0
> 11:02:02.114 8AF#8000#00: C0TTid0155er00/00
< 11:02:02.130 8AF#8000#00: C0TTid0156tt16tf0tl0251tv00350tg0tu0
> 11:02:02.176 8AF#8000#00: C0TTid0156er00/00
< 11:02:02.192 8AF#8000#00: C0TTid0157tt08tf0tl0871tv12500tg3tu1
> 11:02:02.223 8AF#8000#00: C0TTid0157er00/00
< 11:02:02.254 8AF#8000#00: C0TTid0158tt28tf0tl0626tv04000tg6tu0
> 11:02:02.286 8AF#8000#00: C0TTid0158er00/00
< 11:02:02.301 8AF#8000#00: C0TTid0159tt17tf0tl0251tv00350tg0tu0
> 11:02:02.348 8AF#8000#00: C0TTid0159er00/00
< 11:02:02.364 8AF#8000#00: C0TTid0160tt29tf1tl1080tv43670tg5tu0
> 11:02:02.410 8AF#8000#00: C0TTid0160er00/00
< 11:02:02.426 8AF#8000#00: C0TTid0161tt18tf1tl0210tv12500tg0tu1
> 11:02:02.473 8AF#8000#00: C0TTid0161er00/00
< 11:02:02.488 8AF#8000#00: C0TTid0162tt19tf0tl0534tv00670tg2tu0
> 11:02:02.535 8AF#8000#00: C0TTid0162er00/00
< 11:02:02.551 8AF#8000#00: C0TTid0163tt30tf0tl0519tv04000tg2tu0
> 11:02:02.598 8AF#8000#00: C0TTid0163er00/00
< 11:02:02.613 8AF#8000#00: C0TTid0164tt00tf0tl0519tv04000tg2tu0
> 11:02:02.644 8AF#8000#00: C0TTid0164er00/00
< 11:02:02.676 8AF#8000#00: C0TTid0165tt31tf0tl0422tv00650tg2tu0
> 11:02:02.707 8AF#8000#00: C0TTid0165er00/00
< 11:02:02.738 8AF#8000#00: C0TTid0166tt20tf0tl0343tv00650tg4tu0
> 11:02:02.769 8AF#8000#00: C0TTid0166er00/00
< 11:02:02.800 8AF#8000#00: C0TTid0167tt01tf1tl0519tv03600tg2tu0
> 11:02:02.832 8AF#8000#00: C0TTid0167er00/00
< 11:02:02.847 8AF#8000#00: C0TTid0168tt32tf0tl0219tv00150tg1tu0
> 11:02:02.863 8AF#8000#00: C0TTid0168er00/00
< 11:02:02.878 8AF#8000#00: C0TTid0169tt21tf0tl0442tv00610tg6tu1
> 11:02:02.925 8AF#8000#00: C0TTid0169er00/00
< 11:02:02.941 8AF#8000#00: C0TTid0170tt10tf0tl0519tv04000tg2tu0
> 11:02:02.972 8AF#8000#00: C0TTid0170er00/00
< 11:02:03.003 8AF#8000#00: C0TTid0171tt02tf0tl0219tv00150tg1tu0
> 11:02:03.034 8AF#8000#00: C0TTid0171er00/00
< 11:02:03.034 8AF#8000#00: C0CUid0172cu0
> 11:02:03.050 8AF#8000#00: C0CUid0172er00/00
< 11:02:03.050 8AF#8000#00: C0CEid0173
> 11:02:03.081 8AF#8000#00: C0CEid0173er00/00
< 11:02:03.081 8AF#8000#00: C0QCid0174
> 11:02:03.097 8AF#8000#00: C0QCid0174er00/00qc1
< 11:02:03.097 8AF#8000#00: C0AWid0175
> 11:02:03.440 8AF#8000#00: C0AWid0175er00/00
< 11:02:03.440 8AF#8000#00: PXAFid0176af0
> 11:02:03.580 8AF#8000#00: PXAFid0176er00
< 11:02:03.580 8AF#8000#00: C0QSid0177
> 11:02:03.627 8AF#8000#00: C0QSid0177er00/00qs0 0 0 0 0 0 0 0
< 11:02:03.627 8AF#8000#00: C0RJid0178
> 11:02:03.674 8AF#8000#00: C0RJid0178er00/00tq1 1 1 1 1 1 1 1
< 11:02:03.674 8AF#8000#00: C0AWid0179
> 11:02:04.033 8AF#8000#00: C0AWid0179er00/00
< 11:02:04.048 8AF#8000#00: C0CPid0180cl00000000000000cb00000000000000
> 11:02:04.080 8AF#8000#00: C0CPid0180er00/00
< 11:02:04.080 8AF#8000#00: C0CQid0181
> 11:02:04.095 8AF#8000#00: C0CQid0181er00/00cq0
< 11:02:04.095 8AF#8000#00: C0CBid0182bt7F
> 11:02:04.111 8AF#8000#00: C0CBid0182er00/00
< 11:08:51.426 8AF#8000#00: C0QWid0183
> 11:08:51.474 8AF#8000#00: C0QWid0183er00/00qw1
< 11:08:51.480 8AF#8000#00: C0RTid0184
> 11:08:51.523 8AF#8000#00: C0RTid0184er00/00rt0 0 0 0 0 0 0 0
< 11:08:51.559 8AF#8000#00: C0COid0185
> 11:08:51.791 8AF#8000#00: C0COid0185er00/00
< 11:08:51.793 8AF#8000#00: C0AWid0186
> 11:08:52.140 8AF#8000#00: C0AWid0186er00/00
< 11:08:57.406 8AF#8000#00: C0CTid0187cp30
> 11:08:58.180 8AF#8000#00: C0CTid0187er00/00ct0
< 11:08:58.192 8AF#8000#00: C0HOid0188
> 11:08:58.206 8AF#8000#00: C0HOid0188er00/00
< 11:08:59.856 8AF#8000#00: C0COid0189
> 11:09:00.065 8AF#8000#00: C0COid0189er00/00
< 11:09:00.067 8AF#8000#00: C0AWid0190
> 11:09:00.414 8AF#8000#00: C0AWid0190er00/00
< 11:09:00.457 8AF#8000#00: C0CPid0191cl0000000000003Fcb0000000000003F
> 11:09:00.491 8AF#8000#00: C0CPid0191er00/00
< 11:09:00.492 8AF#8000#00: C0RCid0192
> 11:09:00.706 8AF#8000#00: C0RCid0192er00/00cd00000000000000ce00000000000000000000000000
< 11:09:00.739 8AF#8000#00: C0CIid0193cp06cv1281bi0043bw085
> 11:09:06.965 8AF#8000#00: C0CIid0193er00/00bb/08T0211937
< 11:09:06.972 8AF#8000#00: C0CLid0194bd1bp0616cn05co0960cf300cv1281
> 11:09:15.636 8AF#8000#00: C0CLid0194er00/00ci00000000bb/18235979391514415035/18235979391514514050/00/00/00
< 11:09:15.638 8AF#8000#00: C0RCid0195
> 11:09:15.952 8AF#8000#00: C0RCid0195er00/00cd00000000000020ce00000000000000000000000020
< 11:09:18.846 8AF#8000#00: C0CPid0196cl00000000000000cb00000000000000
> 11:09:18.882 8AF#8000#00: C0CPid0196er00/00
< 11:09:41.715 8AF#8000#00: C0CTid0197cp30
> 11:09:44.608 8AF#8000#00: C0CTid0197er00/00ct0
< 11:09:44.620 8AF#8000#00: C0HOid0198
> 11:09:44.641 8AF#8000#00: C0HOid0198er00/00
< 11:09:54.620 8AF#8000#00: C0COid0199
> 11:09:54.844 8AF#8000#00: C0COid0199er00/00
< 11:09:54.846 8AF#8000#00: C0AWid0200
> 11:09:55.193 8AF#8000#00: C0AWid0200er00/00
< 11:09:55.216 8AF#8000#00: C0JXid0201xs07990
> 11:09:57.094 8AF#8000#00: C0JXid0201er00/00
< 11:10:02.150 8AF#8000#00: X0OSid0202on1od01os058oe058
> 11:10:02.260 8AF#8000#00: X0OSid0202er00
< 11:10:02.961 8AF#8000#00: X0ORid0203on1
> 11:10:02.970 8AF#8000#00: X0ORid0203er00
< 11:10:21.282 8AF#8000#00: C0CTid0204cp30
> 11:10:22.059 8AF#8000#00: C0CTid0204er00/00ct0
< 11:10:22.071 8AF#8000#00: C0HOid0205
> 11:10:22.085 8AF#8000#00: C0HOid0205er00/00
< 11:10:25.041 8AF#8000#00: C0COid0206
> 11:10:25.255 8AF#8000#00: C0COid0206er00/00
< 11:10:25.257 8AF#8000#00: C0AWid0207
> 11:10:25.604 8AF#8000#00: C0AWid0207er00/00
< 11:10:25.648 8AF#8000#00: C0CPid0208cl000000001F8000cb000000001F8000
> 11:10:25.683 8AF#8000#00: C0CPid0208er00/00
< 11:10:25.684 8AF#8000#00: C0RCid0209
> 11:10:25.896 8AF#8000#00: C0RCid0209er00/00cd00000000000020ce00000000000000000000000020
< 11:10:25.909 8AF#8000#00: C0CIid0210cp21cv1281bi0043bw085
> 11:10:30.780 8AF#8000#00: C0CIid0210er00/00bb/08APE07902
< 11:10:30.784 8AF#8000#00: C0CLid0211bd1bp0615cn05co0960cf300cv1281
> 11:10:39.475 8AF#8000#00: C0CLid0211er00/00ci00000000bb/07G200049/00/00/00/00
< 11:10:39.478 8AF#8000#00: C0RCid0212
> 11:10:39.711 8AF#8000#00: C0RCid0212er00/00cd00000000100020ce00000000000000000000100020
< 11:10:39.758 8AF#8000#00: C0CPid0213cl00000000000000cb00000000000000
> 11:10:39.792 8AF#8000#00: C0CPid0213er00/00
< 11:10:39.881 8AF#8000#00: C0CTid0214cp30
> 11:10:41.426 8AF#8000#00: C0CTid0214er00/00ct0
< 11:10:41.469 8AF#8000#00: C0CTid0215cp30
> 11:10:42.253 8AF#8000#00: C0CTid0215er00/00ct0
< 11:10:42.265 8AF#8000#00: C0HOid0216
> 11:10:42.279 8AF#8000#00: C0HOid0216er00/00
< 11:10:43.394 8AF#8000#00: C0COid0217
> 11:10:43.612 8AF#8000#00: C0COid0217er00/00
< 11:10:43.614 8AF#8000#00: C0AWid0218
> 11:10:43.962 8AF#8000#00: C0AWid0218er00/00
< 11:10:44.007 8AF#8000#00: C0CPid0219cl00000000200000cb00000000200000
> 11:10:44.064 8AF#8000#00: C0CPid0219er00/00
< 11:10:44.066 8AF#8000#00: C0RCid0220
> 11:10:44.293 8AF#8000#00: C0RCid0220er00/00cd00000000100020ce00000000000000000000100020
< 11:10:44.310 8AF#8000#00: C0CIid0221cp22cv1281bi0043bw085
> 11:10:49.088 8AF#8000#00: C0CIid0221er00/00bb/08R1300202
< 11:10:49.092 8AF#8000#00: C0CLid0222bd1bp0603cn05co0960cf070cv1281
> 11:10:57.767 8AF#8000#00: C0CLid0222er00/00ci00000000bb/00/00/00/00/00
< 11:10:57.770 8AF#8000#00: C0RCid0223
> 11:10:58.074 8AF#8000#00: C0RCid0223er00/00cd00000000300020ce00000000000000000000300020
< 11:10:58.144 8AF#8000#00: C0CPid0224cl00000000000000cb00000000000000
> 11:10:58.194 8AF#8000#00: C0CPid0224er00/00
< 11:10:58.222 8AF#8000#00: C0CTid0225cp30
> 11:10:59.699 8AF#8000#00: C0CTid0225er00/00ct0
< 11:10:59.968 8AF#8000#00: C0RIid0226
> 11:10:59.982 8AF#8000#00: C0RIid0226er00/00si2013-09-16sn6619
< 11:11:02.522 8AF#8000#00: C0TPid0227xp01899&yp5298 5208 5118 5028 4938 4848 4758 4668tm1&tt23tp2244tz2164th2450td1
> 11:11:10.269 8AF#8000#00: C0TPid0227er00/00
< 11:11:10.271 8AF#8000#00: C0RTid0228
> 11:11:10.313 8AF#8000#00: C0RTid0228er00/00rt1 1 1 1 1 1 1 1
< 11:11:10.527 8AF#8000#00: C0RTid0229
> 11:11:10.569 8AF#8000#00: C0RTid0229er00/00rt1 1 1 1 1 1 1 1
< 11:11:10.949 8AF#8000#00: C0ASid0230at0&tm1&xp04620&yp1445&th2450te2450lp2333&ch000&zl1833&zx1783&ip0020&it0&fp0001&av00115&as0500&ta010&ba0000&oa030&lm1&ll4&lv1&ld00&de0500&wt10&mv00000&mc00&mp000&ms0300&gi000&gj0gk0zu0060&zr01142&mh0000&zo005&po0050
> 11:12:06.161 8AF#8000#00: C0ASid0230er00/00
< 11:12:06.430 8AF#8000#00: C0DSid0231dm3&tm1&xp04555&yp5300 5210 5120 5030 4940 4850 4760 4670zx2035&lp2255&zl2045&ip0000&it0&fp0004&th2450te2450dv00115&ds0500&ss0100&rv000&ta000&ba0000&lm0&zo005&ll1&lv1&de0500&mv00000&mc00&mp000&ms0300&wt00&gi000&gj0gk0zu0100&dj00zr02592&mh0000&po0100
> 11:12:11.156 8AF#8000#00: C0DSid0231er00/00
< 11:12:11.633 8AF#8000#00: C0ASid0232at0&tm1 1 1 0&xp04620 04620 04620 00000&yp1445 1445 1445 0000&th2450te2450lp2333 2333 2333 2450&ch000&zl1833 1833 1833 2450&zx1783 1783 1783 0000&ip0020&it0&fp0001 0001 0001 0000&av00115 00115 00115 00000&as0500&ta010&ba0000&oa030&lm1&ll4&lv1&ld00&de0500&wt10&mv00000&mc00&mp000&ms0300&gi000&gj0gk0zu0060 0060 0060 0000&zr01142 01142 01142 00000&mh0000&zo005&po0050
> 11:12:32.714 8AF#8000#00: C0ASid0232er00/00
< 11:12:32.899 8AF#8000#00: C0DSid0233dm3 3 3 0&tm1 1 1 0&xp04645 04645 04735 00000&yp5300 5210 4760 0000&zx2035 2035 2035 0000&lp2255 2255 2255 2450&zl2045 2045 2045 2450&ip0000&it0&fp0004 0004 0004 0000&th2450te2450dv00115 00115 00115 00000&ds0500&ss0100&rv000&ta000&ba0000&lm0&zo005&ll1&lv1&de0500&mv00000&mc00&mp000&ms0300&wt00&gi000&gj0gk0zu0100 0100 0100 0000&dj00zr02592 02592 02592 00000&mh0000&po0100
> 11:12:40.897 8AF#8000#00: C0DSid0233er00/00
< 11:12:41.062 8AF#8000#00: C0TRid0234xp08000&yp4050 3782 3514 3246 2978 2710 2442 2175tp1970tz1870th2450te2450tm1&
> 11:12:49.157 8AF#8000#00: C0TRid0234er00/00
< 11:12:49.162 8AF#8000#00: C0RTid0235
> 11:12:49.213 8AF#8000#00: C0RTid0235er00/00rt0 0 0 0 0 0 0 0
< 11:12:50.020 8AF#8000#00: C0TPid0236xp01989 01989 01989 01989 01989 01989 01989 00000yp5298 5208 5118 5028 4938 4848 4758 0000tm1 1 1 1 1 1 1 0tt23tp2244tz2164th2450td1
> 11:12:57.148 8AF#8000#00: C0TPid0236er00/00
< 11:12:57.150 8AF#8000#00: C0RTid0237
> 11:12:57.208 8AF#8000#00: C0RTid0237er00/00rt1 1 1 1 1 1 1 0
< 11:12:57.386 8AF#8000#00: C0RTid0238
> 11:12:57.427 8AF#8000#00: C0RTid0238er00/00rt1 1 1 1 1 1 1 0
< 11:12:57.799 8AF#8000#00: C0ASid0239at2&tm1 1 1 1 1 1 1 0xp03200 03200 03200 03200 03200 03200 03290 00000yp1885 1795 1705 1615 1345 1255 1885 0000th2450te2450lp2156 2156 2156 2156 2156 2156 2156 2450ch000&zl1946 1946 1946 1946 1946 1946 1946 2450zx1896 1896 1896 1896 1896 1896 1896 0000ip0005&it0&fp0005 0005 0005 0005 0005 0005 0005 0000av00152 00152 00152 00152 00152 00152 00152 00000as0500&ta000&ba0050&oa000&lm1&ll1&lv1&ld00&de0020&wt10&mv00000&mc00&mp000&ms0300&gi000&gj0gk0zu0040 0040 0040 0040 0040 0040 0040 0000zr06451 06451 06451 06451 06451 06451 06451 00000mh0000&zo005&po0050
> 11:13:15.746 8AF#8000#00: C0ASid0239er00/00
< 11:13:16.018 8AF#8000#00: C0DSid0240dm3 3 3 3 3 3 3 0tm1 1 1 1 1 1 1 0xp04555 04555 04555 04555 04555 04555 04645 00000yp5300 5210 5120 5030 4760 4670 5300 0000zx2035 2035 2035 2035 2035 2035 2035 0000lp2255 2255 2255 2255 2255 2255 2255 2450zl2040 2040 2040 2040 2040 2040 2040 2450ip0000&it0&fp0000&th2450te2450dv00152 00152 00152 00152 00152 00152 00152 00000ds2500&ss2500&rv000&ta000&ba0050&lm0&zo005&ll1&lv1&de0020&mv00000&mc00&mp000&ms0300&wt10&gi000&gj0gk0zu0100 0100 0100 0100 0100 0100 0100 0000dj00zr02592 02592 02592 02592 02592 02592 02592 00000mh0000&po0050
> 11:13:31.156 8AF#8000#00: C0DSid0240er00/00
< 11:13:31.279 8AF#8000#00: C0TRid0241xp08000&yp4050 3782 3514 3246 2978 2710 2442 2175tp1970tz1870th2450te2450tm1&
> 11:13:39.271 8AF#8000#00: C0TRid0241er00/00
< 11:13:39.276 8AF#8000#00: C0RTid0242
> 11:13:39.316 8AF#8000#00: C0RTid0242er00/00rt0 0 0 0 0 0 0 0
< 11:13:40.331 8AF#8000#00: C0TPid0243xp01989 02079 00000&yp4668 5298 0000&tm1 1 0&tt23tp2244tz2164th2450td1
> 11:13:52.780 8AF#8000#00: C0TPid0243er00/00
< 11:13:52.782 8AF#8000#00: C0RTid0244
> 11:13:52.827 8AF#8000#00: C0RTid0244er00/00rt1 1 0 0 0 0 0 0
< 11:13:52.890 8AF#8000#00: C0RTid0245
> 11:13:52.930 8AF#8000#00: C0RTid0245er00/00rt1 1 0 0 0 0 0 0
< 11:13:53.205 8AF#8000#00: C0ASid0246at0&tm1 1 0&xp05837 05837 00000&yp2447 2357 0000&th2450te2450lp2252 2252 2450&ch000&zl1712 1712 2450&zx1662 1662 0000&ip0020&it0&fp0001 0001 0000&av00107 00107 00000&as0500&ta000&ba0000&oa020&lm1&ll3&lv1&ld00&de0020&wt10&mv00000&mc00&mp000&ms0300&gi000&gj0gk0zu0067 0067 0000&zr00000&mh0000&zo005&po0050
> 11:14:03.190 8AF#8000#00: C0ASid0246er00/00
< 11:14:03.381 8AF#8000#00: C0DSid0247dm3 3 0&tm1 1 0&xp05837 05837 00000&yp2447 2357 0000&zx1662 1662 0000&lp2252 2252 2450&zl1662 1662 2450&ip0020&it0&fp0001 0001 0000&th2450te2450dv00107 00107 00000&ds0500&ss0100&rv000&ta000&ba0000&lm1&zo005&ll3&lv1&de0020&mv00000&mc00&mp000&ms0300&wt00&gi000&gj0gk0zu0067 0067 0000&dj00zr00000&mh0000&po0050
> 11:14:09.698 8AF#8000#00: C0DSid0247er00/00
< 11:14:10.011 8AF#8000#00: C0ASid0248at0&tm1 1 0&xp05837 05837 00000&yp2447 2357 0000&th2450te2450lp2252 2252 2450&ch000&zl1712 1712 2450&zx1662 1662 0000&ip0020&it0&fp0001 0001 0000&av00061 00080 00000&as0500&ta000&ba0000&oa020&lm1&ll3&lv1&ld00&de0020&wt10&mv00000&mc00&mp000&ms0300&gi000&gj0gk0zu0067 0067 0000&zr00000&mh0000&zo005&po0050
> 11:14:17.800 8AF#8000#00: C0ASid0248er00/00
< 11:14:17.973 8AF#8000#00: C0DSid0249dm3 3 0&tm1 1 0&xp04555 04555 00000&yp4940 4850 0000&zx2035 2035 0000&lp2255 2255 2450&zl2045 2045 2450&ip0000&it0&fp0000&th2450te2450dv00061 00080 00000&ds0500&ss0100&rv000&ta000&ba0000&lm0&zo005&ll1&lv1&de0020&mv00000&mc00&mp000&ms0300&wt00&gi000&gj0gk0zu0100 0100 0000&dj00zr02592 02592 00000&mh0000&po0100
> 11:14:26.419 8AF#8000#00: C0DSid0249er00/00
< 11:14:26.550 8AF#8000#00: C0TRid0250xp08000&yp4050 3782 3514 3246 2978 2710 2442 2175tp1970tz1870th2450te2450tm1&
> 11:14:34.228 8AF#8000#00: C0TRid0250er00/00
< 11:14:34.233 8AF#8000#00: C0RTid0251
> 11:14:34.276 8AF#8000#00: C0RTid0251er00/00rt0 0 0 0 0 0 0 0
< 11:14:34.366 8AF#8000#00: C0TPid0252xp02079 02079 00000&yp5208 5118 0000&tm1 1 0&tt23tp2244tz2164th2450td1
> 11:14:40.956 8AF#8000#00: C0TPid0252er00/00
< 11:14:40.958 8AF#8000#00: C0RTid0253
> 11:14:41.000 8AF#8000#00: C0RTid0253er00/00rt1 1 0 0 0 0 0 0
< 11:14:41.065 8AF#8000#00: C0RTid0254
> 11:14:41.106 8AF#8000#00: C0RTid0254er00/00rt1 1 0 0 0 0 0 0
< 11:14:41.397 8AF#8000#00: C0ASid0255at2&tm1 1 0&xp03200 03200 00000&yp1525 1435 0000&th2450te2450lp2156 2156 2450&ch000&zl1946 1946 2450&zx1896 1896 0000&ip0005&it0&fp0003 0002 0000&av00097 00078 00000&as0500&ta000&ba0050&oa000&lm1&ll1&lv1&ld00&de0020&wt10&mv00000&mc00&mp000&ms0300&gi000&gj0gk0zu0040 0040 0000&zr06451 06451 00000&mh0000&zo005&po0050
> 11:14:50.774 8AF#8000#00: C0ASid0255er99/00 P106/70
< 11:14:50.784 8AF#8000#00: C0RLid0256
> 11:14:50.848 8AF#8000#00: C0RLid0256er00/00lh+0000 +1919 +1916 +1917 +1921 +1920 +1922 +1825
< 11:14:50.867 8AF#8000#00: C0ASid0257at2&tm1 0&xp03200 03200 00000&yp1525 1435 0000&th2450te2450lp2156 2156 2450&ch000&zl1896 1946 2450&zx1896 1896 0000&ip0000 0005&it0&fp0000 0002 0000&av00097 00078 00000&as0500&ta000&ba0000 0050&oa000&lm0 1&ll1&lv1&ld00&de0020&wt10&mv00000&mc00&mp000&ms0300&gi000&gj0gk0zu0040 0040 0000&zr06451 06451 00000&mh0000&zo005&po0050
> 11:14:57.171 8AF#8000#00: C0ASid0257er00/00
< 11:14:57.371 8AF#8000#00: C0DSid0258dm3 3 0&tm1 1 0&xp04555 04555 00000&yp4940 4850 0000&zx2035 2035 0000&lp2255 2255 2450&zl2045 2045 2450&ip0000&it0&fp0000&th2450te2450dv00097 00078 00000&ds2500&ss2500&rv000&ta000&ba0050&lm0&zo005&ll1&lv1&de0020&mv00000&mc00&mp000&ms0300&wt10&gi000&gj0gk0zu0100 0100 0000&dj00zr02592 02592 00000&mh0000&po0050

Joe4 · Apr 21, 2020

OK, I think the way you are importing the files, by doing a query and then linking to it, makes Excel automatically put them in different columns. If we import the data into a new file, and then copy it to column A, we can do what you want.

I also cleaned up a little bit of your code, eliminating some lines and streamlining it a bit more.
See how this works for you:

VBA Code:

Sub ErrorFinder()

Dim wb1 As Workbook
Dim wb2 As Workbook
Dim c As Range
Dim j As Integer
Dim Source As Worksheet
Dim Target As Worksheet
Dim wb As Workbook
Dim wbCSV As Workbook
Dim myPath As String
Dim myFile As Variant
Dim fileType As String
Dim lr As Long
    
Application.ScreenUpdating = False
    
'Capture active workbook
Set wb1 = ActiveWorkbook
    
'Change worksheet designations as needed
Set Source = ActiveWorkbook.Worksheets("Input")
Set Target = ActiveWorkbook.Worksheets("Results")

'Clear Previous trace Data
Sheets("Input").Activate
Sheets("Input").Range("A2:A" & Cells(Rows.Count, "A").End(xlUp).Row).Clear

'Get Target Folder Path From User
With Application.FileDialog(msoFileDialogFolderPicker)
        .Title = "Select Source Folder"
        .AllowMultiSelect = False
        .Show
        myPath = .SelectedItems(1) & "\"
End With

'Specify file type
fileType = "*.trc*"

'Target Path with file type
myFile = Dir(myPath & fileType)


'Loop through each Trace file in specified folder
  Do While myFile <> ""
    
'   Open text file in new workbook
    Workbooks.OpenText Filename:=myPath & myFile, Origin:=437 _
        , StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
        ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=False _
        , Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1)), _
        TrailingMinusNumbers:=True
    
'   Capture workbook
    Set wb2 = ActiveWorkbook
    
'   Copy data from new workbook to original workbook
    Range("A1").CurrentRegion.Copy
    wb1.Activate
    Sheets("input").Activate
    Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).Select
    ActiveSheet.Paste
    
'   Close new workbook
    wb2.Close False
    
'   Get next file
    myFile = Dir
    
  Loop

' Find last row on Input sheet with data
  lr = Sheets("Input").Cells(Rows.Count, "A").End(xlUp).Row

    'Start transposing the errors
    
    'Clears Results from previous Trace
    Sheets("Results").Activate
    Sheets("Results").Range("A2:A" & Cells(Rows.Count, "A").End(xlUp).Row).Clear
    
    j = 2 ' Start copying to row 2 in target sheet
    For Each c In Source.Range("A2:A" & lr)
        If InStr(c, "er99") > 0 Then
           Source.Rows(c.Row).Copy Target.Rows(j)
           j = j + 1
        End If
    Next c
    
    
'Select Results Sheet
Sheets("Results").Select
    
'Find last row with data in column A
  lr = Cells(Rows.Count, "A").End(xlUp).Row
'Clear and insert Formula's
Range("B2:C" & Cells(Rows.Count, "B").End(xlUp).Row).ClearContents
Range("B2:B" & lr).FormulaR1C1 = _
    "=IFERROR(MID(RC[-1],FIND(""00 "",RC[-1])+3,FIND(""00 "",RC[-1])-FIND(""00"",RC[-1])-1),"""")"
Range("C2:C" & lr).FormulaR1C1 = "=RC[-1]"
       
       
Application.ScreenUpdating = True

End Sub

Stevie787 · Apr 22, 2020

Thank you so much for this.

I understand what you mean, Excel can't import via copy and paste, you have too open separate workbooks, copy then paste the data 'manually' I should've thought about this before instead of trying to make one way work.

Stay safe and thanks again.

Joe4 · Apr 22, 2020

You are welcome.

The way you were doing it before wasn't copy/paste, it was more like a "linking". And Excel apparently does not like to link two different files to the same column.

How to import txt files into one column

Stevie787

New Member

Excel Facts

Stevie787

New Member

Joe4

MrExcel MVP, Junior Admin

Stevie787

New Member

Attachments

Joe4

MrExcel MVP, Junior Admin

Stevie787

New Member

Joe4

MrExcel MVP, Junior Admin

Similar threads

Forum statistics

Share this page

How to import txt files into one column

Stevie787

New Member

Excel Facts

Stevie787

New Member

Joe4

MrExcel MVP, Junior Admin

Stevie787

New Member

Attachments

Joe4

MrExcel MVP, Junior Admin

Stevie787

New Member

Joe4

MrExcel MVP, Junior Admin

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock