Compare rows of a file with data from a worksheet

Question

Compare rows of a file with data from a worksheet

Navigation

#1 by (0 votes)

1

I'm new to Python and I have a problem that's getting me out of the way ...

It is as follows, I want to get the strings from a file and compare its elements with the phrases written in the spreadsheets of my .xlsx file.

Only the strings of the file will have what is written in the spreadsheet plus some codes.

I want the program to do the following: If the line in the file has what is in the column I'm going through, do the else statement, skip to the next line.

Follow the code:

#!/usr/bin/python
import subprocess
import xlrd

arquivo = open("/home/weslei/Documentos/t.txt", "r")
read = arquivo.readlines()

xls = xlrd.open_workbook('chips.xlsx')
plan = xls.sheets()[0]
b =  plan.col(0)

for i in read:
   for n in b:
       if n == i[20:57]:
           print "instrucao"
       else:
           print "next"

The file I'm reading has this content:

|7891515433963     |AMENDOIM SEM PELE MANIX 40G          |UN|34119/6|AF  4,9900|
|7897846301872     |AREIA HIG ABSORCAT             4KG   |UN|32306/2|AF  7,9900|
|7898948468012     |ARROZ CARRIJO TIPO1            5KG   |UN|32471/7|AF 13,8000|
|7896290300974     |ARROZ PRATO FINO ORGAN INTEG 1K UN   |UN|33908/7|AF 14,9500|
|7896290300318     |ARROZ PRATO FINO PARBOLIZADO   2KG   |UN|32034/4|AF  8,7500|
|7896290300295     |ARROZ PRATO FINO PARBOLIZADO 1K UN   |UN|32185/3|AF  4,3900|
|0000000000000     |ARROZ PRATO RICO AGULINHA 5KG        |UN|34335/0|AF  0,0000|

And the spreadsheet:

ELMA CHIPS  
AMENDOIM SEM PELE MANIX 40G 1,79
BACONZITOS 55G  3,68
*BACONZITOS 110G    6,15
*BATATA SENSAÇÃO FG.GRELH. 90G  6,15
*CEBOLITOS 60G  3,68
*CEBOLITOS ASSADO 110G/120G 6,49
*CHEETOS 51G/ 55G/57G/59G/61G    2,49 
*CHEETOS  130G/150G/160G    6,28
*DEMONTÃO RUFLES BACON. 75G 4,45
*DORITOS 55G    3,65
*DORITOS QUEIJO 96G/110G/100G   6,25
*DORITOS QUEIJO NACHO 167GR 9,98
*DORITOS  200G/220G 9,98
*FANDANGOS 63G   2,45 
*FANDANGOS PRESUNTO/QUEIJO 175G 6,25
*FANDANGOS PRESUNTO 164GR   6,25
*PANETINI PRESUNTO / QUEIJO 40G 1,89
*PINGO D'OURO 65G   2,99
*PINGO D'OURO 90G   3,68
*RUFFLES 90G/100G   6,15
RUFFLES 96GR    6,15
*RUFFLES 175G    9,98 
*RUFFLES 57/50G  3,50 
*SALGADINHOS TORCIDA 60/50G 1,75

Detail : The program outputs the columns as text:u'*SALGADINHOS TORCIDA 60/50G' , all columns exit with text:u , causing the iteration error pq in the file does not have text:u .

python excel

asked by anonymous 13.10.2016 / 21:37

1 answer

MySQL 2002 error: Can not connect to local MySQL server through socket How to put the caret (cursor) in the right position?

score 0 · Accepted Answer

I want the program to do the following: If the file line has what's in the column I'm going through: do the else : skip to the next line.

To move to the next iteration you can use continue . Would not it be more practical to check if the column content is present on the line and use continue ?

The program outputs the columns as text:u'*SALGADINHOS TORCIDA 60/50G' all columns exit with text:u , causing the iteration error pq in the file does not have text:u .

You should be using Python 2.x , the prefix u indicates that it is a string Unicode . You can use the str function to return a string or encode to UTF-8 with the str.encode function, for example: nome_da_variavel.encode('utf-8') .

Two suggestions:

Use with when opening the file, so the file handle will be closed automatically after use. It is similar to using of C #.
Instead of comparing if n == i[20:57] , you can if n in i .

The code may look like this (I tested it in Python 3, it may be slightly different in Python 2):

#!/usr/bin/env python
# -*- coding: utf-8 -*-

with open('t.txt', 'r') as arquivo:
    xls = xlrd.open_workbook('chips.xlsx')

    planilha = xls.sheets()[0]
    coluna = planilha.col(0)

    for linha in arquivo:
        linha = linha.rstrip() # Para remover a quebra de linha

        for item in coluna:
            if item.value in linha:
                print ("Item encontrado: ", item)
                # Aqui você pode usar o "continue" para pular para o próximo item