Convert xlsx to csv while dropping sheets and keeping date formats

Question

I have a .xlsx file that I want to convert to .csv file. I have done a demo file as shown in the screenshot. In the .xlsx file, I have 3 sheets and I want to keep the last sheet only. In addition, I want to preserve my dates in a MM/DD/YYYY format.

Found a few solutions here and there on converting then dropping sheets or vice versa. The closest I have come to is using the solution from this link :

But it doesn't keep the date format of MM/DD/YYYY and instead converts it to numbers e.g. 44079. Tried searching solution to convert the numbers to date but there is nothing on this.

Can anyone help me with this? I can provide more clarification if needed.

I am coding in Python.

It doesn't look like your dates are in a `MM/DD/YYYY` format to begin with, though. — Paul M., Jul 17 '20 at 09:48
Maybe try `xlsx2csv` https://github.com/dilshod/xlsx2csv. Always You can modify and convert using pandas. — ipj, Jul 17 '20 at 09:51
Hi @PaulM.if I go to the date cell it is showing 5/9/2020 as seen in the screenshot. I think the displaying of it is in YYYY/MM. Please correct me if I'm wrong. — Newbee Coding, Jul 17 '20 at 09:51
Hi @ipj seems a bit complicated for me. Need a simpler solution — Newbee Coding, Jul 17 '20 at 09:55
Just 2 lines of code: `from xlsx2csv import Xlsx2csv Xlsx2csv("myfile.xlsx", outputencoding="utf-8").convert("myfile.csv")` — ipj, Jul 17 '20 at 09:58
@ipj seems to not be working. It is saving an empty csv file for me — Newbee Coding, Jul 17 '20 at 10:21

Newbee Coding · Answer 1 · 2020-07-18T04:04:57.790

Hi I solved my own question by using the answer from this Python using pandas to convert xlsx to csv file. How to delete index column?

In addition, because the date is converted into something, not I want in the converted .csv filee.g.

05-09-2020 00:00:00

I used pandas and load the converted csv file to a dataframe. From there I used df['date made'] = pd.to_datetime(df['date made']) to convert the date from an object to datetime. After that I used df['date made'] = df['date made'].dt.strftime(%m/%d/%Y) to get

09/05/2020

which is the date format I wanted. I repeat the steps for Date Due as well.

Hope this helps those who are looking to convert .xlsx to .csv and do some formatting on the date.

Convert xlsx to csv while dropping sheets and keeping date formats

1 Answers1