CODEWITHSUNDEEP
pythonexcelxlwings
YJH16120
YJH16120

Reputation: 545

Preventing date conversion in Excel with xlwings

the problem is as the title states. I have a column AX filled with values. The name of the column is "Remarks" and it will contain remarks but some of those remarks are dates and some are full blown notes like "Person A owes Person B X amount."

The problem I'm currently facing now is that in xlwings the columns that are just dates like "1/8/24" are converted to the date data type. I do not want this conversion to happen. I want it to remain as "1/8/24" literally and remain as the data type of "Text".

The full workflow is as follows:

  1. Read data from excel (I have no write access)
  2. Create a new excel workbook
  3. Put processed data into new excel workbook

So I tried to fix it in two places

  1. After I read the data I converted the AX columns' values all to string with str(cell.value) among other options, none of which worked.
  2. Before the new excel workbook is saved.

Nothing in option 1 worked and I figured that it had something to with how Excel is handling dates. So, I'm now trying to prevent the conversion and just have "1/8/24" appear literally but nothing is working. I checked the documentation and I tried Range.options to prevent the conversion but it doesn't help much. As when I inspected the cell with "1/8/24" it showed up as a datetime.datetime object. Converting that with str just turns it back into a date anyways. So, I figured that I have to find a way to do the converting after it was written into the workbook.

I messed around with data types in Excel and I found out that if I used this

The Text to Columns button in Data tab

Clicked next on everything

Final page of the wizard that appears

Then selected "Text" in the final screen the dates appeared. So, that leads me to try a new option which is to convert the data type of the entire column to just "Text". So I tried out stuff like this sheet.range("AX1").expand("down").api.NumberFormat = "@". But the workbook that was generated still doesn't show "1/8/24" literally. Instead it shows some number like 45299. Surprisingly when I converted that cell into "Long Date" it gets turned into a date "1st August 2024". This is where I stopped working as I ran out of ideas and have no idea how to continue. Any guidance is very much appreciated, thank you.

Upvotes: 2

Views: 76

Answers (1)

Michal
Michal

Reputation: 6009

Dates, or dates looking strings, are always a pain. I think the simplest way to deal with it in your case is to insert a ' as the first character in every cell to force Excel to treat it like a string, something like .range(f'A{i}').value = f"'{value}" (not tested, assuming that the column of data in question is in column A).

Upvotes: 0

Related Questions