CODEWITHSUNDEEP
pythonstringexcelsplit
Optimesh
Optimesh

Reputation: 2857

Errors when copy - pasting a column from Excel

I want to write a function to process some data I bring from Excel. The data is essentially in an Excel column (transaction IDs). For reasons of my own convenience, I thought I'd use raw_input with copy-pasting the column from Excel, store it and run the function on that.

However, whatever I do I get errors (I actually got stuck in this very first stage of bringing in the data), and I'm pretty sure the reason is because each item is in a new line (when I use Excel's option to transpose the column to a row, I get no errors).

So, for instance, if I wanted to try and set a sample string to work with, e.g.:

some_string = "014300071432Gre
014300054037Col
014300065692ASC"

(this is the formatting you get when pasting from a column in Excel), and just call some_string, I'd get:

  File "<stdin>", line 1
    al = "014300071432Gre
                        ^
SyntaxError: EOL while scanning string literal

I tried removing the line-breaks with .split() but that didn't work

I also tried the triple quotes I saw suggested in several threads, but that didn't work either. It only got me more confused because I thought triple quotes are used when you don't want python to evaluate something.

I've placed some Sample Data in a Google doc.

Would really appreciate any help. Thanks!

Upvotes: 0

Views: 772

Answers (3)

coolcoder613
coolcoder613

Reputation: 41

some_string = """014300071432Gre
014300054037Col
014300065692ASC"""

triple quotes are a multiline string,
you could write this as:

some_string = "014300071432Gre\n014300054037Col\n014300065692ASC"

Upvotes: 0

John Y
John Y

Reputation: 14529

You're right that the difficulty in using raw_input with a copied column of Excel data is the newlines. The issue is that raw_input specifically reads one line. From the official docs:

raw_input([prompt])
If the prompt argument is present, it is written to standard output without a trailing newline. The function then reads a line from input, converts it to a string (stripping a trailing newline), and returns that.

By definition, a newline character marks the end of a line. So there really isn't a simple way to paste a column of Excel data into raw_input.

In most cases, the best way to read Excel data from Python is simply to read the Excel file directly. The best package for this is xlrd. Assuming your workbook is named myData.xls and you want to read A2:A5 from the first sheet, you would do something like

import xlrd

wb = xlrd.open_workbook('myData.xls')
ws = wb.sheet_by_index(0)
result = ws.col_values(0, 1, 5)

At that point, result would be a 4-element list of cell values (A2, A3, A4, and A5).

If you really need the user interface to be "copy a range of cells in Excel; paste into my app" then you probably have to look into building a GUI which has a multiline text input box. Here you have lots of choices, from Python's included Tkinter, to third-party libraries for Python, to non-Python GUIs (as long as they can read the input and then pass it to your Python program).

Edit: You can read the clipboard directly (so don't do the paste step at all). See these questions for more information. The simplest solution taken from those questions relies on Tkinter:

from Tkinter import Tk

r = Tk()
result = r.selection_get(selection='CLIPBOARD')
r.destroy()

The above assumes that the clipboard is already populated. In other words, the flow would be something like

  1. Your program prompts the user to copy a selection in Excel
  2. The user copies a selection in Excel
  3. The user responds to your program's prompt (to let your program know the clipboard is ready)
  4. Your program issues the above snippet to grab the clipboard contents into result
  5. Your program processes result as desired

No doubt there are more sophisticated ways, but that should be enough to get you going.

Upvotes: 1

x3al
x3al

Reputation: 586

some_string = '''014300071432Gre
014300054037Col
014300065692ASC'''

Upvotes: 0

Related Questions