How to read excel file in a specific text file format?

Hi Everyone, I need help in converting one of my excel file into text file, but in a specific format.

Example:
The header of the Excel File is below:

Cell A = H Cell B = ABC Cell C = 7/14/2021 Cell D = V1
Cell E = ABC@GMAIL.COM

The result should be:
HABC - 20 SPACES - 7/14/2021 - 16 SPACES - V1 - 48 SPACES - ABC@GMAIL.COM - Rest all spaces - The length of the row must add up to 1221

Appreciate all your help.

Thanks,
Tani

Hi Everyone, I need help in converting one of my excel file into text
file, but in a specific format.

Example:
The header of the Excel File is below:

Cell A = H Cell B = ABC Cell C = 7/14/2021 Cell D = V1
Cell E = ABC@GMAIL.COM

I would use the openpyxl library to read the excel file: openpyxl · PyPI

Note that the library breaks Excel spreadsheets into workbooks, each
containing a single spreadsheet. You basic excel file has just the one
workbook numbered 0.

The result should be:
HABC - 20 SPACES - 7/14/2021 - 16 SPACES - V1 - 48 SPACES -
ABC@GMAIL.COM - Rest all spaces - The length of the row must add up to
1221

This is a string formatting problem.

I’d do this in 2 stages:

  • 0: install openpyxl with “python3 -m pip install --user openpyxl”
  • 1: read the excel file using openpyxl and print the values from the
    rows so ensure you’re reading them correctly - just use the print()
    function
  • 2: write a function fmt_row(row_data) which accepts the row data and
    formats it in a single long string as you describe and returns that
    string
  • 3: profit! print the returned string

To lay out the values the easiest way of probably using a format string,
for example:

x = 3
y = 4
s = f"{x:4},{y:6}"

The f-string above is formally spercified here:
2. Lexical analysis — Python 3.9.6 documentation and
that page has some examples lower down. The PEP for this is PEP 498
here: PEP 498 -- Literal String Interpolation | Python.org which also has examples.

Cheers,
Cameron Simpson cs@cskk.id.au

1 Like