Python CSV File Read: trailing zero truncated in read

Hello, Experts,

I am writing a program in python v 3.7x that will read a CSV file and ingest the contents of that file into a data frame and ultimately written to MySQL.

Here is a column that when I preview the CSV file (not open) you see that the DOB has the 0 as the leading 0.


When you open up the file via Numbers (MacOS) or Excel (MacOS) or read the file via the pandas 1.1.x library the leading 0 you see above is truncated.

Numbers screencap:





Excel screencap:


Python Read Screencap:



Python Code to read CSV:

import pandas as pd

class ReadCSV:
   def __init__(self):
      self.csv_file_path = '/Users/usr/Downloads/filename.csv'
      self.col_names = ['uin', 'store_no', 'dba_name', 'action', 'emp_lname', 'emp_fname', 'emp_mname', 'dob', 'email', 'role', 'hire_orig_date', 'rehire_date', 'term_date']
   def csvReader(self):
      hireReport = pd.read_csv(self.csv_file_path, names=self.col_names, header=0, na_filter=False, encoding='utf8')
      return hireReport

Select allOpen in new window

Python code to write to MySQL:

from modules.db.dbconn import Connection
from modules.utils.buildDataFrame import DataFrameBuilder
from modules.utils.readCSV import ReadCSV
import pandas as pd

dbCaller = Connection()
dfCaller = DataFrameBuilder()
csvCaller = ReadCSV()

class  SQLWriter:
   def writeToSQL(self, df, table, engine):
      df.to_sql(name=table, con=engine, index=False, if_exists='append')
      return print('writing data from action csv file to MySQL...')

Select allOpen in new window

Python code for data frame composition:

from modules.utils.readCSV import ReadCSV
import pandas as pd
import numpy as np

csvCaller = ReadCSV()

class DataFrameBuilder:
   
   def generateActionDataFrame(self, data):
      orig_df = pd.DataFrame(data=data, index=None)
      df = orig_df.astype({'dob': 'object', 'hire_orig_date': 'datetime64', 'rehire_date': 'datetime64', 'term_date': 'datetime64'})
      print(df.dob)
      return df

Select allOpen in new window

I can ultimately append the leading zeros for anything in that dob column using a conditional check that will get the length of the string in that field and anything = 7 to append a 0 in front of the dob.

Should I:

1. continue with the logic I explained just above? Because this is the easiest way...
2. there is another way (please suggest any recommendations)
3. use date data type instead for this column and format the column when reading from SQL to be mmddyyy with actually containing the 0 leading?

I appreciate your thoughts and suggestions in advance.

Thank you,

Isaiah