1

I'd like to push contents from the string to xls contents are

abc,[1,2],abc/er/t_y,def,[3,4],def/er/t_d,ghi,ghi/tr/t_p,jkl,[5],jkl/tr/t_m_n,nop,nop/tr/t_k

this is my sample code (using xlwt)

workbook = xlwt.Workbook()
sh = workbook.add_sheet("Sheet1")
def exporttoexcel ():  
    print("I am in print excel")
    rowCount = 1
    for row in finalvalue:  # in finalvalue abc,[1,2],abc/er/ty.. is stored as type= str
        colCount = 0
        for column in row.split(","):  
            sh.write(rowCount, colCount, column)
            colCount += 1
        rowCount += 1
    workbook.save("myxl.xls")

exporttoexcel()

while ingesting data in excel there are few rules to follow

- column headers are main,ids,UI
- each cell have one value except ids [ids may or may not be there]
- after three columns it should move to the next row
- the second column i.e **id** should have only ids and if not available it should be kept as blank

how to push data into excel which looks similar to this with the above rules?

 | A  | B | C |
1|main|ids|UI|
2|abc |1,2|abc/tr/t_y|
3|def |3,4|def/tr/t_d|
4|ghi |   |ghi/tr/t_p|
5|jkl |5  |jkl/tr/t_m_n|
6|nop |   |nop/tr/t_k|
AmRey
  • 27
  • 1
  • 6
  • There is no question here? – Grismar Feb 06 '20 at 06:24
  • @Grismar updated the description to clarify the actual questions – AmRey Feb 06 '20 at 06:51
  • Your rules have a contradiction: on the one hand "each cell should have one value", but on the other hand, if no id is available, the "cell should be blank"? Also: your example data has no lines that *don't* have data for `main` or `UI` - is there always data guaranteed to be there in the input and are the `ids` the only value that's sometimes blank? Is your problem how to split up the string so that your provided code works? – Grismar Feb 06 '20 at 21:43
  • @Grismar Yes.. there will be value for main and UI always and for ids there is no guarantee. I want to split the string into separate cells with a group of three as shown in the figure – AmRey Feb 07 '20 at 05:09
  • Ok @amrey, then I think the code I posted in the answer actually solves your problem. – Grismar Feb 07 '20 at 05:44

2 Answers2

0

Use Regular expression to check value with []

import re
m = re.search(r"\[(\w+)\]", column)
Anupam Chaplot
  • 1,134
  • 1
  • 9
  • 22
0

If your problem is how to break up the input string into something you can process with your code:

import re

content = 'abc,[1,2],abc/er/ty,def,[3,4],def/er/td,ghi,ghi/tr/tp,jkl,[5],jkl/tr/tm,nop,nop/tr/tk'

finalvalue = []
for match in re.finditer(r"(\w+),(\[\d+(?:,\d+)*\],)?([\w/]+)", content):
    finalvalue.append((
        match.group(1),
        None if match.group(2) is None else match.group(2)[1:-2],
        match.group(3)
    ))

print(finalvalue)

Result:

[('abc', '1,2', 'abc/er/ty'), ('def', '3,4', 'def/er/td'), ('ghi', None, 'ghi/tr/tp'), ('jkl', '5', 'jkl/tr/tm'), ('nop', None, 'nop/tr/tk')]

Note: rows are no longer stored as string, but as tuple, so you can simply your code a bit.

Grismar
  • 27,561
  • 4
  • 31
  • 54
  • @https://stackoverflow.com/users/4390160/grismar Somehow this code leads me to get all the sets which doesn't have ids wid wrong grouping. here is the code `flvalue = [] def convstrtotup(): print("I am in convert str to tuple") for match in re.finditer(r"(\w+),(\[\d+(?:,\d+)*\],)?([\w/]+)", finalvalue): flvalue.append(( match.group(1), None if match.group(2) is None else match.group(2)[1:-2], match.group(3) )) print("Str to tuple>>:", flvalue) convstrtotup()` op I am gettin like `[('tp','ghi'),('tk','nop')]` – AmRey Feb 07 '20 at 06:51
  • I'm sorry, but I'm not going to debug code posted in a comment with mistakes. Once I remove the bugs in the code in your comment, it works just fine and the code you post didn't get the output you included in your comment. If you feel the solution doesn't work with certain inputs, just add that to your question, don't post code in comments. – Grismar Feb 08 '20 at 23:22
  • @https://stackoverflow.com/users/4390160/grismar see this https://regex101.com/r/6swbXw/1 This was not producing the output. Added additional input to question as you said... I apologies for adding code in a comment. was not aware of the standards. [I used this regex intstead **(\w+),\[([^][]+)\],([\w/]+)** ] – AmRey Feb 10 '20 at 05:36
  • The problem there is quite simple, you don't have numbers in brackets, but numbers in strings in brackets, i.e. not `[1,2]`, but `['1', '2']`, which is not what you specified in the question. – Grismar Feb 10 '20 at 05:49
  • Also, you have spaces in the same, list which also wasn't specified. You'll end up with something like `(\w+),(\['?\d+'?(?:,\s*'?\d+'?)*\],)?([\w/]+)` – Grismar Feb 10 '20 at 05:52