Need help in converting multiple text files into excel worksheets in a workbook

[ahjiefreak]

Hi,

I have multiple text files (.txt) which is a table like input:-

Column1 Column2 Column3 Column4
1 5 10 15
5 10 20 40
..........

This is just one of the .txt files and I want to insert these txt files into separate worksheets in excel workbook. Any idea how to use bash script to automate this as i have thousands of these text files to transferred to. Please help! Been trying to do it for weeks yet cant find any solution.

Thanks alot.

[eggi]

Hey There,

If you have the .txt files, just process the data (unless it's already set up this way, in which case you're halfway done) so that the columns are delimited by a pipe or tab (the two delimiters Excel will process automatically).

Then just redirect that output into a file called: Whatever.xls

and open it with Excel and you should be all set. The columns may look ugly, but everything fits in it's right place. There's a simple trick involving selecting all the columns in an Excel spreadsheet that will cause them to all expand to the length of the greatest field, but I don't recall that one offhand.

So, if you had these separated by an unknown number of spaces, just do:

sed 's/ */|/' yourfile.txt >yourfile.xls

The sed command may interpret the space differently, so may have to modify the command. I have one machine where / */ splits words (like h|e|y) and I have to use + and another where it won't. Or you could use awk, or whatever works best for you.

Good luck :)

, Mike

[ahjiefreak]

Hi there,

I have hundreds of files to process in this case and the thing is i would like to combine a bunch of text files into different worksheet for a similar workbook.

Means workbook A contain spreadsheet A1, A2, A3...etc.

Pls advise. Thanks.
Hey There,

If you have the .txt files, just process the data (unless it's already set up this way, in which case you're halfway done) so that the columns are delimited by a pipe or tab (the two delimiters Excel will process automatically).

Then just redirect that output into a file called: Whatever.xls

and open it with Excel and you should be all set. The columns may look ugly, but everything fits in it's right place. There's a simple trick involving selecting all the columns in an Excel spreadsheet that will cause them to all expand to the length of the greatest field, but I don't recall that one offhand.

So, if you had these separated by an unknown number of spaces, just do:

sed 's/ */|/' yourfile.txt >yourfile.xls

The sed command may interpret the space differently, so may have to modify the command. I have one machine where / */ splits words (like h|e|y) and I have to use + and another where it won't. Or you could use awk, or whatever works best for you.

Good luck :)

, Mike

[eggi]

Hey,

I found this online. Looks like someone's done lots of work on this already. No sense in repeating it ;) It looks like it takes care of everything you need:

http://search.cpan.org/src/JMCNAMARA/Spreadsheet-WriteExcel-2.21/doc/tpj.html

Let me know how that works out for ya :)

, Mike