Format date string in a file using Python
<p>I get csv files from my client which contains variable number of columns. Out of these columns there can be some columns containing date string but the order is not defined, for example :</p>

<pre><code>column1str|column2dt|column3str|column4int|column5int|column6dt
ab c1|10/20/2010|1234|10.02|530.55|30-01-2011
ab c2|10/10/2010|12346|11.03|531|05-05-2012
abc3|10/10/2010|122|12|532.44|11-09-2008
abc4|10/11/2010|110|13|533|01-11-2013
abc5|10/10/2010|11111|14|534|30-02-2012
</code></pre>

<p>I get the format of date string from client as input, in the above input there are two formats of date string <code>MM/dd/yyyy</code> and <code>dd-MM-yyyy</code>. </p>

<p>I want to convert all the dates in a particular format <code>dd-MM-yyyyTHH:mmZ</code> in the file itself. I know how to convert date string to desired date string when the input date format is given. The challenge I am facing here is how can I replace the date string at particular column in the file.</p>

<p>First, read that for reference for Python datetime.strptime() format strings:
<a href="https://docs.python.org/3.5/library/datetime.html#strftime-strptime-behavior" rel="nofollow">https://docs.python.org/3.5/library/datetime.html#strftime-strptime-behavior</a></p>

<p>And that for CSV parsing: <a href="https://docs.python.org/3.5/library/csv.html" rel="nofollow">https://docs.python.org/3.5/library/csv.html</a></p>

<p>My answer will use standard Python only. As a valid alternative you could use a specialized data analysis library such as pandas as already suggested.</p>

<p>your <code>MM/dd/yyyy</code> would be <code>%m/%d/%Y</code> in strptime format (which is actually C standard format), and <code>dd-MM-yyyy</code> would be <code>%d-%m-%Y</code>.</p>

<p>Now I'm not sure if you want the dates to be "autodiscovered" by your python script or if you want to be able to specify the appropriate columns and formats by hand. So I will suggest a script for both:</p>

<p>This will convert all dates in the columns names and input formats specified in the INPUT_DATE_FORMATS map:</p>

<pre><code>from datetime import datetime

import csv

# file that will be read as input
INPUT_FILENAME = 'yourfile.csv'
# file that will be produced as output (with properly formatted dates)
OUTPUT_FILENAME = 'newfile.csv'


INPUT_DATE_FORMATS = {'column2dt': '%m/%d/%Y', 'column6dt': '%d-%m-%Y'}

OUTPUT_DATE_FORMAT = '%d-%m-%YT%H:%MZ'

with open(INPUT_FILENAME, 'rt') as finput:
    reader = csv.DictReader(finput, delimiter='|')
    with open(OUTPUT_FILENAME, 'wt') as foutput:
        writer = csv.DictWriter(foutput, fieldnames=reader.fieldnames, delimiter='|') # you can change delimiter if you want
        for row in reader: # read each entry one by one
            for header, value in row.items(): # read each field one by one
                date_format = INPUT_DATE_FORMATS.get(header)
                if date_format:
                    parsed_date = datetime.strptime(value, date_format)
                    row[header] = parsed_date.strftime(OUTPUT_DATE_FORMAT)
            writer.writerow(row)
</code></pre>

<p>This will try yo parse each field in the input file with all formats specificied in INPUT_DATE_FORMATS and will write a new file with all those dates formatted with OUTPUT_DATE_FORMAT:</p>

<pre><code>from datetime import datetime

import csv

# file that will be read as input
INPUT_FILENAME = 'yourfile.csv'
# file that will be produced as output (with properly formatted dates)
OUTPUT_FILENAME = 'newfile.csv'


INPUT_DATE_FORMATS = ('%m/%d/%Y', '%d-%m-%Y')
OUTPUT_DATE_FORMAT = '%d-%m-%YT%H:%MZ'

with open(INPUT_FILENAME, 'rt') as finput:
    reader = csv.DictReader(finput, delimiter='|')
    with open(OUTPUT_FILENAME, 'wt') as foutput:
        writer = csv.DictWriter(foutput, fieldnames=reader.fieldnames, delimiter='|') # you can change delimiter if you want
        for row in reader: # read each entry one by one
            for header, value in row.items(): # read each field one by one
                for date_format in INPUT_DATE_FORMATS: # try to parse a date
                    try:
                        parsed_date = datetime.strptime(value, date_format)
                        row[header] = parsed_date.strftime(OUTPUT_DATE_FORMAT)
                    except ValueError:
                        pass
            writer.writerow(row)
</code></pre>

python datetime