i have hundreds of xlsx files have columns containing long numeric account numbers. need automatically convert of these files csv. trivial tools ssconvert. however, due bug feature in excel , libreoffice, long numeric fields displayed using scientific notation , formatted number (not underlying data) preserved if exported csv.
this means automated conversion csv truncate account numbers, since value 1240800388917 written csv 1.2408e+12 or 1240800000000, causing data corruption.
this easy fix manually opening excel file , setting these columns "text" format. however, it's bit tedious hundreds of files, because many of these files have strange macros , formatting make libreoffice take several minutes open each 1 (another reason why i'd convert them csv in first place).
what's easiest way use python automatically open each file , change entire column's formatting "text"? see plenty of python examples how read xls/xlsx files, , in cases write them, can find few guides on manipulating column's default formatting.
took me trial , error , digging around in code, solution turned out trivial.
from openpyxl import load_workbook wb = load_workbook('myfile.xlsx') ws = wb.active row in ws.rows: row[col_index].number_format = row[col_index].style.number_format = '@' wb.save('myfile-fixed.xlsx') 
Comments
Post a Comment