Python Forum
Formatting Data/Time with Pyodbc and openpyxl
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Formatting Data/Time with Pyodbc and openpyxl
#1
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
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
Smile Set 'Time' format cell when writing data to excel and not 'custom' limors 3 1,380 Mar-29-2021, 09:36 PM
Last Post: Larz60+
  Confusion about [date]time [formatting] Mark17 0 651 Dec-17-2020, 07:25 PM
Last Post: Mark17
  Openpyxl: Excel formula & condition formatting removed JaneTan 0 988 Sep-25-2020, 07:02 AM
Last Post: JaneTan
  ValueError: time data 'None' does not match format '%Y-%m-%dT%H:%M:%S.%f' rajesh3383 4 4,463 Sep-03-2020, 08:22 PM
Last Post: buran
  Get database used data space from pyodbc susja 1 699 Aug-14-2020, 02:01 PM
Last Post: susja
  Find data using a period of time in SQLITE3 SmukasPlays 2 811 Jul-30-2020, 02:02 PM
Last Post: SmukasPlays
  Bode plot from time series experiment data discus 4 2,346 Jun-20-2020, 07:46 AM
Last Post: discus
  openpyxl Pasting data to different cells Kristenl2784 3 2,323 Jun-15-2020, 08:50 PM
Last Post: Yoriz
  getting error ValueError: time data '' does not match format '%H:%M' srisrinu 2 3,035 Apr-09-2020, 11:12 AM
Last Post: srisrinu
  Help Graphing Arduino Data Real Time in Python nschulz 0 1,292 Mar-12-2020, 06:15 PM
Last Post: nschulz

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020