Copy data from multiple txt files in one single Excel

[1Ronin]

Hello,

I'm new on this forum and I need a macro to help me to copy the values from multiple "txt" files into a single Excel file.
The details are:
- I have many "txt" files (could be hundreds or thousands)
- the files names are similar to: 1715900115406111-30062017111747.10007-tor (it's a txt file, despite the strange extension!)
- each txt file can have thousands of rows; number of rows can be different from file to file
- each row has 2 groups of values delimited by semicolon; an example: 0.010;0.885
- interested data are in the 2nd group
- all files are in the same folder
What I need:
- make/open a Master file (Excel)
- Master file can be in the same folder with txt files or not
- open first txt file
- convert txt -> columns
- copy txt file name to 1st row in Master; for example: A1 = 1715900115406111
- copy txt file date to 2nd row in Master; for example: A2 = 30.06.2017
- copy txt file hour to 3rd row in Master; for example: A3 = 11:17:47
- copy txt file station number to 4th row in Master; for example: A4 = 10007
- copy all data from txt file column "B" starting with row 5, below the station number; for example: A5:A14560
- do the same thing for all files in the next columns...

I have almost no knowledge about VB so in need a little help on this . I make same small macros, but nothing like this.
If I have 5-10 txt files I can do it manually, but for hundreds of files is not possible.

Please help me with a macro.
Thanks a lot.

 

[1Ronin]

Hi again,


I modify yesterday an macro, to copy the data horizontally (how it was original macro) and copy from your macro the formatting for date and time.
This horizontal macro is good when I have less than 16k values and can sort easily by time, day, station.
If I have more than 16k values in TXT file your macro (vertical copy) is recommended. In fact, there is one file between the test files with 28k values...
I let code below, maybe is helping somebody. Not so clean like yours...

Sub Extract()

'Define parameters
    Dim path As Variant
    Dim txtfile As Variant
    Dim x1 As Integer
    Dim y1 As Integer
   
    Dim name As String          'name of tor file
    Dim serial As String        'serial number of pump
    Dim day As String           'day of test
    Dim hour As String          'hour of test
    Dim station As String       'station number
    Dim wbthis As Workbook      'the tor file
       
    Dim z As String
    Dim r As String
      
'relates to the name of the excel sheet
    z = Range("B1")             
    
    
'Attention here, rellates to the locaiton of the tor files
    path = Range("C1")         
        
    txtfile = Dir(path & "*.*tor")
    x1 = 1
    y1 = 3


    Application.ScreenUpdating = False
   
   
'Select older extracted data a deletes them, to modfiy size if anything changes
    Windows("" & z & ".xlsm").Activate
    Sheets("Sheet1").Activate
    Range("A3:xfd300").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.ClearContents


      
'Program start
  
   
    Do While txtfile <> ""
    
    
'opens the files one by one untill all files are opened in the locaiton folder
    Workbooks.Open Filename:=path & txtfile
    txtfile = Dir
    Set wbthis = ActiveWorkbook
 
 
'extract the tor file name and other details
    name = ActiveWorkbook.name
    serial = Left(name, 16)
    da = Mid(name, 18, 8)
    day = Left(da, 2) & "." & Mid(da, 3, 2) & "." & Right(da, 4)
    hou = Mid(name, 26, 6)
    hour = Left(hou, 2) & ":" & Mid(hou, 3, 2) & ":" & Right(hou, 2)
    station = Mid(name, 33, 5)
   
   
'puts data into columns
    Range("A:A").Select
    Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, Semicolon:=True
        


'adds formula to find the interested part of the results
    Range("B1:B16000").Select
    Selection.Copy
    
        
'returning to excel sheet
    Windows("" & z & ".xlsm").Activate
    Sheets("Sheet1").Activate
    
    
'pasting data
    Cells(y1, x1) = name        'insert NAME of tor file
    x1 = x1 + 1
    Cells(y1, x1) = serial      'insert serial number
    x1 = x1 + 1
    Cells(y1, x1) = day         'insert day
    x1 = x1 + 1
    Cells(y1, x1) = hour        'insert hour
    x1 = x1 + 1
    Cells(y1, x1) = station     'insert station number
    x1 = x1 + 1
    Cells(y1, x1).Select        'insert tor values on each row
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=True




'closes tor file
    wbthis.Activate
    Application.CutCopyMode = False
    wbthis.Close False
    y1 = y1 + 1
    x1 = 1


        
    Loop
    
    
    MsgBox "Done!!!"
        
Exit Sub




        
End Sub

 

[Warship]

Hi Warship,


Works perfect now.
Many thanks for your support.

​

Best regards,

Was my pleasure sir!
Had me scratching my head for a second.
Glad we got you running.
Also thanks for dropping your code.
You never know what might help another.

My best...