Extracting columns from Excel using Python
I have an Excel file with ff: row / col structure
ID English Spanish French
1 Hello Hilo Halu
2 Hi Hye Ghi
3 Bus Buzz Bas
I would like to read an Excel file, extract the row and column values, and create 3 bases of new files in English, Spanish and French columns.
So, I would have something like:
Russian File:
"1" = "Hello"
"2" = "Hi"
"3" = "Bus"
I am using xlrd. I can open, read and print the contents of the file. However, this is what I get from using this command (with the Excel file already open):
for index in xrange(0,2):
theWord = '\n' + str(sh.col_values(index, start_rowx=index, end_rowx=1)) + '=' + str(sh.col_values(index+1, start_rowx=index, end_rowx = 1))
print theWord
OUTPUT:
[u'Parameter/Variable/Key/String']=[u'ENGLISH'] <-- is this a list?, didn't the str() use to strip it out?
What is u doing ? How do I remove square brackets?
source to share
u
means it is a unicode string, it gets added there when you call str()
. If you write a line to a file, it won't be there. What you get is 1 row from column. This is because you are using end_rowx=1
it returns a list with one item.
Try to get lists of column values:
ids = sh.col_values(0, start_rowx=1)
english = sh.col_values(1, start_rowx=1)
spanish = sh.col_values(2, start_rowx=1)
french = sh.col_values(3, start_rowx=1)
and then you can zip
include them in the tuple lists:
english_with_IDS = zip(ids, english)
spanish_with_IDS = zip(ids, spanish)
french_with_IDS = zip(ids, french)
which are in the form:
("1", "Hello"),("2", "Hi"), ("3", "Bus")
If you want to print pairs:
for id, word in english_with_IDS:
print id + "=" + word
col_values
returns a list of column values if you want single values to be called sh.cell_value(rowx, cellx)
.
source to share
Use pandas :
In [1]: import pandas as pd
In [2]: df = pd.ExcelFile('test.xls').parse('Sheet1', index_col=0) # reads file
In [3]: df.index = df.index.map(int)
In [4]: for col in df.columns:
...: column = df[col]
...: column.to_csv(column.name, sep='=') # writes each column to a file
...: # with filename == column name
In [5]: !cat English # English file content
1=Hello
2=Hi
3=Bus
source to share
import xlrd
sh = xlrd.open_workbook('input.xls').sheet_by_index(0)
english = open("english.txt", 'w')
spanish = open("spanish.txt", 'w')
french = open("french.txt", 'w')
try:
for rownum in range(sh.nrows):
english.write(str(rownum)+ " = " +str(sh.cell(rownum, 0).value)+"\n")
spanish.write(str(rownum)+ " = " +str(sh.cell(rownum, 1).value)+"\n")
french.write(str(rownum)+ " = " +str(sh.cell(rownum, 2).value)+"\n")
finally:
english.close()
spanish.close()
french.close()
source to share