Request for VBA code for importing multiple tab-delineated .txt files into Excel 2010

[gmackey]

Hello all,

Thank you for taking the time to review my request. I've been poking around the boards and Google trying to find a code that will work for what I'm trying to do. This forum has been very informative, but I'm not a programmer and I'm having trouble changing all the coding from the existing threads to fit my needs. Any help provided would be much appreciated, so thanks in advance to anyone willing to help out.

The Rub:
I have a system of equipment controlled by a master PLC unit that collects process data and stores in a tab delineated text file once per day. The data are recorded in 9 columns from various system components approximately every 5 seconds, providing data files with about 10,000 rows each. To stay in compliance with our permit, I need to import and process the data and submit a report once per month (31 data files). I have the data processing component nailed down, but could use some help on the import side.

Import Request:
The data are stored in rows with the date and time being the first column. I want to import 1 months worth of data from a single file folder. Ideally, the import function would either open a 'Select Files to Import' box, or just import all the files from a selected folder. Please include a provision in the code for the insertion point for the first data log (i.e. start import at A23). Each data file should be posted below the previous data file (i.e. continuing downwards, not horizontally) without column headers in a single (active) worksheet. The ideal result would be a continuous data log that starts at 00:00 am of the 1st file and continues through 23:59 pm of the last file in the folder.

Other info:
I'm using Excel 2010, so there should be no issues with capacity of rows. I posted a few rows of data from one of the files to show what it looks like.

Finally, I know I'm like a beggar with my hand out, so if someone knows a slice of code that almost fits and could direct me on modifying it (with the assumption that I'm a total programming newbie), I would be happy to try. I'm actually banking on the idea that people who read this post are totally altruistic and actually love to code for free. I hope I'm right!

Thanks in advance!

Graham

Data example:

[TABLE="width: 1412"]
<tbody>[TR]
[TD]Date
[/TD]
[TD]FE001_SVE_FLOW_SCL
[/TD]
[TD]PT001_SVE_PRESS_SCL
[/TD]
[TD]TE001_INLET_TEMP_SCL
[/TD]
[TD]TE002_DISCHARGE_TEMP_SCL
[/TD]
[TD]PT_003_T001_AVG
[/TD]
[TD]FIT002_UPI_DISCHARGE_SCL
[/TD]
[TD]SAMPLE_RECORD
[/TD]
[TD]SAMPLE_COUNTER
[/TD]
[/TR]
[TR]
[TD="align: right"]10/13/2016
[/TD]
[TD="align: right"]240
[/TD]
[TD="align: right"]0.001239796
[/TD]
[TD="align: right"]75.41008759
[/TD]
[TD="align: right"]73.07545471
[/TD]
[TD="align: right"]26
[/TD]
[TD="align: right"][/TD]
[TD="align: right"]2
[/TD]
[TD="align: right"]90
[/TD]
[/TR]
[TR]
[TD="align: right"]10/13/2016
[/TD]
[TD="align: right"]240
[/TD]
[TD="align: right"]0.001640345
[/TD]
[TD="align: right"]75.37957001
[/TD]
[TD="align: right"]73.04493713
[/TD]
[TD="align: right"]26
[/TD]
[TD="align: right"][/TD]
[TD="align: right"]2
[/TD]
[TD="align: right"]74
[/TD]
[/TR]
[TR]
[TD="align: right"]10/13/2016
[/TD]
[TD="align: right"]240
[/TD]
[TD="align: right"]0.001831083
[/TD]
[TD="align: right"]75.33379364
[/TD]
[TD="align: right"]72.99916077
[/TD]
[TD="align: right"]26
[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"]5
[/TD]
[/TR]
[TR]
[TD="align: right"]10/13/2016
[/TD]
[TD="align: right"]240
[/TD]
[TD="align: right"]0.009425455
[/TD]
[TD="align: right"]75.27275848
[/TD]
[TD="align: right"]72.92286682
[/TD]
[TD="align: right"]26
[/TD]
[TD="align: right"][/TD]
[TD="align: right"]3
[/TD]
[TD="align: right"]33
[/TD]
[/TR]
</tbody>[/TABLE]

 

[gmackey]

Bump

 

[gmackey]

Bump.

 

[John_w]

Try this as a starting point. I have hard-coded the folder path - change this to your actual folder. Alternatively, look at Application.FileDialog (msoFileDialogFolderPicker) to allow the user to browse and select a folder.

Public Sub Import_Files()
    
    Dim sourceFolder As String
    Dim fileName As String
    Dim destCell As Range
    Dim startRow As Long
       
    'Folder containing the .txt files
    
    sourceFolder = "C:\path\to\folder\"     'CHANGE THIS
    If Right(sourceFolder, 1) <> "\" Then sourceFolder = sourceFolder & "\"
    
    'Cell where import will start
    
    Set destCell = ActiveSheet.Range("A23")
    
    'Start import at row 1 in first .txt file to include column headings
    
    startRow = 1
    
    fileName = Dir(sourceFolder & "*.txt")
    
    While fileName <> ""
    
        With destcell.Worksheet
        
            'Import this file starting at destCell
            
            With .QueryTables.Add(Connection:="TEXT;" & sourceFolder & fileName, Destination:=destCell)
                .TextFileStartRow = startRow
                .TextFileParseType = xlDelimited
                .TextFileTabDelimiter = True
                .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1)
                .Refresh BackgroundQuery:=False
            End With
            .QueryTables(1).Delete
            
            'Update destination cell for next file
            
            Set destCell = .Cells(Rows.Count, "A").End(xlUp).Offset(1)
            
        End With
        
        'Start import at row 2 in subsequent .txt files to ignore column headings
        
        startRow = 2
        
        'Get next file
        
        fileName = Dir()
    Wend
       
End Sub

The file names returned by the Dir function are not in a defined order, so you might need to sort the data on column A after the import has finished - record a macro to do this and it can incorporated into the above code.

 

[gmackey]

This works pretty much perfectly. The only issue is that the import shifts the other existing columns to the right. I managed to record a macro to import onto a blank worksheet and then copy the data over to where I need it. Thanks so much for your help!