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
  • 280 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by eggi

Dani AI

Generated

Short answer: script it. For thousands of similar .txt files the most reliable, repeatable solution is a small script that (1) normalizes the delimiter/encoding, (2) parses each file into a table, (3) sanitizes the filename to a valid Excel sheet name, and (4) writes each table to a separate worksheet in a single .xlsx workbook. As noted, making the files import-friendly first will save headaches; needs one sheet per file, so the per-file loop pattern below fits well.

A compact Python workflow (pandas + an Excel engine) is simple and robust:

import pandas as pd
from pathlib import Path
import re

writer = pd.ExcelWriter('combined.xlsx', engine='openpyxl')
for f in Path('txt_files').glob('*.txt'):
    df = pd.read_csv(f, sep=r'\s+', engine='python', header=0)   # adjust sep/header to your files
    sheet = re.sub(r'[:\\/?*\[\]]', '_', f.stem)[:31]            # sanitize and trim to 31 chars
    df.to_excel(writer, sheet_name=sheet, index=False)
writer.save()

Troubleshooting / practical notes:

  • If delimiters vary, use sep=r'\s+' or detect with a small sniff step; if files lack headers use header=None and supply names=[...]. Watch file encoding (UTF-8 vs Latin-1) and normalize it first if necessary.
  • Excel sheet rules: names max 31 characters and cannot contain : \ / ? * [ ] ; ensure uniqueness by appending a counter if needed.
  • Performance: a workbook with thousands of sheets can be slow and large. Consider grouping files into multiple workbooks, or export a single worksheet with a "source_filename" column if downstream analysis allows that.
  • Prefer .xlsx (modern limits and better libraries) rather than old .xls (65,536 row limit).

Recommended Answers

All 3 Replies

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.