0

I am trying to import the range A1:C4 from Excel into Python as a list, then add '_a' to the end of every element in that list. Currently it is returning the TypeError "can only concatenate list(not 'str') to list"

I've gotten it to import a range before then return that range:

#Excel formula: =funky(A1:C4)

listy = ["item_0"]

@xl_func #needed before any function that is called on in the excel spreadsheet
def funky(add): #needs one input to function, in this case it is being given the range A1:C4 in the excel spreadsheet
    global listy
    listy.extend(add) #adds all the elements given to the function into 'listy' (in this case it is the values of range A1:C4 in the excel spreadsheet)
    return(listy[1:]) #returns all elements past element [0]

but it still doesn't allow me to do any sort of edits to the values of the range.

This is something I've tried to do, with the same range but a different function:

@xl_func
def iter(add): #When I changed line 4 to .extend([add]) I also changed 'add' to '[add]'
    list = ["item_0"]
    list.append(add) #I also tried .extend(add) and .extend([add]) but got the same error
    list = [item + '_a' for item in list] #adds '_a' to the end of every item in 'list'
    return list #returns the edited 'list'

What the Excel formula looks like:

=iter(A1:C4)

This function (iter(add)) is what is returning the TypeError. funky(add) does not return this error.

Connor
  • 13
  • 5
  • I wonder if you need to force "add" to be a str like ```list.append(str(add))``` – Suraj Shourie Aug 10 '23 at 15:21
  • @SurajShourie I can try that and see if it works. Then I'll try a different method to force it to be a list. – Connor Aug 11 '23 at 15:38
  • Just tested it, doesn't work to force it as a string because `list.append(str(add))` doesn't recognize `add` as a variable. Note that is when I am also forcing it to be a string variable coming in, aka `def iter(str(add)):` I'll try with just forcing it in `list.append` – Connor Aug 11 '23 at 15:40
  • So with just `list.append(str(add))`, it changes and outputs `[['item_1', 'item_2', 'item_3'], ['item_4', 'item_5', 'item_6'], ['item_7', 'item_8', 'item_9'], ['item_10', 'item_11', 'item_12']]_a` Which is progress, but not much. Maybe I can split this into a list and filter out everything that isn't 'item_`x`'. – Connor Aug 11 '23 at 15:42
  • So given that the new output is a list of lists, it doesn't change anything if I split it into another list. which sucks, maybe I can look up how to split elements in a nested list? – Connor Aug 11 '23 at 15:53
  • So I figured out how to split the list, but now I need to remove certain characters from the list, specifically the `[[`, `[`, `'`, `]`, and `]]` characters – Connor Aug 11 '23 at 17:23
  • Okay so I figured out an incredible jank solution. Before converting the `str(add)` into a list (by using `split()`), I put in a lot of `strip()` functions that remove the unwanted characters. – Connor Aug 11 '23 at 17:36

1 Answers1

0

Okay, so I solved my own question by looking around for some better solutions, and I found one that helped a lot after a little bit of messing around with it.

I dissolved add into a single big list using merged = list(itertools.chain.from_iterable(add))

(Credit: https://stackoverflow.com/a/953097/21975226)

Then everything worked as intended!

My main problem was that I was trying to add a string to the end of a list object, which is why it was returning the TypeError.

What I did to fix that was dissolve the input list (add) and use .extend() to add it to list (which I renamed to list_0 because of conflict with list(itertools.chain.from_iterable(add))). Then the line that adds the string _a to the end of every element in list_0 worked perfectly!

Connor
  • 13
  • 5