How to clean empty spaces on your workbook

[Danielli Carvalho]

Hello, everyone.
I would like to check for empty cells on my file and replace them all with zeros but had no sucess untill now.

I'm new using python so maybe you can help me with this.
Thank you in advance.

[Charlie Clark]

On 17 Dec 2019, at 13:40, Danielli Carvalho wrote:

> Hello, everyone.
> I would like to check for empty cells on my file and replace them all with
> zeros but had no sucess untill now.
> I'm new using python so maybe you can help me with this.

Hi Danielli,

what have you tried so far?

Charlie

--
Charlie Clark
Managing Director
Clark Consulting & Research
German Office
Kronenstr. 27a
Düsseldorf
D- 40217
Tel: +49-211-600-3657
Mobile: +49-178-782-6226

[Biru Singh]

Hi,

You can do something like this,

for i in range(1,ws.max_row):

    for j in range(1,ws.max_column):

        If ws.cell(row=i,column=j).value is None:

            ws.cell(row=j,column=i).value ='your string'

Where ws is your worksheet object.

    

--
You received this message because you are subscribed to the Google Groups "openpyxl-users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to openpyxl-user...@googlegroups.com.
To view this discussion on the web, visit https://groups.google.com/d/msgid/openpyxl-users/df409b46-a2bc-452d-b363-dc835f4169d4%40googlegroups.com.

[Charlie Clark]

On 17 Dec 2019, at 15:18, Biru Singh wrote:

> You can do something like this,
>
> for i in range(1,ws.max_row):

Yes, but why would you? when you can write:

for row in ws:
for cell in row

We've put a lot of work into making the API of the library as clean and
simple as possible.

[Biru Singh]

This is something new i am seeing. I always used nested for loop. I will definitely try this. 

Does it work in all version of openpyxl ?

Thanks,

🙂

--
You received this message because you are subscribed to the Google Groups "openpyxl-users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to openpyxl-user...@googlegroups.com.

To view this discussion on the web, visit https://groups.google.com/d/msgid/openpyxl-users/40BBC9F5-B26C-460D-89C6-22DAE7820581%40clark-consulting.eu.

[Charlie Clark]

On 17 Dec 2019, at 16:17, Biru Singh wrote:

> This is something new i am seeing. I always used nested for loop.

This is covered in the documentation.

> I will
> definitely try this.
> Does it work in all version of openpyxl ?

Should work for > 2.0, though I do not recommend using < 2.4 as haven't
broken too much of the API since then.

[Danielli Carvalho]

I tried some things like this

for row in ws.iter_rows('C{}:C{}'.format(ws.min_row, ws.max_row)):

    for cell in row:

        if cell.value is None:

            cell.value = 0

[Danielli Carvalho]

I tried with your code but it didn't work neither. :( 

I think I'm missing some details

Em terça-feira, 17 de dezembro de 2019 11:18:38 UTC-3, Biru Singh escreveu:

Hi,

You can do something like this,

for i in range(1,ws.max_row):

    for j in range(1,ws.max_column):

        If ws.cell(row=i,column=j).value is None:

            ws.cell(row=j,column=i).value ='your string'

Where ws is your worksheet object.

    

On Tue 17 Dec, 2019, 6:10 PM Danielli Carvalho, <danilsc...@gmail.com> wrote:

Hello, everyone.
I would like to check for empty cells on my file and replace them all with zeros but had no sucess untill now.

I'm new using python so maybe you can help me with this.
Thank you in advance.

--
You received this message because you are subscribed to the Google Groups "openpyxl-users" group.

To unsubscribe from this group and stop receiving emails from it, send an email to openpyx...@googlegroups.com.

[Charlie Clark]

On 17 Dec 2019, at 16:39, Danielli Carvalho wrote:

> for row in ws.iter_rows('C{}:C{}'.format(ws.min_row, ws.max_row)):
> for cell in row:
> if cell.value is None:
> cell.value = 0

Did you subsequently save the file?

Have you checked what the existing values are for cells that you think
are empty? It's also possible that they have empty strings.

If you only want to look at the rows in column C:

for row in.ws.iter_rows(min_col=3, max_col=3):
cell = row[0]
if not cell.value:
cell.value = 0

