Creating excel masterfile

[BBMcL]

Hi, advanced thanks for any help...

I'm relatively new to programming (two weeks or so), so forgive me if I make some terminological errors here.

I'm using OS X and have a collection of excel files with a lot of data on them. I want to take one particular column out of a particular excel file, and put it into a new excel file. How can I do this?

[bvdet]

Module xlrd is a cross platform tool for reading Excel files. Example:

import xlrd
wb = xlrd.open_workbook('my_spreadsheet.xls')
 
sheetList = wb.sheet_names()
 
sheet1 = wb.sheet_by_index(6)
sheet2 = wb.sheet_by_name(u'Embeds')
 
for rownum in range(sheet1.nrows):
    print sheet1.row_values(rownum)
 
sixth_column = sheet1.col_values(5)
 
cell_A1 = sheet1.cell(0,0).value
cell_C4 = sheet1.cell(rowx=3,colx=2).value

Read the column of data that you want and create a csv file. If you want the data in an Excel column, you will need to put each data item on a separate line. Examples:

# add column data on one row
f =open('sixth_column.csv', 'w')
f.write(",".join(sixth_column))
f.close()
 
# add column data in column
f = open('sixth_column1.csv', 'w')
f.write("\n".join(sixth_column))
f.close()

The CSV files can be opened in Excel.

To add another column to an existing CSV file (simplified version):

fList = [item.strip() for item in open('sixth_column1.csv', 'r').readlines()]
f = open('sixth_seventh_columns.csv', 'w')
f.write('\n'.join([','.join([item1, item2]) for item1, item2 in zip(fList, sheet1.col_values(6))]))
f.close()

To add another column as a row to an existing file:

f = open('sixth_column.csv', 'a')
f.write("\n"+",".join(sheet1.col_values(6)))
f.close()

[Glenton]

You could also do this with VBA. Although I have a nasty feeling that excel for OS X doesn't have VBA. It's been a while since I played with excel, and I don't have it anymore, but it's not very hard to do.

On the other hand it looks like bvdet has shown you a pretty easy way! Nice one bvdet! Very cool

[BBMcL]

This was very helpful, so thank you very much.

After playing around with this for quite a while today, I think I'm well on my way to figuring out the specific task I have.

I can extract a particular column from an excel .xls file just fine. Let's say I extract it from extract_file.xls. I then use the command to join that column with another file - say, 'other_file.csv' - and that seems to work. But when I try to open other_file.csv in Excel, it does not have the column I thought I had added.

Here's what I've been doing specifically:

import xlrd
Path = '/users/myname/Desktop/'
File = 'extract_file.xls'
wb = xlrd.open_workbook('/users/myname/Desktop/'extract_file.xls')
sheetList = wb.sheet_names()
Sheet1 = wb.sheet_by_index(0)
sixth_column = Sheet1.col_values(5)
for rownum in range(Sheet1.nrows):
              print sixth_column
# With the above steps, I can extract just fine. But I run into trouble when I try to add to the other file - namely, 'other_file.csv'
 
Path = 'users/my_name/Desktop/'
File = other_file.csv'
f = open('other_file.csv', 'w')
f.write("\n".join(sixth_column))
f.close

# But it doesn't change the file...

[bvdet]

Please use code tags when posting code. See posting guidelines.

If you want to add to another file, do not use write ('w') mode. The file will be truncated to 0 bytes. Use append ('a') mode. Pay close attention to my example. Also, you must add parentheses after f.close. f.close returns the function, but you want to call the function.

[BBMcL]

Great. That helps quite a bit. Thank you.