Import set of text files to a set of columns

microsoft excelmicrosoft-excel-2013text editing

I have a set of text files named outfile00.txt until outfile297.txt. Each file has a number (decimal or otherwise) on every line except the first one (which is like a header).

I need to import the contents of each file to a column starting from column A (which will correspond to outfile00.txt) until all text files have been imported.

Here is a sample of the beginning of outfile00.txt:

KP=0.50 ,Ki=0.10 ,Kd=0.05
0.00
0.00
0.00
0.00

The actual file has about 1500 lines. I've tried manually importing the text file using the Data tab; Excel has no issues and the text is imported.

How can I import all the text files as a batch?

EDIT

I believe I had not defined my problem clearly, so here are some extra details.

With regards to cybernard's method,

If I create a tab-separated values file (the tab character is between the letter and number on each line) as follows,

I can import the text file directly and get the following,

I was hoping to create a file similar to the aforementioned TSV file. An example would be as follows,

KP=0.50 ,Ki=0.10 ,Kd=0.05   KP=0.50 ,Ki=0.10 ,Kd=0.10   KP=0.50 ,Ki=0.10 ,Kd=0.15
0.00    117.00  123.00
0.00    118.00  124.00
0.00    119.00  125.00
0.00    120.00  126.00

which produces the following import results,

I need to know how I can generate such a TSV file from my source files.

Best Answer

You can use Power Query to combine all files in a folder. Power Query is a free add-in from Microsoft for Excel 2010 and 2013 and is built into Excel 2016 as Get and Transform.

Ken Puls has a detailed tutorial here.

Once the query is set up, files can be added or removed from the folder and the query can be refreshed with the click of a button.

Related Solutions

Excel – How to set Excel to always import all columns of CSV files as Text

This works:

Sub OpenCsvAsText(ByVal strFilepath As String)

    Dim intFileNo As Integer
    Dim iCol As Long
    Dim nCol As Long
    Dim strLine As String
    Dim varColumnFormat As Variant
    Dim varTemp As Variant

    '// Read first line of file to figure out how many columns there are
    intFileNo = FreeFile()
    Open strFilepath For Input As #intFileNo
    Line Input #intFileNo, strLine
    Close #intFileNo
    varTemp = Split(strLine, ",")
    nCol = UBound(varTemp) + 1

    '// Prepare description of column format
    ReDim varColumnFormat(0 To nCol - 1)
    For iCol = 1 To nCol
        varColumnFormat(iCol - 1) = Array(iCol, xlTextFormat)
        ' What's this? See VBA help for OpenText method (FieldInfo argument).
    Next iCol

    '// Open the file using the specified column formats
    Workbooks.OpenText _
            Filename:=strFilepath, _
            DataType:=xlDelimited, _
            ConsecutiveDelimiter:=False, Comma:=True, _
            FieldInfo:=varColumnFormat

End Sub

Usage:

OpenCsvAsText "C:\MyDir\MyFile.txt"

Comma-separated file is now open as Excel sheet with all columns formatted as text.

Note that @Wetmelon's wizard solution works just fine, but if you're opening many files then you may, like me, grow weary of, each time, scrolling to column 60 in order to Shift-Click it.

EDIT @GSerg states in the comment below that this "doesn't work" and "eats spaces and leading zeroes". I'll just quote the comment to the question, which is more descriptive:

For reasons unknown, even if you explicitly provide formats for all columns in VBA, Excel will ignore it if the file extension is CSV. As soon as you change the extension, that same code will yield the correct results.

So the code above "works", but gets killed by this ridiculous Excel behaviour. Whichever way you cut it, you're stuck having to change the extension to something other than ".csv", sorry! After that, you're home free.

Excel – Numbers in scientific notation imported to Excel 2016 as text

Ok. I replicated your problem by adding two spaces and a tab character in front of the last number in the text file. It did what you described.

To solve the problem, I then opened up the file as a delimited file, with space and tab delimiters checked, and checked the box that states "treat consecutive delimiters as one". The last number was now imported as scientific notation and I was able to do calculations from it.

Related Question

Excel – How to set the default data type to “text” while importing data from a text file in Excel