DataWright Information Services

Consulting and Resources for Excel and Access




Cleaning up dates in text files

You can use the TransferText command in Access to import and
export text files. A common example is writing out CSV files to be
imported into another database system. It’s quick, and you can build
output file names dynamically, but there is one major drawback. If
you export dates they are written out as a timestamp: dates with
trailing zeros (representing the time portion of the date), and many
systems won’t import dates with that format. I came up against this
problem recently, and had to find a way to automate the creation and
cleanup process. With hundreds of files to process, any manual
intervention wasn’t going to be feasible.

The sample database shows the original code and one of several
ways to automate the cleanup.

Using DoCmd.TransferText generates a file with timestamps in the
date fields

If you click the top button on the form you will see a Notepad
file like this:

The output file, showing timestamp dates

The code that generates the file is shown below. There are two
lines of interest. The first generates the file name and path, and the
second writes the file.

Private Sub cmdFirst_Click()
''========================================================================
''Code for writing out a delimited text file.
''Demonstrates trailing zeros in date fields.
''Created by Denis Wright, 2 July 2009
''========================================================================
    Dim strFile As String
    
    'Write out a CSV file to the same directory as the database, date-stamped.
    'The field headers are written out with the file
    strFile = CurrentProject.Path & "Orders_" & Format(Date, "yyyymmdd") & ".csv"
    DoCmd.TransferText acExportDelim, , "tblOrders", strFile, True
    
End Sub

Use the FileSystemObject’s
text features to clean up the file

The second button demonstrates the clean-up method. It adds a bit
more processing to the original, summarised below:

  • The first output file now has a .txt extension. It is still
    comma delimited but we intend to throw it away once the dates
    are cleaned up, so the .csv extension is used for the final
    output.
  • The FileSystemObject has a number of useful features for
    working with text files.

    • OpenTextFile is used to open the text files. If the file
      already exists it is opened; if not, the file is created and
      then opened. When you open a file you can define whether you
      will be reading, writing or appending text. If you write,
      you will completely overwrite the original file; however,
      you may want to output data to a log file, in which case you
      would append instead.
    • ReadLine and WriteLine are used to process text files,
      one line at a time. With ReadLine you can transfer the
      contents of each line into a string variable; once
      processed, that string can then be written out to the other
      text file using WriteLine.
    • The process loops through all of the lines until you get
      to the end of the input file. The AtEndOfStream property is
      used to define the end of the file.

If you press the second button on the form you will get this
output:

The second process results in the desired format

The code is shown below:

Private Sub cmdSecond_Click()
''========================================================================
''Code for writing out a delimited text file, then removing trailing zeros
'' from date fields.
''Uses the FileSystemObject
''Created by Denis Wright, 2 July 2009
''========================================================================
    Dim oFSO As Object
    Dim oFS_TXT, oFS_CSV
    Dim strFile As String
    Dim strOutFile As String
    Dim strText As String
    Const STR_REPLACE = " 0:00:00"
    Const ForReading = 1
    Const ForWriting = 2
    'If you want to continually add to a log file you can use:
    'Const ForAppending = 8
    
    'Write out a TXT file to the same directory as the database, date-stamped
    'The field headers are written out with the file
    strFile = CurrentProject.Path & "Orders_" & Format(Date, "yyyymmdd") & ".txt"
    DoCmd.TransferText acExportDelim, , "tblOrders", strFile, True
    
    'Change the file string to use a CSV extension; this will be used for writing
    'the final file
    strOutFile = Replace(strFile, ".txt", ".csv")
    
    'Now, process that text file to remove the trailing zeros in the date format
    'We use the Scripting runtime to read the txt file into a variable, line by line
    'Processed lines are then written out to the final CSV file
    'When processing is complete, the original txt file is deleted
    Set oFSO = CreateObject("Scripting.FileSystemObject")
    Set oFS_TXT = oFSO.OpenTextFile(strFile, ForReading)
    Set oFS_CSV = oFSO.OpenTextFile(strOutFile, ForWriting)
    
    Do Until oFS_TXT.AtEndOfStream
        strText = oFS_TXT.ReadLine
        strText = Replace(strText, STR_REPLACE, "")
        oFS_CSV.WriteLine (strText)
    Loop
    
    'clean up references and delete the text file
    oFS_TXT.Close
    oFS_CSV.Close
    Set oFSO = Nothing
    Set oFS_TXT = Nothing
    Set oFS_CSV = Nothing
    Kill strFile
    
End Sub

Mission accomplished; the new CSV file has the desired
formatting, and imports into the corporate system. This database has
been tested in Access 2003 and 2007.

Extras in the download

On the menu form are two checkboxes that let you view the code,
or the CSV file output, once the routine has run.