Need to make multiple files from a single excel file

Answer 1

You can use Spreadsheet::ParseExcel to read a spreadsheet. Unfortunately that is all I can help you with because, frankly, the description of your problem makes no sense.

Answer 2

You could use Visual Basic for Applications to loop over the cells and then save to a text file.

OR

Save the file as a comma separated value file and use perl or python to easily parse the lines. (split on the comma for columns, end of line character for rows)

Answer 3

In python, you can use xlrd to read an Excel spreadsheet into data you can work with. See the README for sample usage. You can then use xlwt to create new spreadsheets.

Answer 4

in Excel, save your file as CSV.

in Python, use the CSV reader module to read it (read the python docs, search for csv)

now you say you have rows of maybe 20 columns and you want to put columns 1..10 in file A and columns 11..20 in file B, yes ?

open 2 csv writers (let's call them A and B)

you will read rows :

for row in csvreader: A.writerow( row[:10 ] ) B.writerow( row[11: ] )

that's it.

go here : http://stackoverflow.com/questions/1223967/how-can-i-merge-fields-in-a-csv-string-using-python

Answer 5

I think the xlrd and xlwt modules are the way to go in Python.

# Read the first 5 rows and columns of an excel file
import xlrd # Import the package
book = xlrd.open_workbook("sample.xls") # Open an .xls file
sheet = book.sheet_by_index(0) # Get the first sheet
for row in range(5): # Loop for five times (five rows)
    # grab the current row
    rowValues = sheet.row_values(row, start_col=0, end_colx=4)
    # Do magic here, like printing
    import xlrd # Import the package
    print "%-10s | %-10s | %-10s | %-10s | %-10s" % tuple(rowValues)

Now if you feel like writing back Excel files...

import xlwt # Import the package
wbook = xlwt.Workbook() # Create a new workbook
sheet = wbook.add_sheet("Sample Sheet") # Create a sheet
data = "Sample data" # Something to write into the sheet
for rowx in range(5):
    # Loop through the first five rows
    for colx in range(5):
        # Loop through the first five columns
        # Write the data to rox, column
        sheet.write(rowx, colx, data)
# Save our workbook on the harddrive
wbook.save("myFile.xls")

I have used this method in the part extensively to read/write data from Excel files for Input/Output models to use in NetworkX. The above examples are from my blog entries talking about that adventure.

As I am a new user, I can only post one link. Maybe you can Google xlwt? :)

Answer 6

As others have commented your question is almost totally incomprehensible. Based on the difficulty you have describing your issue, you might want to take a look at this post.

Some here have suggested saving your file as a CSV. Saving your file as a CSV file will greatly simplify the job of parsing it, but it will make converting to and from excel format a manual process. This may be acceptable if you have a small number of files to process. If you have hundreds, it won't work so well.

The Spreadsheet::ParseExcel and Spreadsheet::WriteExcel modules will help your read and write your spreadsheet file in native format.

The Text::CSV_XS module provides a powerful, fast CSV parser for perl.

Answer 7

Use Python and xlrd & xlwt. See http://www.python-excel.org

The following script should do what you want:

import xlrd, xlwt, sys

def raj_split(in_path, out_stem):
    in_book = xlrd.open_workbook(in_path)
    in_sheet = in_book.sheet_by_index(0)
    first_row = in_sheet.row_values(0)
    # find the rightmost 1 value in the first row
    split_pos = max(
        colx for colx, value in enumerate(first_row) if value == 1.0
        ) + 1
    out_book = xlwt.Workbook()
    out_sheet = out_book.add_sheet("Sheet1", cell_overwrite_ok=True)
    # copy the common cells
    for rowx in xrange(in_sheet.nrows):
        row_vals = in_sheet.row_values(rowx, end_colx=split_pos)
        for colx in xrange(split_pos):
            out_sheet.write(rowx, colx, row_vals[colx])
    out_num = 0
    # for each output file ...
    for out_col in range(split_pos, in_sheet.ncols):
        out_num += 1
        # ... overwrite the `split_pos` column
        for rowx, value in enumerate(in_sheet.col_values(colx=out_col)):
            out_sheet.write(rowx, split_pos, value)
        # ... and save the file.
        out_book.save("%s_%03d.xls" % (out_stem, out_num))

raj_split(*sys.argv[1:3])

Answer 8

thanks for all your reply..

John Machin ----> when i try to run this script...it gives an error for path, i specify the exact path for excel file and the stem . But the path is not accepted.

Answer 9

Actually, this is exactly what xlutils' filter functionality was designed for.

See the 'xutils.filter' section in the tutorial on http://www.python-excel.org

There's also more here:

https://secure.simplistix.co.uk/svn/xlutils/trunk/xlutils/docs/filter.txt

cheers,

Chris