Need to make multiple files from a single excel file

Question

I have a excel file. With many columns . I need to make multiple files using this

Eg: 0 0 0 0 0 1 1 1 1 1 2 2 2 2 2 2 2 2 2 2 2 2. So these are the excel columns with each having many rows. I need one file which would contain 0 0 0 0 0 1 1 1 1 1 2 then second will contain only the second no 0 0 0 0 0 1 1 1 1 1 2....similarly the others.

Thank you all for ur reply. To simplify the question:

1. I have a excel file as i mentioned with number of columns and rows. The columns are named as

alt text http://img44.imageshack.us/img44/3397/84200961244pm.png

Now I need to split this file into many excel files the 1st will have

A to O columns with all rows. The second will have A to N + P(this will not have columns O) and similarly the other 2. There will be like many columns with 2 and i will have to make a file containg all the columns containing O and 1 and each 2 at a time. i.e 1st 2 then the 2nd 2 and so on.

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

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 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 : How can I merge fields in a CSV string using Python?

Answer 5

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 6

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 7

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)