[Danielli Carvalho]

Yes. I did save after this, but nothing changed.

No. Not sure how to do that. I tought that only checking for empty cells it would work.
And also, I would like to search for the empty cells troughtout the file.

[Charlie Clark]

On 17 Dec 2019, at 17:25, Danielli Carvalho wrote:

> Yes. I did save after this, but nothing changed.
> No. Not sure how to do that. I tought that only checking for empty
> cells it
> would work.
> And also, I would like to search for the empty cells troughtout the
> file.

Let's assume that C10 is "empty" you can do this to get a good idea of
what the cell actually contains:

c = ws["C10"]
print([c.value])

By putting the value into a list you'll be able to see its value more
clearly.

[Danielli Carvalho]

Thanks, Charlie.

I tried what you said with two different files.

I created a new one just to perform this test and it returned "None", but with the file I want to use, nothing happens.

[Danielli Carvalho]

But assuming that it returns "None", the code should work, right?

Em terça-feira, 17 de dezembro de 2019 13:28:56 UTC-3, Charlie Clark escreveu:

[Charlie Clark]

On 17 Dec 2019, at 18:08, Danielli Carvalho wrote:

> Thanks, Charlie.
> I tried what you said with two different files.
> I created a new one just to perform this test and it returned "None",
> but
> with the file I want to use, nothing happens.

I'm sorry, Danielli, but you must include more of your code and what
you're seeing. Saying "nothing happens" is particularly unhelpful for
us: we are not telepathic and I haven't yet engineered openpyxl to log
everything…

You've already demonstrated that the approach generally works, just not
with one particular file. So, please do as I suggested and test with a
particular cell that you expect to contain nothing and provide us with
the results.

[Danielli Carvalho]

I told you I already did what you said in both situations. With a new small file that I created and the file I want to use.

One of the attempts with the file I want to use showed a memory error. It didn't return anything from a cell that I already know is showing up blank.

Anyway, no reason to be mad about it. I thought the idea was to talk about a problem and maybe someone could help you solve it.

It was just a request for help.

But thanks anyway. 

Have a nice day.

[Biru Singh]

Hi,

Can you just check the same code with  equating cell value with blank single quotes instead of None. Sometimes it works.

--

You received this message because you are subscribed to the Google Groups "openpyxl-users" group.

To unsubscribe from this group and stop receiving emails from it, send an email to openpyxl-user...@googlegroups.com.
To view this discussion on the web, visit https://groups.google.com/d/msgid/openpyxl-users/4b318470-7ffd-4067-8db2-0ee62770f8ec%40googlegroups.com.

[Danielli Carvalho]

Hello Biru!

I tried with a smaller file and it worked. 

But my file is a very large database with more than 100.000 rows.

I'm using Spyder and the following error is showing up:

  File "C:\ProgramData\Anaconda3\lib\site-packages\openpyxl\worksheet\_reader.py", line 337, in bind_cells
    self.ws._cells[(cell['row'], cell['column'])] = c

MemoryError

To view this discussion on the web, visit https://groups.google.com/d/msgid/openpyxl-users/CAGVRZa2RUZE3pOH5pnciSGfNaP9k%3DYXPsNNWGzCkesZ7cOj4cQ%40mail.gmail.com.

[Biru Singh]

Hi,

Then i would suggest you to use pandas. It's just 3-4 lines of code to replace null values with anything you want.

Just google it you will find the solution in stack overflow.

Very commonly used functionality in pandas.

Cheers,

🙂

To view this discussion on the web, visit https://groups.google.com/d/msgid/openpyxl-users/CAFPj%2BOVANQXaxQvk2jOEHoD6KsK4D1R5g9Nu_Z4zMwMq5kg2xw%40mail.gmail.com.

[Danielli Carvalho]

I'll take a look at it! 

I've heard the name panda for python before, but that's also new to me.

Thank you so much for your help, Biru! 😊

Best Regards!

Danielli C. 

To view this discussion on the web, visit https://groups.google.com/d/msgid/openpyxl-users/CAGVRZa3jcbm1tYFbpzd%3D-yUqKMJ%3D-t1nh84qDiqFXg6NJQEfAw%40mail.gmail.com.