Member Avatar for 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.

  • 2 Contributors
  • 3 Replies
  • 273 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by eggi
Member Avatar for 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

Member Avatar for 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

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.