CODEWITHSUNDEEP
pythonexceltype-conversion
Igor Barboza
Igor Barboza

Reputation: 13

Converting a txt file to xls file

I need some help with specifying format of values in xls. I am converting a txt file into xls.

Below is my current code. As I read the txt file, i need to put the values as Int/Float or str. Int is ok, as well str, but I can't solve a problem with floats.

366351.77 or -6000.00, for example, are still being inserted as a str in the sheet.

I don't know if the problem is when i'm reading the content of the txt file or if it is when i tried to convert the string content of the txt to a float format.

import sys
import openpyxl

delimitador, caminho, destino = sys.argv[1], sys.argv[2], sys.argv[3]
xls = openpyxl.Workbook()
sheet = xls.active
sheet.column_dimensions['A']
txt = open(caminho)


def is_float(s):
    result = False
    if s.count(".") == 1:
       if s.replace(".", "").isdigit():
          result = True
    return result


    i = 1
    for linha in txt:
        coluna = linha.split(delimitador)
        ii = 1
        for celula in coluna:
            cell = sheet.cell(row=i, column=ii)
            if is_float(celula):
                #cell.value = float(celula.decode('latin-1').encode('utf-8'))
                cell.value = 'float'            
            elif celula.isdigit():
                cell.value = int(celula.decode('latin-1').encode('utf-8'))
            else:
                cell.value = celula.decode('latin-1').encode('utf-8')
            ii += 1
        i += 1
   xls.save(destino)
   txt.close()

Upvotes: 1

Views: 135

Answers (2)

Igor Barboza
Igor Barboza

Reputation: 13

First of all tks so much Nicarus, your help solved my problem.

Here is my final code, now The values as Number(Values, Accounts, Index..) are being correctly added as a number in the Worksheet, and the others value as (Date, Description...) are being added as String

FinalCode

Upvotes: 0

Nick
Nick

Reputation: 7451

Use the set_explicit_value method of the cell object.

cell.set_explicit_value(value=your_float, data_type='f')

Docs: https://openpyxl.readthedocs.io/en/default/api/openpyxl.cell.cell.html#openpyxl.cell.cell.Cell.set_explicit_value

Upvotes: 1

Related Questions