Geek asks and answers

Python xlrd saves float instead of ints when converting xlsx to csv

I use xlrdto read the file .xlsxand save it to a file .csv. Everything is in order, the problem is that all the values ​​of the intfile are .xlsxautomatically converted to floata file .csv. This means that if I have a 40cell inside the file .xlsx, it appears as 40.0in the file .csv.

I am using the following code to read and convert it to .csv.

    wb = xlrd.open_workbook('share\docs\excelcontrol2.xlsx')
    sh = wb.sheet_by_name('Hoja1')
    archivo_csv = open('share\docs\output.csv', 'wb')
    wr = csv.writer(archivo_csv, delimiter=";")
    for rownum in xrange(sh.nrows):
        wr.writerow(sh.row_values(rownum))
    archivo_csv.close()

Files. xlsxcontain intand floatamong other things. How to save a file .csvto save the original format? I mean, without changing intto floatand leaving everything else as it is?

Thanks in advance.

+3
3

xlrd docs Excel XL_CELL_NUMBER float Python.

, , int float.

+2

, int float, CSV. , .

0

xlsx Excel LibreOfficeCalc , :

  • int, 0 ,
  • , 1 .

openpyxl. Cell , , . , int float.

:

from openpyxl import load_workbook

def csv_from_excel(xlsx_file_path):
    """

    :param xlsx_file_path: String. Path of the excel file.

    Example :
    while calling csv_from_excel("one/two/my_file.xlsx"), the file "one/two/my_file.csv" is created.

    """

    file_name, extension = os.path.splitext(xlsx_file_path)
    csv_file_path = file_name + ".csv"
    wb = load_workbook(filename=xlsx_file_path)
    first_sheet = wb.get_sheet_names()[0]
    worksheet = wb.get_sheet_by_name(first_sheet)
    content = []
    for row in worksheet.iter_rows():
        my_row = []
        for cell in row:
            value = cell.internal_value
            the_format = cell.number_format
            if value_is_float_in_int_format(value, the_format):  # case when excel will gives 80 instead of 80.0
                value = float(value)
            my_row.append(value)
        content.append(my_row)
    write_csv_file(csv_file_path, content)


def value_is_float_in_int_format(value, the_format):
    result = isinstance(value, int)
    result = result and not (the_format == "General" or the_format == "0")
    return result


def write_csv_file(csv_file_path, content, delimiter=CSV_DEFAULT_DELIMITER):
    """

    :param csv_file_path: String. Path of the csv file to write on.
    :param delimiter: Char. Delimiter for the csv file (can be ';' ',' or '\t' for tab)
    :param content: List of List of String. Content to write in list of list.

    """
    logger.debug("FILE I/O : writing content in the file %s ", csv_file_path)
    with open(csv_file_path, "w") as a_file:
        writer = csv.writer(a_file, lineterminator='\n', delimiter=delimiter)
        writer.writerows(content)


my_xlsx_file = "/home/session/Documents/my_file.xlsx"
csv_from_excel(my_xlsx_file)  # this creates the csv file
0

More articles:


All Articles