0

I am extracting data from an Oracle 11g Database using python and writing it to an Excel file. During extraction, I'm using a python list of tuples (each tuple indicates each row in dataset) and the openpyxl module to write the data into Excel. It's working fine for some datasets but for some, it's throwing the exception:

openpyxl.utils.exceptions.IllegalCharacterError

This is the solution I've already tried:

Openpyxl.utils.exceptions.IllegalcharacterError

Here is my Code:

for i in range(0,len(list)): 
    for j in range(0,len(header)): 
        worksheet_ntn.cell(row = i+2, column = j+1).value = list[i][j]

Here is the error message:

    raise IllegalCharacterError
openpyxl.utils.exceptions.IllegalCharacterError
Gino Mempin
  • 25,369
  • 29
  • 96
  • 135
user2849401
  • 1
  • 1
  • 1

2 Answers2

2

I did get this error because of some hex charactres in some of my strings.

'Suport\x1f_01'

The encode\decode solutions mess with the accente words too

So...

i resolve this with repr()

    value = repr(value)

That give a safe representation, with quotation marks

And then i remove the first and last charactres

    value = repr(value)[1:-1]

Now you can safe insert value on your cell

Guilherme
  • 1,705
  • 1
  • 25
  • 45
0

The exception tells you everything you need to know: you must replace the characters that cause the exception. This can be done using re.sub() but, seeing as only you can decide what you want to replace them with — spaces, empty strings, etc. — only you can do this.

Charlie Clark
  • 18,477
  • 4
  • 49
  • 55
  • Is this happening because the dataset contains illegal character? If so then how I'm able to extract the same dataset into Excel using Oracle SQL Developer. – user2849401 May 28 '19 at 09:55
  • Yes, the dataset contains illegal characters. As for how any particular application handles them, you'll have to consult the relevant documentation. – Charlie Clark May 28 '19 at 11:00