How can I import data from text files into Excel?

Question

I have multiple folders. There are multiple txt files inside these folder. I need to extract data (just a single value: value --->554) from a particular type of txt file in this folder.(individual_values.txt)

No  100    Value   555      level match   0.443        top level  0.443       bottom 4343

There will be many folders with same txt file names but diff value. Can all these values be copyed to excel one below the other.

I have to extract a value from a txt file which i mentioned above. Its a same text file with same name located inside different folders. All i want to do is extract this value from all the text file and paste it in excel or txt one below the other in each row.

Eg: The above is a text file here I have to get the value of 555 and similarly from other diff values.

555

666

666

776

Answer 1

Yes.

(you might want to clarify your question )

Answer 2

No.

just making sure you have a 50/50 chance of getting the right answer

(assuming it was a question answerable by Yes and No) hehehe

Answer 3

Your question isn't very clear, I imagine you want to know how this can be done.

You probably need to write a script that traverses the folders, reads the individual files, parses them for the value you want, and generates a Comma Separated Values (CSV) file. CSV files can easily be imported to Excel.

Answer 4

File_not_found

Gotta have all three binary states for the response.

Answer 5

There are two or three basic methods you can use to get stuff into a Excel Spreadsheet.

• You can use OLE wrappers to manipulate Excel.
• You can write the file in a binary form
• You can use Excel's import methods to take delimited text in as a spreadsheet.

I chose the latter way, because 1) it is the simplest, and 2) your problem is so poorly stated as it does not require a more complex way. The solution below outputs a tab-delimited text file that Excel can easily support.

In Perl:

use IO::File;

my @field_names = split m|/|, 'No/Value/level match/top level/bottom';
#' # <-- catch runaway quote

my $input    = IO::File->new( '<data.txt' );
die 'Could not open data.txt for input!' unless $input;

my @data_rows;
while ( my $line = <$input> ) { 
    my %fields = $line =~ /(level match|top level|bottom|Value|No)\s+(\d+\S*)/g;
    push @data_rows, \%fields if exists $fields{Value};
}

$input->close();

my $tab_file = IO::File->new( '>data.tab' );
die 'Could not open data.tab for output!' unless $tab_file;

$tab_file->print( join( "\t", @field_names ), "\n" );
foreach my $data_ref ( @data ) { 
    $tab_file->print( join( "\t", @$data_ref{@field_names} ), "\n" );
}

$tab_file->close();

NOTE: Excel's text processing is really quite neat. Try opening the text below (replacing the \t with actual tabs) -- or even copying and pasting it:

1\t2\t3\t=SUM(A1:C1)

Answer 6

I chose c#, because i thought it would be fun to use a recursive lambda. This will create the csv file containing matches to the regex pattern.

    string root_path = @"c:\Temp\test";
    string match_filename = "test.txt";

    Func<string,string,StringBuilder, StringBuilder> getdata = null;

    getdata = (path,filename,content) => {
        Directory.GetFiles(path)
        .Where(f=>
            Path.GetFileName(f)
            .Equals(filename,StringComparison.OrdinalIgnoreCase))
        .Select(f=>File.ReadAllText(f))
        .Select(c=> Regex.Match(c, @"value[\s\t]*(\d+)",
            RegexOptions.IgnoreCase))
        .Where(m=>m.Success)
        .Select(m=>m.Groups[1].Value)
        .ToList()
        .ForEach(m=>content.AppendLine(m));
        Directory.GetDirectories(path)
            .ToList()
            .ForEach(d=>getdata(d,filename,content));
                return content;
    };
    File.WriteAllText(
        Path.Combine(root_path, "data.csv"),
        getdata(root_path, match_filename, new StringBuilder()).ToString());