UCLAdan06 has asked for the wisdom of the Perl Monks concerning the following question:

Perl Monks, I have only recently made the journey to Tibet and am new to this. My goal is to read numerical data from a .txt file into an excel workbook. When there are spaces in the .txt, the next value should go into the next data box on the workbook. I. e. 123 456 789 987 654 321: 123, 456, and 789 would be put in separate boxes horizontally- 987, 654, and 321 would be put in the next row horizontally. 

my $excel_input_file= 'c:\begperl\data.new.txt';
my $workbook = Excel::Writer::XLSX->new('c:\begperl\data.xlsx');
my $worksheet = $workbook->add_worksheet();
my $worksheet2 = $workbook->add_worksheet();

open( my $in2, '<', $excel_input_file) or die "Can't open $excel_input
+_file: $!";
open (my $out2, '>', $workbook) or die "Could not open file '$workbook
+' $!";

#put stuff that reads txt file numerical data into excel
# each value separated by space goes in next column
#each new line from txt file goes in next row



#close up shop
close $in;
close $out;

[download]

Replies are listed 'Best First'.
Re: .TXT to Excel
by GotToBTru (Prior) on Sep 20, 2016 at 20:14 UTC

    What provides the line break, ie, why is 987 on the next line and not just in the next column?

    It is pretty easy to find examples of how to read data out of files. You will likely use chomp and split.

    Use Super Search here to find examples of how to use Excel::Writer.

    But God demonstrates His own love toward us, in that while we were yet sinners, Christ died for us. Romans 5:8 (NASB)
[reply]
Re: .TXT to Excel
by GrandFather (Saint) on Sep 20, 2016 at 21:09 UTC

    Why are you doing this? The assistance you need is different depending on the motive for your task:

    1. This is homework: you need hints so you can figure out how to do it for yourself
    2. You are learning and this is a self assigned exercise: as above, but sample code and some discussion are indicated
    3. You have a real world task: sample code using "production" techniques, probably including use of modules, are indicated, but tutorial type discussion is not needed
    4. You have a real world problem to solve and are using it to learn Perl and programming fundamentals: as above except some discussion of the code is appropriate.

    So, why are you doing this?

    Premature optimization is the root of all job security
[reply]

      Grandfather, this is a real world problem that I have been instructed to use Perl for. I have a large model that will accept data from excel files and my current data is in .txt files. I have rows and columns of data in a .txt file that need to be put into an excel spreadsheet- each item in its own box. In the txt file, there are spaces between the columns of data, and I need a Perl mechanism for reading these spaces and assigning a new cell for each number.

[reply]

        This sounds exactly like what csv2xls already does.

[reply]
Re: .TXT to Excel
by Laurent_R (Canon) on Sep 20, 2016 at 22:01 UTC
    Your statement of the problem is incomplete, as mentioned already by GotToBTru: we don't know how to figure out that you need to go to the next row. You mention new line in the pseudo-code, but there is no new line in your data sample (or perhaps you should read Markup in the Monastery, data samples examples should have the same tags as code).

    Having said that, any good introduction to or tutorial for Perl should tell you how to split a space-separated line into an array. I hope this clue helps you for the start.

[reply]
Re: .TXT to Excel
by AnomalousMonk (Archbishop) on Sep 20, 2016 at 20:02 UTC
    ... I have only recently made the journey to Tibet ...

    I sure hope you're not working on the problem of The Nine Billion Names of God!

    Give a man a fish:  <%-{-{-{-<

[reply]
[d/l]
Re: .TXT to Excel
by RonW (Parson) on Sep 22, 2016 at 19:08 UTC
    Can you show us several rows of sample input data and a "mock up" of the expected spreadsheet?
[reply]

Back to Seekers of Perl Wisdom