0

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,

a   1
b   2
c   3
e   4
f   5
g   6

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

Imported Tab-separated values file

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,

Imported results from generated TSV file

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

  • Have you ever "recorded" a macro? You need, if not already, to enable the Developer's tab in Excel. A google search will solved the tab on how to. I did it so long ago I can't recall how to enable that feature. If you already know how to record a macro, then it's a matter of instantiating a counting varible; use a counter with a recorded macro of importing a text file. Press record, do a single import exactly how you want it. Press stop. Your macro is now recorded. Alt + F11 to examine the code. Modify the code. Test. Troubleshoot. – ejbytes Aug 5 '16 at 1:38
  • Here's another hint: dim counter as integer, dim mystring as String, counter = 1, mystgring = "C:/file" & cstr(counter) & ".txt"... if counter < 100 "file" & "00" & cstr(counter) & ".txt". That hint should help you figure out a script to configure in your Macro. How to "for loop vba", "counter loop". – ejbytes Aug 5 '16 at 1:42
1

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.

  • Thanks. I've read the tutorial but I can't seem to change each file to a different column. To make it worse, the tutorial mentioned fnGetFileContents([Folder Path]&[Name]) in the custom column when it should be File.Contents([Folder Path]&[Name]). Spent some time solving that. I feel like I'm close to the solution though if I can just figure out having each file in its own column. – Khalid Hussain Aug 5 '16 at 14:47
  • Just follow the tutorial. It works if you follow the steps. take your time. – teylyn Aug 6 '16 at 10:41
0

First 00 will have to be renamed 0. You may have to remove other 0 offsets. For example 01,02,03 won't work, rename to 1,2,3.

from the command prompt go into the folder containing all your files.

FOR /L %i IN (0,1,297) DO type outfile%i.txt >>master.txt

This should combine all your files into master.txt.

So you want a tab key between files. In notepad, create a file that contains a single tab named tab.txt after you save it ensure it is 1 byte in size, and you did accidentally hit other characters.

FOR /L %i IN (0,1,297) DO type outfile%i.txt tab.txt>>master.txt

  • To eliminate the hassle of renaming all files: You could also use something like this: IF %i LSS 10 (type outfile00%i.txt) ELSE IF %i LSS 100 (type outfile0%i.txt) ELSE (type outfile%i.txt) – ejbytes Aug 5 '16 at 1:31
  • I think this solution would work if I could append the files after a tab character. From what I understand, it just piles one file after another so I end up with a really long master file. I've already had such a master file. – Khalid Hussain Aug 5 '16 at 14:49
  • @KhalidHussain updated my answer so you can have your TAB character. – cybernard Aug 5 '16 at 16:35
  • @cybernard, I've updated my answer to clarify where the TAB character should I occur. I hope I've understood you correctly. – Khalid Hussain Aug 7 '16 at 2:24
0

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

Did you know there's a command just for that?

paste outfile*.txt > out.txt

If you want to write your own algorithm, pick the (scripting) language of your choice.

  1. awk: awk -f awk.awk outfile*.txt > out.txt

    BEGIN {count = 1}
    FNR == 1 && FNR < NR {count++}
    
    {
      if ( _[FNR] ) {
        while ( c[FNR] != count-1 ) {
          _[FNR] = (_[FNR] " " sprintf("%8s",""))
          c[FNR]++
        }
        _[FNR] = (_[FNR] "\t" sprintf("%-8s",$0))
        c[FNR]++
      }
      else if ( count == 1 ) {
        _[FNR] = sprintf("%-8s",$0)
        c[FNR]++
      }
      else {
        while ( c[FNR] != count-1 ) {
          if ( _[FNR] )
            _[FNR] = (_[FNR] " " sprintf("%8s",""))
          else
            _[FNR] = sprintf("%8s","")
          c[FNR]++
        }
        _[FNR] = (_[FNR] "\t" sprintf("%-8s",$0))
        c[FNR]++
      }
    }
    
    END {
      for (i=1; i<=length(_); i++)
        print _[i]
    }
    
  2. perl: perl perl.pl > out.txt

    use strict;
    use warnings;
    
    my $i;
    my @rows;
    my @files = map { "outfile$_.txt" } 0 .. 297;
    
    foreach my $file (@files) {
        open my $infh, '<', $file or die $!;
        $i=0;
        while (<$infh>) {
            chomp;
            my $data = $_;
            if (length $rows[$i]) {
                $rows[$i++] .= "\t" . $data;
            } else {
                $rows[$i++] = $data;
            }
        }
    }
    
    foreach my $row (@rows) {
        print "$row\n";
    }
    

P.S. In case you're wondering, bash, awk and perl are available on several platforms, including Windows (cygwin being my recommendation there).

Update: Here's the input files I used, together with the output of paste outfile*.txt > out.txt on Git for Windows.

==> outfile00.txt <==
KP=0.50 ,Ki=0.10 ,Kd=0.05
0.00
0.00
0.00
0.00

==> outfile01.txt <==
KP=1.50 ,Ki=1.10 ,Kd=1.05
1.00
1.00
1.00
1.00

==> out.txt <==
KP=0.50 ,Ki=0.10 ,Kd=0.05       KP=1.50 ,Ki=1.10 ,Kd=1.05
0.00    1.00
0.00    1.00
0.00    1.00
0.00    1.00
  • Interesting, although it doesn't produce the result I want. I've even tried with the --serial flag. Are you getting different results? I'm using the paste that came with Git Bash for Windows. – Khalid Hussain Jul 28 '17 at 19:32
  • Could be due to the large number of files? Try first with just a few. There are three solutions posted here, and all have been tested with the sample data you provided. I suggest you enrich the what I tried section in your question showing the various outputs so we can all see where you got stuck. Same for ejbyte's and teylyn's answers. – simlev Jul 29 '17 at 21:35

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.