Python Forum
Formatting Data/Time with Pyodbc and openpyxl - Printable Version

+- Python Forum (https://python-forum.io)
+-- Forum: Python Coding (https://python-forum.io/forum-7.html)
+--- Forum: General Coding Help (https://python-forum.io/forum-8.html)
+--- Thread: Formatting Data/Time with Pyodbc and openpyxl (/thread-29081.html)

Formatting Data/Time with Pyodbc and openpyxl - bearcats6001 - Aug-17-2020

I have a python script that takes records from an .MDB using a sql query and prints them to a excel document. Two of the fields that are printed are date/time fields. where about in my script would I place the date.time portion of the conversion? and what would that look like? below is the excel portion of the script. I need the format to be YYYY/MM/DD and time to be xx:xx. Thanks!

import pyodbc
from platform import python_version
pyVersion = str(python_version())

if (pyVersion[0:3] == "2.7"):
    from Tkinter import *
    import Tkinter, Tkconstants, tkFileDialog, tkMessageBox
if (pyVersion[0] == "3"):
    from tkinter import filedialog, messagebox
    from tkinter import * 

import os
import glob
from openpyxl import Workbook
import datetime

if len(lstDuplicates) > 0:

    dirName = os.path.dirname(mdbPath.get())
    mdbName = os.path.basename(mdbPath.get())
    xlfilepath = os.path.join (dirName,mdbName.split('.')[0] + "_Duplicates.xlsx")
    print(xlfilepath)
    wb = Workbook()
    ws = wb.active
    ws.title = "Duplicates"
    wb.save(xlfilepath)

    ws['A1'] = "DuplicateRecords"
    ws['B1'] = "Purpose"
    ws['C1'] = "Length Surveyed"
    ws['D1'] = "Direction"
    ws['E1'] = "Inspector"
    ws['F1'] = "Date"
    ws['G1'] = "Time"

    for x in range(len(lstDuplicates)):
        ws['A' + str(x + 2)] = lstDuplicates[x]

    for x in range(len(lstPurpose)):
        ws['B' + str(x + 2)] = lstPurpose[x]

    for x in range(len(lstLength)):
        ws['C' + str(x + 2)] = lstLength[x]
    for x in range(len(lstDirection)):
        ws['D' + str(x + 2)] = lstDirection[x]
    for x in range(len(lstInspector)):
        ws['E' + str(x + 2)] = lstInspector[x]
    for x in range(len(lstDate)):
        ws['F' + str(x + 2)] = lstDate[x]
    for x in range(len(lstTime)):
        ws['G' + str(x + 2)] = lstTime[x]


    wb.save(xlfilepath)


return