Member Avatar for donnyv

Hi,

I want to read the data from a worksheet in an excel workbook, write it to a new worksheet and save that as a dbf or csv file (one worksheet)...basically extracting worksheets from a workbook and saving them individually, renaming them as the title of the worksheet with the title of the workbook (either one first).

What I have tried to do (I don't have the code on me right now)
is read each column in the worksheet and write it to a new file, but I get all of the 5 columns from the original file written to the A column of the new file. How can I start each column at the top of the appropriate column, or is there a better way to do it?

I can post the code later if need be...Thanks.

  • 3 Contributors
  • 2 Replies
  • 255 Views
  • 19 Hours Discussion Span
  • Latest Post Latest Post by J-M DESMETTRE

Dani AI

Generated

Short summary and a practical, modern approach (builds on ’s goal and the pointers from and ). Reading an Excel workbook, exporting each sheet as a separate file, and naming files from the workbook + sheet is easiest with pandas for CSV output; if you must produce legacy .dbf files, use a DBF-focused writer and explicitly map column types/lengths because DBF has fixed-type/width constraints. The examples below show a minimal, reliable workflow and a few common gotchas.

Read all sheets and write CSVs (keeps column layout intact and avoids the “everything ended up in column A” symptom that happens when data are written as single strings or with the wrong writer options):

import pandas as pd
from pathlib import Path

src = Path("workbook.xlsx")
sheets = pd.read_excel(src, sheet_name=None)   # returns {sheet_name: DataFrame}

out = Path("out")
out.mkdir(exist_ok=True)

for sheet_name, df in sheets.items():
    safe = "".join(c if c.isalnum() or c in "-_ " else "_" for c in sheet_name).strip()
    fname = f"{src.stem}_{safe}.csv"
    df.to_csv(out / fname, index=False)         # index=False avoids extra index column

See pandas for read/write details. (pandas.pydata.org)

When you need DBF output: DBF requires you to declare field names, types and widths up front (and many DBF variants restrict field names to 10 chars), so build a field-spec string from your DataFrame and write rows with a DBF writer such as the maintained dbf package. Example sketch (trim/uppercase names, map dtypes, cap character widths, then append rows):

import dbf
# build spec like "NAME C(20); AGE N(3,0); BDAY D"
table = dbf.Table("out.dbf", spec_string)
table.open(dbf.READ_WRITE)
for row in df.itertuples(index=False, name=None):
    table.append(row)
table.close()

Use a current DBF library and remember DBF limits (field name length, fixed widths, numeric precision)—plan for truncation or type conversion. (github.com)

Notes and troubleshooting: if reading .xlsx files, prefer engines that support modern Excel (pandas/openpyxl) — xlrd no longer supports .xlsx files as of its 2.x change. Always inspect the plain CSV in a text editor to confirm delimiters/quotes, and sanitize sheet/filename characters. For DBF work, test a small subset first so you can tune field widths and numeric precision before processing the whole workbook. (xlrd.readthedocs.io)

Recommended Answers

All 2 Replies

Member Avatar for J-M DESMETTRE

For the .dbf, take a look at the following link :

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.